Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Use IN instead of EXISTS with ExecuteDelete and entity containment #31386

Closed
roji opened this issue Jul 31, 2023 · 0 comments · Fixed by #31387
Closed

Use IN instead of EXISTS with ExecuteDelete and entity containment #31386

roji opened this issue Jul 31, 2023 · 0 comments · Fixed by #31387
Assignees
Labels
area-bulkcud area-perf closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported type-enhancement
Milestone

Comments

@roji
Copy link
Member

roji commented Jul 31, 2023

When the base query is complex and can't be natively translated to DELETE, we fall back to placing it in a subquery and deleting all rows where the IDs match. For example, we currently generate:

DELETE FROM [o]
FROM [Order Details] AS [o]
WHERE EXISTS (
    SELECT 1
    FROM [Order Details] AS [o0]
    WHERE [o0].[OrderID] < 10300 AND [o0].[OrderID] = [o].[OrderID] AND [o0].[ProductID] = [o].[ProductID])

As we've seen in #30955, doing the same thing with IN can be far more efficient.

@roji roji self-assigned this Jul 31, 2023
roji added a commit to roji/efcore that referenced this issue Jul 31, 2023
roji added a commit to roji/efcore that referenced this issue Aug 1, 2023
@roji roji changed the title Use IN instead of EXISTS with ExecuteUpdate/Delete Use IN instead of EXISTS with Delete Aug 1, 2023
@roji roji changed the title Use IN instead of EXISTS with Delete Use IN instead of EXISTS with ExecuteDelete and entity containment Aug 1, 2023
@ajcvickers ajcvickers added the closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. label Aug 2, 2023
@ajcvickers ajcvickers added this to the 8.0.0 milestone Aug 2, 2023
roji added a commit that referenced this issue Aug 3, 2023
@ajcvickers ajcvickers modified the milestones: 8.0.0, 8.0.0-rc1 Aug 19, 2023
@ajcvickers ajcvickers modified the milestones: 8.0.0-rc1, 8.0.0 Nov 14, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area-bulkcud area-perf closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported type-enhancement
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants