Query Analyzer vs. ASP Web Application | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Query Analyzer vs. ASP Web Application

I have a web application that uses ASP, VB COM and SQL Server 2000. When I run a specific stored procedure with the ASP calling application, the procedure executes in 5 seconds, excellent time considering there are 21 joins. When I run the procedure with the same parameters from Query Analyzer it takes 2 minutes. Anyone run into this kind of a problem before? I have reindexed the tables and updated the statistics along with freeing up the stored procedure cache. I’m not complaining, as long as the application is running fine, I’m happy, but I am curious…
"How do you expect to beat me when I am forever?"
Are you using the same kind of data input? Another crude question… Check if there are two stored procedures by the same name but different user name in the database. The procedure created by the user same as who is executing will have priority. Gaurav
By referring to Query execution plan in QA and capturing trace using PROFILER while from ASP page. _________
Satya SKJ
The actual performance differeces between different data access methodologies might not be very clear to me but I know that the Query Analyzer uses ODBC (and some associated connection-specific settings). Some loud thinking below: -Is your ASP script using ODBC? OLEDB + ADO?
-Any connection-specific settings within QA that are not in your ASP?
-Is there even a remote chance that the IUSR_MACHINE account has anything to do with the difference?
-Have you tried looking at the performance statistics for other stored procedures you are running in you ASPs? Tracing both scenarios (ASP vs. QA) in the Profiler might bring out a few differences for this specific stored procedure. Nathan H.O.
The ASP page uses OLEDB+ADO. I will look at the execution plans an capture it via the Profiler for the ASP page. Only one stored procedure, I double checked. We are not running under IUSR_MACHINE, we are using NT authentication. Data input is the same, I simply wrote out the call the ASP page was making. Thanks for the suggestions, we’ll see how it goes.
"How do you expect to beat me when I am forever?"
]]>