Sproc runs faster in QA than Web App
-
Help!!!
I've run into a problem with one database, and two sprocs. Each SQL Server has 20 to 30 databases which are exact replica's of one another...simply storing data for different customers.
The problem database is mid size (about 4 GB/OLTP Environment), when I run the sprocs (reports) from the front end the application times out. However when I run the sprocs from QA they return data withing a matter of seconds
...no other databases are having trouble, running the sprocs in question, from the front end (they run in less than a few seconds).
When I run the sprocs in QA, I'm using the exact statement passed to SQL (collected in Profiler)...when I recompile the sprocs (Drop and Create) the front end seems to speed up for some time, however this doesn't last forever.
The problem just started occuring in the past few days...no changes have been made to the server or sprocs, or the front end from what I'm told. What could cause this issue and why would happen to only one database...when the identical sproc is running for 200 other databases??
Please help!!
-
Sounds like a permissions problem on the front end, can you verify that you can run other queries against this table using the credentials supplied by that particular application?
Check the login and passwords in your connection strings AND the permissions that the user has against the table/views/procs etc.
-
I don't believe it's a permissions issue as everyone using the web app log's into SQL with the same credentials. (They don't actually use a SQL/NT Login...as far as the customer knows they are all logging in with unique user names...not the same SQL account)
Also, when I recompile the sproc, the sproc will return results to the front end...but after some time (Several hours) the time out issues will begin again.
I read somewhere that this may be a tuning issue and possibly QA and the Front End are not using the same connection properties. However, after running the estimated execution plan, the sproc is really not so bad...perhaps I could place a index on the temp table created...
On the other hand, this sproc runs in a few seconds on one of our largest databases (10GB), returning results to the front end very quickly. Why would the sproc run quickly from the front end for this database...an not another?
-
I use the same SQL credentials that are passed in the connection string...
Our environment consists of a database for every customer, they're individual user credentials (for each store) are entered into a table in our database...which determines what kind of access they have on the front end. They're access is not determined by SQL Security settings. Every connection string from the Web App uses the same generic SQL Login (which has close to administrator priviliges).
If the permissions in the connection string were the issue, everyone would be affected...not just one customer database (group of users belonging to one store).
-
First, determine where the time is spent - on the db server or not (network, client, etc.). Was your QA trial run from the same box as the Web app?
If the times for the actual db queries (excluding network, etc.) are still found to vary widely, you are likely encountering either a out-of-date-statistics or a recompilation issue. Your mention of a temp table makes me suspect recompilation. Compilation requires serialization, so can be a bottleneck. Also make sure the sp call is owner-qualified and case-sensitive matched in the client code. Failure to do so can cause query-plan lookups to take longer due to lack of a hash match.
-
I'll give that a shot and let you know what I find
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply