Executing stored proc from DotNet takes very long but in SSMS it is immediate

Question

I have a stored proc on SQL Server 2000 that takes 3 parameters. When I call the stored proc from DotNet using SqlCommand.ExecuteReader () it takes about 28 seconds.

When I run the same query inside SSMS directly it returns immediately.

When I take the query out of the stored proc and run it directly using DotNet it also returns immediately.

These are the results from a SQL Profiler session

SP Inside Dot Net

Duration: 28030
Reads: 2663365
Writes: 0

SP Inside SSMS

Duration: 450
Reads: 23535
Writes: 65

Query directly inside Dot Net

Duration: 360
Reads: 24865
Writes: 57

The following things stand out to me:

The stats for the SSMS and direct query in Dot Net are very similar
The Dot Net SP one does a huge amount of reads but no writes
The other two make very few reads, but a couple of writes

Any help would be appreciated.

Here is a slightly obviscated version of the SP:

I doubt that it is a query plan issue because even if I run it repeatedly from DotNet, I always get the same results.

Here is a version of the SP that's been altered slightly because of IP issues. I hope it still makes sense:

SELECT 
t1.pkiOrderID,
t1.fkiBasketId,
t1.sOriginBasketCode,
t1.dtDateCreated,
t1.sOrderCode,
t1.fkiUserCde,
t1.fkiOrgCde,
t1.sApprovalPerson,
t1.dtDateApproved,
t1.sRequestNo,
t1.dtRequiredDate,
t1.Requestor,
t1.OnBehalfOf,
t1.OrderDesc,
t1.OrderTypeId,
t1.fkiAgentID,
t1.fkiAgentRegionID,
stat.iStatus,
count(oi.pkiOrderItemId) as OrderItems,
count(wf.fkiOrderId) as WorkflowCount,
t1.Currency_Id,
t1.ExchangeRate,
t1.ref_odr_idn,
t2.sOrderCode as ref_odr_cde,
t1.ref_rfq_nbr,
t1.ref_rfs_nbr,
t1.ref_doc_nbr,
t1.ref_rsn,
t1.ref_forip_cde,
t1.ref_fa_nbr,
t1.odr_sub_typ
FROM    tbl1 t1 INNER JOIN 
tbl1Status stat ON
t1.pkiOrderID = stat.fkiOrderID AND
stat.dtDateStatusChanged = (SELECT MAX(stat2.dtDateStatusChanged) 
FROM tbl1Status stat2
WHERE stat2.fkiOrderId = t1.pkiOrderID) LEFT OUTER JOIN 
tbl1Item oi ON
t1.pkiOrderID = oi.fkiOrderId LEFT OUTER JOIN
tbl1Workflows wf ON
t1.pkiOrderID = wf.fkiOrderId LEFT OUTER JOIN 
tbl1 t2 ON 
t1.ref_odr_idn = t2.pkiOrderID
WHERE (t1.fkiUserCde = 'x'
or t1.fkiUserCde in (select fkiUserCde from tbl1 where fkiOrgCde in 
(select sys_org_cde from tbl3 t3 where t3.sys_lnk_org_cde = '123')))
AND ((t1.fkiOrgCde = '123'
and ('123' not in (select sys_org_cde from tbl3 t3) 
or (t1.OrderTypeID <     1 or stat.iStatus IN (2,3,4,5,6,7))))
OR (t1.fkiOrgCde in (select sys_org_cde from tbl3 t3 where     t3.sys_lnk_org_cde = '123')
and t1.OrderTypeID = 1 
and stat.iStatus NOT IN (2,3,4,5,6,7)))           
          AND   t1.OrderTypeID = 2

        GROUP BY
            t1.pkiOrderID,
            t1.fkiBasketId,
            t1.sOriginBasketCode,
            t1.dtDateCreated,
            t1.sOrderCode,
            t1.fkiUserCde,
            t1.fkiOrgCde,
            t1.sApprovalPerson,
            t1.dtDateApproved,
            t1.sRequestNo,
            t1.dtRequiredDate,
            t1.Requestor,
            t1.OnBehalfOf,
            t1.OrderDesc,
            t1.OrderTypeId,
            t1.fkiAgentID,
            t1.fkiAgentRegionID,
            stat.iStatus,
            t1.Currency_Id,
            t1.ExchangeRate,
            t1.ref_odr_idn,
            t2.sOrderCode,
            t1.ref_rfq_nbr,
            t1.ref_rfs_nbr,
            t1.ref_doc_nbr,
            t1.ref_rsn,
            t1.ref_forip_cde,
            t1.ref_fa_nbr,
            t1.odr_sub_typ
        ORDER BY t1.dtDateCreated DESC

Sorry about the formatting. I struggled to get it readable at all on the forum.

Are the two "Text" entries in profiler the same? There should be zero performance difference between the SSMS and SP in DotNet. Are you setting the same params? — DaveShaw, Jul 06 '10 at 14:34
Very odd. Are you passing the same parameters all three times? — Chris Van Opstal, Jul 06 '10 at 14:35
Yip. I actually copied the text from the profiler into SSMS to make sure that it was exactly the same. — Johann Strydom, Jul 06 '10 at 14:36
Are you running it in SSMS AFTER you run it in .Net? If so, sql server probably has the query plan cached and is simply reusing it. Can you post your actual proc here? — NotMe, Jul 06 '10 at 14:41
I doubt it is a query plan thing because I get the same performance from DotNet even if I run it multiple times. I also alwas get the same performance from SSMS. I've uploaded a version of the proc, but as you'll see there's nothing spectacular there. — Johann Strydom, Jul 06 '10 at 14:59
@Johann: Here is more about Parameter Sniffing. http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/ Did you use different parameters when running in your different environments. Edit: Oh, I see that has been asked already. :) — Jacques Bosch, Jul 06 '10 at 16:22

score 27 · Accepted Answer · edited Dec 21 '22 at 11:19

27

Since my comment seemed to provide the correct answer, I decided to move it into a full answer for posterity in the spirit of stackoverflow.

Your problem seems to be caused by SQL Server's Parameter Sniffing. To prevent it, just assign your incoming parameter values to other variables declared right at the top of your SP.

See this nice Article about it

Example:

CREATE PROCEDURE dbo.MyProcedure
(
    @Param1 INT
)
AS

declare @MyParam1 INT
set @MyParam1 = @Param1

SELECT * FROM dbo.MyTable WHERE ColumnName = @MyParam1 

GO

I copied this information from eggheadcafe.com.

Edit: As per Johann Strydom's comment, here is another option: Optimize Parameter Driven Queries with SQL Server OPTIMIZE FOR Hint.

edited Dec 21 '22 at 11:19

Glorfindel

21,988
13
81
109

answered Jul 09 '10 at 14:36

Jacques Bosch

2,165
3
25
36

3

If if could give you a million up votes I would. You are a life saver! – Draco Oct 09 '13 at 09:47
1

Glad it was of help. It's one of those nasty obscure ones. :) – Jacques Bosch Oct 09 '13 at 16:03
1

Since this was posted I discovered a better way to prevent parameter sniffing is to use OPTION (OPTIMIZE FOR) – Johann Strydom Sep 08 '15 at 17:08

score 4 · Answer 2 · answered Jul 06 '10 at 15:17

4

Just recreated the stored proc and that fixed it. Very strange indeed.

answered Jul 06 '10 at 15:17

Johann Strydom

1,482
14
18

1

Not really. Recreating it will have dropped the bad execution plan in the cache. Chances are you were a victim of parameter sniffing. – Martin Smith Jul 06 '10 at 15:34
1

Perhaps this was your problem? http://www.eggheadcafe.com/tutorials/aspnet/353cb16c-3cde-44dd-a264-750c1ce4e423/sql-server-query-analyzer.aspx – Jacques Bosch Jul 06 '10 at 15:44
@Johann If/when the issue happens again I suggest following the advice in this answer to get both execution plans then compare and contrast http://stackoverflow.com/questions/3070653/very-different-execution-times-of-sql-query-in-c-and-sql-server-management-studi/3071727#3071727 – Martin Smith Jul 06 '10 at 15:46
@Jacques Your link seems to have addressed the problem. When I recreated the SP at the client it didn't help like it did locally, but assigning the parameters to local variables did seem to fix the problem in production as well. – Johann Strydom Jul 07 '10 at 13:52
1

I'm still confused as to why DotNet and SSMS used different execution plans even when I passed the same parameters though. – Johann Strydom Jul 07 '10 at 14:55
@Johann The default `SET OPTION` s are not the same between DotNet and Management Studio (I forget which one(s) - maybe `ARITHABORT` but you can easily see in Profiler) As a result they can not use each other's plans. – Martin Smith Jul 07 '10 at 22:10
Which idiot at microsoft invented this ? I had the same problem, nothing worked until I recreated the stored proc from my dotnet client. – GuidoG Sep 04 '15 at 13:11
Recreating the stored proc will only help for awhile and then the problem may reappear. Depends on what's going on in the data. – Johann Strydom Sep 08 '15 at 17:05

Executing stored proc from DotNet takes very long but in SSMS it is immediate

2 Answers2

Linked