none
adding SQL Server options to queries RRS feed

  • Question

  • I have two queries:

    DECLARE @p0 AS VarChar(2)
    SET @p0 = 'WA'
    DECLARE @p1 AS DateTime
    SET @p1 = '12/31/2006 12:00:00 AM'
    DECLARE @p2 AS DateTime
    SET @p2 = '12/1/2006 12:00:00 AM'

    -- query 1
    SELECT [t0].[CLM_ID] AS [ClaimId], [t0].[CLM_ENR_ID] AS [EnrolleeId]
    FROM [dbo].[Claim_Master] AS [t0]
    WHERE ([t0].[CLM_ST_Of_Jurisdiction] = @p0) AND (([t0].[CLM_Added]) <= @p1) AND (([t0].[CLM_Added]) >= @p2)

    -- query 2
    SELECT [t0].[CLM_ID] AS [ClaimId], [t0].[CLM_ENR_ID] AS [EnrolleeId]
    FROM [dbo].[Claim_Master] AS [t0]
    WHERE ([t0].[CLM_ST_Of_Jurisdiction] = 'WA') AND ([t0].[CLM_Added] <= '12/31/2006') AND ([t0].[CLM_Added] >= '12/1/2006')

    Query 1 takes 38 seconds to run and query 2 takes 1 second.  I just recomputed the statistics and that didn't seem to help.  If I change query 1 to this it runs in 1 second:

    -- query 1
    SELECT [t0].[CLM_ID] AS [ClaimId], [t0].[CLM_ENR_ID] AS [EnrolleeId]
    FROM [dbo].[Claim_Master] AS [t0]
    WHERE ([t0].[CLM_ST_Of_Jurisdiction] = @p0) AND (([t0].[CLM_Added]) <= @p1) AND (([t0].[CLM_Added]) >= @p2)
    OPTION (RECOMPILE)

    Is there a way to tell LINQ to SQL to add extra hints like OPTION (RECOMPILE)?

    Jon

    Friday, March 28, 2008 1:09 PM

Answers

  • Hello Jon,

     

    I recently encountered a similar issue in regards to the long run time due to the use of sp_executesql and caching of poor execution plans.

     

    There is no support by default to add hints such as OPTION (RECOMPILE) in LINQ to SQL, however it is possible to obtain the DbCommand associated with the query, modify and execute it to obtain the desired results.

     

    Using:

    DbCommand command = context.GetCommand(query.AsQueryable());

     

    You can then append the "OPTION (RECOMPILE)" suffix to the command.CommandText.

     

    I suggest then using the context.ExecuteQuery<TResult>(commandText, params) method or the non-generic ADO.Net call to command.ExecuteReader() to retrieve the results.  The DataReader can then be passed into the context.Translate method to perform the mapping etc.  Each has their own implications and beware of utilizing the context's connection.

     

    Cheers,

     

    Dusten

    Saturday, April 26, 2008 7:38 PM