LINQ Generated SQL code in .Net 4.0 slower than LinqPad Generated SQL code

clyons · January 2013

I've got a pretty simple Linq query that when run in an ASP.Net 4.0 webpage it generates SQL Queries that are huge and take a ton of time to complete (30+ seconds). When I put the code in LinqPad and run there the SQL queries are small, neat and fast (< 1 second).

I've tried rewriting this query so many ways, and have even resorted to trying toEnumerable, toList, and toArray the thing to make Linq generate in 1 query instead of 6 separate queries. All that did was timeout my SQL query.

Anyway, just don't understand why it's being so difficult and what I can do to code in a way that ASP.Net 4.0 generates the kind of queries that LinqPad does. LinqPad is AWESOME and helps out a ton in so many ways, but when I optimize my queries using LinqPad, then just have to go around again and optimize in ASP.Net 4.0 because is doesn't act the same...just isn't efficient. I would REALLY appreciate any help anyone can give me on the matter...I still consider myself a Linq newbie.

Here is code in my ASP.Net 4.0 Web Form:
Takes over 30 seconds to run in 6 queries using SQL Profiler


		clearEntities clear = new clearEntities();
        using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, new System.Transactions.TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
        {
            int ClubID = Convert.ToInt32(ddClub.SelectedValue);
            int CategoryID = Convert.ToInt32(ddCategory.SelectedValue);
            string Filter = tbFilter.Text;
        
			IQueryable<InventoryOrder> data = clear.InventoryOrders.Where(i => (i.ClubID == ClubID || ClubID == -1) &&
                                                     (CategoryID == -1 || CategoryID == i.SKU.SKUParent.SKUCategoryID) &&
                                                     (Filter == null || i.SKU.SKUParent.Parent.Contains(Filter) || i.SKU.SKUParent.Description.Contains(Filter) || i.SKU.UPC == Filter) &&
                                                                (i.InventoryOrderStatusID == 1 || i.InventoryOrderStatusID == 2));
																
																
			lbTotalOrderedOrders.Text 	= data.Where(i => i.InventoryOrderStatusID == 1).Count();
			int TotalOrderedItems 		= data.Where(i => i.InventoryOrderStatusID == 1).Sum(i => i.InventoryOrderHistories.Where(ioh => ioh.StatusMovedToID == 1).FirstOrDefault().Quantity) ?? 0;
			decimal TotalOrderedCost 	= data.Where(i => i.InventoryOrderStatusID == 1).Sum(i => i.SKU.UnitCost * i.InventoryOrderHistories.Where(ioh => ioh.StatusMovedToID == 1).FirstOrDefault().Quantity) ?? 0.0m;
			int TotalShippedOrders 		= data.Where(i => i.InventoryOrderStatusID == 2).Count();
			int TotalShippedItems 		= data.Where(i => i.InventoryOrderStatusID == 2).Sum(i => i.InventoryOrderHistories.Where(ioh => ioh.StatusMovedToID == 1).FirstOrDefault().Quantity) ?? 0;
			decimal TotalShippedCost 	= data.Where(i => i.InventoryOrderStatusID == 2).Sum(i => i.SKU.UnitCost * i.InventoryOrderHistories.Where(ioh => ioh.StatusMovedToID == 1).FirstOrDefault().Quantity) ?? 0.0m;	
		}

Here is code that I put into LinqPad:
Takes < 1 second to run in 6 queries using SQL Profiler


int ClubID = -1;
        int CategoryID = -1;
        string Filter = "";
        
		IQueryable<InventoryOrder> data = InventoryOrders.Where(i => (i.ClubID == ClubID || ClubID == -1) &&
                                                     (CategoryID == -1 || CategoryID == i.SKU.SKUParent.SKUCategoryID) &&
                                                     (Filter == null || i.SKU.SKUParent.Parent.Contains(Filter) || i.SKU.SKUParent.Description.Contains(Filter) || i.SKU.UPC == Filter) &&
                                                                (i.InventoryOrderStatusID == 1 || i.InventoryOrderStatusID == 2));
																
																
																int TotalOrderedOrders = data.Where(i => i.InventoryOrderStatusID == 1).Count();
        int TotalOrderedItems = data.Where(i => i.InventoryOrderStatusID == 1).Sum(i => i.InventoryOrderHistories.Where(ioh => ioh.StatusMovedToID == 1).FirstOrDefault().Quantity) ?? 0;
        decimal TotalOrderedCost = data.Where(i => i.InventoryOrderStatusID == 1).Sum(i => i.SKU.UnitCost * i.InventoryOrderHistories.Where(ioh => ioh.StatusMovedToID == 1).FirstOrDefault().Quantity) ?? 0.0m;
        int TotalShippedOrders = data.Where(i => i.InventoryOrderStatusID == 2).Count();
        int TotalShippedItems = data.Where(i => i.InventoryOrderStatusID == 2).Sum(i => i.InventoryOrderHistories.Where(ioh => ioh.StatusMovedToID == 1).FirstOrDefault().Quantity) ?? 0;
        decimal TotalShippedCost = data.Where(i => i.InventoryOrderStatusID == 2).Sum(i => i.SKU.UnitCost * i.InventoryOrderHistories.Where(ioh => ioh.StatusMovedToID == 1).FirstOrDefault().Quantity) ?? 0.0m;
		
		TotalOrderedItems.Dump();
		TotalOrderedCost.Dump();
		TotalShippedOrders.Dump();
		TotalShippedItems.Dump();
		TotalShippedCost.Dump();

clyons · February 2013

Nevermind, figured out it wasn't a problem with the queries but an issue with the SQL Server. It's a dev server and there's something weird going on with it. When pointed at production it flies.

LINQ Generated SQL code in .Net 4.0 slower than LinqPad Generated SQL code

Comments