Very strange - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Very strange

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-13 : 21:32:39

I have a stored procedure that executes in 15 seconds. If I cut the code and execute in QA, it executes in under 1 second.

I'm highly suspicious of the compilation, but there's no way to view the compiled code or execution plan of a store procedure is there?

Any ideas why appreciated.

here's the code.

Sam
-------------------------------------
CREATE PROCEDURE dbo.FE_ContestCenter
@ClientID INT = NULL ,
@CourseID INT = NULL
AS
SET NOCOUNT ON

DECLARE @MyCourseID INT
IF @CourseID is null
select @CourseID = (select top 1 CourseID FROM Courses)

IF @ClientID IS NULL
SELECT @ClientID = (SELECT TOP 1 ClientID FROM Clients)
SET @MyCourseID = @CourseID

SELECT CenterName ,
Sum(CASE WHEN CRecordFinish IS NOT NULL THEN 1 ELSE 0 END) as UCount, -- Total Users Completed

Sum (CASE WHEN CRecordFinish IS NOT NULL THEN 1 ELSE 0 END)*100.0/ -- Percentage Users Completed
(CASE WHEN Count(*)=0 THEN 1 ELSE Count(*) END) as [Percentage Complete],

Count(*) AS [Total Users], -- Total Users

MAX(CRecordFinish) as MaxFinishDate, -- Date of last completed User

MAX(IsNull(BranchCount,0)) as BranchCount -- Count of Branches for this Center

FROM Users U
LEFT OUTER JOIN CourseRecords CR ON CR.UserID = U.UserID and CR.CourseID = @MyCourseID
AND CR.CRecordFinish > (SELECT CourseStartDate FROM Courses WHERE CourseID = @MyCourseID)
inner join Centers C on U.CenterID=C.Centerid
LEFT OUTER JOIN (
SELECT CenterID, Count(*) as BranchCount FROM Branches Group by CenterID -- Count of Branches by Center
) BC on BC.CenterID=C.CenterID
WHERE U.Inactive=0 AND U.ClientID = @ClientID
GROUP BY C.CenterName
ORDER BY [Percentage Complete] DESC, MaxFinishDate

SET NOCOUNT OFF
GO

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-03-13 : 23:19:46

Does the dramatic difference happen in all permutations of @CourseID and @ClientID being null and not null?

I've always been under the impression that you can view the execution plan of a stored procedure from QA; just enable viewing the plan (ctrl-k) and then run the SP. Unless I'm wrong, that should at least help you find out which part(s) of the query are being handled differently.

Cheers
-b

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-13 : 23:56:21

OK, CTRL-K on EXEC DBO.FE_ContestCenter

does give an execution plan. Thanks.

Yes, it is different in the procedure. Very different. Same results, but incredibly slow.

Any way to post the execution plans?

Sam

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-03-14 : 00:04:00

You can do

set showplan_all on
go

...before you run the SP, and you'll get an even more detailed plan than the graphical one. At that point, you'll have to hope that someone else chimes in, because that gets over my head pretty quick -- I know to look for "table scan", and that's about it

Cheers
-b

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-14 : 00:20:07

Well, I resorted to an old trick...

Rewrite the code to do the same thing and test.

It works perfectly now, I moved a nested query out of the left outer join as follows:

This:
FROM Users U
LEFT OUTER JOIN CourseRecords CR ON CR.UserID = U.UserID and CR.CourseID = @MyCourseID
AND CR.CRecordFinish > (SELECT CourseStartDate FROM Courses WHERE CourseID = @MyCourseID)
inner join Centers C on U.CenterID=C.Centerid
LEFT OUTER JOIN (
SELECT CenterID, Count(*) as BranchCount FROM Branches Group by CenterID -- Count of Branches by Center
) BC on BC.CenterID=C.CenterID
WHERE U.Inactive=0 AND U.ClientID = @ClientID

BECAME THIS:
FROM Users U
inner join Centers C on U.CenterID=C.Centerid
LEFT OUTER JOIN Courserecords CR ON CR.UserID = U.UserID
LEFT OUTER JOIN (
SELECT CenterID, Count(*) as BranchCount FROM Branches Group by CenterID -- Count of Branches by Center
) BC on BC.CenterID=C.CenterID
WHERE U.Inactive=0 AND U.ClientID = @ClientID AND (CR.CourseID IS NULL OR (CR.CourseID = @CourseID and CR.CRecordFinish>@CourseStartDate))

I liked the first query better as it logically generated fewer rows. The revised query builds more rows in a simpler manner, then filters them in the last WHERE.

But it's all magic anyway.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources