Entity Framework Left out join taking forever (performance issues)

Question

I'm using the entity framework and linq to do multiple left outer joins but it takes 3 seconds to run the query through the entity framework.

I used ((System.Data.Objects.ObjectQuery)query).ToTraceString(); and tested the sql statement in management studio and it executed instantly.

Here is the linq code.

 var query = from Inventory in db.INVENTORies                        

                    join NewInventory in db.NEW_INVENTORY on Inventory.Barcode equals NewInventory.Barcode into lj_1                          
                    from q_NewInventory in lj_1.DefaultIfEmpty()
                    join Categories in db.Categorys on Inventory.CAT_ID equals Categories.CAT_ID into lj_2
                    from q_Categories in lj_2.DefaultIfEmpty()
                    join SCategories in db.StoneCategories on Inventory.TP_ID equals SCategories.TP_ID into lj_3
                    from q_SubCategory in lj_3.DefaultIfEmpty()
                    join qSupplier in db.Suppliers on Inventory.SUP_ID equals qSupplier.SUP_ID into lj_4
                    from q_Supplier in lj_4.DefaultIfEmpty()
                    join qStatus in db.Statuses on Inventory.ST_ID equals qStatus.ST_ID into lj_5
                    from q_Status in lj_5.DefaultIfEmpty()
                    join q_Locations in db.Locations on Inventory.LOC_ID equals q_Locations.LOC_ID into lj_7
                    from q_locations in lj_7.DefaultIfEmpty()
                    join q_Stone1 in db.Stones on Inventory.StoneID_1 equals q_Stone1.STONE_ID into lj_s1
                    from q_stone1 in lj_s1.DefaultIfEmpty()
                    join q_Stone2 in db.Stones on Inventory.StoneID_2 equals q_Stone2.STONE_ID into lj_s2
                    from q_stone2 in lj_s2.DefaultIfEmpty()
                    join q_Stone3 in db.Stones on Inventory.StoneID_3 equals q_Stone3.STONE_ID into lj_s3
                    from q_stone3 in lj_s3.DefaultIfEmpty()
                    join q_Stone4 in db.Stones on Inventory.StoneID_4 equals q_Stone4.STONE_ID into lj_s4
                    from q_stone4 in lj_s4.DefaultIfEmpty()
                    join q_Stone5 in db.Stones on Inventory.StoneID_5 equals q_Stone5.STONE_ID into lj_s5
                    from q_stone5 in lj_s5.DefaultIfEmpty()
                    join q_Stone6 in db.Stones on Inventory.StoneID_6 equals q_Stone6.STONE_ID into lj_s6
                    from q_stone6 in lj_s6.DefaultIfEmpty()
                    join q_Stone7 in db.Stones on Inventory.StoneID_7 equals q_Stone7.STONE_ID into lj_s7
                    from q_stone7 in lj_s7.DefaultIfEmpty()
                    join q_Stone8 in db.Stones on Inventory.StoneID_8 equals q_Stone8.STONE_ID into lj_s8
                    from q_stone8 in lj_s8.DefaultIfEmpty()
                    join qMasterInventory in db.MASTERINVENTORies on q_NewInventory.InvItemNo equals qMasterInventory.INVITEMNO into lj_6
                    from q_MasterInventory in lj_6.DefaultIfEmpty()
                    where Inventory.Barcode == _Barcode

                    select new 
                    { 
                        inv_InvID = Inventory.INV_ID, inv_Barcode = Inventory.Barcode,
                        inv_catID = Inventory.CAT_ID, inv_SubCatID = Inventory.TP_ID, inv_Price = Inventory.ITEM_PRICE, inv_Cost = Inventory.ITEM_COST,
                        inv_PricePoint = Inventory.PricePoint, inv_StatusID = Inventory.ST_ID, inv_StID = Inventory.ST_ID, inv_SupID = Inventory.SUP_ID,
                        inv_LocID = Inventory.LOC_ID, inv_LabSupplier = Inventory.LabSupplier, inv_LabStone1 = Inventory.LabStone1, inv_LabCategory = Inventory.LabCategory,
                        inv_LabExtra = Inventory.LabExtra, inv_LabMadeIn = Inventory.LabMadeIn, inv_Width = Inventory.ChainThickNess, inv_Size = Inventory.ChainSize,
                        inv_Stone1 = Inventory.StoneID_1,inv_Stone2 = Inventory.StoneID_2,inv_Stone3 = Inventory.StoneID_3,inv_Stone4 = Inventory.StoneID_4,inv_Stone5 = Inventory.StoneID_5,inv_Stone6 = Inventory.StoneID_6,inv_Stone7 = Inventory.StoneID_7,inv_Stone8 = Inventory.StoneID_8,inv_Stone9 = Inventory.StoneID_9,inv_Stone10 = Inventory.StoneID_10,
                        stat_Status = q_Status.DESCRIPTION,
                        cat_Category = q_Categories.DESCRIPTION,
                        subCat_SubCategory = q_SubCategory.DESCRIPTION,
                        sup_Supplier = q_Supplier.Name,
                        loc_Location = q_locations.DESCRIPTION,

                        mas_SKU = q_MasterInventory.INVITEMNO,
                        mas_GUID = q_MasterInventory.ItemGUID,

                        stone1 = q_stone1.DESCRIPTION,
                        stone2 = q_stone2.DESCRIPTION,
                        stone3 = q_stone3.DESCRIPTION,
                        stone4 = q_stone4.DESCRIPTION,
                        stone5 = q_stone5.DESCRIPTION,
                        stone6 = q_stone6.DESCRIPTION,
                        stone7 = q_stone7.DESCRIPTION,
                        stone8 = q_stone8.DESCRIPTION,

                    };

I think the time is spent in EF to rewrite this query to SQL. As Mitch pointed out this query looks horrible and the SQL created out of it will probably be much worse. Probably you should take a look at your model/mapping - the improvements to the performance are likely there. — Pawel, Jan 27 '12 at 01:27
Yes, the inventory table has 10 stoneid columns. I'm writing a Wpf interface over an old inventory tracking database. I can't modify the SQL tables or I'll break the current Clarion application that's primarily using the database. — Timothy von Hollen, Jan 27 '12 at 01:38
Might be better to use NHibernate, use LINQ where things are simple. Native SQL for scenarios like you have above. Edit: Or EF "Defined Query" — Phill, Jan 27 '12 at 02:01
Ok thanks for the feedback Phill, I'll try a defined query and see if that solves the issue -- like Pawel pointed out, the ef conversion must be taking the time to rewrite that query. — Timothy von Hollen, Jan 27 '12 at 02:21

score 1 · Answer 1 · answered Jan 27 '12 at 08:56

1

Create database view for this query and map the view to new read only entity. After that compare differences between pure LINQ execution and a new execution. If the problem was with converting your terrible LINQ query to SQL you will avoid it by using this approach. If it is still slow the problem will be elsewhere.

answered Jan 27 '12 at 08:56

Ladislav Mrnka

360,892
59
660
670

The problem is converting the LINQ query to sql. I've written a view and mapped it to a ready only entity and the query gets executed instantly. – Timothy von Hollen Jan 29 '12 at 06:09

score 0 · Answer 2 · edited May 23 '17 at 10:27

0

Guys in comments said that you have to re-write it in T-SQL. True. You can try one of SQL to LINQ converter tools.
Also use MS SQL Profiler to see what happens and improve TSQL performance.
Try to add indexes but not too much :)

edited May 23 '17 at 10:27

Community

1
1

answered Jan 27 '12 at 02:18

NoWar

36,338
80
323
498

The sql that gets generated by ef gets executed instantly. The performance issues aren't with the multiple joins but between the linq and the sql that gets generated. The database is also indexed and the wall of sql that gets generated by ef doesn't have problems(performance) executing in Sql Management Studio. – Timothy von Hollen Jan 27 '12 at 02:50

Entity Framework Left out join taking forever (performance issues)

2 Answers2