October 17, 2002 at 7:13 am
I have a store procedure when called in Query Analyzer like this:
sp_RM_beveragereport_dept '10/07/02', '10/13/02', 0, 'bill brown', 1
Runs in less than a min.
When called by a .NET application it runs in 25 mins.
I used SQL Profiler and pasted the sp call into analyzer and it also took 25 mins to run, it is called like this:
exec sp_executesql N'sp_rm_beveragereport_dept ''10/7/2002'', ''10/13/2002'', 0, ''Ahmad Al-Masri'', 1'
Any help is greatly appreciated.
Scott
October 17, 2002 at 6:25 pm
How are you calling the stored procedure in .net. It looks like you are not using parameters, which could be an issue with compilation.
What is the SQL of the report?
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 17, 2002 at 11:00 pm
How many rows, bytes per row is returned?
October 18, 2002 at 8:43 am
Well two things.....
1. If that stored proc is slow in the query analyzer as well you can stop looking at .NET........
2. Not smart to execute an already compiled stored procedure with SP_Ececutesql. A direct call would be faster.
But you shuld focus on why it takes so long for SQL to retrieve the answers.... I saw in teh example that you use different parameters. I assume it is just as slow if you execute it with the same parameters.
If so i am very curious what is going on in your query........
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply