Options
LINQ Generated SQL code in .Net 4.0 slower than LinqPad Generated SQL code
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
Here is code that I put into LinqPad:
Takes < 1 second to run in 6 queries using SQL Profiler
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();
Comments