Ok, I have an SP that is called from a .NET webapp, and it takes 24+seconds, while in Query Analyzer it takes 2 seconds. First there were timeouts if exceeded 30 secs, now down to 24+. So, while it doesn't hit the timeout, it still takes too long.
I already recompiled, rebuilt indexes, remote query and login timeout set to '0', etc., etc. The timeout setting in the web.config file is 120 (2mins) - no blocking, no deadlocks, nothing in the trace, other to show its exactly 30 secs before timeout thru app.
Now, we pointed the production web server to test SQL (same code, db, config parms, SP, service packs, etc, and it returned the data rather quickly w/no timeouts. So, their pinning their accusation on the database, and I'm stuck like a pig in a polk
First, any idea why it takes much longer thru web app, than SQL? We also tried connecting thru access natively, and it too takes 24+secs.
1) How can I prevent timeouts; 2)why does it take much longer, please explain??