none
Stored Procedure running slow in ADO.NET

    Question

  • We have a stored procedure which is running fine on a SQL server 2000 from Query Analyzer. However, when we try to execute the same stored procedure from ADO.NET in an executable, the execution is hung or takes extremely long. Does anyone have any ideas or suggestions  about how it could happen and how to fix. thanks

    Wednesday, January 09, 2008 7:50 PM

Answers

  •  

    I've found the solution, finally. Query Analyser works with ARITHABORT set to ON and ADO.Net runs with ARITHABORT set to OFF. This causes 2 query plans in the procedure cache. Depending on the parameters in the query these plans can be different, and therefore giving very different query performance.

    Clearing the procedure cache means that next time you run the query it will calculate a plan that is best for the parameters that you're passing in.

    So when you get this problem its best to use the WITH RECOMPILE hint to compile the execution plan every time you run the procedure.

    Friday, February 15, 2008 5:06 PM

All replies

  •  

    Make sure the exe is coming to the point of execution and also successfully executing - this you can do by putting some log lines before and after the SP execution with date & time. I feel the SP is fine.

     

    Btw what is the time its taking to execute in Query Analyzer.

    Wednesday, January 09, 2008 7:57 PM
  •  

    Acturally, the execution is hung or takes extremely long time when the SP is being executed in ADO.NET.
    Wednesday, January 09, 2008 8:16 PM
  • Did you find a solution to this?  I am have a similar problem with a stored procedue with returns a recordset of about 10000 rows.  It takes 2 seconds on QA but over 3 minutes when using with ADO.NET.  any ideas?

     

     

     

    Friday, January 11, 2008 3:33 PM
  • Use the profiler to see if any plumbing is added to the execution and to see how long the duration of the single components  (compiling, executing etc.) lasts.

    Jens K. Suessmeyer


    ---
    http://www.sqlserver2005.de
    --- 

    Friday, January 11, 2008 10:27 PM
    Moderator
  • What does your SP do? does it fetch a large volume of data?

    Saturday, January 12, 2008 2:57 PM
  • Hi,

     

    My Stored Procedure is just a simple select statement from a complex view.  It returns 22275 rows.  My profile results are as follows:

     

    with ADO.NET

     

    CPU = 100422

    Reads = 12263320

    Writes = 2

    Duration = 101822

     

    with Query Analyzer

     

    CPU = 2937

    Reads = 85299

    Writes = 0

    Duration = 5118

     

    As you can see its signifcally slower when used in my application.

     

     

     

    Monday, January 14, 2008 10:01 AM
  • Then you might show us your calling code as well as the procedure code, as there might be a different execution plan according to the procedure code and way how you call it.

     

     

    Jens K. Suessmeyer

    Monday, January 14, 2008 10:50 AM
    Moderator
  • Hi,

     

    My calling code is

     

    Code Block

    Dim connString As String = m_connectionString

    Dim spName As String = "spGetQualityPivotData"

    'Create parameter for userid

    Dim parUserID As New SqlParameter("@UserID", SqlDbType.Int)

    parUserID.Direction = ParameterDirection.Input

    parUserID.Value = userID

    'Create parameter for user security

    Dim parUserSecurity As New SqlParameter("@UserSecurity", SqlDbType.Int)

    parUserSecurity.Direction = ParameterDirection.Input

    parUserSecurity.Value = userSecurity

    'Create parameter for buying agency

    Dim parBuyingAgency As New SqlParameter("@BuyingAgencyID", SqlDbType.NVarChar)

    parBuyingAgency.Direction = ParameterDirection.Input

    parBuyingAgency.Value = buyingAgency

    'Create parameter for buying agency

    Dim parBuyingAgencyEx As New SqlParameter("@BuyingAgencyIDExclude", SqlDbType.NVarChar)

    parBuyingAgencyEx.Direction = ParameterDirection.Input

    parBuyingAgencyEx.Value = buyingAgencyExclude

    'Create parameter for year

    Dim parYear As New SqlParameter("@SpendYear", SqlDbType.Int)

    parYear.Direction = ParameterDirection.Input

    parYear.Value = spendYear

    Dim parameters As SqlParameter() = {parUserID, parUserSecurity, parBuyingAgency, parBuyingAgencyEx, parYear}

    Dim ds As DataSet

    Try

    SqlHelperParameterCache.CacheParameterSet(connString, spName, parameters)

    ds = SqlHelper.ExecuteDataset(connString, CommandType.StoredProcedure, spName, parameters)

    ds.Tables(0).TableName = "PivotData"

    Catch ex As Exception

    Dim msg As String

    msg = "Error getting report data from the database."

    Throw New DALException(msg, ex, Nothing)

    Finally

    End Try

     

     

    and my SP is

     

    Code Block

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

     

     

     

     

    ALTER PROCEDURE [dbo].[spGetQualityPivotData] (@UserID int,

    @UserSecurity int,

    @BuyingAgencyID varchar(30),

    @BuyingAgencyIDExclude varchar(30),

    @SpendYear int)

    AS

    --If UserSecurity = 1 then get all spend records

    IF @UserSecurity = 1

    BEGIN

     

    SELECT DmBuyingAgencyName AS BuyingAgency, ClientName AS Client, ClientShortname, ProductName AS Product, AudienceName AS Audience, StationName AS Station, SalesHouseName AS SalesHouse, ChannelName AS Channel, MonthShortName AS [Month], QuarterNumber AS [Quarter], SpotYear AS [Year], TypeName AS SpecialType, TotalEquivImpressions AS TotalImpressions, CentreImps, EndImps, BlankPiBImps, FirstPiBImps, LastPiBImps, SecondPiBImps, ThirdPiBImps, PenPiBImps, DayPtOneImps, DayPtTwoImps, DayPtThreeImps, DayPtFourImps, DayPtFiveImps, DayPtSixImps, DayPtSevenImps, DayPtEightImps

    FROM vwQualityAllDetails

    WHERE DmBuyingAgencyID Not In (Select TheString From fnCharListToTable(@BuyingAgencyIDExclude,','))

    AND SpotYear = @SpendYear

     

    END

    ELSE IF (@UserSecurity = 2) -- Only get BuyingAgency clients and top level rest

    BEGIN

    SELECT DmBuyingAgencyName AS BuyingAgency, ClientName AS Client, ClientShortname, ProductName AS Product, AudienceName AS Audience, StationName AS Station, SalesHouseName AS SalesHouse, ChannelName AS Channel, MonthShortName AS [Month], QuarterNumber AS [Quarter], SpotYear AS [Year], TypeName AS SpecialType, TotalEquivImpressions AS TotalImpressions, CentreImps, EndImps, BlankPiBImps, FirstPiBImps, LastPiBImps, SecondPiBImps, ThirdPiBImps, PenPiBImps, DayPtOneImps, DayPtTwoImps, DayPtThreeImps, DayPtFourImps, DayPtFiveImps, DayPtSixImps, DayPtSevenImps, DayPtEightImps

    FROM vwQualityAllDetails

    WHERE DmBuyingAgencyID In (Select TheString From fnCharListToTable(@BuyingAgencyID,','))

    AND SpotYear = @SpendYear

    UNION ALL

    SELECT DmBuyingAgencyName AS BuyingAgency, ' ALL ' + DmBuyingAgencyName AS Client, ' ALL ' + DmBuyingAgencyName AS ClientShortname, ' ALL ' + DmBuyingAgencyName AS Product, AudienceName AS Audience, StationName AS Station, SalesHouseName AS SalesHouse, ChannelName AS Channel, MonthShortName AS [Month], QuarterNumber AS [Quarter], SpotYear AS [Year], TypeName AS SpecialType, TotalEquivImpressions AS TotalImpressions, CentreImps, EndImps, BlankPiBImps, FirstPiBImps, LastPiBImps, SecondPiBImps, ThirdPiBImps, PenPiBImps, DayPtOneImps, DayPtTwoImps, DayPtThreeImps, DayPtFourImps, DayPtFiveImps, DayPtSixImps, DayPtSevenImps, DayPtEightImps

    FROM vwQualityAllDetails

    WHERE DmBuyingAgencyID NOT IN (Select TheString From fnCharListToTable(@BuyingAgencyID,','))

    AND DmBuyingAgencyID NOT IN (Select TheString From fnCharListToTable(@BuyingAgencyIDExclude,','))

    AND SpotYear = @SpendYear

    END

    ELSE IF (@UserSecurity = 3) OR (@UserSecurity = 4) -- Only get Buying Agency clients

    BEGIN

    SELECT DmBuyingAgencyName AS BuyingAgency, ClientName AS Client, ClientShortname, ProductName AS Product, AudienceName AS Audience, StationName AS Station, SalesHouseName AS SalesHouse, ChannelName AS Channel, MonthShortName AS [Month], QuarterNumber AS [Quarter], SpotYear AS [Year], TypeName AS SpecialType, TotalEquivImpressions AS TotalImpressions, CentreImps, EndImps, BlankPiBImps, FirstPiBImps, LastPiBImps, SecondPiBImps, ThirdPiBImps, PenPiBImps, DayPtOneImps, DayPtTwoImps, DayPtThreeImps, DayPtFourImps, DayPtFiveImps, DayPtSixImps, DayPtSevenImps, DayPtEightImps

    FROM vwQualityAllDetails

    WHERE DmBuyingAgencyID In (Select TheString From fnCharListToTable(@BuyingAgencyID,','))

    AND SpotYear = @SpendYear

    END

     

     

     

     

    Monday, January 14, 2008 11:50 AM
  • I'm calling the procedure with the same parameters in both the application and Query Analyzer

     

    that is ...   '1', '1', '3', '6,10', '2007'

     

    thanks

     

    Monday, January 14, 2008 11:53 AM
  • I think this is to do with how SQLOLEDB executes its plan different from when its executed in QA. I can get the execution plan for the query when ran in QA. Its difficult to post here but if anyone is geninuely interested in helping me you can send me your email address and I can send it to you. I cant however get the execution plan when the SP is executed from my application so I cant compare. My SP contains a very complex view pulling in data from many tables from different databases. Could this be the case of slow execution time over ADO.NET?

     

    Tuesday, January 15, 2008 10:11 AM
  • Yes, I think heavy compolexity of the SP is the reason for its slow exec in .net. Since ADO.NET has to go through .Net CLR domain to SQL server domain. MSIL code conversion to its binary might be taking time.
    However you can try to build SQL CLR object for that SP instead of making normal ado.net uses. It will surely be faster.

    Tuesday, January 15, 2008 10:47 AM
  • So you are saying even though the joins are performed in a VIEW there are still performace issues.  I could understand if I performed all the joins and cross joins between tables within my SP, but I thought using a VIEW to capture this information in a "table" the performance issues are resolved.  In QA, the results are what I would expect, but does ADO.NET handle the VIEW in a different way or something?

     

     

    Tuesday, January 15, 2008 4:23 PM
  • Funny thing, we were experiencing virtually the same issues (sp called from ado.net application executes much slower than executing from query analyser), however we are running mssql 2005 sp2.  We found that clearing the plan cache (dbcc freeproccache) resolved the issue.  Any ideas on how to clear a single plan from plan cache without clearing the entire buffer?  We do know which procedure is causing the issue and would rather clean the plan cache of this particular sp rather than dump every plan. 

     

    Wednesday, January 16, 2008 6:36 PM
  • I think Blaine is right. So it does mean that SQL Server processes SP faster (since it caches the compiled SP instead of compile it again). It also mean that ADO.Net is not taking much time compare to SQL server in processingn SP (since it does not include caching)


    Thursday, January 17, 2008 5:50 AM
  • thanks! clearing the cache seems to have speeded up the SP from 3 mins to about 15 seconds from ADO.NET

     

    Thursday, January 17, 2008 5:32 PM
  •  

    I've been getting the very same problems that you've described. Clearing the procedure cache has appeared to fix the problem a couple of times but not always...

     

    Although I don't understand why clearing the procedure cache would solve the problem. Surely it will use the same cached query plan for the stored procedure whether you call it in Query Analyser or through ADO.Net. If it doesn't I'd be interested to know what it does instead and why.

     

    The thing that really gets me is that the number of reads can be up to 1000 times more through ADO.Net than Query Analyser to run the same procedure with the same parameters. The query plan in Query Analyser looks fine, all the effort is spent doing index seeks, so no long scans or bookmark lookups. Which makes me think that the excution plan through ADO.Net is different for some reason. Is there a way of look at the execution plan of a procedure through the SQL Profiler? I'm using Sql 2000.

     

    Any clues anyone...?

    Friday, February 15, 2008 11:23 AM
  •  

    I've found the solution, finally. Query Analyser works with ARITHABORT set to ON and ADO.Net runs with ARITHABORT set to OFF. This causes 2 query plans in the procedure cache. Depending on the parameters in the query these plans can be different, and therefore giving very different query performance.

    Clearing the procedure cache means that next time you run the query it will calculate a plan that is best for the parameters that you're passing in.

    So when you get this problem its best to use the WITH RECOMPILE hint to compile the execution plan every time you run the procedure.

    Friday, February 15, 2008 5:06 PM
  • Jon;

     

    Where did you dig this up???  This is great information and I really appreciate the fact that you posted it in this thread.  It is extremely helpful.

     

    Thank-you, thank-you, thank-you!!!!

     

     

     

    Friday, February 15, 2008 7:20 PM
  • Thank you so much jon, you are amazing. If i were a woman I would have your babies about now!!!

     

    Cheers

     

    Si

     

    Monday, February 18, 2008 10:08 AM
  • Woh. It worked for me. Thanks so much Jon
    Friday, September 23, 2011 7:12 PM
  • Jon, I don't know how you found out about that ARITHABORT, but you're amazing :-)

    Thank you.

    Wednesday, June 25, 2014 3:57 PM