How could imporeve UNION ALL performance

classic Classic list List threaded Threaded
2 messages Options
wengyao04 wengyao04
Reply | Threaded
Open this post in threaded view
|

How could imporeve UNION ALL performance

Hi we use ignite as our searching backend.
Our cache is in replicated mode and primary_sync. Our query is more like the
following. We find that that the query time is the sum of two sub-select
query. Since our two sub select query is independent, is there a way to run
them in parallel ? Thanks

SELECT market.id, market.name, market.lastPrice
FROM ((
           SELECT marketR.id, marketR.name, marketR.lastPrice
           FROM STOCK marketR
           JOIN TABLE(exchange  int = ?) ON i.exchange = marketR.exchange
           WHERE marketR.subscription = 'TRUE' AND marketR.last > 50.
           )
           UNION ALL
           (
            SELECT marketD.id, marketD.name, marketD.lastPrice
            FROM STOCK marketD
            JOIN TABLE(exchange  int = ?) ON i.exchange = marketD.exchange
            WHERE marketD.subscription = 'FALSE' AND marketD.lastPrice > 50.
           ))
market ORDER BY market.lastPrice DESC LIMIT 5000



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Andrew Mashenkov Andrew Mashenkov
Reply | Threaded
Open this post in threaded view
|

Re: How could imporeve UNION ALL performance

Hi,
 Can you share a query plan?
Do you have any indices on last and lastprice columns? Have you tried to run queries separately without Union?


чт, 8 нояб. 2018 г., 23:47 wengyao04 [hidden email]:
Hi we use ignite as our searching backend.
Our cache is in replicated mode and primary_sync. Our query is more like the
following. We find that that the query time is the sum of two sub-select
query. Since our two sub select query is independent, is there a way to run
them in parallel ? Thanks

SELECT market.id, market.name, market.lastPrice
FROM ((
           SELECT marketR.id, marketR.name, marketR.lastPrice
           FROM STOCK marketR
           JOIN TABLE(exchange  int = ?) ON i.exchange = marketR.exchange
           WHERE marketR.subscription = 'TRUE' AND marketR.last > 50.
           )
           UNION ALL
           (
            SELECT marketD.id, marketD.name, marketD.lastPrice
            FROM STOCK marketD
            JOIN TABLE(exchange  int = ?) ON i.exchange = marketD.exchange
            WHERE marketD.subscription = 'FALSE' AND marketD.lastPrice > 50.
           ))
market ORDER BY market.lastPrice DESC LIMIT 5000



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Regards,
Andrew.