none
Baffled by Sql Timeout RRS feed

  • Question

  •  

    I'm at my wits end with this one. We have a large Customer Care system written using Sql Server 2000 and Asp.Net 2.0. This system is used for entering and tracking enquiries that come in. At this point the table is quite large but nothing I would consider should affect performance - roughly 150k rows.

    Some users have departmental access where I display by default all open enquiries for their department. These are displayed in a Gridview and there are filtering options. You can switch to view closed enquiries, filter by category or member of staff. The results are paged using a special stored procedure to get the first 20 rows depending on the page. i.e. the paging is done in the sp on the db server. Now if I select to view all closed enquiries I quickly get back the first 20 records of a total of 27000 records. No problem. If I select a member of staff it times out. Every single time.

    I have run the stored procedure in sql management studio with the same parameters and in the worst case scenario it finishes in under 2 seconds. I have stepped through the code and verified that the correct values are going into the parameters for the sp. I have even rewritten the code to send the same query in text so I could write the actual query that is being sent to the db to the web page and copy and paste it into management studio to check it. Again it executes in about a second but the web app continues to time out.

    The total for any single member of staff would be a fraction of the 27000 in this example department so I'm baffled. I can't see how this is actually a timeout when the same query runs so quickly in sql management studio.

    Monday, September 10, 2007 10:58 AM

Answers

  • We've had a similar issue a while back.

    The connection settings on your development machine's SQL Query analyzer or SQL Management Studio can be different from what the database defaults are, which your application connection uses.

    Our problem was with the ARITHABORT paramenter being ON with one and OFF with the other, which made a huge difference with the query run times.

     

    Just a possibility.

    Monday, September 10, 2007 1:55 PM

All replies

  • We've had a similar issue a while back.

    The connection settings on your development machine's SQL Query analyzer or SQL Management Studio can be different from what the database defaults are, which your application connection uses.

    Our problem was with the ARITHABORT paramenter being ON with one and OFF with the other, which made a huge difference with the query run times.

     

    Just a possibility.

    Monday, September 10, 2007 1:55 PM
  • Many thanks Bans. Setting ARITHABORT to ON in my query solved the problem. Quite an obscure little setting.

    Tuesday, September 11, 2007 11:17 AM