Entity Framework – Query – “SELECT 1 AS [C1]” – Workfile & WorkTable

Background

Sent a couple of goodwill posts towards Entity Framework.

Ever the curmudgeon, it is time to start taking some shots.

 

Sample Generated Query

Query Snippet – Original

Here is a very small portion of one of the queries generated by Entity Framework.


select 1

where 
(
	NOT EXISTS
         (

		SELECT 1 AS [C1]
            
		FROM
                (

		    SELECT
 
		        [Extent10].[End_Id] AS [End_Id],
                        [Extent10].[FootnoteParent_Id] AS [FootnoteParent_Id],
                        [Extent11].[Id] AS [Id2],
                        [Extent12].[Id] AS [Id3],
                        [Extent12].[BeginDate] AS [BeginDate]
             
		   FROM [dbo].[UcNoteContainerFootnotes] AS [Extent10]
				
		   INNER JOIN [dbo].[CourseUCTCA] AS [Extent11]
				 
			ON ([Extent10].[Container_Id] = [Extent11].[NoteContainer_Id])
			AND ([Extent11].[NoteContainer_Id] IS NOT NULL)
             
		  LEFT OUTER JOIN [dbo].[AcademicYears] AS [Extent12] 
			ON [Extent10].[Begin_Id] = [Extent12].[Id]

            
		  WHERE [Extent10].[Container_Id] IS NOT NULL 

			
	      ) AS [Filter2]
          
	     LEFT OUTER JOIN [dbo].[AcademicYears] AS [Extent13]
		   
		ON [Filter2].[End_Id] = [Extent13].[Id]

	) -- NOT EXISTS

) -- SELECT 1


Query Snippet – Revised

Here is a rewrite



select 1

where not exists 
(

	SELECT 
			1

	FROM [dbo].[UcNoteContainerFootnotes] AS [Extent10]
	
	INNER JOIN [dbo].[CourseUCTCA] AS [Extent11]
				 
		ON ([Extent10].[Container_Id] = [Extent11].[NoteContainer_Id])
	

) -- SELECT 1


Explanation

  1. We can discard is not null, due to the same columns being referenced in the “Inner Join
    • The Inner Join “ON ([Extent10].[Container_Id] = [Extent11].[NoteContainer_Id])
    • And, the where clause we can likely jettison are
      • AND ([Extent11].[NoteContainer_Id] IS NOT NULL)
      • WHERE  ( [Extent10].[Container_Id] IS NOT NULL )
  2. We can also likely discard the Left Outer Join, as they are quite not needed in a Not Exists Clause
    • LEFT OUTER JOIN [dbo].[AcademicYears] AS [Extent12]
      • ON [Extent10].[Begin_Id] = [Extent12].[Id]
    • LEFT OUTER JOIN [dbo].[AcademicYears] AS [Extent13]
      • ON [Extent10].[End_Id] = [Extent13].[Id]

 

Obviously, we will have to test things out.

 

 

Query Plan

Query Snippet – Original

Query Snippet – Revised

Explanation

  1. Query Comparison
    • The Original Query is at 17%
    • And, the revised is at 83%
  2. Join Type
    • Original Query uses “Hash Join
      • Cost is 79%
    • And, the revised is “Nested Loops
      • Cost is 2%

 

Statistics I/O

Query Snippet – Original

 

Query Snippet – Revised

Tabulate

Table Original Query Revised Query
 UcNoteContainerFootnotes Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 CourseUCTCA  Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 Workfile  Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 WorkTable  Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

 

Explanation

  • The reworked query appears to be less taxing in terms of IO
    • CourseUCTCA
      • Original Query :- Logical Reads is at 8
      • Revised Query :- Logical Reads is at 4
    • Workfile
      • Original Query because it is an Hash Join
    • WorkTable
      • Original Query, also because it is an Hash Join

 

Summary

In later posts, will talk more about WorkTable and Workfiles.

What they are and how to measure their cost.

And, very importantly, how to make sure code re-write is accurate.

Unfortunately, Entity Framework generated SQL Code can suffer from a little intemperance

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s