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

Posted to user@ignite.apache.org by neerajbhatt <ne...@gmail.com> on 2017/07/11 11:39:53 UTC

query performance degrade when adding a filter criteria (order by)

Hi All

Our below  query is working fine SELECT  r.uniqueSkuId FROM A_CACHE.Rank as
r, B_CACHE.Item as T  WHERE T.id = r.id  AND T.b=? AND T.c=? AND T.d=? order
by r.rank desc limit 3

but when we add additional criteria (r.a=?) as below the time increase from
10 ms to ~500 ms


SELECT  r.uniqueSkuId FROM A_CACHE.Rank as r, B_CACHE.Item as T  WHERE T.id
= r.id  AND r.a=? AND T.b=? AND T.c=? AND T.d=? order by r.rank desc limit 3


Thanks





--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/query-performance-degrade-when-adding-a-filter-criteria-order-by-tp14624.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: query performance degrade when adding a filter criteria (order by)

Posted by vkulichenko <va...@gmail.com>.

Hi,

Did you check execution plans?

-Val



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/query-performance-degrade-when-adding-a-filter-criteria-order-by-tp14624p15023.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: query performance degrade when adding a filter criteria (order by)

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

Hi,

Possibly, H2 planned use different indices for these queries.
Try to use HINTS [1] if it is impossible to make H2 use proper index
without breaking other queries.

[1]
https://apacheignite.readme.io/docs/sql-performance-and-debugging#section-index-hints

On Tue, Jul 11, 2017 at 2:47 PM, neerajbhatt <ne...@gmail.com>
wrote:

> Also field a in A_CACHE.Rank (r) is indexed
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/query-performance-degrade-when-
> adding-a-filter-criteria-order-by-tp14624p14625.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>

-- 
Best regards,
Andrey V. Mashenkov

Re: query performance degrade when adding a filter criteria (order by)

Posted by neerajbhatt <ne...@gmail.com>.

Also field a in A_CACHE.Rank (r) is indexed



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/query-performance-degrade-when-adding-a-filter-criteria-order-by-tp14624p14625.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.