Entity Framework og nullable variable

Hos en kunde havde de problemer med at det tog rigtigt lang tid når de forsøgte at hente data ud af deres views gennem Entity Framework.

Det viste sig at de flere steder brugte OR i deres WHERE clauses. Hvilket var det første problem.
Mens en OR i en SELECT eller CASE clause ikke er noget stort problem, er det generelt ikke en god idé at have OR i din WHERE clause. Dette fordi en OR resulterer i at der laves et fuldt tablescan.

OR vs UNION

Lad os prøve at se på det konkrete eksempel jeg havde ude hos kunden, for at se hvordan vi kan gøre det bedre.

Nedenstående rå SQL query tager mig over 30 sekunder at eksekvere på min database gennem SQL Server Management Studio (SSMS):

SELECT *
FROM GetCustomerPurchases_View
WHERE (
         CardIdentification IN ('ABC', 'DEF', 'JKL', 'ASD')
         OR CustomerId = 123
      )
      AND PurchaseTime > '2021-11-17 00:00:00'
      AND CardIdentification IS NOT NULL

Ændrer jeg koden til at bruge UNION i stedet for OR, vil den resulterende SQL query komme til at se således ud:

SELECT *
FROM GetCustomerPurchases_View
WHERE CardIdentification IN ('ABC', 'DEF', 'JKL', 'ASD')
      AND PurchaseTime > '2021-11-17 00:00:00'
UNION
SELECT *
FROM GetCustomerPurchases_View
WHERE CustomerId = 123
      AND PurchaseTime > '2021-11-17 00:00:00'
      AND CardIdentification IS NOT NULL

Når jeg har lavet query om til at bruge UNION i stedet for OR, tager det mig under et sekund at eksekvere min query, og jeg får det samme datasæt tilbage.

OR vs UNION

Hvorfor er querien med UNION hurtigere end OR?

Som nævnt tidligere, resulterer en OR i vores WHERE clause, at der bliver lavet et fuldt tablescan. Men med UNION får SQL Server i stedet lov til at lave en eksekutionplan hvor den vælger den optimale strategi for de enkelte subqueries, inden der bliver lavet UNION på datasættene fra vores subqueries. Dette kan resultere i nogle meget hurtigere subqueries, og af hvad jeg har erfaret, er det konsekvent hurtigere end et fuldt tablescan.

At få det lavet i LINQ

Da kunden kører med EntityFramework skulle vi have implenteret vores query som

At få det implementeret i rå SQL som LINQ var dog straks sværere.

Et intuitivt første forsøg ville være at gøre som herunder, hvor vi blot omskriver vores SQL query til LINQ:

var listWithCustomerPurchases = context.GetCustomerPurchases_View
   .Where(x =>
      cardIdentificationList.Contains(x.CardIdentification)
      && x.PurchaseTime >= cutOffDate)
   .Union(
      context.GetCustomerPurchases_View
      .Where(x =>
         x.CustomerId == Customer_Id 
         && x.PurchaseTime >= cutOffDate
         && x.CardIdentification != null)
      )
   .ToList();

Men i det konkrete eksempel ude hos kunden, resulterede det i at vi nu stadig fik et uønsket OR i vores SQL statement fra LINQ:

SELECT *
FROM [GetCustomerPurchases_View] AS [Extent1]
WHERE (
   ([Extent1].[CustomerId] = @p__linq__0) 
   OR (([Extent1].[CustomerId] IS NULL) AND (@p__linq__0 IS NULL))
) 
AND ([Extent1].[PurchaseTime] >= @p__linq__1) 
AND ([Extent1].[CardIdentification] IS NOT NULL)
@p__linq__0=13213;@p__linq__1=65465

Dette uønskede OR kom fra at LINQ kunne se at vores Customer_Id kunne være NULL.

Så, for at fjerne vores uønskede OR, skal vi sikre os at Customer_Id ikke kan være NULL. Ude hos kudnen gjorde vi det helt konkret ved at tjekke for om Customer_Id er NULL, og hvis ikke, tage value fra denne over i en variable som ikke er nullable.

Denne løsning ses implementeret herunder

var query = context.GetCustomerPurchases_View
   .Where(x =>
      cardIdentificationList.Contains(x.CardIdentification)
      && x.PurchaseTime >= cutOffDate);

if(Customer_Id .HasValue){
   var customerIdLong = Customer_Id.Value;

   var queryWithCustomerIds = context.GetCustomerPurchases_View
      .Where(x =>
         x.CustomerId== customerIdLong 
         && x.PurchaseTime >= cutOffDate
         && x.CardIdentification != null);
      )
   query = query.Union(queryWithCustomerIds);
}

var listWithCustomerPurchases = query.ToList();

Nu hvor jeg kun laver union når jeg ved at Customer_Id ikke er null, kommer den resulterende SQL til at se ud om vi ønsker den.

SELECT *
FROM [GetCustomerPurchases_View] AS [Extent1]
WHERE ([Extent1].[CustomerId] = @p__linq__0) 
AND ([Extent1].[PurchaseTime] >= @p__linq__1) 
AND ([Extent1].[CardIdentification] IS NOT NULL)
@p__linq__0=13213;@p__linq__1=65465

Det har også den øgede bonus at querien er endnu hurtigere hvis Customer_Id rent faktisk er NULL, da der så er lidt mindre at checke for vores WHERE clause.

SELECT *
FROM [GetCustomerPurchases_View] AS [Extent1]
WHERE ([Extent1].[PurchaseTime] >= @p__linq__0)
AND ([Extent1].[CardIdentification] IS NOT NULL)
@p__linq__0=65465

Tags: Programmering