Optimize Entity Framework Generated SQL Server Execution Plan

Question

I have a data structure that is basically a document with a dictionary of tags. I am attempting to bring back all documents of a given formtype that have a tag named 'Last Name' and a tag value of 'Smith'. There may be 0..N 'Last Name' tags associated with the document.

I am using the following linq query to try to match a source document to children with matching tags:

DB.Documents
    .Where(doc => doc.FormID == pd.IndexForm.FormID)
    .Where(doc => doc.Document_StringIndex_ReadOnly
                .Join(Fields,
                        dsi => new { FieldName = dsi.FieldName, FieldValue = dsi.StringValue },
                        dsi2 => new { FieldName = dsi2.FieldName, FieldValue = dsi2.StringValue },
                        (dsi, dsi2) => dsi.Document).Count() > 0);

Which generates the following query when output using .ToTraceString()

SELECT 
[Project1].*
FROM ( SELECT 
    [Extent1].*
    (SELECT 
        COUNT(cast(1 as bit)) AS [A1]
        FROM   [dbo].[Document_StringIndex_ReadOnly] AS [Extent2]
        INNER JOIN  (SELECT [Extent3].*
            FROM  [dbo].[Document] AS [Extent3]
            INNER JOIN [dbo].[Document_StringIndex_ReadOnly] AS [Extent4] ON [Extent3].[DocumentID] = [Extent4].[DocumentID] ) AS [Join1] ON (([Extent2].[FieldName] = [Join1].[FieldName]) OR (([Extent2].[FieldName] IS NULL) AND ([Join1].[FieldName] IS NULL))) AND (([Extent2].[StringValue] = [Join1].[StringValue]) OR (([Extent2].[StringValue] IS NULL) AND ([Join1].[StringValue] IS NULL)))
        LEFT OUTER JOIN [dbo].[Document] AS [Extent5] ON [Extent2].[DocumentID] = [Extent5].[DocumentID]
        WHERE ([Extent1].[DocumentID] = [Extent2].[DocumentID]) AND ([Join1].[DocumentID1] = @p__linq__7) AND ([Join1].[FieldName] = @p__linq__8)) AS [C1]
    FROM [dbo].[Document] AS [Extent1]
    WHERE [Extent1].[FormID] = @p__linq__5
)  AS [Project1]
WHERE [Project1].[C1] > 0

If I do a direct substitution of constants for my parameters (as shown below) the query executes very quickly. However, if I leave the parameters in place the query takes several minutes.

SELECT 
[Project1].*
FROM ( SELECT 
    [Extent1].*
    (SELECT 
        COUNT(cast(1 as bit)) AS [A1]
        FROM   [dbo].[Document_StringIndex_ReadOnly] AS [Extent2]
        INNER JOIN  (SELECT [Extent3].*
            FROM  [dbo].[Document] AS [Extent3]
            INNER JOIN [dbo].[Document_StringIndex_ReadOnly] AS [Extent4] ON [Extent3].[DocumentID] = [Extent4].[DocumentID] ) AS [Join1] ON (([Extent2].[FieldName] = [Join1].[FieldName]) OR (([Extent2].[FieldName] IS NULL) AND ([Join1].[FieldName] IS NULL))) AND (([Extent2].[StringValue] = [Join1].[StringValue]) OR (([Extent2].[StringValue] IS NULL) AND ([Join1].[StringValue] IS NULL)))
        LEFT OUTER JOIN [dbo].[Document] AS [Extent5] ON [Extent2].[DocumentID] = [Extent5].[DocumentID]
        WHERE ([Extent1].[DocumentID] = [Extent2].[DocumentID]) AND ([Join1].[DocumentID1] = 1015) AND ([Join1].[FieldName] = 'DDKey')) AS [C1]
    FROM [dbo].[Document] AS [Extent1]
    WHERE [Extent1].[FormID] = 22
)  AS [Project1]
WHERE [Project1].[C1] > 0

After generating an execution plan, I learned that if I directly substitute the parameter values, SQL Server performs an index seek, and my query is fast. As soon as I leave the parameters in place, SQL Server will perform an index scan, and my query times out. Is there any way to prod SQL server to always seek? Can I force entity framework to not use parameterized queries?

What version of Linq to Entities are you using?

Phil
– Phil

2012-06-01 19:14:18 +00:00
Commented Jun 1, 2012 at 19:14 — Phil
– Phil, Commented Jun 1, 2012 at 19:14

Phil · Accepted Answer · 2012-06-01 19:30:40Z

4

In the generated SQL, this line

[Join1].[FieldName] = @p__linq__8

may be the problem.

If FieldName is varchar(...) and @p__linq__8 is nvarchar(...) then this clause will cause a table scan since the parameter type doesn't match the index type.

When you directly substitute 'DDKey' then the types match so you get an index seek. Try your query with N'DDkey' and see if you get a table scan.

This is an issue with various versions of Linq to Sql and Linq to Entities, but may be fixed in later releases.

One way to work around the problem if you can't update to the latest version would be to change FieldName to be nvarchar(...).

edited Jun 1, 2012 at 19:30

answered Jun 1, 2012 at 19:24

Phil

43.2k9 gold badges102 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1431334 Over a year ago

Thank You. Changing my parameters to varchar generates the correct execution plan. It will take a while to rebuild the table and indexes with nvarchar column types, but it looks like that was my problem.

Collectives™ on Stack Overflow

Optimize Entity Framework Generated SQL Server Execution Plan

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related