如他们响应完成对异步SQL调用 [英] respond to async sql calls as they complete

问题描述

背景：

我有查询的批量历史结果分贝的服务。
中的批次基于开始时间和结束时间。所有批次之间的数据是相互排斥的，所以对于给定批次的，它们可以以任何顺序执行。部分批次可能需要更多的时间比其他人来执行。如果有任何批次失败，则整个过程被中止/停止并记录错误。当数据返回给客户端，需要从所有批次的数据进行组合。

I have a service which queries the db for historical results in batches. The batches are based on a start time and an end time. The data between all the batches is mutually exclusive, so for a given set of batches, they can be run in any order. Some batches can take more time to execute than others. If any batch fails then the entire process is aborted/stopped and the error is logged. When returning back data to the client, the data from all the batches need to be combined.

问题：

返回数据给客户端尽可能快

Return the data to the client as fast as possible.

初始解决方案：

起初，我正在执行批次才能同步。这是不采取的，该数据是相互排斥的事实的优势。初次使用后，发现该负载方法时间太长。一些调试后，我发现了缓慢的主要原因是SQL查询的执行时间。所以接下来的解决办法是尽量使用异步执行每批次 BeginExecuteReader（）和 EndExecuteReader（）。呼吁所有批次异步后，该服务将等待在循环和轮询每300毫秒检查，如果任何一个查询已完成。如果是，那么它会被读

Initially I was executing the batches in order synchronously. This was not taking advantage of the fact that the data is mutually exclusive. After initial use, it was found that this load method was taking too long. After some debugging, I found the main cause for slowness was the execution time of the sql queries. So the next solution was to try to execute each batch asynchronously using BeginExecuteReader() and EndExecuteReader(). After calling all batches asynchronously, the service would wait in a while loop and poll every 300ms to check if any of the queries had completed. If it was, 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 decreased the loading time for large data sets, but for very small data sets (but across many batches), the poll is actually adding to the loading delay.

问：

1. 如何异步执行的SQL，而不是做投票？


2. 开始，只要在完成读取每个批次？

更新：
很抱歉，但我忘了补充，我需要在被调用同样的方法返回部分异步调用。这样做的原因是，我需要填充数据集被传递作为参数这种方法。从开始的读者使用 IAsyncCallback 需要我改变整个类。我希望，我也不会做。

Update: Sorry about this, but I forgot to add the part where I need to return in the same method that is calling the asynchronous calls. The reason for this is that the dataset that I need to populate is passed in as a parameter to this method. Using the IAsyncCallback from the begin reader would require me to change the entire class. I was hoping that I wouldn't have to do that.

推荐答案

没有足够的信息来提出一个方法，你应该去，但defintely一个你可能，这将是任务并行库和任务< T>

Not enough info to suggest a way you should go, but defintely one you might, and that would be Task Parallel library and Task<T>.

负载的乐趣。但是，不要发疯有了它，你可以很容易与您的超级骗子多线程的努力比你同步批量较慢结束。

Loads of fun. However, don't go mad with it, you could easily end up with your super duper multithreaded effort being slower than your synchronous batch.

如果说T，被连接到一个数据库，它抛出一个查询，返回一个DataReader，说有一个批次8查询。

If say T, was connect to a db, throw a query at it, return a datareader, and say there were 8 queries in a batch.

您设置的任务0到7，启动计时器超时，踢他们全部关闭。
当他们完成你保持READERR并设置基于任务ID一点你的标志。当它到达255引发OnBatchComplete事件，复制出来你的读者，并将其传递到结合任务。超时熄灭第一，采取相应的行动。如果有在任务的错误，有它返回一些合适的理由，泡出来给调用者，可能杀死任何仍在运行的查询。

You set up the tasks 0 to 7, start a timer for timeout, kick them all off. As they complete you keep the readerr and set a bit of your flag based on the task id. When it gets to 255 raise an OnBatchComplete event, copy out your readers and pass them to the combine task. Timeout goes off first, act accordingly. If there's an error in task, have it return some suitable reason, bubble out to the caller, possibly killing any still running queries.

不知道怎么的结合过程无论是工作，但如果它可以使组织一次说查询1安培; 3准备好了，你可以做一个中间过程，或者如果它在一定的逻辑顺序，一旦所有的疑问都准备好读，你可以开始阅读到简单的类，然后抛出每一个在结合任务....

Don't know how your combine process works either, but if it could be organised so say once queries 1 & 3 are ready, you could do an intermediate process, or if it's in some logical order, once all the queries are ready to read, could you start reading into to simple classes and then throw each one at a combine task....

这是不公平我不明白有趣的东西这样的....

It's not fair I don't get fun stuff like this....

这篇关于如他们响应完成对异步SQL调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！