Any ideas to increase performance of this query?

Cristiano 2018-04-20 14:22:47

Hi there, I have a query that takes about 15 seconds to execute and I wonder if there is room to increase its performance. Most of the entities involved are views. The query plan shows a bunch of index scans, but none of them with a high cost. Also, looking at the statistics, some of them were old, but I after updating them nothing changed.

Thanks in advance,

Hugo Kornelis 2018-04-22 19:21:05
Looking for quick wins, my eye is drawn towards the table S_RELACIONAMENTOACIONISTA. The table occurs in two places in the execution plan, both are Table Scan operators (so this has to be a heap table, a table with no clustered index).

Near the bottom of the plan, in NodeID 64, the scan itself is not a big cost but the rows are fed into an Index Spool. In this context, that means that the optimizer can benefit so much from an index that it decides to build it on the spot. The overhead of building it every time the query runs is outweighed by the savings. Of course, it would be better if a useable index already exists.

If I had to suggest an index for just this sole purpose, I'd use a nonclustered filtered index. Indexed columns would be unoidfilha and Mrfmesano; included columns would be Entcodigo, unoid, unoidfilha, and tpoid; the filter would be WHERE tpoid = 3. But if I were actually tuning your system, I would try to find an index that might be slightly less usseful for this query but that would benefit other queries as well, or I woould use a clustered index on tpoid, unoidfilha, and Mrfmesano.

The other location where this same table is used is in Table Scan with NodeID 31. Here the table is scanned once and no Index Spool is used, but the data goes into a Hash Match operator. With an appropriate index, the optimizer might have chosen a Nested Loops + Index Seek, which is faster as long as the number of rows is limited (but scales horribly, so it can be a dangerous replacement)! The Hash Match shows that this occurence of the table joins on three columns: Mrfmesano, unoid, and entcodigovisualiza. The table scan itself filters on tpoid = 3, and unoidfilha is the only other column returned. A single index for this location alone would be another nonclustered filtered index, on Mrfmesano, unoid, and entcodigovisualiza; with unoidfilha included and the same tpoid = 3 filter.

But I would NOT recommend creating two indexes. So here are the two recommendations I do have. Pick one.

  1. Create a clustered index on tpoid, Mrfmesano, unoidfilha (in that order!). This is ideal for removing the index spool and might also benefit the other Table Scan.
  2. Create a nonclustered index on Mrfmesano, unoidfilha (in that order!). Include columns Entcodigo, unoid, unoidfilha, entcodigovisualiza, and tpoid. Add WHERE tpoid =3 as the filter.

Again (I can't stress this enough) – you should look at ALL your queries instead of just a single one before deciding on an indexing strategy. But based on just this one query, I would recommend one of the two above.