Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> .NET Development >> .NET Framework Data Access and Storage

Stored Procedures takes long time to execute from Web Application

Hi,

We have a got unique problem with one of our Stored Procedure. This stored procedure takes a long time to execute when called from data access layer which is in C#, takes around 5 minutes but takes very less time to execute when executed from query analyzer, takes around 6 seconds. We are using data access block of Enterprise Library 2. All the other stored procedure within the application works fine. It is only this one stored procedure which is behaving very odd. The same stored procedure works fine if we give a short date range (6 months) from the data access layer but takes lot of time to execute when you give a bigger date range (4 years). Also doesn't seem to be a data load or network issue the reason being the 4 years date range query returns only 12 records. I have checked through the profiler it is actually the execution of the query which is taking time but the same query works fine when run through query analyzer. I have used same parameters format used by the C# layer in the query analyzer to execute the query.

Does anyone have any idea on this? Any ray of light will do. Also Is anyone aware how does the execution of SP differs when called from C# that from query analyzer, I assume both of them use the same engine and mechanism. Any inputs on this will also be very helpful.

Thanks in Advance for your help.

Many Thanks.

Kind Regards,

Sharad.

[1673 byte] By [SharadShetty] at [2007-12-24]
«« Order of evaluation of grouped functions?
»» Moving a control on a Canvas at run time
# 1
Do you have index on this date field? If not, it could lead to the table scan and, as a result, to performance impact.
VMazur at 2007-8-31 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 2

Yes there is an index on the date field. I do think it is the problem with the index, since the same SP works fine when we execute it from query analyzer and it is only when you call from C# it is long long time to complete.

Having done further investigation, I have found that it is something to do with tempdb, some how when you execute the SP from query analyzer it tends to get space on tempdb very quickly even if there are no space i.e. space on tempdb gets increased. But when you do the same thing from C# there is contention for space in tempdb and it takes time to get that space. I have modified the SP to use table variable rather than temporary table but then also it tends to use tempdb since the number of records to act on is huge hence it uses tempdb internally.

Anyways thanks for your response.

Does anyone have any idea how does the tempdb allocation differs when you execute from query analyzer and when you execute from C# ? Is it something to do rights and priority given to a particular process Id if two resource are trying to get some resource, which in this case is space in temp db.

Many Thanks,

Sharad.

SharadShetty at 2007-8-31 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 3
were you able to solve this problem?
i am facing a similar one
garfield167 at 2008-1-3 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
.NET Development
.NET Framework Data Access and Storage

.NET Development Hot Topic

.NET Development New Topic

.NET Development

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified