2

I have a rather complicated SQL view in my SQL Azure database. When I get into SQL Management Portal and run

SELECT TOP(1) * FROM MyComplicatedView WHERE Id = 'SomeSpecificValue'

the query finishes in about 3 seconds. Now I have some code that reads that view via Linq-to-Sql. I have a Table-attributed class mapped onto that view:

[Table]    
class MyComplicatedView {
   [Column]
   public Guid Id {get;set;}

   // several more columns here
}

and my code uses DataContext:

var context = new DataContext(connStr);
context.Log = Console.Out;
var table = context.GetTable<MyComplicatedView>();
var first = table.FirstOrDefault(item => item.Id == "SomeSpecificValue");

and this time it takes about 30 seconds and then times out. The console output (taken from here) shows that the query contains a SELECT TOP(1) - almost the same as the query I run via the portal:

SELECT TOP (1) [t0].[Id], [t0].[OtherColumn1], [t0].[OtherColumn2], --etc
FROM [MyComplicatedView] AS [t0]
WHERE [t0].[Id] = @p0
-- @p0: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [SomeSpecificValue]

and when I run the previous query via the portal with the same parameter value I get the same nice timing (around 3 seconds):

SELECT TOP (1) [t0].[Id], [t0].[OtherColumn1], [t0].[OtherColumn2], --etc
FROM [MyComplicatedView] AS [t0]
WHERE [t0].[Id] = 'SomeSpecificValue'

and finally I run the following code in the portal:

DECLARE @p0 uniqueidentifier;
SET @p0 = 'SomeSpecificValue';
SELECT TOP (1) [t0].[Id], [t0].[OtherColumn1], [t0].[OtherColumn2], --etc
FROM [MyComplicatedView] AS [t0]
WHERE [t0].[Id] = @p0

and the timing is now much worse - something close to 10 seconds and still much better that that of the query that times out.

Why the timing difference? How do I get good timing all the time?

Community
  • 1
  • 1
sharptooth
  • 167,383
  • 100
  • 513
  • 979
  • The only thing I can think of is latency, however, 30 seconds is *really* slow. Is the SQL *exactly* the same? – James Jan 20 '14 at 12:38
  • @James: It's slightly different, I've added it. – sharptooth Jan 20 '14 at 12:49
  • 1
    I'm assuming you have indexes and so on correctly set up. Please note that the query optimizer does not have access to the values when analyzing the query so it might pick vastly different indexes when using parameters compared to when the value is part of the query, especially if you have bad indexes set up. – Lasse V. Karlsen Jan 20 '14 at 12:50
  • @LasseV.Karlsen: Well, I do have a clustered index on the table column from which `Id` is being read. However the optimizer can indeed be wrong. Is there a way to make Linq-To-Sql craft SQL queries that match the portal queries? – sharptooth Jan 20 '14 at 12:55
  • Is the type of your `Id` column actually a uniqueidentifier? Or is it some varchar type column which stores uniqueidentifier strings? – Michael Petito Aug 15 '14 at 03:41

1 Answers1

1

The answer I think is to check the execution plans of both queries. You may be a victim of SQL Server parameter sniffing as explained here Why does a parameterized query produces vastly slower query plan vs non-parameterized query

Community
  • 1
  • 1
PabloInNZ
  • 515
  • 4
  • 14