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

Answer 1

 

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.

Answer 2

 

Acturally, the execution is hung or takes extremely long time when the SP is being executed in ADO.NET.

Answer 3

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?

 

 

 

Answer 4

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
--- 

Answer 5

What does your SP do? does it fetch a large volume of data?

Answer 6

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.

 

 

 

Answer 7

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

Answer 8

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

 

 

 

 

Answer 9

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

 

Answer 10

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?

 

Answer 11

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.

Answer 12

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?

 

 

Answer 13

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. 

 

Answer 14

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)

Answer 15

thanks! clearing the cache seems to have speeded up the SP from 3 mins to about 15 seconds from ADO.NET

 

Answer 16

 

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...?

Answer 17

 

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.

Answer 18

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!!!!

 

 

 

Answer 19

Thank you so much jon, you are amazing. If i were a woman I would have your babies about now!!!

 

Cheers

 

Si

 

Answer 20

Woh. It worked for me. Thanks so much Jon