使用ADO.NET异步调用Oracle DB [英] Asynchronous calls to Oracle DB using ADO.NET

问题描述

我正在尝试使用.NET Framework 4.5中提供的异步和等待功能来执行多个数据库调用.这是我第一次实现此功能.

如果每个查询耗时7秒，则过去通常需要35秒(5个查询* 7秒).使用以下实现，我期望它应该在将近7到9秒的时间内在ASP页面中获取并填充控件.但是，仍然需要35秒，证明了我的同步行为.

有人可以在下面的异步实现中帮助我解决问题的地方吗?

我非常感谢您的投入，从几天开始我就为此投入了很多精力

 受保护的void Page_Load(对象发送者，System.EventArgs e){RegisterAsyncTask(new PageAsyncTask(FillControlsAsync));}公共异步任务FillControlsAsync(){Task []任务=新Task [] {PopulateControlTask​​(query1，"controlID1")，PopulateControlTask​​(query2，"controlID2")，PopulateControlTask​​(query3，"controlID3")，PopulateControlTask​​(query4，"controlID4")，PopulateControlTask​​(query5，"controlID5")});等待Task.WhenAll(tasks);}公共异步任务PopulateControlTask​​(字符串查询，字符串控制){等待Task.Run(()=>{数据集ds;OracleCommand cmd;OracleDataAdapter da;尝试{如果(！Page.IsPostBack){cmd =新的OracleCommand(query，cn);da =新的OracleDataAdapter(cmd);ds = new DataSet();da.Fill(ds);开关(控制){情况"controlID1"://一些控件的自定义代码1//就像将数据源附加到控件一样.休息;情况"controlID2"://一些控件的自定义代码2休息;情况"controlID2"://一些控件的自定义代码3休息;情况"controlID3"://一些控件的自定义代码4休息;情况"controlID4"://一些控件的自定义代码5休息;}}}catch(异常e){//这里有一些错误处理}});} 

解决方案

async 和 await 用于异步代码.通常，如果您具有可伸缩的数据库，则可以使数据库调用异步，从而扩展服务器.请注意，ASP.NET上 async 的主要优点是可伸缩性，而不是响应时间.

但是，正如其他人指出的那样，Oracle不支持异步代码.

但这并不重要，因为您发布的代码实际上并不是异步的！这就是我所说的伪异步"，因为它只是使用 Task.Run 而不是使用自然异步的API将同步工作推到后台线程.(但是，正如已经指出的，在这种情况下(即Oracle)，您没有任何自然异步的API可以使用.)

因此，您最终得到的是 parallelism ，而不是 asynchrony .特别是，该代码将自身分散到5个线程池线程中以完成其工作.

现在，您需要做的第一件事是问自己是否真的想要服务器上的并行性.您的请求将占用5个线程，而不是1个(或0个)线程.这会极大地影响Web服务器的可伸缩性(以不利的方式).另外，还要考虑后端的功能.如果是单个服务器，并且这些查询都在单个硬盘驱动器上访问单个数据库，那么并行执行其中的5个查询是否会带来任何好处，或者由于磁盘争用而变得更糟甚至更糟?(您应该能够启动一个快速的控制台应用程序，以测试您的数据库在空闲和处于负载状态时如何响应串行请求与并行请求).

我发现绝大多数时候，答案是不，我不想为了这个请求而将我的整个数据库服务器都屈服"-换句话说，要避免服务器上的并行性.

但是，如果您权衡了所有选项并决定是的，那么您的情况就是在ASP.NET上并行处理适当的罕见情况之一，然后，您应该提出在此处发布的问题:为什么这些命令依次运行 而不是?(注意:此处是 sequential 与 concurrent 而不是em> synchronous vs asynchronous )

答案:我不知道.

但是我有一个猜测:如果数据库连接(您的代码段中的 cn )是共享的，则数据库连接本身很可能一次只限于一个查询.其他数据库连接系统也有类似的限制.我要尝试的第一件事是为每个查询提供自己的连接.

也就是说，如果您要并行化Web服务器.这是大如果".

I am trying to perform multiple database calls using async and await features available in .NET Framework 4.5. This is the first time I am implementing this feature.

if each query is taking 7 seconds, it used to take 35 seconds(5 queries * 7 seconds). With below implementation, I was expecting it should fetch and populate controls in asp page in close to 7-9 seconds. However, it is still taking 35 seconds, proving me the synchronous behavior.

can someone please help me where I am going wrong with this below asynchronous implementation.

I appreciate on any inputs, I have been breaking my head around this since couple of days

    protected void Page_Load(object sender, System.EventArgs e)
    {
        RegisterAsyncTask(new PageAsyncTask(FillControlsAsync));
    }

    public async Task FillControlsAsync()
    {
         Task[] tasks = new Task[]{
         PopulateControlTask(query1, "controlID1"),
         PopulateControlTask(query2, "controlID2"),
         PopulateControlTask(query3, "controlID3"),
         PopulateControlTask(query4, "controlID4"),
         PopulateControlTask(query5, "controlID5")
        });

        await Task.WhenAll(tasks);
    }
    public async Task PopulateControlTask(string query, string control)
    {
       await Task.Run(() =>
           {
               DataSet ds;
               OracleCommand cmd;
               OracleDataAdapter da;
               try
               {
                   if (!Page.IsPostBack)
                   {
                       cmd = new OracleCommand(query, cn);
                       da = new OracleDataAdapter(cmd);
                       ds = new DataSet();
                       da.Fill(ds);
                       switch (control)
                       {
                           case "controlID1":
                                //some custom code for control 1
                                // like attaching the datasource to control.
                               break;
                           case "controlID2":
                               //some custom code for control 2
                               break;
                            case "controlID2":
                            //some custom code for control 3
                            break;
                            case "controlID3":
                            //some custom code for control 4
                            break;
                            case "controlID4":
                            //some custom code for control 5
                            break;
                    }
                }
            }
            catch(Exception e)
            {
                 //some error handling here
            }
        });
    }

解决方案

async and await are for asynchronous code. Normally, if you had a scalable database, you could make your db calls asynchronous and thus scale your server. Note that the primary benefit of async on ASP.NET is scalability, not response time.

However, as others have noted, Oracle doesn't support asynchronous code.

But that is immaterial, since the code you posted isn't actually asynchronous to begin with! It's what I call "fake asynchronous", because it's just pushing synchronous work off to a background thread using Task.Run instead of using naturally-asynchronous APIs. (But as already noted, in this case (that is, Oracle), you don't have any naturally-asynchronous APIs to work with).

So, what you end up with is parallelism, not asynchrony. In particular, the code is spreading itself over 5 thread pool threads to do its work.

Now, the first thing you need to do is ask yourself if you really want parallelism on the server. Your requests will take 5 threads instead of 1 (or 0). This can greatly impact the scalability of your web server (in a bad way). Also, take into consideration the capabilities of your backend. If it's a single server and these queries are all hitting a single database on a single hard drive, will parallelizing 5 of them actually produce any benefit or will it actually be just as bad if not worse due to disk contention? (You should be able to whip up a quick console app to test how your db responds with serial vs parallel requests, when idle and when under load).

I find that the vast majority of the time, the answer is "no, I do not want to bring my entire db server to its knees for this one request" - in other words, avoid parallelism on the server.

But if you have weighed the options and decided that yes, yours is one of the rare cases where parallelism is appropriate on ASP.NET, then you should ask the question that you've posted here: why are these running sequentially and not concurrently? (side note: it's sequential vs concurrent here, not synchronous vs asynchronous)

Answer: I don't know.

But I have a guess: if the database connection (cn in your code snippet) is shared, then it's likely that the db connection itself is limited to one query at a time. Other database connection systems have similar restrictions. The first thing I'd try is giving each query its own connection.

That is, if you want to parallelize your web server. Which is a big "if".

这篇关于使用ADO.NET异步调用Oracle DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！