none
Outrageously slow generated query RRS feed

  • Question

  • I have some slow performance in my application, and have been using the ToTraceString() function to look into the generated SQL.

    string queryString = context.t_item_style
    	.Include("Brand")
    	.Include("Item")
    	.Include("Item.Size")
    	.Include("Item.Category")
    	.ToTraceString();

    This seemingly innocuous bit of code produces this:

    SELECT

    Project1.id, 

    Project1.brand, 

    Project1.code, 

    Project1.cost, 

    Project1.entered_on, 

    Project1.name, 

    Project1.price, 

    Project1.replacement, 

    Project1.status, 

    Project1.id1, 

    Project1.name1, 

    Project1.C1, 

    Project1.id2, 

    Project1.barcode, 

    Project1.category, 

    Project1.cmg_id, 

    Project1.size, 

    Project1.style, 

    Project1.hr, 

    Project1.id3, 

    Project1.is_displayed, 

    Project1.desc, 

    Project1.id4, 

    Project1.name2, 

    Project1.is_displayed1

    FROM (SELECT

    Extent1.id, 

    Extent1.brand, 

    Extent1.name, 

    Extent1.code, 

    Extent1.price, 

    Extent1.status, 

    Extent1.cost, 

    Extent1.replacement, 

    Extent1.entered_on, 

    Extent2.id AS id1, 

    Extent2.name AS name1, 

    Join3.id AS id2, 

    Join3.style, 

    Join3.size, 

    Join3.category, 

    Join3.barcode, 

    Join3.cmg_id, 

    Join3.id1 AS id3, 

    Join3.hr, 

    Join3.is_displayed, 

    Join3.id2 AS id4, 

    Join3.name AS name2, 

    Join3.desc, 

    Join3.is_displayed1, 

    CASE WHEN (Join3.id IS  NULL) THEN (NULL)  ELSE (1) END AS C1

    FROM t_item_style AS Extent1 INNER JOIN t_item_brand AS Extent2 ON Extent1.brand = Extent2.id LEFT OUTER JOIN (SELECT

    Extent3.id, 

    Extent3.style, 

    Extent3.size, 

    Extent3.category, 

    Extent3.barcode, 

    Extent3.cmg_id, 

    Extent4.id AS id1, 

    Extent4.hr, 

    Extent4.is_displayed, 

    Extent5.id AS id2, 

    Extent5.name, 

    Extent5.desc, 

    Extent5.is_displayed AS is_displayed1

    FROM t_item AS Extent3 INNER JOIN t_item_size AS Extent4 ON Extent3.size = Extent4.id LEFT OUTER JOIN t_item_category AS Extent5 ON Extent3.category = Extent5.id) AS Join3 ON Extent1.id = Join3.style) AS Project1

     ORDER BY 

    Project1.id ASC, 

    Project1.id1 ASC, 

    Project1.C1 ASC;

     

     

     

    The above query can be summarized as:

    select * from t_item_style as tStyle

    join t_item as tItem on tItem.style = tStyle.id

    join t_item_brand as tBrand on tStyle.brand = tBrand.id

    join t_item_category as tCategory on tItem.category = tCategory.id

    join t_item_size as tSize on tItem.size = tSize.id

     

     

    Ah, but the difference is that while the second query takes 0.015 seconds to execute, the first one takes OVER FOUR HUNDRED. That's right. Nearly 8 minutes to get back the equivalent data set. I realize that generated queries can be a bit slower, but 4 orders of magnitude?!

    So the question I have is, what can I do to prevent the Entity Framework from generating such a horrible query? How can I eke out some performance without tossing out the O-RM system entirely?

    Thursday, August 18, 2011 8:56 PM

Answers

All replies