Gunnar Peipman's ASP.NET blog

ASP.NET, C#, SharePoint, SQL Server and general software development topics.

Sponsors

News

 
 
 
DZone MVB

Links

Social

LINQ To Entities, SQL and performance

I have introduced my photo gallery building process in couple of postings. As my gallery uses Entity Framework 4.0 it is good source for some internal processing that takes place inside Entity Framework. In this posting I will show you some LINQ To Entities queries and SQL that is generated for them. I added also some thoughts about performance.

My gallery model is shown here with database structure.

mygallery-semicomposite mygallery2010-db

Class diagram of gallery.

Database structure of gallery.

Get all public gallery items that have no parent album

First example shows LINQ query that returns all gallery items that have no parent album. I can use this query for gallery main page by example.


query = from g in context.GalleryItems

        where g.ParentAlbum == null &&

              g.Visible

        select g;


This query produces the following SQL query. Take a closer look at it and see how inheritance hierarchy is handled here.


SELECT

    1 AS [C1],

    CASE

        WHEN ([UnionAll1].[C1] = 1)

        THEN '0X0X'

        ELSE '0X1X'

    END AS [C2],

    [UnionAll1].[gallery_item_id] AS [C3],

    [Extent3].[title] AS [title],

    [Extent3].[description] AS [description],

    [Extent3].[visible] AS [visible],

    CASE

        WHEN ([UnionAll1].[C1] = 1)

        THEN CAST(NULL AS varchar(1))

        ELSE [UnionAll1].[file_name]

    END AS [C4],

    CASE

        WHEN ([UnionAll1].[C1] = 1)

        THEN CAST(NULL AS bit)

        ELSE [UnionAll1].[gallery_thumb]

    END AS [C5],

    [Extent3].[parent_album_id] AS [parent_album_id]

FROM (

        SELECT

            [Extent1].[gallery_item_id] AS [gallery_item_id],

            [Extent1].[file_name] AS [file_name],

            [Extent1].[gallery_thumb] AS [gallery_thumb],

            cast(0 as bit) AS [C1]

        FROM

            [dbo].[photo] AS [Extent1]

        UNION ALL

        SELECT

            [Extent2].[gallery_item_id] AS [gallery_item_id],

            CAST(NULL AS varchar(1)) AS [C1],

            CAST(NULL AS bit) AS [C2],

            cast(1 as bit) AS [C3]

        FROM

            [dbo].[album] AS [Extent2]

    ) AS [UnionAll1]

INNER JOIN

    [dbo].[gallery_item] AS [Extent3] ON

        [UnionAll1].[gallery_item_id] = [Extent3].[gallery_item_id]

WHERE

    ([Extent3].[visible] = 1) AND

    ([Extent3].[parent_album_id] IS NULL)


Get all top level galleries

Well… I have one simple rule in my gallery – all gallery items that are not albums must have parent album. Query shown above returns always only Album type objects. Let’s say now in LINQ To Entities query that we need only albums to be searched.


query = from g in context.GalleryItems.OfType<Album>()

        where g.ParentAlbum == null &&

              g.Visible

        select g;


This is the query that is produced by Entity Framework.


SELECT

    1 AS [C1],

    '0X0X' AS [C2],

    [Extent1].[gallery_item_id] AS [gallery_item_id],

    [Extent2].[title] AS [title],

    [Extent2].[description] AS [description],

    [Extent2].[visible] AS [visible],

    [Extent2].[parent_album_id] AS [parent_album_id]

FROM

    [dbo].[album] AS [Extent1]

    INNER JOIN [dbo].[gallery_item] AS [Extent2]

        ON [Extent1].[gallery_item_id] = [Extent2].[gallery_item_id]

WHERE

    ([Extent2].[visible] = 1) AND

    ([Extent2].[parent_album_id] IS NULL)


We can see that this query is not so complex than previous one. It is shorter and doesn’t have any complex parts like unions and from clauses that contain sub-query.

Getting better performance

Two examples above showed us very well how same thing can be done in more that one way. First example produced long and complex SQL-query while second example produced short and simple query. Both of them return exactly the same results.

When writing LINQ To Entities queries it is very good idea to make sure that queries use minimal set of data. If we have 1.000 albums and 100.000 photos then second query takes less time to execute because it contains no selects or joins to photos table.


kick it on DotNetKicks.com pimp it Progg it 顶 Shout it

Comments

sixYo said:

cool

thanks for share !

# August 2, 2009 5:20 AM

Muhammad Mosa said:

Very good tip, I was pointing that on a presentation and now can point to this post as sample of optimizing LINQ to Entities Queries and to point that the performance of generated SQL depends also on LINQ to Entities queries

# August 2, 2009 5:49 AM

DigiMortal said:

Thanks for feedback, Muhammad!

I plan to write posting about Linq To X and repositories and point out some mistakes that developers make. I think this future posting may be also interesting to you. So stay tuned! :)

# August 2, 2009 6:04 AM

weblogs.asp.net said:

Linq to entities sql and performance.. WTF? :)

# June 21, 2011 6:00 AM