Query 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

Transact-SQL (2000)

Query Plan Optimization

Author

Topic

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2004-03-03 : 17:18:28

Hey all... I've been gone quite awhile and am now in need of a little assistance. I have a select with 5 tables one of which, T1, has a little over 3 million rows. The following query (actual field and table names changed to protect the innocent) runs for about 20 minutes.


DECLARE @Start_Date datetime
DECLARE @End_Date datetime
SET @Start_Date = '2/16/2004'
SET @End_Date = '2/29/2004'

SELECT	T1.Field1,
	T2.Field2
FROM
	Table1 AS T1
	INNER JOIN
	Table2 AS T2
		ON (	T2.[ID] = T1.[ID])
	INNER JOIN 
	Table3 AS T3
		ON (	T3.[ID] = T2.[ID] AND
			T3.Field1 = @Input1 AND
			T3.Field2 = @Input2
		   )
	INNER JOIN
	Table4 AS T4
		ON (	T4.[ID] = T1.[ID] AND
			T4.Field1 = @Input3
		   )
	INNER JOIN
	T5 AS T5
		ON (	T5.[ID] = T3.[ID2])
WHERE 	T1.DateField >= @Start_Date AND
	T1.DateField <= @End_Date

If I change the where clause to this...


WHERE 	T1.DateField >= '2/16/2004' AND
	T1.DateField <= '2/29/2004 11:59:59 PM'

... it runs in about 20 seconds.

The two selects generate completely different query plans and I don't understand why. The plan for the first version performs all of the joins and then applies the date filter to the entire record set. This causes SQL Server to have to process about 250,000 rows. The second version of the query with the hard coded dates applies the date filter right away so it only has to look at about 4,500 rows.

Any thoughts or comments on this behavior would be greatly appreciated. There is a clustered index on an ID field in T1 and a non clustered index on T1.DateField. Please let me know if you need any further details.

Thanks!

Jeff Banschbach, MCDBA

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2004-03-03 : 21:22:16

As usual, I just had to tell someone I couldn't figure it out before I could figure it out. I added the WITH RECOMPILE clause to the stored procedure that this select resides in and am now getting the desired query plan and response time for every run. Thanks for reading!

Jeff Banschbach, MCDBA

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-03-04 : 10:08:34

Have a look at other topics discussing the merit/demerits of WITH RECOMPILE...including
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=14368
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25743

WITH RECOMPILE might be better utilised as a 'occasional job' to recompile 'key procedures'
The cost of the WITH RECOMPILE may be too high if executed for every running of a SP.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-04 : 12:24:41

You should use sp_recompile, it will schedule a recompilation when the proc is run the next time.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources