Timeout expired

Question

Timeout expired

clucasi

SSCrazy

Points: 2113
More actions
June 4, 2010 at 1:02 am

#145165

I am running a stored procedure using cursors ( I know it s not the best way but the rows have be done sequentially) to update, insert and delete records in 4 tables 2 of these tables have over a 1000000 records, there are 187998 records it has manipulate.
After about 8 hours (just have i left work for the day. Typical!!) a error appears as shown below
Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
it has no problem with 91188 rows.
Please help

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

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

Abhijit More SSCertifiable Points: 6897 More actions · Answer 1

The good way of manupulating millions/ billion records is to manipulate it in batch say batch of 1000 records / 200 records. Also avoid using the cursor, as it holds the lock on the table. Well it all depends which type of cursor you are using. but to be a good practice aviod using the cursor. making the transaction as short as possible.

Abhijit - http://abhijitmore.wordpress.com

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Luca, why not break the 187998 rows into chunks of say 10000 and then carry out your processing ?? AS u have rightly said, CURSORs are troublesome, try using set-based techniques..

clucasi SSCrazy Points: 2113 More actions · Answer 3

Thanks for your replies , but I am still not sure on how I am going to tackle this problem, when you mention run in batches of 1000 I am not sure how to do this, do you mean running the stored procedure in batches of 1000 records at a time( I am relatively new to writing sql server 2005). Isn't there some kind of timeout I could set, I estimated that the query is going to take about 18 hours.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

clucasi (6/4/2010)
Thanks for your replies , but I am still not sure on how I am going to tackle this problem, when you mention run in batches of 1000 I am not sure how to do this, do you mean running the stored procedure in batches of 1000 records at a time( I am relatively new to writing sql server 2005). Isn't there some kind of timeout I could set, I estimated that the query is going to take about 18 hours.

this link can help you to understand the "batch process"

http://www.sqlservercentral.com/articles/Transacions/69132/

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)