Following on from this thread www.dotnetnuke.com/Community/Forums/t...
I have another problem, this time with the vw_portals which is using up a huge amount of resource on my SQL Server.
I am running DNN v 4.9.4 and have a very large DNN application incorporating over 300 child portals.
Please see the note below from my DBA - can anyone help?
As requested i’ve had a quick look and I think the slow aspect of the code is in the view “vw_Portals” where it looks up the Logofile and BackGroundFile data.
Probably because instead of storing the FileID ” in the Portals table as a number it is being stored as “FileID=16036 where as in the File table it is “16036” hence the code below is required to concatenate the text “fileid=” and destroying the possibility of any indexes being used.
CREATE VIEW [dbo].[vw_Portals]
AS
SELECT
PortalID,
PortalName,
CASE WHEN LEFT(LOWER(LogoFile), 6) = 'fileid'
THEN
(SELECT Folder + FileName
FROM dbo.Files
WHERE 'fileid=' + convert(varchar,dbo.Files.FileID) = LogoFile
)
ELSE
LogoFile
END
AS LogoFile,
FooterText,
ExpiryDate,
UserRegistration,
BannerAdvertising,
AdministratorId,
Currency,
HostFee,
HostSpace,
PageQuota,
UserQuota,
AdministratorRoleId,
RegisteredRoleId,
Description,
KeyWords,
CASE WHEN LEFT(LOWER(BackgroundFile), 6) = 'fileid'
THEN
(SELECT Folder + FileName
FROM dbo.Files
WHERE 'fileid=' + convert(varchar,dbo.Files.FileID) = BackgroundFile
)
ELSE
BackgroundFile
END