Oracle Client vs. Task-based Asynchronous Pattern (async/await)

Question

I'd like to write a bunch of methods querying the Oracle Database in the async/await way. Since ODP.NET seems to support neither awaitable *Async methods nor Begin/EndOperationName pairs, what options do I have to implement this manually?

All examples for I/O-intensive async methods I've seen so far call only other async methods from the .NET library, but no light is shed on the way the context switching is done internally. The documentation says that in these cases no separate thread is used and the multithreading overhead is apparently worth only for CPU-intensive operations. So I guess using Task.Run() is not an option, or am I wrong?

Does ODP.NET have *any* sort of asynchronous pattern which could be adapted? You might want to read http://blogs.msdn.com/b/pfxteam/archive/2012/03/24/10287244.aspx — Jon Skeet, Jan 03 '13 at 10:08
Is using something other than ODP.NET an option (commercial ADO.NET provider)? — Yahia, Jan 03 '13 at 10:40
@metalheart ok, then the only option is to use a connection pool and build your tasks so that they request their needed connections from that pool... this way you should be able to use `Task.Run`... although not as performant as "real async" it should give you some benefit... — Yahia, Jan 03 '13 at 12:39
@Jon: There is definetely no asynchronous execution support in ODP.NET currently. — metalheart, Jan 04 '13 at 08:29
@Yahia: I am using connection pooling as it is enabled by default in the provider, but it doesn't solve problems discussed in [this post](https://forums.oracle.com/forums/thread.jspa?messageID=4155012) — metalheart, Jan 04 '13 at 08:35
@metalheart I understand that... as I said: you won't achieve the same effect as with real async implementation but it would be better than "pure sync"... the only ADO.NET providers for Oracle with real async support I know of are commercial which you already ruled out... so there is not much to help you, sorry! — Yahia, Jan 04 '13 at 08:40
Assuming you are developing a server app: You should make yourself familiar with when async IO helps and when it doesn't. http://stackoverflow.com/a/25087273/122718 http://stackoverflow.com/a/12796711/122718 (You made some vague statements in the comments about "scaling better". That's why I recommend this reading material. Oracle will not scale better in any way if you use async IO on the client.) — usr, Sep 15 '14 at 11:52

score 1 · Answer 1 · answered Jan 15 '16 at 10:54

As long as I know Oracle ODP is a sync wrapper for an async library. I found this post as I just wondering the same: Will the introduction of an async pattern for Oracle ODP calls improve performance? (I am using WCF on IIS NET TCP).

But, as it have been said, as long as the introduction of the async pattern is done creating a new Task and the calling thread is already from the thread pool, no improvement can't be done, and it will be just a overhead.

score -1 · Answer 2 · answered Jan 05 '13 at 12:45

you can always use a Task.Factory.StartNew with the TaskCreationOptions.LongRunning, so that .NET will create a new thread rather than using a threadpool thread. Below is the manual asynchronous code that you can apply to your operation.

private static void ManualAsyncOperation()
        {

            Task<string> task = Task.Factory.StartNew(() =>
                {
                    Console.WriteLine("Accessing database .....");
                    //Mimic the DB operation 
                    Thread.Sleep(1000);

                    return "Hello wolrd";
                },TaskCreationOptions.LongRunning);

            var awaiter =task.GetAwaiter();
            awaiter.OnCompleted(() =>
                {
                    try
                    {
                        var result = awaiter.GetResult();

                        Console.WriteLine("Result: {0}", result);
                    }
                    catch (Exception exception)
                    {

                        Console.WriteLine("Exception: {0}",exception);
                    }
                });
            Console.WriteLine("Continuing on the main thread and waiting for the result ....");
            Console.WriteLine();

            Console.ReadLine();

        }

I was curious if it's possible to implement async behavior without using threads... probably my solution wouldn't scale well when calling many DB operations this way. — metalheart, Jan 07 '13 at 07:57
Why are you using GetAwaiter() instead of a normal continuation (or `await`)? — Reed Copsey, Apr 30 '13 at 18:33
@ReedCopsey As with this specific question, the author asked "Since ODP.NET seems to support neither awaitable *Async methods nor Begin/EndOperationName pairs, what options do I have to implement this manually?", so I used GetAwaiter(). Sorry for delayed response. — Toan Nguyen, May 05 '13 at 12:05
@ToanNguyen You should just use ContinueWith on the task you're creating from Task.Factory.StartNew - There's no reason to get the awaiter in this case... it's an odd choice to create a continuation. — Reed Copsey, May 06 '13 at 01:18

score -1 · Answer 3 · answered Sep 15 '14 at 11:44

-1

I'm using this

public static class TaskHelper
{
    public async static Task<T> AsAsync<T>(Func<T> function, TaskCreationOptions taskCreationOptions = TaskCreationOptions.None)
    {
        return await Task.Factory.StartNew(function, taskCreationOptions);
    }

    public async static Task AsAsync(Action action, TaskCreationOptions taskCreationOptions = TaskCreationOptions.None)
    {
        await Task.Factory.StartNew(action, taskCreationOptions);
    }
}

Any synchronous function can be made asynchronous and you can await of it.

answered Sep 15 '14 at 11:44

Demetrius Axenowski

306
3
11

1

This reduced throughput. Unsuitable for server-side use. – usr Sep 15 '14 at 11:50
Sure, you have to avoid using of async operations, if you don't need them to be async. But if you want a non blocking behavior for UI or IIS, you have to do it like this. But like I sad you have to deside, do I need async operation here, or not. – Demetrius Axenowski Sep 15 '14 at 12:14
In IIS you don't need non-blocking behavior. For UI, this is useful. – usr Sep 15 '14 at 12:16
Like I know by using async, the IIS can answer more requests at a same time. – Demetrius Axenowski Sep 15 '14 at 12:40
It cannot. IIS requests use the standard thread pool. You give one thread back and pull another one out immediately. IIS and ASP.NET have no practically relevant limits (that are not easily raised) regarding parallel execution of requests. – usr Sep 15 '14 at 12:43
That's a workload well suited for async: A 1000ms delay :) It does not work by putting the delay on the thread pool (in that case no speedup would be observed). It works by using Task.Delay which is thread-less. – usr Sep 15 '14 at 12:55
Hm, I'm not sure we talking about the same. :) My point is, if you have long running operations, e.g. Azure Services, you should call them async to increase IIS throughput. Or am I wrong? I'm not sure about Oracle. Maybe your point is, that to use this for Oracle query will not give any benefits. It's right for "normal" queries, no matter for Oracle or other databases. For long running query, hm it should help. Please tell me, where is my fallacy? – Demetrius Axenowski Sep 15 '14 at 13:27
Async only helps if the long-running operation is thread-less. This requires OS and library cooperation. Oracle does not have it. SQL client has it, Task.Delay has it, sockets, web-services an Azure have it. That's the difference. – usr Sep 15 '14 at 13:29
Now I see what my problem is. You are right. On IIS we don't have any benefits to wrap synchronous operations into a Task, because it will still run on the IIS ThreadPool. Thank's for open my eyes. :) – Demetrius Axenowski Sep 16 '14 at 09:19

Oracle Client vs. Task-based Asynchronous Pattern (async/await)

3 Answers3

Linked