Venexus DotNetNuke Blog - Performance Issue with DotNetNuke Stored Procedure GetTab

Performance Issue with DotNetNuke Stored Procedure GetTab by Jeff Smith

While working with Microsoft concerning the DNN SQLDataProvider and 64-Bit SQL Server 2005 Issue, it was suggested that there was a performance issue with GetTab. While this does not help the issue we are having, it did help with performance...

"There are some implicit converts happening even though you have explicit converts in the statements"

Current GetTab SQL:

select TabID,
TabOrder,
Tabs.PortalID,
TabName,
IsVisible,
ParentId,
[Level],
DisableLink,
Title,
Description,
KeyWords,
IsDeleted,
'Url'= case when F2.FileName is null then Tabs.Url else F2.Folder + F2.FileName end,
SkinSrc,
ContainerSrc,
TabPath,
StartDate,
EndDate,
'IconFile' = case when Files.FileName is null then Tabs.IconFile else Files.Folder + Files.FileName end,
'HasChildren' = case when exists (select 1 from Tabs T2 where T2.ParentId = Tabs.TabId) then 'true' else 'false' end,
RefreshInterval,
PageHeadText
from Tabs
left outer join Files on Tabs.IconFile = 'fileid=' + convert(varchar,Files.FileID)
left outer join Files F2 on Tabs.Url = 'fileid=' + convert(varchar,F2.FileID)
where TabId = @TabId

The FIX:

select TabID,
TabOrder,
Tabs.PortalID,
TabName,
IsVisible,
ParentId,
[Level],
DisableLink,
Title,
Description,
KeyWords,
IsDeleted,
'Url'= case when Files.FileName is null then Tabs.Url else files.Folder + Files.FileName end,
SkinSrc,
ContainerSrc,
TabPath,
StartDate,
EndDate,
'IconFile' = case when Files.FileName is null then Tabs.IconFile else Files.Folder + Files.FileName end,
'HasChildren' = case when exists (select 1 from Tabs T2 where T2.ParentId = Tabs.TabId) then 'true' else 'false' end,
RefreshInterval,
PageHeadText
from Tabs
left outer join Files on Tabs.IconFile = 'fileid=' + convert(nvarchar,Files.FileID)
and Tabs.Url = 'fileid=' + convert(nvarchar,Files.FileID)
where TabId = @TabId