Posts Tagged Paralelism

Running queries in parallel with Entity Framework (and not only with it)

From time to time I have to run two or more queries that I know will always be two or more – like some first/skip records and also total count. If you write it as two queries and execute, that means two round trips to database. Although it may not matter if the network latency is very small, why not to challenge myself and try to find some workarounds.

Sure you can create some stored procedures and get the data back from these, but I was thinking about more LINQ to Entitiesish way. I recalled a way I one time used inside one project. Although it was done in pure SQL, it, as it turned out, works, kind of, for LINQ to Entities as well.

The idea is using “one row table” and put the queries as columns. Let me demonstrate:

select
  (select foo, bar from table1 where ...),
  (select baz, foo from table2 where ...)
from OneRowTable;

Where the OneRowTable can be specially created table or i.e. for Firebird RDB$DATABASE or for Oracle Database dual. It isn’t the nicest SQL (and also challenges optimizer), but works. In columns as queries you can put anything you want as long as it is syntactically correct.

OK, what about the Entity Framework or LINQ to Entities respectively. I created the “one row table” first:

create table OneRowTable(x bit primary key);
insert into OneRowTable values (0);

The table needs to have the primary key to be able to import it into entity model, the datatype doesn’t matter (I was using MS SQL, hence the bit).

What about the queries? Similar approach:

var allinone = context.OneRowTable.Select(_ => new
{
	AData = context.a.Where(a => a.x.HasValue && a.x.Value > 10).Select(a => new { A1 = a.id, A2 = a.id * 2 }),
	BData = context.b.Where(b => b.id < 999).Select(b => new { B1 = b.id, B2 = b.y }),
});
string query = (allinone as ObjectQuery).ToTraceString();
var data = allinone.First();
var adata = data.AData;
var bdata = data.BData;

The a and b are my testing tables. You can check there’s only one query executed. Encapsulating this into some method is only piece of cake.

And how the query looks like? Well for my MS SQL test:

SELECT
[UnionAll1].[x] AS [C1],
[UnionAll1].[C2] AS [C2],
[UnionAll1].[C1] AS [C3],
[UnionAll1].[id] AS [C4],
[UnionAll1].[id1] AS [C5],
[UnionAll1].[C3] AS [C6],
[UnionAll1].[C4] AS [C7],
[UnionAll1].[C5] AS [C8],
[UnionAll1].[C6] AS [C9]
FROM  (SELECT
	[Project1].[C2] AS [C1],
	[Extent1].[x] AS [x],
	1 AS [C2],
	[Project1].[id] AS [id],
	[Project1].[id] AS [id1],
	[Project1].[C1] AS [C3],
	CAST(NULL AS int) AS [C4],
	CAST(NULL AS int) AS [C5],
	CAST(NULL AS varchar(1)) AS [C6]
	FROM  [dbo].[OneRowTable] AS [Extent1]
	LEFT OUTER JOIN  (SELECT
		[Extent2].[id] AS [id],
		[Extent2].[id] * 2 AS [C1],
		1 AS [C2]
		FROM [dbo].[a] AS [Extent2]
		WHERE ([Extent2].[x] IS NOT NULL) AND ([Extent2].[x] > 10) ) AS [Project1] ON 1 = 1
UNION ALL
	SELECT
	2 AS [C1],
	[Extent3].[x] AS [x],
	[Extent4].[id] AS [id],
	CAST(NULL AS int) AS [C2],
	CAST(NULL AS int) AS [C3],
	CAST(NULL AS int) AS [C4],
	[Extent4].[id] AS [id1],
	[Extent4].[id] AS [id2],
	[Extent4].[y] AS [y]
	FROM  [dbo].[OneRowTable] AS [Extent3]
	CROSS JOIN [dbo].[b] AS [Extent4]
	WHERE [Extent4].[id] < 999) AS [UnionAll1]
ORDER BY [UnionAll1].[x] ASC, [UnionAll1].[C1] ASC

Not exactly the original shape. The translator took another way creating two one row results and using union all to get it into one query. Except this, the query is in general the same (the explicit joins are as result same as the subselects, though little bit more confusing in this case).

Again, this isn’t general purpose way of doing it and may result in worse performance than running queries separately and I would recommend using it only after careful testing and on controlled limited set of queries.

Tags: , , , , , ,

CountdownEvent example

Yesterday I wrote about new CountdownEvent class. But what’s better than see some example of usage? ;-)

Below is pretty simple example of usage. You can see, it’s very similar to work with array of i.e. ManualResetEvent. But you have also some handy methods and properties. For instance: AddCount/TryAddCount or CurrentCount. Very handy.

class Program
{
    static void Main(string[] args)
    {
        CountdownEvent cde = new CountdownEvent(10);
        for (int i = 0; i < cde.InitialCount; i++)
        {
            new Thread(new ParameterizedThreadStart(Dummy)).Start(cde);
            //ThreadPool.QueueUserWorkItem(new WaitCallback(Dummy), cde);
        }
        cde.Wait(2000);
        Console.WriteLine("Threads done in first 2 seconds: {0}.", cde.InitialCount - cde.CurrentCount);
        cde.Wait();
        Console.WriteLine("All threads done.");
    }

    static void Dummy(object o)
    {
        Thread.Sleep(new Random().Next(5000));
        (o as CountdownEvent).Signal();
    }
}

As I said, the work is similar to work with array of ManualResetEvent, just packed into nicer cake. In fact, if you start ILDasm and look into the code you'll see, that's implemented very similarly. It's using ManualResetEventSlim (also new in .NET 4) internally to signal and smart work with Interlocked class do decrement (or increment) the number of signals received.

Do you like the class too?

Tags: , ,

CountdownEvent class

Today, while just randomly walking thru MSDN documentation I found new CountdownEvent class. It’s nothing ultra special. You can write similar class yourself in a couple of hours. But it’s great that you don’t have to. And with all the new stuff, like Task object, writing the multi-threaded apps is more easier than before (but sometimes it’s neat to just write some algorithm/problem using only thread and critical sections).

Tags: , ,

Running sync methods in async way

Probably you heard about the very good library called Power Threading Library. Shortly, it allows you to run async methods in a near-sync-looking code (and besides provides some useful classes for working in multithreaded environment). But the problem is, that you have to use methods ready for async way. That easy for dtabase calls or web service calls. But you may have your own code and you want to utilize this library to really burn up your CPU.

The obvious way is to define a delegate and use BeginInvoke/EndInvoke. However that’s not what I was interested in. Thus I created some helper methods to use any method you have in async way with Power Threading Library. Interesting fact is that’s also faster than using delegate (Jeffrey mentioned, more info here).

using System;
using System.Threading;

using Wintellect.Threading.AsyncProgModel;

public class AsyncEnumeratorSyncHelper
{
    private AsyncEnumeratorSyncHelper()
    { }

    public static AsyncResult<T> BeginHelper<T>(AsyncCallback callback, object state, Func<T> method)
    {
        AsyncResult<T> ar = new AsyncResult<T>(callback, state);

        Action<object> work = (object asyncResult) => ExecuteHelper(method, (AsyncResult<T>)asyncResult);
        ThreadPool.QueueUserWorkItem(new WaitCallback(work), ar);

        return ar;
    }

    public static AsyncResult BeginHelper(AsyncCallback callback, object state, Action method)
    {
        // just dummy object
        return BeginHelper<object>(callback, state, () => { method(); return null; });
    }

    public static T EndHelper<T>(IAsyncResult asyncResult)
    {
        AsyncResult<T> ar = (AsyncResult<T>)asyncResult;

        return ar.EndInvoke();
    }

    public static void EndHelper(IAsyncResult asyncResult)
    {
        // just dummy object
        EndHelper<object>(asyncResult);
    }

    private static void ExecuteHelper<T>(Func<T> method, AsyncResult<T> asyncResult)
    {
        try
        {
            T result = method();
            asyncResult.SetAsCompleted(result, false);
        }
        catch (Exception ex)
        {
            asyncResult.SetAsCompleted(ex, false);
        }
    }
}

With this wrapper you can call any method in async way very easily.

Still you may notice, that it’s expecting only methods without any input params. Although it looks like a problem, you can easily use lambdas to “push” params inside. If you have method int Foo(string x) you’ll just create () => Foo("rrr").

Feel free to post any problems or feedback here or in PowerThreading list.

Tags: , ,