I've discovered that the SP GetDesktopModulesByPortal is taking the most time (average 25 seconds). The SQL is: SELECT DISTINCT DM.* FROM dbo.DesktopModules DM LEFT OUTER JOIN dbo.PortalDesktopModules PDM on DM.DesktopModuleId = PDM.DesktopModuleId WHERE IsPremium = 0 OR (PortalId = @PortalId AND PortalDesktopModuleId IS NOT Null) ORDER BY FriendlyName But I don't know where it's executed (and why) and what I could do to improve the performance.

We have been working more on the SProc. This is not efficient SQL, which shows itself when you have a lot of portals and modules.

Outer Joins with a select on not NOT NULL will generate a carthesian product or all row combinations. This very inefficient if you have a lot of modules and portals. We have modified the SProc.

The original one:

ALTER PROCEDURE [dbo].[GetDesktopModulesByPortal]
	@PortalId int
AS
	SELECT DISTINCT 
		DM.*
	FROM dbo.DesktopModules DM
		LEFT OUTER JOIN dbo.PortalDesktopModules PDM on DM.DesktopModuleId = PDM.DesktopModuleId
	WHERE  IsPremium = 0 
		OR (PortalId = @PortalId AND PortalDesktopModuleId IS NOT Null)
	ORDER BY FriendlyName

The modified one:

ALTER PROCEDURE [dbo].[GetDesktopModulesByPortal] @PortalId int
AS 
    SELECT DISTINCT
            DM.*
    FROM    dbo.DesktopModules DM
    WHERE   ( IsPremium = 0 )
            OR ( DesktopModuleID IN ( SELECT    DesktopModuleID
                                      FROM      dbo.PortalDesktopModules PDM
                                      WHERE     PDM.PortalId = @PortalId ) )
    ORDER BY FriendlyName

This SProc performs as needed (still the site does not run OK, but that might be other problems)

---

Regards,
Ernst Peter



XCESS: DNN/Evoq specialist, zie www.xcess.nl

Events 7.0.1 - Released
Reports 6.1.0 - Released

We made a number of performance improvements in 5.4.  The new 5.4.1 will be released tomorrow barring any unforseen issues.  One of the biggest performance issues we saw in 5.4 was caused by the change to the Human Friendly URL format.  The older friendly URL format is much more performant.  We are looking at making some additional performance enhancements in the next major release.

---

Joe Brinkman
DNN Corp.