SQL Timeouts and Slowness

[darbnacnud]

I'm confused. I get time-outs and significant delays in retrieving data from my SQL database, even if I use Stored Procedures. I believe I have set up indexes correctly to maximize performance, but I still have the problem. I can run a Crystal Report that generates a report on the data and it performs admirably. Why the differece? What am I missing? Any help for this novice will be appreciated.

 

[NoCoolHandle]

More likley than not it is the type of Cursor/Recordset that you are using.

Snapshot and Keyset require you build the entire return in tempdb before sending any data back. Dynamic and Forwardonly will start sending databack as soon as they get it..(No need to build the recordset in tempdb first as they only ever get the next logical row if and when you ask for it..).

Recomendations..
1. Make sure you have a where clause
2. Always use a column list
3. Think about using a top X clause and bring back your results in groups of X
4. Try to find a way for a forward only read only recordset to work..
5. If you must bring back huge recordsets and you want them to display quickly use a dyanaset (they have more overhead in some ways but less in others)



HTH

Rob

 

[SQLSister]

One common cause is the use of cursors on a table with many records. Other causes of timeouts are infinite loops, statistics which haven't been updated, hardware that is not sufficient for the size of your database, deadlocks, blocking, poor query design, poor index design, network bottlenecks, etc. This is a complex subject and will require advanced skills on your part to search for and find the answers to your problems. I recently got a very good book on the this subject which you might want to get a copy of as it will tell you far more than I ever could in a forum such as this.

It's called - SQL Server Query Performance Tuning Distilled by Sajal Dam. Really good stuff in here, I've learned a lot of new things just in the couple of days I've had it. Excellent discussions of how to analyze the execution plan and what counters to look at in system monitor and how to find the slow running queries, etc. It's well worth the money.

Since you seem to have a particular stored procedure you know is giving you trouble, you might post it here and we could take a crack at tuning it. But of course if the problem lies outside the code itself, then it is very difficult for us to find the problem. However, I just fixed the code for one of our web programmers and took the execution time down from well over 30 seconds to under a second so it can be done and looking at the particular stored procedure or query is where most of us start to search for the cause of a problem like this.

 

[PCStorm]

We found differences with Crystal running faster than stored procedures alone EVEN with Crystal running the exact same stored procedures. This was with both Oracle and MSSQL. Something to do with how Crystal is launching it. Although we never fully investigated it we suspected network bottlenecks.