In my work for a major traffic site (15 million pages a month with peaks of 1 million a day) we found a way to reduce the execution time of a heap of stored procedures. A large proportion of sprocs have an OUTER JOIN to the Files table and do a convert(varchar like this
left outer join Files on Announcements.URL = 'fileid=' +
convert(varchar,Files.FileID)
which is not only an expensive function but ti also makes it impossible to Index the Files table.
So what we did was to create a view of the Files table and then do the OUTER JOIN to that view. This allowed us to remove the convert(varchar operation and also index the Files view.
the resulting sproc for GetAnnouncements is below:
CREATE procedure [dbo].[GetAnnouncements]
@ModuleId int
as
select
Announcements.ItemId,
Announcements.ModuleID,
Announcements.CreatedByUser,
Announcements.CreatedDate,
Announcements.Title,
'URL' = case when Files.FileName is null then Announcements.URL else Files.Folder + Files.FileName end,
Announcements.ExpireDate,
Announcements.Description,
Announcements.ViewOrder,
UrlTracking.TrackClicks,
UrlTracking.NewWindow
from Announcements
left outer join UrlTracking on Announcements.URL = UrlTracking.Url and UrlTracking.ModuleId = @ModuleID
left outer join view_File as Files (NOEXPAND) on Announcements.URL = Files.FileIdnTxt
--left outer join UrlTracking on Announcements.URL = UrlTracking.Url and UrlTracking.ModuleId = @ModuleID
--left outer join Files on Announcements.URL = 'fileid=' +
--convert(varchar,Files.FileID)
where Announcements.ModuleId = @ModuleId
and (Announcements.ExpireDate > GetDate() or Announcements.ExpireDate is null)
order by Announcements.ViewOrder asc, Announcements.CreatedDate desc