question

Mischa Spelt avatar image
0 Likes"
Mischa Spelt asked Ben Wilson commented

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?

FlexSim 21.0.3
flexsim 21.0.3
union-with-field.fsm (77.1 KiB)
· 1
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Ben Wilson avatar image Ben Wilson ♦♦ commented ·

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 Likes 0 ·

1 Answer

anthony.johnson avatar image
1 Like"
anthony.johnson answered anthony.johnson edited

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(n2) 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 :-).

5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.