|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Slow Stored Procedure when run via ado, fast from query analyzerI've got a stored procedure reading data from the database that runs in about 2 seconds from query analyzer, and takes 50+ seconds run from ADO in asp classic. I'm using SQLOLEDB. I'm totally baffled, any ideas what I could check? I'm getting the 50+ seconds time from Sql Profiler...so I'm assuming that 50+ seconds is the server side execution time and isn't including the time to transport the data from the sqlserver to the webserver... Any ideas? In terms of what is happening in Sql Server Profiler, I'm tracking RPC:Starting and RPC:Completed. The duration value on RPC:Completed is 69780ms, where when it is run from Query Analyzer it's SQL:StmtStarting and SQL:StmtCompleted with a duration of 516ms. I've tried both cursor locations without seeing any difference in the time reported by Profiler. I'm not locking records or maintaing a recordset for more than two lines... [like set rs = adocmd.execute() // aryData = rs.getrows() ] What is so different about how Query Analyzer executes the query that makes it so much faster there? In my company our developers test the queries in query analyzer, so we deployed this stored procedure and now that it's in production we're having performance issues with the asp classic page. Kind Regards Bruce Could be a case of "parameter sniffing". Search for that expression within
this newsgroup. ML Hi
You don't say how you are running the stored procedure from QA or whether the procedure is being recompiled in ADO. You may want to script the trace and see what happens when it is run through QA! You may also want to check what the connection settings are for each environment. Check out the following to improve AD performance: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/ScaleNetChapt12.asp You may also want to make sure that things like owner pre-fixes are being used. If you are passing atypical parameter values, you may want to read up on parameter sniffing in Ken Henderson's "The Guru's Guide to SQL Server Architecture and Internals" ISBN 0-201-70047-6 John Show quote "barooz" <BruceTheNo***@yahoo.com> wrote in message news:1122724300.224361.236220@f14g2000cwb.googlegroups.com... > Greetings folks. > > > I've got a stored procedure reading data from the database that runs in > about 2 seconds from query analyzer, and takes 50+ seconds run from ADO > in asp classic. I'm using SQLOLEDB. I'm totally baffled, any ideas > what I could check? I'm getting the 50+ seconds time from Sql > Profiler...so I'm assuming that 50+ seconds is the server side > execution time and isn't including the time to transport the data from > the sqlserver to the webserver... Any ideas? > > In terms of what is happening in Sql Server Profiler, I'm tracking > RPC:Starting and RPC:Completed. The duration value on RPC:Completed is > 69780ms, where when it is run from Query Analyzer it's SQL:StmtStarting > and SQL:StmtCompleted with a duration of 516ms. > > > I've tried both cursor locations without seeing any difference in the > time reported by Profiler. I'm not locking records or maintaing a > recordset for more than two lines... > [like set rs = adocmd.execute() // aryData = rs.getrows() ] > > What is so different about how Query Analyzer executes the query that > makes it so much faster there? > > In my company our developers test the queries in query analyzer, so we > deployed this stored procedure and now that it's in production we're > having performance issues with the asp classic page. > > Kind Regards > > Bruce >  |
|||||||||||||||||||||||
Other interesting topics