Open danny-adu opened 1 month ago
Hello @danny-adu
One problem when selecting only the "right" columns is that the sub-query is generated by Entity Framework itself, and we don't really know what the developer will want to do, which might lead to issues.
Even your optimized query currently doesn't work. You perform a SET A.[Visits] = B.[Visits] + 1
but the B
table doesn't longer contain the [Visits]
column as you only selected the key SELECT [b].[BlogId]
.
So, currently, that's one of the main reasons everything is selected, even if not in the most optimized way, as we don't always know exactly what the developer will do.
Let me know if that answers your question correctly.
Best Regards,
Jon
Feature Suggestion: Optimize Bulk Update and Delete Operations
Background
Currently, the
BulkUpdateAsync
andBulkDeleteAsync
methods provided byZ.EntityFramework.Extensions.EFCore
are very useful for batch operations. However, there are scenarios where these operations can be further optimized, particularly in terms of selecting columns in subqueries and ensuring efficient locking mechanisms.Suggestion
I would like to suggest an optimization for the
BulkUpdateAsync
andBulkDeleteAsync
methods. Specifically, when performing bulk updates or deletes based on complex conditions, it would be beneficial to:Select only necessary columns in subqueries: When forming subqueries to identify rows for updating or deleting, only the primary key or unique identifier columns should be selected. This ensures minimal data retrieval and reduces overhead.
Efficient Locking Mechanisms: Ensure that the operations are optimized to use row or page locks where possible, instead of escalating to table locks, to improve concurrency and performance.
Example
Here is an example of the current SQL generated and the proposed optimized SQL:
Current SQL: