Differential performance between Query Analyser and remote .asp

Question

Post reply

Differential performance between Query Analyser and remote .asp

Gary Bourne-389258

Old Hand

Points: 360
More actions
September 26, 2007 at 7:41 am

#118726

One of our developers has created a .asp web page that calls a procedure in a SQL Server database and returns the results to a grid. This generally takes about 20secs to return.
I have run the same procedure with the same inputs through a local Query Analyser session and it is taking about 3 minutes to run.
I find this a little confusing, has anyone experienced anything like this?
In addition to this question the web page in question is occasionally sending back the following error:
Microsoft OLE DB Provider for SQL Server error '80040e31'
Timeout expired
Once this happens the only way we have found to fix the problem is to alter the timeout period on the SQL Server. The fact of altering by a few seconds up or down appears to get it going again.
Any thoughts?
Thanks for you time.
Gary.
Kindest Regards,
Mr B

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Hi There,

Just a thought.

Where are you ASP pages sitting in relation to your Database?

Also is you QA in a different location to your Database?

I know that when we access our SQL DB's from some sites it's slower because we are physically in a very different local.

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Gary Bourne-389258 Old Hand Points: 360 More actions · Answer 2

The .asp page is located on the same server as the database but takes only 20secs when run locally on the server and from a users PC.

The Query Analyser is located on the same server as the database.

Kindest Regards,

Mr B

mengus Ten Centuries Points: 1187 More actions · Answer 3

Gary,

More frequent than not, when running a stored procedure call from Query Analyzer with the same inputs as a Web Page, the execution time is usually relatively smaller. This is because for Web Pages, the page still has to render the data into the grid for the rows returned.

Please provide sample snippets of the stored procedure and the inputs. From my previous experiences, if in fact the stored procedures are identical, then, it would be a network issue, in which you may be connecting to the database at a slower bandwidth, compare to the web server.

Regards,

Wameng Vang

MCTS

Gary Bourne-389258 Old Hand Points: 360 More actions · Answer 4

"parameter sniffing at work"

I saw this within another thread 2 below this one. Defined local variables as discussed and it seems to have dome the trick! The execution time of the Stored Procedure is down to 20 secs which is the same as the web page.

Kindest Regards,

Mr B

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 5

Another thing that I have seen make a big difference is the connection options e.g. ANSI_NULLS. Make sure query analyzer is using the same options as the connection from your ASP page.