Issue
I have this query:
SELECT TOP 1
MAX(HORA_LEIT), ST_BOMBA, Q_BOMBA, SEQUENCIAL
FROM
DADOS
WHERE
COD_PONTO = 2085
AND (ST_BOMBA = 'ON' OR ST_BOMBA = 'OFF')
GROUP BY
ST_BOMBA, Q_BOMBA, SEQUENCIAL
ORDER BY
MAX(HORA_LEIT) DESC
I decided to create two indexes:
CREATE INDEX ix_1
ON dados (cod_ponto, St_bomba)
INCLUDE (q_bomba, sequencial, hora_leit)
WHERE St_bomba IN ('ON', 'OFF')
Actual execution plan: https://www.brentozar.com/pastetheplan/?id=HkxiKmDXs
and
CREATE INDEX ix_2
ON dados (cod_ponto, hora_leit)
INCLUDE (St_bomba, q_bomba, sequencial)
WHERE St_bomba IN ('ON', 'OFF')
Actual execution plan: https://www.brentozar.com/pastetheplan/?id=By_1tmDQj
I figured out, as far as I can see, that the first execution plan is better, although the query optimizer is giving preference to the second one.
Am I misevaluating the performance?
Solution
The ix_2
is clearly better.
The first thing to note is that the query is written in a very convoluted way.
The query
SELECT TOP 1
MAX(HORA_LEIT), ST_BOMBA, Q_BOMBA, SEQUENCIAL
FROM
DADOS
WHERE
COD_PONTO = 2085
AND (ST_BOMBA = 'ON' OR ST_BOMBA = 'OFF')
GROUP BY
ST_BOMBA, Q_BOMBA, SEQUENCIAL
ORDER BY
MAX(HORA_LEIT) DESC
is equivalent to
SELECT TOP 1
HORA_LEIT, ST_BOMBA, Q_BOMBA, SEQUENCIAL
FROM
DADOS
WHERE
COD_PONTO = 2085
AND (ST_BOMBA = 'ON' OR ST_BOMBA = 'OFF')
ORDER BY
HORA_LEIT DESC
As you are only interested in the TOP 1
the GROUP BY
can be optimized out here.
Side note this may not be immediately apparent why but...
Take a row with the highest
HORA_LEIT
in the table (matching the where conditions). This is going to be in a group at least tied for first place when ordered byMAX(HORA_LEIT) DESC
.So taking the
ST_BOMBA, Q_BOMBA, SEQUENCIAL
from that row is going to produce the values for a valid group that satisfies the initial query. If different rows have the sameHORA_LEIT
equalling theMAX
but differentST_BOMBA, Q_BOMBA, SEQUENCIAL
then it is undeterminstic which one you get in both versions of the query.
Both execution plans recognize this and don't contain any aggregation operators.
For index 2 the filtered index condition guarantees that all rows match the ST_BOMBA
condition. It just has to do a backward ordered index seek on COD_PONTO = 2085
and read the first row and then stop (as the second key column is HORA_LEIT
).
For index 1 the index seek is actually two seeks.
- seek on
(COD_PONTO, ST_BOMBA)
=(2085, 'ON')
- seek on
(COD_PONTO, ST_BOMBA)
=(2085, 'OFF')
The combined rows matching either of those conditions then go into the Top N sort to get the TOP 1 matching row as ordered by HORA_LEIT DESC
. It is only 96 rows in this case but is potentially unbounded and just depends on your data.
Reading a single row and stopping is better than reading an arbitrary number of rows and sorting them.
Answered By - Martin Smith Answer Checked By - Willingham (PHPFixing Volunteer)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.