Different SQL between C# and vb.net using LINQ to SQL causes performance issues

Hi All,

I am currently working on a project and we are using VB.NET, I am using LINQ to SQL for my data access. I have just implemented my search query and thought I would check the generated SQL's execution plan and found that the subtree cost was about 7.3. I know that I get different SQL between C# and VB.NET when using LINQ to SQL, as VB.NET seems to add a WHERE COALESCE instead of the straight WHERE that C# will do. In the past I did a quick check on a small query to see if it would make a difference and found that there was not a noticable one.

Now having a larger more complex query I thought I would check the execution plan for the same query but written in C#, I now get a much smaller SQL query and the subtree cost drops from 7.3 to 0.1. A big difference IMO. Has anyone come across this before and what are your throughts on this. It baffles me that you would get such a difference in queries between the two languages.

 

Below I will show the LINQ to SQL query and the generated SQL for both VB.NET and C#:

VB.NET
------------

Dim query = (From menuprod In db.MenuProducts _
                     Let pis = menuprod.Product.ProductItems.SelectMany(Function(item) item.ProductItemSites).Where(Function(item) item.SiteID = inSiteID) _
                     Where menuprod.MenuID = inMenuID _
                     And menuprod.Product.ProductItems.First(Function(item) item.IsDefaultItem).ProductItemImages.Any(Function(image) image.ViewType = 0) _
                     And pis.Any() _
                     And Not pis.All(Function(item) item.SizeStocks.All(Function(size) size.OnlineStock = 0 And size.NationalStock = 0)) _
                     Order By menuprod.Product.ProductName _
                     Select New ProductDisplayItem With { _
                        .Product = menuprod.Product, _
                        .DefaultProductItem = menuprod.Product.ProductItems.FirstOrDefault(Function(item) item.IsDefaultItem), _
                        .MinWasPrice = pis.Min(Function(item) item.WasPrice), _
                        .MaxWasPrice = pis.Max(Function(item) item.WasPrice), _
                        .MinNowPrice = pis.Min(Function(item) item.NowPrice), _
                        .MaxNowPrice = pis.Max(Function(item) item.NowPrice) _
                    }).Take(6).ToList()

---

exec sp_executesql N'SELECT TOP (6) [t15].[test], [t15].[ID], [t15].[CreatedDate], [t15].[CreatedUserID], [t15].[UpdatedDate], [t15].[UpdatedUserID], [t15].[DeletedDate], [t15].[DeletedUserID], [t15].[StockCode], [t15].[ProductName], [t15].[Summary], [t15].[BrandID], [t15].[Maskname], [t15].[value] AS [MinWasPrice], [t15].[value2] AS [MaxWasPrice], [t15].[value3] AS [MinNowPrice], [t15].[value4] AS [MaxNowPrice]
FROM (
    SELECT [t2].[test], [t2].[ID], [t2].[CreatedDate], [t2].[CreatedUserID], [t2].[UpdatedDate], [t2].[UpdatedUserID], [t2].[DeletedDate], [t2].[DeletedUserID], [t2].[StockCode], [t2].[ProductName], [t2].[Summary], [t2].[BrandID], [t2].[Maskname], (
        SELECT MIN([t5].[WasPrice])
        FROM [dbo].[tbl_Product] AS [t3], [dbo].[tbl_ProductItem] AS [t4], [dbo].[tbl_ProductItemSite] AS [t5]
        WHERE ([t5].[SiteID] = @p0) AND ([t3].[ID] = [t0].[ProductID]) AND ([t4].[ProductID] = [t3].[ID]) AND ([t5].[ProductItemID] = [t4].[ID])
        ) AS [value], (
        SELECT MAX([t8].[WasPrice])
        FROM [dbo].[tbl_Product] AS [t6], [dbo].[tbl_ProductItem] AS [t7], [dbo].[tbl_ProductItemSite] AS [t8]
        WHERE ([t8].[SiteID] = @p0) AND ([t6].[ID] = [t0].[ProductID]) AND ([t7].[ProductID] = [t6].[ID]) AND ([t8].[ProductItemID] = [t7].[ID])
        ) AS [value2], (
        SELECT MIN([t11].[NowPrice])
        FROM [dbo].[tbl_Product] AS [t9], [dbo].[tbl_ProductItem] AS [t10], [dbo].[tbl_ProductItemSite] AS [t11]
        WHERE ([t11].[SiteID] = @p0) AND ([t9].[ID] = [t0].[ProductID]) AND ([t10].[ProductID] = [t9].[ID]) AND ([t11].[ProductItemID] = [t10].[ID])
        ) AS [value3], (
        SELECT MAX([t14].[NowPrice])
        FROM [dbo].[tbl_Product] AS [t12], [dbo].[tbl_ProductItem] AS [t13], [dbo].[tbl_ProductItemSite] AS [t14]
        WHERE ([t14].[SiteID] = @p0) AND ([t12].[ID] = [t0].[ProductID]) AND ([t13].[ProductID] = [t12].[ID]) AND ([t14].[ProductItemID] = [t13].[ID])
        ) AS [value4], [t0].[MenuID], [t0].[ProductID]
    FROM [dbo].[tbl_MenuProduct] AS [t0]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t1].[ID], [t1].[CreatedDate], [t1].[CreatedUserID], [t1].[UpdatedDate], [t1].[UpdatedUserID], [t1].[DeletedDate], [t1].[DeletedUserID], [t1].[StockCode], [t1].[ProductName], [t1].[Summary], [t1].[BrandID], [t1].[Maskname]
        FROM [dbo].[tbl_Product] AS [t1]
        ) AS [t2] ON [t2].[ID] = [t0].[ProductID]
    ) AS [t15]
WHERE (COALESCE(
    (CASE
        WHEN ([t15].[MenuID] = @p1) AND (EXISTS(
            SELECT NULL AS [EMPTY]
            FROM (
                SELECT TOP (1) [t28].[ID]
                FROM [dbo].[tbl_Product] AS [t27], [dbo].[tbl_ProductItem] AS [t28]
                WHERE ([t28].[IsDefaultItem] = 1) AND ([t27].[ID] = [t15].[ProductID]) AND ([t28].[ProductID] = [t27].[ID])
                ) AS [t29]
            CROSS JOIN [dbo].[tbl_ProductItemImages] AS [t30]
            WHERE ([t30].[ViewType] = @p2) AND ([t30].[ProductItemID] = [t29].[ID])
            )) AND (EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [dbo].[tbl_Product] AS [t31], [dbo].[tbl_ProductItem] AS [t32], [dbo].[tbl_ProductItemSite] AS [t33]
            WHERE ([t33].[SiteID] = @p0) AND ([t31].[ID] = [t15].[ProductID]) AND ([t32].[ProductID] = [t31].[ID]) AND ([t33].[ProductItemID] = [t32].[ID])
            )) AND (NOT NOT (EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [dbo].[tbl_Product] AS [t34], [dbo].[tbl_ProductItem] AS [t35], [dbo].[tbl_ProductItemSite] AS [t36]
            WHERE ((
                (CASE
                    WHEN NOT (EXISTS(
                        SELECT NULL AS [EMPTY]
                        FROM [dbo].[tbl_SizeStock] AS [t37]
                        WHERE ((
                            (CASE
                                WHEN ([t37].[OnlineStock] = @p3) AND ([t37].[NationalStock] = @p4) THEN 1
                                ELSE 0
                             END)) = 0) AND ([t37].[ProductItemSiteID] = [t36].[ID])
                        )) THEN 1
                    ELSE 0
                 END)) = 0) AND ([t36].[SiteID] = @p0) AND ([t34].[ID] = [t15].[ProductID]) AND ([t35].[ProductID] = [t34].[ID]) AND ([t36].[ProductItemID] = [t35].[ID])
            ))) THEN 1
        WHEN NOT (([t15].[MenuID] = @p1) AND (EXISTS(
            SELECT NULL AS [EMPTY]
            FROM (
                SELECT TOP (1) [t28].[ID]
                FROM [dbo].[tbl_Product] AS [t27], [dbo].[tbl_ProductItem] AS [t28]
                WHERE ([t28].[IsDefaultItem] = 1) AND ([t27].[ID] = [t15].[ProductID]) AND ([t28].[ProductID] = [t27].[ID])
                ) AS [t29]
            CROSS JOIN [dbo].[tbl_ProductItemImages] AS [t30]
            WHERE ([t30].[ViewType] = @p2) AND ([t30].[ProductItemID] = [t29].[ID])
            )) AND (EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [dbo].[tbl_Product] AS [t31], [dbo].[tbl_ProductItem] AS [t32], [dbo].[tbl_ProductItemSite] AS [t33]
            WHERE ([t33].[SiteID] = @p0) AND ([t31].[ID] = [t15].[ProductID]) AND ([t32].[ProductID] = [t31].[ID]) AND ([t33].[ProductItemID] = [t32].[ID])
            )) AND (NOT NOT (EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [dbo].[tbl_Product] AS [t34], [dbo].[tbl_ProductItem] AS [t35], [dbo].[tbl_ProductItemSite] AS [t36]
            WHERE ((
                (CASE
                    WHEN NOT (EXISTS(
                        SELECT NULL AS [EMPTY]
                        FROM [dbo].[tbl_SizeStock] AS [t37]
                        WHERE ((
                            (CASE
                                WHEN ([t37].[OnlineStock] = @p3) AND ([t37].[NationalStock] = @p4) THEN 1
                                ELSE 0
                             END)) = 0) AND ([t37].[ProductItemSiteID] = [t36].[ID])
                        )) THEN 1
                    ELSE 0
                 END)) = 0) AND ([t36].[SiteID] = @p0) AND ([t34].[ID] = [t15].[ProductID]) AND ([t35].[ProductID] = [t34].[ID]) AND ([t36].[ProductItemID] = [t35].[ID])
            )))) THEN 0
        ELSE NULL
     END),@p5)) = 1
ORDER BY [t15].[ProductName]',N'@p0 bigint,@p1 int,@p2 int,@p3 int,@p4 int,@p5 int',@p0=3,@p1=219,@p2=0,@p3=0,@p4=0,@p5=0

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

C#
-----

       var query = (from menuprod in db.MenuProducts
                         let pis = menuprod.Product.ProductItems.SelectMany(item => item.ProductItemSites).Where(item => item.SiteID == inSideID)
                         where menuprod.MenuID == inMenuID
                         && menuprod.Product.ProductItems.First(item => item.IsDefaultItem).ProductItemImages.Any(image => image.ViewType == 0)
                         && pis.Any()
                         && !pis.All(item => item.SizeStocks.All(size => size.OnlineStock == 0 && size.NationalStock == 0))
                         orderby menuprod.Product.ProductName
                         select new {
                             Product = menuprod.Product,
                             DefaultProductItem = menuprod.Product.ProductItems.FirstOrDefault(item => item.IsDefaultItem),
                             MinWasPrice = pis.Min(item => item.WasPrice),
                             MaxWasPrice = pis.Max(item => item.WasPrice),
                             MinNowPrice = pis.Min(item => item.NowPrice),
                             MaxNowPrice = pis.Max(item => item.NowPrice)
                         }).Take(6).ToList();

----

exec sp_executesql N'SELECT TOP (6) [t15].[test], [t15].[ID], [t15].[CreatedDate], [t15].[CreatedUserID], [t15].[UpdatedDate], [t15].[UpdatedUserID], [t15].[DeletedDate], [t15].[DeletedUserID], [t15].[StockCode], [t15].[ProductName], [t15].[Summary], [t15].[BrandID], [t15].[Maskname], [t15].[value] AS [MinWasPrice], [t15].[value2] AS [MaxWasPrice], [t15].[value3] AS [MinNowPrice], [t15].[value4] AS [MaxNowPrice]
FROM (
    SELECT [t2].[test], [t2].[ID], [t2].[CreatedDate], [t2].[CreatedUserID], [t2].[UpdatedDate], [t2].[UpdatedUserID], [t2].[DeletedDate], [t2].[DeletedUserID], [t2].[StockCode], [t2].[ProductName], [t2].[Summary], [t2].[BrandID], [t2].[Maskname], (
        SELECT MIN([t5].[WasPrice])
        FROM [dbo].[tbl_Product] AS [t3], [dbo].[tbl_ProductItem] AS [t4], [dbo].[tbl_ProductItemSite] AS [t5]
        WHERE ([t5].[SiteID] = @p0) AND ([t3].[ID] = [t0].[ProductID]) AND ([t4].[ProductID] = [t3].[ID]) AND ([t5].[ProductItemID] = [t4].[ID])
        ) AS [value], (
        SELECT MAX([t8].[WasPrice])
        FROM [dbo].[tbl_Product] AS [t6], [dbo].[tbl_ProductItem] AS [t7], [dbo].[tbl_ProductItemSite] AS [t8]
        WHERE ([t8].[SiteID] = @p0) AND ([t6].[ID] = [t0].[ProductID]) AND ([t7].[ProductID] = [t6].[ID]) AND ([t8].[ProductItemID] = [t7].[ID])
        ) AS [value2], (
        SELECT MIN([t11].[NowPrice])
        FROM [dbo].[tbl_Product] AS [t9], [dbo].[tbl_ProductItem] AS [t10], [dbo].[tbl_ProductItemSite] AS [t11]
        WHERE ([t11].[SiteID] = @p0) AND ([t9].[ID] = [t0].[ProductID]) AND ([t10].[ProductID] = [t9].[ID]) AND ([t11].[ProductItemID] = [t10].[ID])
        ) AS [value3], (
        SELECT MAX([t14].[NowPrice])
        FROM [dbo].[tbl_Product] AS [t12], [dbo].[tbl_ProductItem] AS [t13], [dbo].[tbl_ProductItemSite] AS [t14]
        WHERE ([t14].[SiteID] = @p0) AND ([t12].[ID] = [t0].[ProductID]) AND ([t13].[ProductID] = [t12].[ID]) AND ([t14].[ProductItemID] = [t13].[ID])
        ) AS [value4], [t0].[MenuID], [t0].[ProductID]
    FROM [dbo].[tbl_MenuProduct] AS [t0]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t1].[ID], [t1].[CreatedDate], [t1].[CreatedUserID], [t1].[UpdatedDate], [t1].[UpdatedUserID], [t1].[DeletedDate], [t1].[DeletedUserID], [t1].[StockCode], [t1].[ProductName], [t1].[Summary], [t1].[BrandID], [t1].[Maskname]
        FROM [dbo].[tbl_Product] AS [t1]
        ) AS [t2] ON [t2].[ID] = [t0].[ProductID]
    ) AS [t15]
WHERE ([t15].[MenuID] = @p1) AND (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM (
        SELECT TOP (1) [t17].[ID]
        FROM [dbo].[tbl_Product] AS [t16], [dbo].[tbl_ProductItem] AS [t17]
        WHERE ([t17].[IsDefaultItem] = 1) AND ([t16].[ID] = [t15].[ProductID]) AND ([t17].[ProductID] = [t16].[ID])
        ) AS [t18]
    CROSS JOIN [dbo].[tbl_ProductItemImages] AS [t19]
    WHERE ([t19].[ViewType] = @p2) AND ([t19].[ProductItemID] = [t18].[ID])
    )) AND (NOT NOT (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[tbl_Product] AS [t20], [dbo].[tbl_ProductItem] AS [t21], [dbo].[tbl_ProductItemSite] AS [t22]
    WHERE ((
        (CASE
            WHEN NOT (EXISTS(
                SELECT NULL AS [EMPTY]
                FROM [dbo].[tbl_SizeStock] AS [t23]
                WHERE ((
                    (CASE
                        WHEN ([t23].[OnlineStock] = @p3) AND ([t23].[NationalStock] = @p4) THEN 1
                        ELSE 0
                     END)) = 0) AND ([t23].[ProductItemSiteID] = [t22].[ID])
                )) THEN 1
            ELSE 0
         END)) = 0) AND ([t22].[SiteID] = @p0) AND ([t20].[ID] = [t15].[ProductID]) AND ([t21].[ProductID] = [t20].[ID]) AND ([t22].[ProductItemID] = [t21].[ID])
    ))) AND (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[tbl_Product] AS [t24], [dbo].[tbl_ProductItem] AS [t25], [dbo].[tbl_ProductItemSite] AS [t26]
    WHERE ([t26].[SiteID] = @p0) AND ([t24].[ID] = [t15].[ProductID]) AND ([t25].[ProductID] = [t24].[ID]) AND ([t26].[ProductItemID] = [t25].[ID])
    ))
ORDER BY [t15].[ProductName]',N'@p0 bigint,@p1 int,@p2 int,@p3 int,@p4 int',@p0=3,@p1=219,@p2=0,@p3=0,@p4=0

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Probably a bit hard to see without knowing my table structure but you can see the SQL difference here.....

 
BTW This was not aimed as a support question but it is the findings of an investigation into the difference between the generated SQL which is created by LINQ to SQL between C# and VB.NET.

 

UPDATE 20080207:

I have inclulded a screenshot of my SQL execution plan. Top query is VB and bottom is C# you can see the C# query is much much much much quicker if you look at the relative % of the batch. I need to investiage this alot more but is looking grim... Might have to look into converting DAL to C# for this project.


 

Also look at the CPU and Reads for the two queries. Top is Vb and bottom C# again yet again a big difference...




Thanks
Stefan
 

15 Comments

  • I have noticied that a couple of your recent posts are more of a support request nature. Consider posting things like this at forums.asp.net where you will probably get an answer faster.

    Regards,

    Ron

  • Sorry Ron,

    I dont see this as a support question personally, I am showing that these is a direct performance hit between LINQ to SQL in VB.NET and C# from my investigations.

    Will keep this in mind for the future but.


    Thanks
    Stefan

  • Ron,

    Looking through my older posts I cannot see any where I am asking for support assistance, maybe you can point me to the posts which are and I will gladly remove them. I can see that I have asked "if anyone has any suggestions let me know", but this is more of way to get ideas. I have come up with MY solution which works and I am sharing that in hope someone finds it useful, then asking that if anyone has a better/different way that maybe to share it with me.


    Thanks

    Stefan

  • The projection in the C# query is different: you don't specify a specific type to instantiate but an anonymous type.

    The query in itself isn't going to perform, no matter what you do. Some rules of thumb:
    - 'let' means a nested query in the form of a derived table
    - SelectMany means a cross join.
    - All and Any mean an EXISTS query which can hurt performance, as it will embed perhaps a huge query inside the exists to check whether the ALL or ANY keyword returns true. Consider rewriting the query as often ALL or ANY can be removed or rewritten into other means, like COUNT + test on a value and ANY(predicate) can be rewritten as a where clause.
    - If the value stored with 'let' is a set, do realize that it can be the set will return in multiple places in the query as SQL doesn't have a notion of a stored value.
    - VB's compiler emits sometimes different IL than C# for constructing the expression tree at runtime. This leads to different expressions in the tree and with that, a different end-result.

    What you can do is use the debugger visualizer for an expression tree in the C# example sourcecode in vs.net 2008's examples, then rework it a bit so you can run it as a form. Then create a solution with VB and C# projects and formalize the queries. You then cast the query to IQueryable and obtain the Expression from it and visualize it in the viewer. This way you can view the exact expression tree as generated by the VB and C# compiler for the query. I bet there are differences in them.

    Do realize that Linq to SQL sometimes generates weird SQL. Like the: WHERE (COALESCE(
    (CASE

    clause in the VB query, this is totally unnecessary the predicates in the case clause are the same and can be used separately as a predicate instead of a case clause.

    Also do realize that VB has a different notion of NULL than C#. THis means that if a null comparison is performed, the VB query will behave like it was ran in-memory which means null==null means false. For C# this is different and which could lead to different queries.

    I find that pretty silly actually, as the developer in all cases has to realize for 200% that the query is ran on a db with different laws and rules. As it's impossible to make the query run as if it's ran in-memory, one shouldn't try to do so or achieve that point, because 80% similar as in-memory, is still 20% not similar. So in short: Linq to Sql should generate the same query for C# as for VB linq statements, however they decided not to do that in some occasions due to the definitions of how some predicates are resolved in VB in-memory and htey wanted the same behavior in the database.

    IMHO a big mistake.

  • Could there be because of the difference between "And" and "AndAlso" in VB?

  • Hi Andrew,

    Thought I would try this just incase it was something as silly as that, but it seemed to make no difference. It looks like VB likes to add the:

    WHERE (COALESCE(
    (CASE
    WHEN
    WHEN NOT
    ...
    ...
    ...

    Wheras C# does not. And in a larger query as in my case it seems this seems to make it a lot more complex.


    Thanks
    Stefan

  • Thanks,

    Just was changing the page size of the front end and it is now set to 12 so thought would rerun these tests just for the heck of it. The VB.NET version dropped to 1.6 from 7.3 and the C# still around 0.1, funny how going from 6 to 12 pages made the VB query perform better but changing it to 5 gives 1.5 still and 7 pages is 1.5 but 6 pages is 7.3. <--- Those are sub tree costs I am blabbing about here.


    Even with these changes the C# is still 10% of the batch. So it deffinately still makes a large difference here.

    Thanks

    Stefan

  • Your main problem in the SQL query (why its taking a good percentage of work time) is the "WHERE (COALESCE(", because the Query engine essentially has to scan every row, apply the coalesce function to the field and check the result.   It's unfortunate that the expression tree emitted by VB.Net is causing this (which I'm guessing is because of Option Strict Off?)

    Try turning Option Strict On and see what you get... since C# is very clear and specific about the types, and VB's tendency to NOT be clear and specific, I wonder if thats how it gets manifested in the expression trees.

  • Eric,

    I tried turning Option Strict on and to no avail. I fixed up the couple errors I had :(, (I know I am bad I should really get into a habbit of setting it on, although I now turned it on by default in my settings). And still gave me the same query. At this point to me looks like a VB thing. Hopefully someone can eventually clarify what is going on here or what I have missed.

    Thanks
    Stefan

  • Is this a bug but? Might just be the way it is. I might file a report on monday(tommorow) and see how we go. Would like to get a definitive answer so I know if I will move DAL to C#, as I think this will give better performance.

    Thanks
    Stefan

  • Can you please provide 2 dlls (C# and VB) with just this query so that I can look at the actual code.
    (I don't need the dependent libs)
    P.S. Why do you use And instead of AndAlso?

  • Hi,

    I will try get this to you in the next day or so. Would you like a compiled app just with the code?

    I am using And because I wasnt thinking at the time. I have changed it to AndAlso and it makes no difference to the outcome.


    Thanks
    Stefan

  • Yeah. I'd like the compiled (C# and VB) variants.

  • Hi Stephan,

    I would also like to look at the assemblies you have and if possible a sample database that you run the query on. From first look the query that was generated for VB is very different than the C# query. Can it be that the LINQ to SQL classes (including the mapping attributes) are different in the c# assembly compare with the VB assembly?
    I would like to take a closer look at that to see if it’s a bug in the VB compiler or error in your program, you can send the files directly to me.

    Thanks much,
    Avner Aharoni
    Program Manager
    Microsoft

  • Eric,

    In the end it turns out you were spot on. I dont understand how when I did the test with option strict on before it didn't work I must have missed something. I feel like an absolute idiot now. :(:( Sorry to all for the time waster....


    Thanks
    Stefan

Comments have been disabled for this content.