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=' +
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]
'URL' = case when Files.FileName is null then Announcements.URL else Files.Folder + Files.FileName end,
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=' +
where Announcements.ModuleId = @ModuleId
and (Announcements.ExpireDate > GetDate() or Announcements.ExpireDate is null)
order by Announcements.ViewOrder asc, Announcements.CreatedDate desc