SQL timeouts

What the 'eck's the problem...?

[giggle]

Hi

Over the past month or so automatic processes I wrote to download data from our SQL database have started timing out almost everytime. It's really frustrating and causing us all sorts of problems.

I've tried everything that my little brain can think of and spoken to everyone concerned with our setup. Nobody has any suggestions.

As a last resort, I was wondering if the phone line that I use to connect to our ADSL provider could have a problem with it and that might be causing problems with our connection and in turn might be responsible for our timeouts?

I'm sorry if this idea is technologically ridiculous but I have nowhere else to point my finger.

Thanks

Mick

[woop01]

Do you have good indexes setup? I only ask because you didn't mention that specifically. It's also the #1 thing for improving the performance of queries for our site.

Are you using stored procedures?

Also, try putting one of the worst queries into Query Analyzer and click Ctrl-L. What are the steps it says it takes to process the query?

[giggle]

Thanks Woop.

I believe that the indexes are generally ok, that is to say that any fields mentioned in the select statements are indexed.

I'm aware of stored procedures, but the selects are part of SQL statements in my VisualBasic code and I'm not sure how to execute a stored procedure from within VB - I will investigate that, thanks.

Are you saying that using stored procedures is more efficient than a simple SQL select statement?

Thanks for the reply.

Mick

[lovethecoast]

If you're timing out with a SQL query, and the SQL Server isn't under heavy load, putting it into a stored procedure more than likely won't help.

You need to optimize the query, or limit the number of rows being returned. Put that query the the optimizer and see where the bottlenecks are.

S

[woop01]

I've always been told that putting things in stored procedures will help with the server load issues to an extent but not on an individual query.

I'm not a dba by trade but ended up becoming one by necessity. I've been where you are regarding timeouts many times. Here are some more things that you might want to look at...

(forgive me if you've done them, I just wish somebody would have told me all of this about two years ago)

-Use "with(nolock)" in your select statements. For example "select from widgets with(nolock) where...".
-Only select the colums of information you need. If you can get it down to colums that are all contained in one index, it really improves things a lot.
-I don't know how exactly your connection is done but moving from cursors really helped us. For us, this meant changing our connections from "rsInfo.OPEN(sql, [connectionstring], 3, 3)" to "rsInfo.OPEN(sql, [connectionstring], 0, 1)".

Also if you could run one of the problem queries in QA and show the basics of the execution plan, I'm sure somebody can help you out a lot more. There are many different things that can cause timeouts and that's the first place to look when trying to figure out which on it is.