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

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.

  • How to convert empty spaces into null values, using SQL Server?
  • Sql Server 2005 - Insert if not exists
  • How to get efficient Sql Server deadlock handling in C# with ADO?
  • SSIS: Script task to write recordset to file
  • MVC5 ASP.net Identity 2.0 user management and deploy from localDB to IIS server 8
  • Date Conversion Issue MS Access to SQL Server
  • 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.

  • How to Insert more than 1000 record using SqlBulkCopy
  • LINQ fails to create database
  • What is the reason of “Transaction context in use by another session”
  • An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
  • SQL Server Consistent Column Encryption
  • How to Connect to SQL Server using LINQ to SQL?
  • 2 Solutions collect form web for “Executing stored proc from DotNet takes very long but in SSMS it is immediate”

    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.

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.