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?

Dovev

Does ODP.NET have any sort of asynchronous pattern which could be adapted? You might want to read blogs.msdn.com/b/pfxteam/archive/2012/03/24/10287244.aspx – Dote

Is using something other than ODP.NET an option (commercial ADO.NET provider)? – Telluride

@Dovev 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... – Telluride

@Jon: There is definetely no asynchronous execution support in ODP.NET currently. – Dovev

@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 – Dovev

@Dovev 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! – Telluride

Assuming you are developing a server app: You should make yourself familiar with when async IO helps and when it doesn't. https://mcmap.net/q/585850/-why-does-the-ef-6-tutorial-use-asynchronous-calls https://mcmap.net/q/585851/-should-we-switch-to-use-async-i-o-by-default (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.) – Ieshaieso

Answer 1

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.

Mikkel

Answer 2

Oracle have now released a new version of Oracle.ManagedDataAccess driver which supports async-await programming model. For more information about the features in this release have a look at Alex Keh's blog.

Macedonia

Answer 3

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();

        }

Hebetude

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. – Dovev

Why are you using GetAwaiter() instead of a normal continuation (or await)? – Mountbatten

@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. – Hebetude

@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. – Mountbatten

Answer 4

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.

Emeraldemerge

This reduced throughput. Unsuitable for server-side use. – Ieshaieso

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. – Emeraldemerge

In IIS you don't need non-blocking behavior. For UI, this is useful. – Ieshaieso

Like I know by using async, the IIS can answer more requests at a same time. – Emeraldemerge

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. – Ieshaieso

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. – Ieshaieso

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? – Emeraldemerge

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. – Ieshaieso

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. :) – Emeraldemerge