While running a SQL Profiler trace against my DotNetNuke database, I noticed a huge CPU cost from the GetSchedule command. My sites were loading incredibly slow and I was on a mission to find out why. This may be fixed in the latest version, but I'm on 4.3 right now. The LEFT JOIN on the ScheduleHistory table was taking the majority of the query's performance. That one proc was single-handedly bringing SQL to its knees and putting my server's CPU usage at 100%. I changed the stored procedure to the query below and I've seen a dramatic increase in performance.
PROCEDURE [dbo].[GetSchedule]varchar(150)S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement, S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, (SELECT TOP 1 NextStart FROM ScheduleHistory S1 WHERE S1.ScheduleID = S.ScheduleID ORDER BY S1.NextStart DESC) as NextStart, S.Servers
FROM
Schedule S
WHERE
(@Server IS NULL or S.Servers LIKE ',%' + @Server + '%,' or S.Servers IS NULL)
GROUP
BY S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement, S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, S.Servers
@Server
AS
SELECT
ALTER