Why does it take so long to get an empty resultset in linqtosql?

Question

I'm running the following query on my database, which generates a sql query I know returns 0 results and when run in sql management studio takes less than a second to return.

var query = (from item in db.Table
             where item.Field == FieldValue // Field is not the primary key but is indexed
             from other in item.Associated_Table
             select other.Table);
List<Table> result = query.ToList();

The Associated_Table is a join table that relates items in Table to other items in Table. The resulting query looks something like this:

declare @p0 as int

SELECT 
   [t2].[ItemCategoryID], 
   [t2].[InventoryItemID], 
   [t2].[SiteID],
   [t2].[ItemDescription], 
   [t2].[AverageMonthlyUsage], 
   [t2].[ReorderLevel], 
   [t2].[ReorderQuantity], 
   [t2].[OtherItemDetails],
   [t2].[Price] AS [IntPrice], 
   [t2].[Ordinal], 
   [t2].[IsBase] AS [IntIsBase], 
   [t2].[Units], 
   [t2].[ProfitCenterID] AS [IntProfitCenterID], 
   [t2].[AccountID], 
   [t2].[PLU] AS [IntPLU], 
   [t2].[BarCode], 
   [t2].[DisplayName], 
   [t2].[ExtraServiceAmount] AS [IntExtraServiceAmount], 
   [t2].[IsSearchable], 
   [t2].[Terminated], 
   [t2].[PdxServiceKey], 
   [t2].[IsOpenPrice], 
   [t2].[ItemPromotionCategoryID]
FROM 
   [dbo].[Inventory.Item] AS [t0]
CROSS JOIN 
   [dbo].[Inventory.ItemExtraAssignment] AS [t1]
INNER JOIN 
   [dbo].[Inventory.Item] AS [t2] 
      ON [t2].[InventoryItemID] = [t1].[ExtraInventoryItemID]
WHERE 
   ([t0].[PLU] = @p0) AND 
   ([t1].[InventoryItemID] = [t0].[InventoryItemID])

In management studio this query runs in under a second and returns 0 results. Why does it take 2 seconds to execute the 2 lines of c# that run this same query? I realize that it will take some overhead for LinqToSql parse the objects but since no objects are being returned it shouldn't have any work to do.

Is there some optimization I'm missing; or maybe Some setting in the dbml or SQL server itself that needs to be changed?

Answer 1

Does it still take 2 seconds to run the query a second time? LINQ to SQL can take a fair amount of time to load its dependencies and perform various bits of initialization the first time, but be quick thereafter.

Additionally, I suggest you look at the SQL Server logs and see how long the query took in the database when being run from LINQ.

Answer 2

The behaviour you are describing is indicative of having a cached query plan that is no longer suitable, due to out of date statistics, skewed indexes or incorrect parameter sniffing.

Try rebuilding the indexes on the involved tables, or at least updating your statistics.

[SSMS emits pre-amble that forces a recompile each time]

Answer 3

Linq must translate the expression tree at run-time. Generally it translates the expression tree as part of the query evaluation, but for many queries you can make this happen seperately by using CompiledQuery.

  //do this once
Func<CustomDataContext, int, List<Table>> queryFunc =
System.Data.Linq.CompiledQuery.Compile<CustomDataAccess, int, List<Table>>
( (dc, i) =>
  from item in dc.Table
  where item.Field == i
  from other in item.Associated_Table
  select other.Table).ToList()
);
   //time this
List<Table> result = queryFunc(db, FieldValue);