SQL Server Performance Forum – Threads Archive
ADO and Parameter Sniffing
Hi, I’m having a problem where my stored procedure is running well under query analyzer, but not under ADO. I’ve read the articles on parameter sniffing, and I suspect this is causing the problem. However, ADO seems to be substituting INPUT parameters with the actual text, as opposed to using a parameter call. ie, the sql profiler code from my ado call looks like:****************************
declare @P1 int
set @P1=60543
declare @P2 int
set @P2=200
declare @P3 int
set @P3=1
exec Srch_DoSearch 20955, ‘Jan 1 1900 12:00:00:000AM’, ‘May 4 2003 8:54:42:000AM’, 5, 200, @P1 output, @P2 output, @P3 output
select @P1, @P2, @P3
**************************** which causes an inefficient plan to be generated (making it up to 100x slower!). However, this works fine: ****************************
declare @P1 int
set @P1=60543
declare @P2 int
set @P2=200
declare @P3 int
set @P3=1
declare @srchId int
set @SrchId = 20955 declare @dtStartDate datetime
declare @dtEndDate datetime
declare @minCount int
declare @MaxCount int set @MinCount = 5
set @MaxCount = 200 exec Srch_DoSearch @SrchId, @dtStartDate,@dtEndDate,@MinCount,@MaxCount, @P1 output, @P2 output, @P3 output
select @P1, @P2, @P3
****************************
I can’t figure out how to get INPUT parameters to use some sort of substitution to avoid parameter sniffing problems. Any help? Thx,
Doug
Here is a link I’ve saved on this very subject:
http://groups.google.com/groups?hl=en&[email protected]&rnum=3 /Argyle
Hi Argyle, Thanks for your response. I’m aware of this article, it’s just that it doesn’t really help me. My stored procedure doesn’t use default parameters. The problem is that ADO is sending in the parameters directly, instead of substituting in a variable, when they are input parameters. Thus, the generated plan for ANY ADO call is bad. I need to get ADO to send in the same types of parameters as input as they do for output, or find an alternate way to call the stored procedure. Thanks,
Doug
Sending parameters only would mean you would rely on index statistics and nothing else. To accomplish that assign your stored procedure parameters to local variables inside the procedure and then use the local variables in your query.<br /><br />And make sure to have the statisitcs up to date with sp_updatestats [<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />]<br /><br />/Argyle
Thanks for your help. I figured this out (sort of). It turns out my theory about the parameters was wrong, and it had nothing to do with that. I’m using inline table-valued functions. In one proc, there are 2 that are nested – one inside the other. The query plan for inline tv functions usually works well. However, in this case, it randomly estimates the table size from the table valued function wrong. Ie, somehow it had 69 million rows instead of 1400, and then generated a lazy spool/table spool in tempdb with 69 million rows! I moved the code into a locally declared table variable and then it worked fine. Eventually, I’ll probably put it back into subqueries, but I like the encapsulation provided by UDFs too much. However, this is probably the millionth time I’ve been burned by them.
Glad it worked out. It’s a problematic area to get the query plans you want for every query scenario. Especially with nested SPs and UDFs that you use. I run into problems with this sometimes too. /Argyle
]]>