Reuse complex projection in operators afterwards #7776
Milestone
Comments
|
I had this implemented in a HUGE branch that did way too much at one time. I've been piecemealing out everything I can as I have time -- I hope to get back to this one soon (note: I am not an EF team member, just doing this out of ❤️, don't lean too heavily on my words) |
|
Alright, I've put together #7857 for implementing |
|
I wanted to be able to do something like this: While the query works, it does 2 queries per product to get the most recent review and the count. Ultimately I'd like to be able to mirror a SQL query something like: To do this I had to write a custom query. Would be nice to see something like this added, or another way to do it more efficiently. |
ajcvickers
added
the
verify-fixed
maumar
removed
the
verify-fixed
|
We still generate sub optimal SQL: query: return AssertQuery(
async,
ss => from root in ss.Set<Level1>()
let first = ss.Set<Level2>().OrderByDescending(f => f.Name).FirstOrDefault(f => f.Id == root.Id)
let second = ss.Set<Level2>().OrderByDescending(s => s.Name).FirstOrDefault(s => s.Name != root.Name)
select new
{
rootId = root.Id,
rootName = root.Name,
firstId = first.Id,
firstName = first.Name,
secondId = second.Id,
secondName = second.Name,
});generates sql: SELECT [l3].[Id] AS [rootId], [l3].[Name] AS [rootName], (
SELECT TOP(1) [l].[Id]
FROM [LevelTwo] AS [l]
WHERE [l].[Id] = [l3].[Id]
ORDER BY [l].[Name] DESC) AS [firstId], (
SELECT TOP(1) [l0].[Name]
FROM [LevelTwo] AS [l0]
WHERE [l0].[Id] = [l3].[Id]
ORDER BY [l0].[Name] DESC) AS [firstName], (
SELECT TOP(1) [l1].[Id]
FROM [LevelTwo] AS [l1]
WHERE (([l1].[Name] <> [l3].[Name]) OR ([l1].[Name] IS NULL OR [l3].[Name] IS NULL)) AND ([l1].[Name] IS NOT NULL OR [l3].[Name] IS NOT NULL)
ORDER BY [l1].[Name] DESC) AS [secondId], (
SELECT TOP(1) [l2].[Name]
FROM [LevelTwo] AS [l2]
WHERE (([l2].[Name] <> [l3].[Name]) OR ([l2].[Name] IS NULL OR [l3].[Name] IS NULL)) AND ([l2].[Name] IS NOT NULL OR [l3].[Name] IS NOT NULL)
ORDER BY [l2].[Name] DESC) AS [secondName]
FROM [LevelOne] AS [l3]What we could generate: SELECT [l3].[Id], [l3].[Name], [first].[Id], [first].[Name], [second].[Id], [second].[Name]
FROM [LevelOne] AS [l3]
OUTER APPLY (SELECT TOP(1) [f].[Id], [f].[Name]
FROM [LevelTwo] AS [f]
WHERE [f].[Id] = [l3].[Id]
ORDER BY [f].[Name] DESC) AS [first]
OUTER APPLY (SELECT TOP(1) [s].[Id], [s].[Name]
FROM [LevelThree] AS [s]
WHERE [s].[Name] <> [l3].[Name]
ORDER BY [s].[Name] DESC) AS [second] |
|
Note that we do the "right thing" when the projections are not scalars but entities: return AssertQuery(
async,
ss => from root in ss.Set<Level1>()
let first = ss.Set<Level2>().OrderByDescending(f => f.Name).FirstOrDefault(f => f.Id == root.Id)
let second = ss.Set<Level2>().OrderByDescending(s => s.Name).FirstOrDefault(s => s.Name != root.Name)
select new
{
rootId = root.Id,
rootName = root.Name,
first,
second,
});sql: SELECT [l].[Id], [l].[Name], [t0].[Id], [t0].[Date], [t0].[Level1_Optional_Id], [t0].[Level1_Required_Id], [t0].[Name], [t0].[OneToMany_Optional_Inverse2Id], [t0].[OneToMany_Optional_Self_Inverse2Id], [t0].[OneToMany_Required_Inverse2Id], [t0].[OneToMany_Required_Self_Inverse2Id], [t0].[OneToOne_Optional_PK_Inverse2Id], [t0].[OneToOne_Optional_Self2Id], [t1].[Id], [t1].[Date], [t1].[Level1_Optional_Id], [t1].[Level1_Required_Id], [t1].[Name], [t1].[OneToMany_Optional_Inverse2Id], [t1].[OneToMany_Optional_Self_Inverse2Id], [t1].[OneToMany_Required_Inverse2Id], [t1].[OneToMany_Required_Self_Inverse2Id], [t1].[OneToOne_Optional_PK_Inverse2Id], [t1].[OneToOne_Optional_Self2Id]
FROM [LevelOne] AS [l]
LEFT JOIN (
SELECT [t].[Id], [t].[Date], [t].[Level1_Optional_Id], [t].[Level1_Required_Id], [t].[Name], [t].[OneToMany_Optional_Inverse2Id], [t].[OneToMany_Optional_Self_Inverse2Id], [t].[OneToMany_Required_Inverse2Id], [t].[OneToMany_Required_Self_Inverse2Id], [t].[OneToOne_Optional_PK_Inverse2Id], [t].[OneToOne_Optional_Self2Id]
FROM (
SELECT [l0].[Id], [l0].[Date], [l0].[Level1_Optional_Id], [l0].[Level1_Required_Id], [l0].[Name], [l0].[OneToMany_Optional_Inverse2Id], [l0].[OneToMany_Optional_Self_Inverse2Id], [l0].[OneToMany_Required_Inverse2Id], [l0].[OneToMany_Required_Self_Inverse2Id], [l0].[OneToOne_Optional_PK_Inverse2Id], [l0].[OneToOne_Optional_Self2Id], ROW_NUMBER() OVER(PARTITION BY [l0].[Id] ORDER BY [l0].[Name] DESC) AS [row]
FROM [LevelTwo] AS [l0]
) AS [t]
WHERE [t].[row] <= 1
) AS [t0] ON [l].[Id] = [t0].[Id]
OUTER APPLY (
SELECT TOP(1) [l1].[Id], [l1].[Date], [l1].[Level1_Optional_Id], [l1].[Level1_Required_Id], [l1].[Name], [l1].[OneToMany_Optional_Inverse2Id], [l1].[OneToMany_Optional_Self_Inverse2Id], [l1].[OneToMany_Required_Inverse2Id], [l1].[OneToMany_Required_Self_Inverse2Id], [l1].[OneToOne_Optional_PK_Inverse2Id], [l1].[OneToOne_Optional_Self2Id]
FROM [LevelTwo] AS [l1]
WHERE (([l1].[Name] <> [l].[Name]) OR ([l1].[Name] IS NULL OR [l].[Name] IS NULL)) AND ([l1].[Name] IS NOT NULL OR [l].[Name] IS NOT NULL)
ORDER BY [l1].[Name] DESC
) AS [t1] |
smitpatel
changed the title
|
Perf research into scalar subquery in projection vs. in CROSS APPLY: SQL Server-- In projection:
SELECT c.*, (SELECT TOP(1) o.OrderDate FROM orders AS o WHERE o.CustomerID = c.CustomerID)
FROM customers AS c
-- In CROSS APPLY:
SELECT c.*, t.OrderDate
FROM customers AS c
CROSS APPLY (SELECT TOP (1) o.* FROM orders AS o WHERE c.CustomerID = o.CustomerID) tTotalSubtreeCost is 16.285336 (projection) vs. 16.280334 (CROSS APPLY) PostgreSQL-- In projection:
SELECT c.*, (SELECT "Orders"."Amount" FROM "Orders" WHERE "Orders"."CustomerID" = c."CustomerID" LIMIT 1)
FROM "Customers" as c;
-- In LATERAL JOIN:
SELECT c.*, t."Amount"
From "Customers" as c,
LATERAL (SELECT * from "Orders" AS o WHERE o."CustomerID" = c."CustomerID" LIMIT 1) t;Total cost is 0.00..2228952 (projection) vs. 0.42..2238952 (LATERAL JOIN) SummarySo there's a difference, but it seems really negligible (and probably constant, not increasing with number of rows). Full detailsSQL ServerSetupDROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Customers;
CREATE TABLE Customers (
"CustomerID" INT PRIMARY KEY IDENTITY,
"Name" NVARCHAR(MAX));
CREATE TABLE Orders (
"OrderId" INT PRIMARY KEY IDENTITY,
"Amount" INT,
"CustomerID" INT,
FOREIGN KEY ("CustomerID") REFERENCES "Customers" ("CustomerID"));
CREATE INDEX ix_foo ON "Orders"("CustomerID");Projection planCROSS APPLY planPostgreSQLSetupDROP TABLE IF EXISTS "Customers" CASCADE;
DROP TABLE IF EXISTS "Orders" CASCADE;
CREATE TABLE "Customers" (
"CustomerID" INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
"Name" TEXT);
CREATE TABLE "Orders" (
"OrderId" INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
"Amount" INT,
"CustomerID" INT,
FOREIGN KEY ("CustomerID") REFERENCES "Customers" ("CustomerID"));
CREATE INDEX ix_foo ON "Orders"("CustomerID");
DO $$BEGIN
FOR i IN 1..500000 LOOP
INSERT INTO "Customers" ("Name") VALUES (i::TEXT);
INSERT INTO "Orders" ("Amount", "CustomerID") VALUES (i, i);
INSERT INTO "Orders" ("Amount", "CustomerID") VALUES (i, i);
END LOOP;
END$$;Projection planLATERAL JOIN plan |
|
In this example you are selecting only 1 column once in sub query. Problem exists when you are selecting multiple times from 1 projection as in my initial example. |
|
@msmolka yeah, I'm aware - this is a related perf investigation for @smitpatel for a solution to the original problem. |
|
Does this comment on the closed duplicate imply a fix for this issue may be on the cards for 7.0? Fantastic news if so. Is anyone able to help me understand whether the following expression, produced by AutoMapper, which leads to multiple scalar sub-queries in the SQL, is also one that could be addressed by a fix for the original issue? A map configuration and query such as this var config = new MapperConfiguration(x =>
{
x.CreateMap<Person, PersonDto>()
.ForMember(d => d.FirstAddress, o => o.MapFrom(s => s.Addresses.OrderBy(a => a.Id).FirstOrDefault()));
x.CreateMap<Address, AddressDto>();
});
var query1 = context.People.ProjectTo<PersonDto>(config);Expression - uses a proxy object for an intermediate Select, which I think roughly correlates with the use of 'let' in the original issue? Full codeusing AutoMapper;
using AutoMapper.QueryableExtensions;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
{
using var context = new PersonContext();
var config = new MapperConfiguration(x =>
{
x.CreateMap<Person, PersonDto>()
.ForMember(d => d.FirstAddress, o => o.MapFrom(s => s.Addresses.OrderBy(a => a.Id).FirstOrDefault()));
x.CreateMap<Address, AddressDto>();
});
var query1 = context.People.ProjectTo<PersonDto>(config);
Console.WriteLine(query1.Expression);
Console.WriteLine(query1.ToQueryString());
}
public class PersonContext : DbContext
{
public DbSet<Person> People { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder.UseSqlServer("Server=.;Database=Test;Trusted_Connection=True;");
}
public class Person
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Address> Addresses { get; set; }
}
public class PersonDto
{
public string Name { get; set; }
public AddressDto FirstAddress { get; set; }
}
public class Address
{
public int Id { get; set; }
public string Line1 { get; set; }
public string Line2 { get; set; }
public Person Person { get; set; }
}
public class AddressDto
{
public string Line1 { get; set; }
public string Line2 { get; set; }
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
The code generated for SQL is far from optimal comparing to EF6
Steps to reproduce
I'm getting model from database in following way:
The code produced by EF is
As far as I remember EF6 generated following query with CROSS APPLY/OUTER APPLY which is about 10x quicker.
Instead of select multiple times from the same row projection. Select each row once using OUTER APPLY.
Further technical details
EF Core version: 1.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Window 10
IDE: Visual Studio 2015
The text was updated successfully, but these errors were encountered: