none
Linq generated SQL with very big nvarchar string RRS feed

  • Question

  •  

    i'm searching for certain item in the database using Linq as follows

     

    var skus = (from sku in Context.ItemSkus select sku);
    
          if (searchKeywords != null)
          {
            skus = skus.Where(sku => (sku.Code.StartsWith(searchKeywords)));
          }
          return skus.OrderBy(sku => sku.Code).Skip(offset ?? 0).Take(count ?? 25).ToList();
    

    it took Veeeeeeeeeeeeeeeeeeeeeeeeeeeery long time to execute and it threw out of time exception

     

    i opened the SQL profiler to review what's going on , i found that statement executed on the DB

     

    exec sp_executesql N'SELECT TOP (2) 
    [Project1].[Record_ID] AS [Record_ID], 
    [Project1].[Item_Barcode] AS [Item_Barcode], 
    [Project1].[Supplier_RecID] AS [Supplier_RecID], 
    [Project1].[UnitCostPrice] AS [UnitCostPrice], 
    [Project1].[UnitSellPrice] AS [UnitSellPrice], 
    [Project1].[LeadTimeDays] AS [LeadTimeDays], 
    [Project1].[AvailableForSale] AS [AvailableForSale], 
    [Project1].[SupplierItemCode] AS [SupplierItemCode], 
    [Project1].[SupplierItemDescription] AS [SupplierItemDescription], 
    [Project1].[MinimumOrderQuantity] AS [MinimumOrderQuantity], 
    [Project1].[Code] AS [Code]
    FROM ( SELECT [Project1].[Record_ID] AS [Record_ID], [Project1].[Item_Barcode] AS [Item_Barcode], [Project1].[Supplier_RecID] AS [Supplier_RecID], [Project1].[UnitCostPrice] AS [UnitCostPrice], [Project1].[UnitSellPrice] AS [UnitSellPrice], [Project1].[LeadTimeDays] AS [LeadTimeDays], [Project1].[AvailableForSale] AS [AvailableForSale], [Project1].[SupplierItemCode] AS [SupplierItemCode], [Project1].[SupplierItemDescription] AS [SupplierItemDescription], [Project1].[MinimumOrderQuantity] AS [MinimumOrderQuantity], [Project1].[Code] AS [Code], row_number() OVER (ORDER BY [Project1].[Code] ASC) AS [row_number]
    	FROM ( SELECT 
    		[Extent1].[Record_ID] AS [Record_ID], 
    		[Extent1].[Item_Barcode] AS [Item_Barcode], 
    		[Extent1].[Supplier_RecID] AS [Supplier_RecID], 
    		[Extent1].[UnitCostPrice] AS [UnitCostPrice], 
    		[Extent1].[UnitSellPrice] AS [UnitSellPrice], 
    		[Extent1].[LeadTimeDays] AS [LeadTimeDays], 
    		[Extent1].[AvailableForSale] AS [AvailableForSale], 
    		[Extent1].[SupplierItemCode] AS [SupplierItemCode], 
    		[Extent1].[SupplierItemDescription] AS [SupplierItemDescription], 
    		[Extent1].[MinimumOrderQuantity] AS [MinimumOrderQuantity], 
    		[Extent1].[Code] AS [Code]
    		FROM [Catalog].[ItemSku] AS [Extent1]
    		WHERE [Extent1].[Code] LIKE @p__linq__0 ESCAPE N''~''
    	) AS [Project1]
    ) AS [Project1]
    WHERE [Project1].[row_number] > 0
    ORDER BY [Project1].[Code] ASC',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'0009780000000521%'
    

    the parameter pemeter generated is 4000 character long  while the code column i'm searching against is 16 character long , when i changed the length from 4000 to 16 , the searching time was reduced from 30 seconds to less than a second
    *** The question is how could the statement be originally tuned , or is it not controllable ???

     

    Wednesday, July 27, 2011 2:44 PM

Answers