You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by davida <da...@live.com> on 2017/02/19 18:47:29 UTC
Ignite sub queries performance
Noticed that ApacheIgnite.NET SqlFieldQueries support nested queries, but
after experimenting a bit found that some take too long to complete (testing
on local machine, Reflective serialization, KPICache.GetSize()=10000
objects, all properties/fields are basic types).
Should nested queries be avoided in general (e.g. I can use scan queries or
split this queries) ?
"select ID, Date, Origin, Destination, EmptyDays, LoadedMiles from KPIDetail
where ID in (select LoadedMiles from KPIDetail where TotalMiles > 7000)"
"select ID, Date, Origin, Destination, EmptyDays, LoadedMiles from KPIDetail
where ID in (select distinct TerritoryID from \"SalesCache\".SalesOrder
order by TerritoryID asc)"
Subquery in 2nd case is executed very fast (there is no many objects in
cache SalesOrder ~30K) ~ 125ms: "select distinct TerritoryID from
\"SalesCache\".SalesOrder order by TerritoryID asc"
Thanks.
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Ignite-sub-queries-performance-tp10720.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Ignite sub queries performance
Posted by Denis Magda <dm...@apache.org>.
It worth to have a look at the execution plan first
https://apacheignite-net.readme.io/docs/sql-queries#using-explain <https://apacheignite-net.readme.io/docs/sql-queries#using-explain>
If you see that a query does a full scan boost it by setting indexes:
https://apacheignite-net.readme.io/docs/sql-queries#configuring-sql-indexes-using-queryentity <https://apacheignite-net.readme.io/docs/sql-queries#configuring-sql-indexes-using-queryentity>
If the indexes are properly set then share the execution plan here and exact execution time.
—
Denis
> On Feb 19, 2017, at 10:47 AM, davida <da...@live.com> wrote:
>
> Noticed that ApacheIgnite.NET SqlFieldQueries support nested queries, but
> after experimenting a bit found that some take too long to complete (testing
> on local machine, Reflective serialization, KPICache.GetSize()=10000
> objects, all properties/fields are basic types).
>
> Should nested queries be avoided in general (e.g. I can use scan queries or
> split this queries) ?
>
> "select ID, Date, Origin, Destination, EmptyDays, LoadedMiles from KPIDetail
> where ID in (select LoadedMiles from KPIDetail where TotalMiles > 7000)"
>
> "select ID, Date, Origin, Destination, EmptyDays, LoadedMiles from KPIDetail
> where ID in (select distinct TerritoryID from \"SalesCache\".SalesOrder
> order by TerritoryID asc)"
>
> Subquery in 2nd case is executed very fast (there is no many objects in
> cache SalesOrder ~30K) ~ 125ms: "select distinct TerritoryID from
> \"SalesCache\".SalesOrder order by TerritoryID asc"
>
> Thanks.
>
>
>
> --
> View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Ignite-sub-queries-performance-tp10720.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.