1

I have a view with two UNION ALL between three tables with three or more identical columns, but displaying different information. But I want to increase its performance. Is there an alternative more faster than UNION ALL? Thanks in advance.

Here is the actual execution plan of the view.

And the definition of the view

  • 2
    Without more information, like query plans, table and index definitions, and the query you're using, no one is going to be able to answer this. It's far too broad. If you look around the site at questions where folks got help with query tuning, you'll see the kind of stuff they posted to get that help. – Erik Darling Oct 16 '17 at 20:46
  • I edited the question and included the actual execution plan of the view. Thanks for the advice. – KPavezC Oct 16 '17 at 20:53
  • 1
    Are you really just running SELECT * from the UNION ALL. That will simply be the sum of the cost of SELECT * from the underlying tables. – David Browne - Microsoft Oct 16 '17 at 20:57
  • 1
    Can you provide the view definition? I'm curious about some of the compute scalar operators in the plan. – Erik Darling Oct 16 '17 at 21:03
  • Would having multiple readers running across multiple partitions help you out? – Ali Razeghi Oct 16 '17 at 22:59
7

Based on the plan you posted: no. Grabbing the info from the tables is 98% of your query time and actually concatenating them together is less than 1% so the UNION ALL is not actually taking up much of the query time.

0

The problem here isn't the fact that you are using a Union, as indiri said. The problem is simply the size of the tables and the number of rows you are likely bringing back.

Inside that query plan there are a couple missing index suggestion. Not all missing index suggestions are created the same and quite often they go a bit too far - but have you tried that? I would even just look to the equality columns - don't make the "mini-clustered indexes" that the optimizer is suggesting with all of the included columns. How many rows come back? Most of the rows in the table? They may not help, but less than 25% or a lot less? Maybe they will help.

Are your statistics up to date? One of your tables is a heap table - no clustered index at all, is there a great clustered index key candidate there?

Do you need to grab all this data for the calls to this view? I would be spending a bit more time looking at the business problem, the response time needs and look to other solutions, maybe intermediate steps, maybe better indexing, maybe trying to bring back less columns if all columns aren't needed, etc.

But again the Union All isn't the real pain here from the paste the plan like indiri said in the answer I upvoted above.

0

Based on the plan you've posted, the bulk of the time is an index scan on dbo.INV_MOVIMIENTOS, with the predicate on mov_Estado_vigencia. With the number of rows you quote, vs the actual rows shown in the plan, it's likely that an index seek will reduce the run time.

Try applying this index:

CREATE INDEX IX_INV_MOVIMIENTOS_mov_Estado_vigencia ON dbo.INV_MOVIMIENTOS (mov_Estado_vigencia);  

If this isn't faster (it may not be - the optimiser may not choose to use it, and for good reason - it will cause a lookup), try the heavier index, which includes all the data being selected, and therefore a seek with no lookup.

CREATE INDEX IX_INV_MOVIMIENTOS_mov_Estado_vigencia ON dbo.INV_MOVIMIENTOS (mov_Estado_vigencia)
include (
    cli_rut
    ,Emp_Codigo
    ,mov_operacion
    ,tip_codigo
    ,mov_fecha_liquidacion
    ,Mov_Moneda_Liqui
    ,mov_monto_operacion_fondo
    ,mov_Estado_Inversion
    ,mov_Usuario_Operacion
    ,mov_Terminal_PC
    ,mov_fecha
    ,mov_observacion
)

If you choose this index, you will need to check and be ok with the disk space used. There will also be an overhead when rows are modified (inserted/updated/deleted) - again you will need to determine if that is acceptable for your system.

(You should also check the index isn't overlapping another index that already exists. If it does, you may need to consider dropping the other / merging their goals).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.