Query: optimize binary expression AndAlso and OrElse where left and right are the same #18525
Comments
|
example test: Where_concat_string_int_comparison4 |
maumar
added a commit
that referenced
this issue
Nov 1, 2019
Resolves #17543 - Queries really slow due to null checks Resolves #18525 - Query: optimize binary expression AndAlso and OrElse where left and right are the same Resolves #18547 - DbFunctions compared to NULL are ignored and break the query builder #17543 Before when rewriting null comparisons we would always remove possibility of nulls in the resulting expression. This is not always needed, specifically in predicates, where it doesn't really matter if expression returns false or null - the result is the same. Now we detect those cases and apply "simplified" null expansion which should lead to better performance. #18525 Null semantics initially expands expressions to a verbose form which then gets simplified in query optimizer. One of the optimizations added is when we do AND/OR where both sides are the same. Other small improvements have been added in this change also. #18525 Previously we assumed that function can only be null if at least one of its arguments is null. This is the case for most built-in functions, but not all. This also goes for user defined functions which can return arbitrary results. Fix is to treat all functions as potentially nullable. This leads to worse queries is some cases, but is also mitigated by other improvements added along side. In the future we will provide metadata to better determine given function's nullability. Also fixed a few small bug found along the way - incorrect Equals comparison for SqlExpression, for cases when TypeMapping was null.
maumar
added a commit
that referenced
this issue
Nov 1, 2019
Resolves #17543 - Queries really slow due to null checks Resolves #18525 - Query: optimize binary expression AndAlso and OrElse where left and right are the same Resolves #18547 - DbFunctions compared to NULL are ignored and break the query builder #17543 Before when rewriting null comparisons we would always remove possibility of nulls in the resulting expression. This is not always needed, specifically in predicates, where it doesn't really matter if expression returns false or null - the result is the same. Now we detect those cases and apply "simplified" null expansion which should lead to better performance. #18525 Null semantics initially expands expressions to a verbose form which then gets simplified in query optimizer. One of the optimizations added is when we do AND/OR where both sides are the same. Other small improvements have been added in this change also. #18525 Previously we assumed that function can only be null if at least one of its arguments is null. This is the case for most built-in functions, but not all. This also goes for user defined functions which can return arbitrary results. Fix is to treat all functions as potentially nullable. This leads to worse queries is some cases, but is also mitigated by other improvements added along side. In the future we will provide metadata to better determine given function's nullability. Also fixed a few small bug found along the way - incorrect Equals comparison for SqlExpression, for cases when TypeMapping was null. Additional refactoring: - removed the second pass of sql optimizations (we do it later when sniffing parameter values anyway) - consolidated NullComparisonTransformingExpressionVisitor into SqlExpressionOptimizingExpressionVisitor
maumar
added a commit
that referenced
this issue
Nov 1, 2019
Resolves #17543 - Queries really slow due to null checks Resolves #18525 - Query: optimize binary expression AndAlso and OrElse where left and right are the same Resolves #18547 - DbFunctions compared to NULL are ignored and break the query builder #17543 Before when rewriting null comparisons we would always remove possibility of nulls in the resulting expression. This is not always needed, specifically in predicates, where it doesn't really matter if expression returns false or null - the result is the same. Now we detect those cases and apply "simplified" null expansion which should lead to better performance. #18525 Null semantics initially expands expressions to a verbose form which then gets simplified in query optimizer. One of the optimizations added is when we do AND/OR where both sides are the same. Other small improvements have been added in this change also. #18547 Previously we assumed that function can only be null if at least one of its arguments is null. This is the case for most built-in functions, but not all. This also goes for user defined functions which can return arbitrary results. Fix is to treat all functions as potentially nullable. This leads to worse queries is some cases, but is also mitigated by other improvements added along side. In the future we will provide metadata to better determine given function's nullability. Also fixed a few small bug found along the way - incorrect Equals comparison for SqlExpression, for cases when TypeMapping was null. Additional refactoring: - removed the second pass of sql optimizations (we do it later when sniffing parameter values anyway) - consolidated NullComparisonTransformingExpressionVisitor into SqlExpressionOptimizingExpressionVisitor
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Sometimes after running optimizations we end up with expressions like:
this can be optimized to:
moreover, case like:
is tautology, which we can also recognize (similarly for AND, resolving to always being false
The text was updated successfully, but these errors were encountered: