1

Closed

[Performance] Produce simpler query expression trees for C# null comparison semantics [FixedIn6.1.0-alpha1] [AffectedLastRTM]

description

See https://entityframework.codeplex.com/workitem/1579 for more details. The attached file contains a repro.

In summary the expressions that we generate to compensate for database null comparisons contain terms that are sometimes unnecessary. This leads to different issues:
  • Perf regressions because the database is unable to leverage indexes when the predicates are complex
  • Large stacks (and potentially stack overflows) because query trees contain additional expressions
We should look at removing the unnecessary terms we add to discriminate between NULL and false when we know that the expression if final (e.g. that it will not be composed over with a negation), e.g., in the following expression:
 ((x = y) AND NOT (x IS NULL OR y IS NULL)) OR (x IS NULL AND y IS NULL)
The part 'AND NOT (x IS NULL OR y IS NULL)' is only there to prevent the result of the whole expression from being NULL when x is NULL or y is NULL but both are not simultaneously NULL. When we know that this expression won't be negated then and is only combined with other ORs or ANDs to form the predicate then we can get rid of that term because NULL and false will have the same effects on the results of the query, e.g. rows won't be returned for either NULL or false.

file attachments

Closed Jan 17 at 9:33 PM by maumar
verified, closing

comments

divega wrote Nov 5, 2013 at 6:49 PM

emilcicos wrote Dec 18, 2013 at 5:14 AM

Remaining known issues are tracked by:
https://entityframework.codeplex.com/workitem/1940

simonAtisolutions wrote Jan 21 at 10:59 PM

This behaviour needs to be used in join operator.

I am using EF 6.0.2 and setting Context.Configuration.UseDatabaseNullSemantics = true; does not affect the SQL produced by the JOIN operator :

FROM [dbo].[EMPLOYEE] AS [Extent1]
    INNER JOIN [dbo].[EMPLOYEE] AS [Extent2] ON ([Extent1].[AdminPIN] = [Extent2].[PIN]) OR (([Extent1].[AdminPIN] IS NULL) AND ([Extent2].[PIN] IS NULL))
[so by upgrading to EF 6 the query is now not compatible with EF 4.3 !!! Different result sets are returned]
E.G.
(PIN & AdminPIN are varchar(200))

var employeeQuery = from emp in Context.EMPLOYEE
join sup in Context.EMPLOYEE on emp.AdminPIN equals sup.PIN
where sup.Employee_ID == criteria.AdminEmployeeId && emp.AdminPIN != null && sup.PIN != null
select emp;
Still Produces SQL:

exec sp_executesql N'SELECT
[Project1].[Employee_ID] AS [Employee_ID], 
[Project1].[First_Name] AS [First_Name], 
[Project1].[Surname] AS [Surname], 
[Project1].[Terminated] AS [Terminated], 
[Project1].[Site_Id] AS [Site_Id], 
[Project1].[Last_Mod_By_User_ID] AS [Last_Mod_By_User_ID], 
[Project1].[Last_Mod_Date] AS [Last_Mod_Date], 
[Project1].[Create_By_User_ID] AS [Create_By_User_ID], 
[Project1].[Create_Date] AS [Create_Date], 
[Project1].[Default_Labour_Activity_ID] AS [Default_Labour_Activity_ID], 
[Project1].[Default_WorkGroup_ID] AS [Default_WorkGroup_ID], 
[Project1].[Default_Start] AS [Default_Start], 
[Project1].[Default_End] AS [Default_End], 
[Project1].[Default_Break_Start] AS [Default_Break_Start], 
[Project1].[Default_Break_Duration] AS [Default_Break_Duration], 
[Project1].[Default_Status_ID] AS [Default_Status_ID], 
[Project1].[Roster_Only] AS [Roster_Only], 
[Project1].[Termination_Date] AS [Termination_Date], 
[Project1].[EmployeeNumber] AS [EmployeeNumber], 
[Project1].[ProductSupportSalesRep] AS [ProductSupportSalesRep], 
[Project1].[ProductSupportSalesGroup] AS [ProductSupportSalesGroup], 
[Project1].[AccountManager] AS [AccountManager], 
[Project1].[AccountManagerGroup] AS [AccountManagerGroup], 
[Project1].[PrimeProductSalesPerson] AS [PrimeProductSalesPerson], 
[Project1].[PrimeProductSalesGroup] AS [PrimeProductSalesGroup], 
[Project1].[PIN] AS [PIN], 
[Project1].[AdminPIN] AS [AdminPIN]
FROM ( SELECT 
    [Extent1].[Employee_ID] AS [Employee_ID], 
    [Extent1].[First_Name] AS [First_Name], 
    [Extent1].[Surname] AS [Surname], 
    [Extent1].[Terminated] AS [Terminated], 
    [Extent1].[Site_Id] AS [Site_Id], 
    [Extent1].[Last_Mod_By_User_ID] AS [Last_Mod_By_User_ID], 
    [Extent1].[Last_Mod_Date] AS [Last_Mod_Date], 
    [Extent1].[Create_By_User_ID] AS [Create_By_User_ID], 
    [Extent1].[Create_Date] AS [Create_Date], 
    [Extent1].[Default_Labour_Activity_ID] AS [Default_Labour_Activity_ID], 
    [Extent1].[Default_WorkGroup_ID] AS [Default_WorkGroup_ID], 
    [Extent1].[Default_Start] AS [Default_Start], 
    [Extent1].[Default_End] AS [Default_End], 
    [Extent1].[Default_Break_Start] AS [Default_Break_Start], 
    [Extent1].[Default_Break_Duration] AS [Default_Break_Duration], 
    [Extent1].[Default_Status_ID] AS [Default_Status_ID], 
    [Extent1].[Roster_Only] AS [Roster_Only], 
    [Extent1].[Termination_Date] AS [Termination_Date], 
    [Extent1].[EmployeeNumber] AS [EmployeeNumber], 
    [Extent1].[ProductSupportSalesRep] AS [ProductSupportSalesRep], 
    [Extent1].[ProductSupportSalesGroup] AS [ProductSupportSalesGroup], 
    [Extent1].[AccountManager] AS [AccountManager], 
    [Extent1].[AccountManagerGroup] AS [AccountManagerGroup], 
    [Extent1].[PrimeProductSalesPerson] AS [PrimeProductSalesPerson], 
    [Extent1].[PrimeProductSalesGroup] AS [PrimeProductSalesGroup], 
    [Extent1].[PIN] AS [PIN], 
    [Extent1].[AdminPIN] AS [AdminPIN]
    FROM  [dbo].[EMPLOYEE] AS [Extent1]
    INNER JOIN [dbo].[EMPLOYEE] AS [Extent2] ON ([Extent1].[AdminPIN] = [Extent2].[PIN]) OR (([Extent1].[AdminPIN] IS NULL) AND ([Extent2].[PIN] IS NULL))
    WHERE ([Extent2].[Employee_ID] = @p__linq__0) AND ([Extent1].[AdminPIN] IS NOT NULL) AND ([Extent2].[PIN] IS NOT NULL)
)  AS [Project1]
ORDER BY [Project1].[Surname] ASC',N'@p__linq__0 int',@p__linq__0=46