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.
Stored Procedures takes long time to execute from Web Application
Bing M.
Bill Reiss
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.