July 18, 2005 at 6:13 am
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
July 18, 2005 at 7:18 am
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
July 19, 2005 at 1:23 am
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.
July 19, 2005 at 5:30 am
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
July 19, 2005 at 6:10 am
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