c# - pull records from database without timeout

Question

Hi folks, i have a sql query with multiple joins & it pulls data from a database for processing. This is supposed to be running on some scheduled basis. So day 1, it might pull 500, day 2 say 400.

Now, if the service is stopped for some reason & the data not processed, then on day3 there could be as much as 1000 records to process. This is causing timeout on the sql query.

How best to handle this situation without causing timeout & gradually reducing workload to process?

TIA

Answer 1

Look at your query, it could be that its not optimised, put indexes where appropriate. Without seeing your table structure, query, cant help any more.

Answer 2

create a batch process. Allow no more than say n records to process. Lets say n = 100 ... then make your select queries to only select top 100 until there are no more records to process.

YourCommandObject.CommandTimeout = 0;

This will allow your command to run forever.

Note this could cause database locks and other issues. If you use the batch process I described above and determine the longest running query you can set your connect timeout to what is necessary.

Answer 3

One practical solution would be to increase the command timeout:

var com = yourConnection.CreateCommand();
com.CommandTimeout = 0;
...

The CommandTimeout property is the time (in seconds) to wait for the command to execute. The default is 30 seconds. A value of 0 indicates no limit, and should be avoided in a CommandTimeout because an attempt to execute a command will wait indefinitely.