Query Time Out Problem

  • There is a table that contains a huge amount of data....

    When i delete or select the entire table i got an error of

    [Microsoft][ODBC SQL Server Driver]Timeout Expired

    Can some one please tell me the reason with the solution.

    Thanks

    Usman

  • Pretty straight-forward. ODBC has a query timeout setting. You've got options. Set the timeout higher. Tune the queries you're running so that they're faster. Verify the indexing strategy is good on the table you're working on. Use truncate instead of delete to remove all the rows from the table. Add a WHERE clause to your query to limit the rows returned.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks for the reply,

    But the problem is why should i set the timeout option in ODBC as i am running the query in Query Analyzer or even in Enterprise Manger.

    And the Query is Simply 

    SELECT * FROM MyTable

    OR

    DELETE FROM MyTable

    And there is a clustered index on the TimeStamp Column of the Table

    Now even if i use the WHERE clause for TimeStamp Column i still get the same error message.

  • Even though you're running through Query Analyzer, I'll bet you're connecting through ODBC instead of through a native connection. Take a look in Control Panel at the ODBC settings. Either up the timeout there, or better still, remove the connection and try it again.

    Regardless of how good a clustered index is (and you'd need to verify that you've got a good one) 'SELECT *' or 'DELETE' without a 'WHERE' clause in either case can be very expensive operations especially against very large tables. If you need to delete all rows from a table, TRUNCATE is a better operation (it's unlogged and therefor much faster).

    Good luck.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • You need to ask find afew points:

    How Big is your table?

    Do you have a clustered index onthe table?

    Is there any other services running (and consuming more memory) on the same server/client at that time?

    Query analyzer too has a timeout setting

    Check under Tools->Options->Connections->Query time_out. If Make you make the number to zero the client will simply wait until the application completes. otherwise you can have a higher number.

     

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply