April 7, 2009 at 1:50 pm
Below is the query I am bashing my head at. I am sure I have been staring at it for too long and it is something stupid that I already know. If I use the variable declared at the top the query runs for eternity. If i pass the same value directly in the where clause it is immediate in returning results.
declare @Date datetime
set @Date = '2009-04-06 05:45:00.000'
select tm.SEQUENCE
, tm.[DATE OPEN]
, tm.[CLOSED ON]
, td.[PROBLEM #]
, td.PDATE
, td.[TIME SPENT]
, a.CODE as Activity
, g.CODE as AppTeam
, p.CODE as Assoc --TicketDetail.*
from
MagicTSD._SMDBA_._TELMASTE_ tm with (nolock)
inner join MagicTSD._SMDBA_._TELDETAI_ td with (nolock)
on tm.SEQUENCE = td.[PROBLEM #]
inner join MagicTSD._SMDBA_._ACTIONS_ a with (nolock)
on a.SEQUENCE = td.[ACTION]
inner join MagicTSD._SMDBA_._PERSONNEL_ p with (nolock)
on p.SEQUENCE = td.PERSON
inner join MagicTSD._SMDBA_._GROUPDET_ gd with (nolock)
on gd._MEMBER_ = p.SEQUENCE
inner join MagicTSD._SMDBA_._GROUPS_ g with (NoLock)
on gd._GROUP_ = g.SEQUENCE
where td.PDATE >= @Date--'2009-04-06 00:00:00.000'
and td.PDATE < dateadd(d,1,@Date)--dateadd(d,1,'2009-04-06 05:45:00.000')
and tm.[DATE OPEN] >= @Date--'2009-04-06 05:45:00.000'
and tm.[DATE OPEN] < dateadd(d,1,@Date)--dateadd(d,1,'2009-04-06 05:45:00.000')
and a.CODE in ('CALLED_CUST','CALLED_LEFT_VM', 'AREP_DUR')
and p.JOB_FUNCTION is not null
and upper(p.JOB_FUNCTION) <> 'LOAD LEVEL NO'
and a.[_INACTIVE_:] = 0
and td.[_INACTIVE_:] = 0
and tm.[_INACTIVE_:] = 0
and p.[_INACTIVE_:] = 0
and g.[_INACTIVE_:] = 0
and gd.[_INACTIVE_:] = 0
PDATE and [DATE OPEN] are both datetime
Let me know what you can. I have become blurry eyed at this.
April 7, 2009 at 1:55 pm
What are your indexes on these two tables?
MagicTSD._SMDBA_._TELMASTE_ tm
MagicTSD._SMDBA_._TELDETAI_ td
April 7, 2009 at 1:58 pm
I suspect "parameter sniffing" is your likely culprit. There are good articles on that if you look up that phrase in Google/Live/Yahoo/Dogpile/whatever.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 2:02 pm
Lynn, unfortunately i have no control over indexes. The showplan is the same for both. there are covering indexes.
GSquared, Thanks. I will look into that.
April 7, 2009 at 2:05 pm
Robert Hermsen (4/7/2009)
Lynn, unfortunately i have no control over indexes. The showplan is the same for both. there are covering indexes.GSquared, Thanks. I will look into that.
That's what I was wondering about. Gus is correct, it could be parameter sniffing so that leads to the next question, is this query actually a part of a stored procedure?
April 7, 2009 at 2:08 pm
nope. Just a plain ol' everyday query in SSMS.
Trying to wrap up development of a larger query set to use for SSRS and this is on of the queries that will be part of it. Has not left the SSMS environment.
April 12, 2009 at 12:37 am
All the answers are in this Books Online entry:
http://technet.microsoft.com/en-us/library/ms175933(SQL.90).aspx
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 12, 2009 at 2:52 am
Hi Paul
Thanks for the link, great article!
Greets
Flo
April 12, 2009 at 3:42 am
Hey Flo,
Yeah - it's one of those articles I keep going back to: for some reason I can never quite remember all the details verbatim 😉 😀
/Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 12, 2009 at 11:33 am
I know what you mean... 😛
Article is bookmarked! 😎
Greets
Flo
April 13, 2009 at 6:47 am
Thanks for the link. I will dig into that.
January 28, 2010 at 2:50 pm
THANK YOU!!! I ran into a similar issue where I created a stored procedure from a well perfroming query (2 seconds), which took 40 seconds as a stored procedure. What happened in the stored proc is I used cast statements on the passed parameter. When I removed them and used the parameters directly, the stored procedure ran at 2 seconds! Woohoo! Thank you!!!
January 29, 2010 at 7:59 am
I will add to the thread that there are MANY cases where you can have widely varying inputs and dynamic sql is by far the best approach to ensure optimal query plans no matter what your input parameter values.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 29, 2010 at 8:12 am
I prefer to use dynamic SQL, and that has always resulted in good performance. However, in this application I chose to encapsulate the query into a stored procedure for the sake of simplicity on the application end. The query is quite complicated and long, and I thought it would be easier to maintain as an sp.
I still see a performance loss even though I've corrected the constant folding problem.
January 29, 2010 at 8:17 am
Voitek (1/29/2010)
I prefer to use dynamic SQL, and that has always resulted in good performance. However, in this application I chose to encapsulate the query into a stored procedure for the sake of simplicity on the application end. The query is quite complicated and long, and I thought it would be easier to maintain as an sp.I still see a performance loss even though I've corrected the constant folding problem.
I have refactored many of my clients sprocs (especially things like search ones) with dynamic sql so it can certainly be done inside the sproc. Amazing perf gains too, not just because the optimizer gets explicit values to compare against. Often you can completely eliminate joins in the process as well - at least in search type sprocs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply