UNION query is very slow

Question

question

Mischa Spelt asked Apr 20, '21 Ben Wilson commented Apr 26, '21

UNION query is very slow

I've noticed some interesting behavior when I tried to run a UNION query ~~with a custom constant field~~.

Consider the attached model, Union_With_Field.fsm (2021.0.3). It fills a statistics collector ProcessorStaytimevsTime and a calculated table based on that.

When executing the query using the timing script at the bottom, the query takes about 2700 ms on my computer:

SELECT Object, Time, Staytime FROM ProcessorStaytimevsTime WHERE Time > 0
UNION 
SELECT Object, Time, Staytime FROM ProcessorStaytimevsTime WHERE Time < 0

Fun fact, the second part (WHERE Time < 0) does not even contribute any rows in this case. When I remove it and just run

SELECT Object, Time, Staytime FROM ProcessorStaytimevsTime WHERE Time > 0

it updates almost instantly (2 ms according to the script).

In the actual model where I found this, I had a table with three data fields (Time, A, B, C) and I was using a double UNION to turn this into a table with columns Time, Type, Value (where Type = "A", "B" or "C") so I can plot A, B and C as three series in the same graph. That query takes 60 seconds (!) to complete.

Question 1: Is it possible to optimize this (I assume that it becomes so slow because the UNION forces it to copy the whole result set somewhere, but I'm sure it is possible to improve).

Question 2: Is there a 'pivot' function in FlexSim 2021, that allows me to plot my three columns A, B and C as three series in the same graph without the calculated table?

Software Version:

FlexSim 21.0.3

flexsim 21.0.3

union-with-field.fsm (77.1 KiB)

· 1

Ben Wilson ♦♦ commented · Apr 26, 2021 at 09:53 PM

Hi @Mischa Spelt, was anthony.johnson's answer helpful? If so, please click the red "Accept" button at the bottom of their answer. Or if you still have questions, add a comment and we'll continue the conversation.

If we haven't heard back from you within 3 business days we'll auto-accept an answer, but you can always unaccept and comment back to reopen your question.

0 ·

Answer 1 · 2021-04-21T17:56:49Z

anthony.johnson answered Apr 21, '21 anthony.johnson edited Apr 21, '21

The reason that UNION is so slow is because UNION must ensure that the result values are unique, and I have not yet implemented optimizations to make that faster. This means, for each new row it adds to the result set, it must go through each existing row in the result set and make sure the new row is not equal to the existing row. The end result is O(n²) execution time. In your case, the UNION is doing an incredible amount of useless comparisons, because the Shift column ensures uniqueness between the two joined sets.

To avoid this, you can use UNION ALL, which doesn't need to ensure unique values.

On question 2, I refer you to @Jordan Johnson :-).

question