You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@ignite.apache.org by wengyao04 <we...@gmail.com> on 2018/11/08 20:25:23 UTC

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/

Re: How could imporeve UNION ALL performance

Posted by Andrey Mashenkov <an...@gmail.com>.

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 wengyao04@gmail.com:

> 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/
>