Optimizing a LINQ query

Question

I have a LINQ query that is returning the results and format I desire, but it seems a bit slow. Wondering if there is a way to improve its performance? The Items and Skus records are 1:1. For each Item there 5 ItemWhse and 5 SkuWhse records for each Skus record.

var orderItems = from item in db.Items
            join sku in db.Skus
                on item.Sku equals sku.Sku
            where item.OrderNumber == 12345678
            select new
            {
                Item = item,
                ItemWhse = from itemWhse in db.ItemWarehouse
                          where itemWhse.OrderNumber == item.OrderNumber
                            && itemWhse.LineNumber == item.LineNumber
                          select itemWhse,
                Sku = sku,
                SkuWhse = from skuWhse in db.SkuWarehouse
                            where skuWhse.Sku == sku.Sku
                            select skuWhse
            };

Are these objects in memory or coming from the database? The devil is often in the details with optimizations. A lot of times, it is faster to grab the data from the tables separately out of the database, then join them in memory. Also, depending on your ORM, you could run something like EF Profiler to see the actual SQL being generated so you can start optimizing that. — Dan Csharpster, Aug 03 '20 at 20:41
There can be numerous causes for bad performance. Start by checking the query plan. — Gert Arnold, Aug 04 '20 at 07:01

lobstar · Answer 1 · 2020-08-04T08:48:33.703

It might be slow because you are using subqueries. Try this out:

var orderItems = from item in db.Items
                             join sku in db.Skus
                                 on item.Sku equals sku.Sku
                             join itemWhse in db.ItemWarehouse
                                 on new { item.OrderNumber, item.LineNumber } equals new { itemWhse.OrderNumber, itemWhse.LineNumber }
                             join skuWhse in db.SkuWarehouse
                                 on item.Sku equals skuWhse.Sku
                             where item.OrderNumber == 12345678
                             select new
                             {
                                 Item = item,
                                 ItemWhse = itemWhse,
                                 Sku = sku,
                                 SkuWhse = skuWhse
                             };

Optimizing a LINQ query

1 Answers1