SQL Performance Help ! - 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 2008 Forums

SQL Server Administration (2008)

SQL Performance Help !

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2011-03-08 : 09:51:38

Folks:

When I execute the following SQL I get the output in 5 secs.

DECLARE @DT DATETIME
SET @DT = '03/04/2011'

SELECT Value FROM vwAssetData
WHERE ADate = @DT
AND LEFT(SYMBOL,2) = 'TU'
AND HNo = 1

When I execute the same SQL but without passing the Date as variable (hardcoding the date), I get the output in 1 sec.

SELECT Value FROM vwAssetData
WHERE ADate = '03/04/2011'
AND LEFT(SYMBOL,2) = 'TU'
AND HNo = 1

Any Idea what might be the problem? I thought it might be parameter sniffing but I have also disabled parameter sniffing on the server by enabling Trace Flag 4136

Any help on this is appreciated... I really need the 1 sec. output because it makes a difference when the same SQL is executed 1000 times for different symbols.

Thanks !

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-03-08 : 10:22:41

ADate is datatype DATETIME and this column has an index I guess.
'03/04/2011' isn't a DATETIME so a convert happens and the index can't be used.

The way to use the variable @DT DATETIME is better because there is no convert needed in the where clause.

No, you're never too old to Yak'n'Roll if you're too young to die.

sqldba20
Posting Yak Master

183 Posts

Posted - 2011-03-08 : 10:43:46

Yes, there is a Index on ADate. So does this mean there is no alternative?

Thanks !

quote:
Originally posted by webfred

ADate is datatype DATETIME and this column has an index I guess.
'03/04/2011' isn't a DATETIME so a convert happens and the index can't be used.

The way to use the variable @DT DATETIME is better because there is no convert needed in the where clause.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-03-08 : 11:11:21

Alternate try the format 'YYYYMMDD'

For today this would be for example:
WHERE ADate = '20110308'

No, you're never too old to Yak'n'Roll if you're too young to die.

sqldba20
Posting Yak Master

183 Posts

Posted - 2011-03-08 : 11:55:45

No Luck even after changing the format to YYYYMMDD in @DT Variable. It still takes 5 secs. whereas the other with no variable takes 1 sec.

DECLARE @DT DATETIME
SET @DT = '20110304'

SELECT Value FROM vwAssetData
WHERE ADate = @DT
AND LEFT(SYMBOL,2) = 'TU'
AND HNo = 1

quote:
Originally posted by webfred

Alternate try the format 'YYYYMMDD'

For today this would be for example:
WHERE ADate = '20110308'

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2011-03-08 : 13:05:31

For me still it looks like there might be some sort of parameter sniffing happening.

Try this


DECLARE @DT DATETIME
SET @DT = '03/04/2011'

SELECT Value FROM vwAssetData
WHERE ADate = @DT
AND LEFT(SYMBOL,2) = 'TU'
AND HNo = 1
OPTION (OPTIMIZE FOR (@DT = '03/04/2011'))

Also do you have an index on Symbol because if you do have then it wont be used as you are using a function on it.

PBUH

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-08 : 15:15:20

When you use variable in script (not in stored procedure) at the time query optimizer creates execution plan he does not know the value of variable, so it does not use hystogram for ADate column and in your case compiles less optimal execution plan.

Btw. why don't use

symbol like 'TU%'

instead of

left(symbol,2) =  'TU'

?

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources