Performance · measured · The Last SQL Bender
A report query reading 190 times less data, with one rule
A report query filtered the date column through a function, so SQL Server read the whole table every time. Quentra proposed the fast form of the query; once the rule was enabled, reads dropped from 42,409 pages to 223.
The problem
The report read the whole table every time
The report query filtered the date column through a function. SQL Server could not use the existing index and scanned the table end to end: 42,409 page reads per run.
What Quentra did
It recognised the pattern and proposed the fast form
Quentra caught the pattern and prepared a version of the query that returns the same result faster. Once the rule was enabled, the query was fixed in flight; the application code and the report did not change.
The result
190 times fewer reads, the same report
The same query now reads 223 pages. Measured on real runs before and after the rule, not estimated.
Slides
13 frames; the arrow keys work too.
Optimizing non-sargable SQL with Quentra: a story.