Answer async sql calls as they finish

Question

Background:

I have a service that requests db for historical results in packages. Packages are based on start time and end time. Data between all batches is mutually exclusive, therefore, for a given set of batches, they can be performed in any order. Some parties may take longer than others. If any batch is not executed, the whole process is interrupted / stopped and an error is logged. When returning data to the client, data from all batches must be combined.

Problem:

Return the data to the client as quickly as possible.

Initial Solutions:

Initially, I performed batches in order synchronously. This did not exploit the fact that the data is mutually exclusive. After initial use, it was discovered that this download method takes too much time. After some debugging, I found that the main reason for the slowness was sql query execution time. So, the next solution was to try to execute each batch asynchronously using BeginExecuteReader() and EndExecuteReader() . After an asynchronous call of all parties, the service will wait in a while loop and poll every 300 ms to check if any of the requests is completed. If it were, then it would be read.

 int batchCount = 0, resultCount = 0; List<AsyncDataResult> asyncCalls = new List<AsyncDataResult>(); while (true) { if (asyncCalls.Count == 0) { break; } if ((asyncCalls[resultCount]).AsyncResult.IsCompleted) { ReadAsyncResultsFromDb(asyncCalls[resultCount]); asyncCalls.RemoveAt(resultCount); } resultCount++; if (resultCount >= asyncCalls.Count) { resultCount = 0; Thread.Sleep(300); } } 

The above approach has reduced the loading time of large data sets, but for very small data sets (but in many batches), polling actually adds to the loading delay.

Question:

• How to execute sql asynchronously but not poll?
• Start reading each batch immediately after its completion?

Update: Sorry, but I forgot to add the part where I need to return in the same method that calls asynchronous calls. The reason for this is because the dataset that I need to fill in is passed as a parameter to this method. Using IAsyncCallback for a novice reader will require me to change the whole class. I was hoping that I would not have to do this.

Answer 1

There is not enough information to suggest the way you should go, but given what you could do, it would be a parallel task library and Task<T> .

Downloading fun. However, do not go crazy with this, you can easily end up with your multi-threaded multi-threaded effort slower than your synchronized batch.

If we say that T, connected to db, threw a request at it, returned the datareader and said that there were 8 requests in the packet.

You set tasks from 0 to 7, start a timer for a timeout, turn off everything. Upon completion, you save the readerr and set the bit of your flag based on the task identifier. When it gets to 255, raise the OnBatchComplete event, copy your readers and pass them to the merge task. The timeout is turned off first, proceed accordingly. If there is an error in the task, return some suitable reason to it, call the caller, possibly killing the queries that are still in progress.

I don’t know how your combined process works, but if it can be organized like this, say that when requests 1 and 3 are ready, you can do an intermediate process or if it is in some logical order, as soon as all requests are ready for reading, you could start reading in simple classes, and then throw each of them into the task of the combine ....

It's not fair, I don’t get such funny things ....

Answer 2

Why vote? Each asynchronous operation that you run returns an IAsyncResult with WaitHandle . Use WaitAny() and the system will tell you:

 /// <summary> /// Do something useful with a completed query /// </summary> /// <param name="result"></param> /// <returns> /// true if the query failed; /// false if the query was successful /// </returns> private static bool DoSomethingUseful( IAsyncResult result ) { throw new NotImplementedException() ; } static void Main( string[] args ) { List<IAsyncResult> batch = SpawnBatch() ; bool errorOccurred = ProcessCompletedBatches( batch , DoSomethingUseful) ; if ( errorOccurred ) { CancelPending( batch ) ; } return ; } public static bool ProcessCompletedBatches( List<IAsyncResult> pending , Func<IAsyncResult,bool> resultHandler ) { bool errorOccurred = false ; while ( ! errorOccurred && pending.Count > 0 ) { WaitHandle[] inFlight = pending.Select( x => x.AsyncWaitHandle ).ToArray() ; int offset = WaitHandle.WaitAny( inFlight ) ; IAsyncResult result = pending[offset] ; pending.RemoveAt(offset) ; errorOccurred = resultHandler(result) ; } return errorOccurred ; } 

Answer 3

Instead of polling, you need to use an asynchronous callback. http://msdn.microsoft.com/en-us/library/system.asynccallback.aspx

What is AsyncCallback?