Bench Marking Business Entity Object Loading

[Karl Shifflett]

WPF Disciples,

 

I have stuck my head in the lions mouth and wrote up a blog post http://karlshifflett.wordpress.com/2008/04/27/sample-series-bench-marking-object-loading/ on business entity loading.

 

I explored four different techniques for business entity object loading from a SQL Database.

 

LINQ to SQL came in dead last. :-{

 

Not that this is fully conclusive or a determining factor in all projects or environments but I hope does get us thinking.

 

I even found an insane technique on Code Project for dynamically creating IL code and running it.  Talk about fast!!!!!!  This thing open the database connection, fired up a reader, queried 19,972 records and built a generic collection of objects, close the database in .063 seconds.  Oh yea, that is really fast.

 

Cheers,

 

Karl

 

[Marlon Grech]

Hi Karl,

The IL generation is a very interesting approach... if you think about it what will happen on runtime is just the loop to generate the IL..... a loop is no performance implication.... then once you generate the IL it's just like you have compiled code..... I like the idea.... Yet I am still puzzled with regards to performance of LINQ to SQL.... I was nearly sure that they do not use Reflection to create the objects....

Regards

--
Regards
Marlon
Blog - http://marlongrech.wordpress.com/
WPF Controls Library - http://www.codeplex.com/avaloncontrolslib

[Karl Shifflett]

The debates begin.

 

The IL code is generated once and executed.  BUT, so is our C# or VB.NET code.  I can’t figure out WHY the generated IL code is so much faster than the Manual code that gets compiled into IL code.  I guess I’m going to have to break out ILDASM and read what the compiler is doing.

 

Reflection is not the enemy.  You can clearly see that the Reflection method presented here is much faster than the LINQ to SQL solution.

 

I did a lot of other testing that I didn’t post.  I had the other methods use the LINQ generated classes for example.  That class is slower but not by much. 

 

The BusinessEntityBase class I provided in PART 3 of my series, has a switch that can be thrown to turn off the special features during loading so I know that my classes will scream too.

 

This dynamic method is INSANELY fast.  So better to compare LINQ to SQL to the Manual or Reflection methods.

 

But why it is slower, I do not know.  It is not generating extra trips to the server and executes the same SQL SELECT statement.

 

Cheers,

 

Karl

[Marlon Grech]

BUQQQ.... this is crazy.... The Dynamic IL generation should be slower than the complied code because there is a loop that is generating the IL....

LINQ should perform as fast as the manual code because that is how an object is created... But you know what... LINQ has object tracking turned on and that could be your bottleneck....

turn it off by doing this

datacontext.ObjectTrackingEnabled = false;

this should give you much higher performance for LINQ....

(Still LINQ will be a bit less performant because it needs to translate the Expression Tree to sql....)

I am really confused... how can this happen..... compiled code slower than runtime ???   maybe you should try the sample in C#... LOL....
Just joking dude.... I know you love VB :)

[Karl Shifflett]

Marlon,

 

Give me your phone number and room number and I’ll call you.

[Mike Brown]

You know the issue is possibly that the SQL is being regenerated with each call and the other methods possibly cache the SQL.

 

You can compile your Linq to SQL queries and you'll get the performance of stored procedures on the DB. Rico Mariani wrote an entire series about performance of LINQ compiled queries...you get just a 3% overhead against writing the data access layer by hand and using raw ADO.NET Don't believe me ... read it yourself.

 

 

[Karl Shifflett]

I’ll give this a try.  The others are using SQL statements and not stored procs.

 

The SQL Duration is very small for the LINQ method, so I don’t think this is the bottleneck.

 

Marlon is going to use Ants Profiler and I’ll buy  it too.

 

From: wpf-di...@googlegroups.com [mailto:wpf-di...@googlegroups.com] On Behalf Of Mike Brown
Sent: Sunday, April 27, 2008 10:10 PM
To: wpf-di...@googlegroups.com
Subject: Re: Bench Marking Business Entity Object Loading

 

You know the issue is possibly that the SQL is being regenerated with each call and the other methods possibly cache the SQL.

[Mike Brown]

It's not the database call that is the issue. LINQ to SQL does have an overhead associated with it. Rico's first post in the series shows that an uncompiled LINQ to SQL query has 1/8 the performance of using raw ado.net.

 

However compiling your LINQ to SQL queries practically negates the overhead of the framework.

 

Let me put it this way...if you write a LINQ to SQL query, you should compile it. I am a firm believer that your Datacontext should not be exposed outside of your data access layer. I would even go so far as to say that your LINQ to SQL objects should be internal to your data library. Basically you should have a Facade that hides the fact that LINQ to SQL even exists. The code that calls your data layer should be passing in objects from your business layer...the datalayer is responsible for translating those calls as needed to get them to the database.

[Corrado Cavalli]

Running Karl’s code on my system produces different outputs (tried many times…)

 

Debug

 

Manual:   ------------------------------

Elapsed Time: 00:00:00.3840000

Count: 19972

 

Reflection:   ------------------------------

Elapsed Time: 00:00:00.3580000

Count: 19972

 

Dynamic:   ------------------------------

Elapsed Time: 00:00:00.0720000

Count: 19972

 

LINQ:   ------------------------------

Elapsed Time: 00:00:00.1360000

Count: 19972

 

Release:

 

Manual:   ------------------------------

Elapsed Time: 00:00:00.4290000

Count: 19972

 

Reflection:   ------------------------------

Elapsed Time: 00:00:00.2760000

Count: 19972

 

Dynamic:   ------------------------------

Elapsed Time: 00:00:00.0720000

Count: 19972

 

LINQ:   ------------------------------

Elapsed Time: 00:00:00.1230000

Count: 19972

 

That’s reflect what I’ve initially expected…

[Karl Shifflett]

Mike,

 

I’m in TOTAL agreement with you on the Façade approach.

 

Question: 

 

How can you precompile LINQ to SQL code like this?  There is nothing to precompile, is there?  Just one very simple statement.

 

    Private Sub LoadLINQ()

        Dim datStart As Date = Now

        Using objDC As New DataClasses1DataContext

            objDC.ObjectTrackingEnabled = False

            Dim objList As List(Of Contact) = objDC.GetTable(Of Contact).ToList

            WriteResults(datStart, Now, "LINQ", objList.Count)

        End Using

    End Sub

 

Karl

 

 

From: wpf-di...@googlegroups.com [mailto:wpf-di...@googlegroups.com] On Behalf Of Mike Brown
Sent: Sunday, April 27, 2008 10:33 PM
To: wpf-di...@googlegroups.com
Subject: Re: Bench Marking Business Entity Object Loading

 

It's not the database call that is the issue. LINQ to SQL does have an overhead associated with it. Rico's first post in the series shows that an uncompiled LINQ to SQL query has 1/8 the performance of using raw ado.net.

 

However compiling your LINQ to SQL queries practically negates the overhead of the framework.

 

Let me put it this way...if you write a LINQ to SQL query, you should compile it. I am a firm believer that your Datacontext should not be exposed outside of your data access layer. I would even go so far as to say that your LINQ to SQL objects should be internal to your data library. Basically you should have a Facade that hides the fact that LINQ to SQL even exists. The code that calls your data layer should be passing in objects from your business layer...the datalayer is responsible for translating those calls as needed to get them to the database.

 

 

On Sun, Apr 27, 2008 at 10:15 PM, Karl Shifflett <ka...@littlerichie.com> wrote:

I'll give this a try.  The others are using SQL statements and not stored procs.

 

The SQL Duration is very small for the LINQ method, so I don't think this is the bottleneck.

 

Marlon is going to use Ants Profiler and I'll buy  it too.

 

From: wpf-di...@googlegroups.com [mailto:wpf-di...@googlegroups.com] On Behalf Of Mike Brown
Sent: Sunday, April 27, 2008 10:10 PM

To: wpf-di...@googlegroups.com
Subject: Re: Bench Marking Business Entity Object Loading

 

You know the issue is possibly that the SQL is being regenerated with each call and the other methods possibly cache the SQL.

 

You can compile your Linq to SQL queries and you'll get the performance of stored procedures on the DB. Rico Mariani wrote an entire series about performance of LINQ compiled queries...you get just a 3% overhead against writing the data access layer by hand and using raw ADO.NET Don't believe me ... read it yourself.

 

 

 

<br

[Karl Shifflett]

WOW.  Talk about different.

 

I’m on Vista x64.

 

Has anyone else run this code?  I would love to get to the bottom of this.  Very strange that Reflection is beating Manual.

 

Corrado, did you run this code as is or modify it?  Your LINQ numbers are so much better than mine.

style='font-size:12.0pt;font-family:"Times New Roman","serif"'>

 

[Corrado Cavalli]

Yes Karl,

Just modified connection string, code runs as-is J

[Karl Shifflett]

Corrado,

 

What O/S and type of computer did you run these tests on?  Maybe this can account for the different results???

 

Vista or XP

x32 or x64

Number of CPU’s or Cores

Processor speed

Main memory

 

Mine is:

Vista x64, Quad, 2.4GHz, 4GB.

 

From: wpf-di...@googlegroups.com [mailto:wpf-di...@googlegroups.com] On Behalf Of Corrado Cavalli

Sent: Monday, April 28, 2008 1:36 AM
To: wpf-di...@googlegroups.com

[Marlon Grech]

Karl to compile the query try something like this.....

Func<Northwinds, IQueryable<Orders>, int> q =
        CompiledQuery.Compile<Northwinds, int, IQueryable<Orders>>
                ((Northwinds nw, int orderid) =>
                            from o in nw.Orders 
                            where o.OrderId == orderid
                            select o );

Northwinds nw = new Northwinds(conn);

foreach (Orders o in q(nw, orderid))
{
     ...
}

Karl I am getting a wiered exception when running the code :(

P.S ok.. so it is 4 o clock in the morning here at Seattle and I cannot sleep!!!! I need to sleep, tomorrow I have to go to MS at 10 am ....

[Karl Shifflett]

I’m going to get Ants Profiler so that I can figure out what is going on and where the holdup is on my system.

[Marlon Grech]

Karl you are still awake as welll.... lol.... you never sleep!!!!

[Karl Shifflett]

Marlon,

 

Holy smokes, you have not slept yet? 

 

Here is the only line of LINQ code I have:                

 

objList As List(Of Contact) = objDC.GetTable(Of Contact).ToList

 

Didn’t see anything to pre-compile.

 

Karl

[Karl Shifflett]

Marlon, no I fell off the wagon and did get some sleep. 

 

I’m much more concerned about you.  Hope you can get some sleep before your day begins.

 

From: wpf-di...@googlegroups.com [mailto:wpf-di...@googlegroups.com] On Behalf Of Marlon Grech

Sent: Monday, April 28, 2008 7:16 AM
To: wpf-di...@googlegroups.com

[Marlon Grech]

I have no idea what GetTable is doing under the covers so I cannot say much....

and no I did not sleep.... I cannot sleep dude.... but now I will switch off my laptop and rey to sleep..... so Good night :)

[Karl Shifflett]

Best to you on your great day in Redmond!

[Corrado Cavalli]

Hi Karl,

My system is a DELL D830 running Vista 32 Sp1 4GB Ram 1 Cpu 2.2 GHz 2Core, I'm using SQLExpress as database engine.

AFAIK it looks very strange to have such performance issue compared to "classical" approach (this excludes IL generation) otherwise no one would adopt L2S :-)

[Karl Shifflett]

I’m going to run these tests on other machines and try and get to the bottom of this.  I have been running tests this morning and get the same results.

 

The Ants Profiler should also shed some light.

 

Marlon has both the Ants Profiler and Mem Profiler.  He will look into this with both products soon too.

 

From: wpf-di...@googlegroups.com [mailto:wpf-di...@googlegroups.com] On Behalf Of Corrado Cavalli

Sent: Monday, April 28, 2008 7:48 AM
To: wpf-di...@googlegroups.com

<br

[Corrado Cavalli]

Have you tried with VS2008 profiler?

[Karl Shifflett]

Not yet.  Thanks for the suggestion!

<br

[Mike Brown]

objList As List(Of Contact) = objDC.GetTable(Of Contact).ToList

 

So you're basically just doing a select * from contact.

 

here's the fully expanded LINQ

from Contact contact in ObjectDC.Contacts

select contact

 

Your function is simple

 

Dim query as Func(context as DataClasses1DataContext, IQueryable( of Contact))= _

System.Data.Linq.CompiledQuery.Compile( _

       Function(database As DataClasses1DataContext)_

           from Contact contact in database.Contacts _

           select contact)

 

So query is a lambda function that takes an instance of your datacontext and returns an IQueryable of Contact ...you want to store this in a static variable so it will always be available otherwise you'll be compiling it every time.

 

So now your code should look like

 

'm_Query defined and initialized on the class somewhere.

Private Sub LoadLINQ()
        Dim datStart As Date = Now
        Using objDC As New DataClasses1DataContext
            objDC.ObjectTrackingEnabled = False

            Dim objList As List(Of Contact) = m_Query(objDC).ToList

            WriteResults(datStart, Now, "LINQ", objList.Count)
        End Using
    End Sub

 

Try that out and see if you get better numbers.

[Karl Shifflett]

I had to make a few changes to the Func statement get it to compile on my system

Private _objPreCompiledQuery As Func(Of DataClasses1DataContext, IQueryable(Of Contact)) = _

System.Data.Linq.CompiledQuery.Compile( _
Function(database As DataClasses1DataContext) _

From contact In database.Contacts _
Select contact)

This is over a network at my work.

I ran two tests in different order. I swapped the two LINQ methods. The very first time, it is slower, after than FAST.

I'm writing a program now to spawn 10 threads and run a set of queries and processes that our users would run. This will vary the queries and requests to attempt to get a real world view of performance. I'll blog this program tonight.

However I would love to know why the first LINQ query takes so long. This happens each time I run the application. Is anyone seeing this type of bottleneck in their ASP.NET applications?

Manual: --------------------------
Elapsed Time: 00:00:00.2656131
Count: 19972
Reflection: ----------------------
Elapsed Time: 00:00:00.2968617
Count: 19972
Dynamic: -------------------------
Elapsed Time: 00:00:00.1406187
Count: 19972
LINQ PreCompiled: ----------------
Elapsed Time: 00:00:00.7030935
Count: 19972
LINQ: ----------------------------
Elapsed Time: 00:00:00.2031159
Count: 19972

Manual: ------------------------
Elapsed Time: 00:00:00.2656131
Count: 19972
Reflection: --------------------
Elapsed Time: 00:00:00.2968617
Count: 19972
Dynamic: -----------------------
Elapsed Time: 00:00:00.1406187
Count: 19972
LINQ: --------------------------
Elapsed Time: 00:00:00.6562206
Count: 19972
LINQ PreCompiled: --------------
Elapsed Time: 00:00:00.2656131
Count: 19972

________________________________

From: wpf-di...@googlegroups.com on behalf of Mike Brown
Sent: Mon 4/28/2008 9:06 AM
To: wpf-di...@googlegroups.com
Subject: Re: Bench Marking Business Entity Object Loading

[Mike Brown]

The first time it is run is when the actual compilation happens. After that you get the benefit of running a cached prepared statement (which performs on the database like a stored proc).

 

I usually stick my compiled queries on my datacontext as a static (shared) variable. Either that or on the relevant datamodel objects.