June 1, 2009 at 8:53 am
I am probably missing something obvious here, but I'm curious as to why this query would time out:
From .NET, using the enterprise libraries I am executing the SqlStringCommand:
;WITH PagingCTE(Row_ID, SomeID, Name, HitCount) as
( SELECT ROW_NUMBER() OVER (ORDER BY T1.HitCount DESC ) AS [Row_ID], T1.SomeID , T1.Name, T1.HitCount
from SomeTable1 T1
inner join SomeTable2 T2 on T1.FieldID = T2.FieldID
where T2.IsDeleted=0 and T1.TypeID=@TypeID
group by T1.SomeID, T1.SomeName, T1.HitCount )
select distinct P.SomeID, P.Name, @TypeID as TypeID, P.HitCount, P.Row_ID
from PagingCTE P where Row_ID >=@firstrow and Row_ID <= @lastrow order by Row_ID
when I run it adding the Int32 parameters @TypeID = 4, @firstrow=1, @lastrow=50
but if I build the string dynamically inserting these integer values directly, the query runs in 1 sec.
i.e.:
;WITH PagingCTE(Row_ID, SomeID, Name, HitCount) as
( SELECT ROW_NUMBER() OVER (ORDER BY T1.HitCount DESC ) AS [Row_ID], T1.SomeID , T1.Name, T1.HitCount
from SomeView1 T1
inner join SomeTable2 T2 on T1.FieldID = T2.FieldID
where T2.IsDeleted=0 and T1.TypeID=4
group by T1.SomeID, T1.SomeName, T1.HitCount )
select distinct P.SomeID, P.Name, 4 as TypeID, P.HitCount, P.Row_ID
from PagingCTE P where Row_ID >=1 and Row_ID <= 50 order by Row_ID
I have used parameterized queries with CTEs many times before and haven't had it time out, so is there something bizarre about this query that I'm not seeing?
(FYI the group by is there because SomeView1 is a view with a lot more fields than I am querying here and there can be hundreds or more records for the same SomeID, Name, HitCount combination...) There is an index on both T2.FieldID and the underlying table field corresponding to T1.FieldID. Since the non-parameterized query is so fast I don't think it's the structure but why would parameterizing the query choke it so hard?
I should also mention that the only difference between the two scenarios is the presence of the parameters - I execute the query using the same .NET code both ways with the same connection parameters etc.
Thanks in advance.
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
June 1, 2009 at 8:58 am
it could be parameter sniffing, search around here and you should find some good answers to it
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 1, 2009 at 11:52 am
Hi
Here you can find a great article by Gail about parameter sniffing:
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
Greets
Flo
June 1, 2009 at 12:06 pm
Thanks, folks. I did find that article when I took Christopher's advice to look up parameter sniffing (albeit after about 20 other links that just said "look up parameter sniffing 😀 ) and thus I learned something new today.
I think I am going to have to use the "recompile" option with many of my dynamic queries because it is very likely there will be huge disparities in rows returned given the nature of our data. The other thing I can do is not parameterize the numeric data and only parameterize the text data (in the example I provided all 3 parameters were integers so putting them inline wasn't problematic, but if I had included one of the user-editable text fields then parameterization is definitely a must.)
Thanks again.
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply