Query once fast once slow - 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 once fast once slow

Author

Topic

N0KIA
Starting Member

1 Post

Posted - 2008-07-14 : 03:14:14

Hi there.

I've two Statements. The first example runs fast, the second runs slow.
Is anyone of you able to explain why?


SELECT     convert(varchar(20),[TAT Werktage]) as [TAT Werktage], SUM(Menge) AS Anzahl
FROM         v_bla_bla_bla
WHERE     (datum >= CONVERT(DATETIME, '2007-01-01', 102) AND datum < CONVERT(DATETIME, '2007-01-13', 
                      102))  AND ([Versand Nummer] IS NOT NULL) and ([TAT Werktage] <= 9)
GROUP BY [TAT Werktage]

--------------------------------------------------------------------

declare 	@start varchar(20), 
	@ende varchar(20)

set @start = '2007-01-01'
set @ende = '2007-01-13'



SELECT     convert(varchar(20),[TAT Werktage]) as [TAT Werktage], SUM(Menge) AS Anzahl
FROM         v_bla_bla_bla
WHERE     (datum >= CONVERT(DATETIME, @start, 102) AND datum < CONVERT(DATETIME, @ende, 
                      102))  AND ([Versand Nummer] IS NOT NULL) and ([TAT Werktage] <= 9)
GROUP BY [TAT Werktage]

In the first example the date is queried via static text.
In the second example the date is queried by two variables.
A solution would be to create the sql text dynamically and execute the whole string -> exec (@sqlstring). You have to fight with quotes (') then.
But my question is, why the first version (static text) is that much more fast.
The field 'datum' is indexed of course.

Thanks for your time + patience.
Kind regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 03:27:41

Yes, it's called Parameter Sniffing.

declare	@start datetime,
		@ende datetime

select	@start = '2007-01-01',
		@ende = '2007-01-13'

SELECT		[TAT Werktage],
			SUM(Menge) AS Anzahl
FROM		v_bla_bla_bla
WHERE		Datum >= @start
			AND Datum < @ende
			AND [Versand Nummer] IS NOT NULL
			and [TAT Werktage] <= 9
GROUP BY	[TAT Werktage]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources