Execution plan Optimization - 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)

Execution plan Optimization

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-30 : 09:52:55

I was up late last night staring at execution plans. This query was the center of my attention:

SELECT	Branchname,
	CompletedUsers AS ucount,
	CompletedUsers * 100.0 / CASE WHEN TotalUsers = 0 THEN 1 ELSE TotalUsers END AS [Percentage Complete],
	TotalUsers AS [Total Users],
	LastCompletion AS MaxFinishDate
	FROM	dbo.Branches B
	INNER JOIN 	(
		SELECT	BranchID, Count(*) AS TotalUsers, Count(CRecordFinish) AS CompletedUsers, MAX(CRecordFinish) AS LastCompletion
			FROM	dbo.Users U
			LEFT OUTER JOIN (
					SELECT	UserID, MIN(CRecordFinish) AS CRecordFinish -- Earliest Finish time for this user on this course credit group
						FROM	dbo.CourseRecords
						WHERE	CRecordFinish > (SELECT CourseStartDate FROM Courses WHERE CourseID = @CourseID)
						AND	CourseID IN (SELECT CourseID FROM dbo.UDF_CreditGroup(@CourseID))
						AND	ClientID = @ClientID
						GROUP BY UserID
					) B ON B.UserID = U.UserID
			WHERE	U.ClientID = @ClientID 
			AND	U.Inactive = 1
			AND	U.CenterID = @CenterID
			GROUP BY BranchID
			) CR ON CR.BranchID = B.BranchID
	WHERE	B.CenterID = (SELECT CenterID FROM dbo.Centers WHERE Centername = @Centername)
	ORDER BY [Percentage Complete] DESC, MaxFinishDate -- Order by Percentage complete, then date of completion

It took 12 seconds for this query to execute.

By changing the element in red to the scalar value below, it executes in under 1 second. This surprised me because the element in red evaluates to a scalar. In other words, I replaced a scalar expression with a scalar variable.

SELECT	Branchname,
	CompletedUsers AS ucount,
	CompletedUsers * 100.0 / CASE WHEN TotalUsers = 0 THEN 1 ELSE TotalUsers END AS [Percentage Complete],
	TotalUsers AS [Total Users],
	LastCompletion AS MaxFinishDate
	FROM	dbo.Branches B
	INNER JOIN 	(
		SELECT	BranchID, Count(*) AS TotalUsers, Count(CRecordFinish) AS CompletedUsers, MAX(CRecordFinish) AS LastCompletion
			FROM	dbo.Users U
			LEFT OUTER JOIN (
					SELECT	UserID, MIN(CRecordFinish) AS CRecordFinish -- Earliest Finish time for this user on this course credit group
						FROM	dbo.CourseRecords
						WHERE	CRecordFinish > (SELECT CourseStartDate FROM Courses WHERE CourseID = @CourseID)
						AND	CourseID IN (SELECT CourseID FROM dbo.UDF_CreditGroup(@CourseID))
						AND	ClientID = @ClientID
						GROUP BY UserID
					) B ON B.UserID = U.UserID
			WHERE	U.ClientID = @ClientID 
			AND	U.Inactive = 1
			AND	U.CenterID = @CenterID
			GROUP BY BranchID
			) CR ON CR.BranchID = B.BranchID
	WHERE	B.CenterID = @CenterID
	ORDER BY [Percentage Complete] DESC, MaxFinishDate -- Order by Percentage complete, then date of completion

Does anyone see a reason why the two queries should perform differently?

Sam

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-07-30 : 09:59:35

Is there any indexing on "Centername" ? I'm guessing not.

Damian

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-30 : 10:09:32

I'm wondering why you would do the subselect in the first place if you already have center id...

And I'm guessing there is a unique (clustered?) Index on Centers, because if it returns more than 1 value you get an error

SELECT * FROM Orders WHERE OrderID = (SELECT OrderId FROM [Order Details])

Returns:

erver: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Also, are you sure it runs in 1 second...it's not still in cache is it?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-30 : 10:15:47

Pretty cool sh_t btw sam...what's the udf do?

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-30 : 11:18:53

Hi Damian. I saw you beginning to post when I turned in sometime after 1AM last night.

The column "Centername" is indexed, but it shouldn't matter. The value @Centername doesn't change in the query, so

(SELECT CenterID FROM Centers WHERE Centername = @Centername)

returns a scalar value "CenterID". Even if the optimizer does a Table Scan, it shouldn't affect the performance. The Center's table holds only about 30 rows...

The UDF returns a set of courses (about 3 rows) that are related. It provides a mechanism to find all users who have taken any of a set of courses.

The whole query creates a recordset of Total Users and Completed Users by department (Branch) for a higher level organization (Center). In this case, the higher level organization is @Centername.

Anyone else have an experience about the optimizer making execution plan mistakes that could be corrected by shifting code around?

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-30 : 11:24:23

Brett,

I didn't realize why you were asking about the use of the subselect when I had @CenterID.

The answer is that I didn't have @CenterID. I had the subselect in both positions in the query where @CenterID is now. Calculating @CenterID before the query, and substituting it into the second location resulted in the quick execution time of under 1 second. As long as I had it calculated, I used it in the first subselect too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources