Posts Tagged Linq To Sql

Performance differences between LINQ To SQL and NHibernate

Posted by Patrik Löwendahl in Data Access, ORM on February 15, 2010

In my current project one of the actions I’ve taken is to have the project and team move away from Linq To Sql to NHibernate. There was a multitude of issues that was the basis for this move, some of the main reasons are outlined in my post:“Top 10 reasons to think twice about using Linq To Sql in your project” but there was also others, like the inability to tweak Linq To Sql to perform in different scenarios which often lead to stored procedures. NHibernate have a multitude of buttons to push and can tweak almost every aspect of the data access which gives us more options.

This post is not about the lack of optimizing options in LTS, nor the options in NHibernate. It’s just to illustrate a simple truth; NHibernate is more mature and have had time to optimize the core functionality like object creation (materialization). Compare these two images:

 

Linq To Sql Materialization 703ms -  DB access 7 ms

 

NHIbernate materialization 159 – DB access 7 ms

Unfortunately I can’t show the real model that we loaded but it was an aggregate with a couple of lists which where 3 levels deep.

Another thing to note here: Linq To Sql needed a stored procedure to load this (due to the fact that load spans work really really bad in depths deeper then 2 levels, read more here: LINQ To SQL: Support for Eager Loading, Really? ) NHibernate usages a couple of batched select queries that was built using NHibernate Criteria and the Future feature.

Look at the database execution times, both are 7ms. So can we please kill the “stored procedures are faster then ORM generated queries”-debate now? Each of these scenarios is run on the same database with the same indexes and both had a database that where equally warmed up before the runs.

, , , ,

4 Comments

Top 10 reasons to think twice about using Linq To Sql in your project

Posted by Patrik Löwendahl in Data Access, ORM on August 31, 2009

I love ORM technology. I use a lot of it when building applications. I never did get completely in love with Linq To Sql though.  I’ve been using it a lot to teach ORM fundamentals just because the learning curve is really low. It just hasn’t appealed to me for production systems.

In my current project I’ve had reason to revisit the reasons why Linq to Sql failed to appeal me and this is the top 10 list.

10) A lot of the generated T-SQL queries are unnecessary complex. Take a look at the query generated by a simple  “firstName like ‘%a%’” kind of query:

SELECT [t0].[id], [t0].[firstname], [t0].[lastname], [t0].[streetaddress],
[t0].[city], [t0].[zipcode], [t0].[email]
FROM [dbo].[Director] AS [t0]
WHERE (
    (CASE
        WHEN (DATALENGTH(@p0) / 2) = 0 THEN 0
        ELSE CHARINDEX(@p0, [t0].[firstname]) - 1
     END)) > @p1

9) Associations between entities in most cases means keys has to be expressed twice in the model (once in the child entity and once in the parent entity). This leads to awkward entity design.

8) There is no batch fetch or batch insert/update for when you want to send a lot of rows to the database in one go. This seriously cripples Linq To Sql’s ability to handle anything but smaller object graphs.

7) Linq To Sql lacks the ability to do many-to-many relationships.

6) Inheritance is limited to one of the least useful scenarios; discriminator columns.

5) You can’t break up a big table into a nice object graph. There is no support for components so you can’t have a table that look like this:

directors

And have an entity that looks like this:

directorsModel 

4) You can’t map your own value objects. So instead of a reusable class like this:

 public class Director
    { ...
       public Email Email { get; set; }
    ... }

You need to do this:

    public partial class Director
    {   ...
        partial void OnEmailChanging(string value)
        {
            _validator.ValidateEmail(value);
        }        ...
    }

3) There is insufficient support for loading N+1 relationships. Linq To Sql only supports joins of 1+1 level graphs, for N+1 it starts throwing out selects. I have blogged about this before: Linq To Sql: Support for Eager Loading, Really?

2) It’s table driven and enforces the database design upon your entity model, leaving you no room for clever modeling in code that aren’t available in relational table structure models.

1) Microsoft has announced that Entity Framework is their recommended data access strategy.

http://blogs.msdn.com/adonet/archive/2008/10/31/clarifying-the-message-on-l2s-futures.aspx 

So to sum up, Linq To Sql just don’t meet my needs for a competent ORM that allows me to model my application as I wish. It doesn’t give me the high performance from the data access I want either.

If the limitations I see don’t worry you are aren’t applicable in your projects, by all means use it.

, , ,

5 Comments