Query performance affected by record size?

[colinc <co...@yahoo.co.uk>]

I have a use case where I would like to store a record that has some
QuerySqlField attributes but also a non-queriable list of child objects.
Something like this:

public class Portfolio {
    @QuerySqlField(index = true)
    private int portCode;
   
    @QuerySqlField
    private String fullName;

    // Other query fields

    private List<Position> positions;
}

The idea is that the cache can be queried for summary data using a
SqlFieldsQuery, or the whole object can be retrieved and processed by a
task.

I anticipated that performing a SqlFieldsQuery on indexed fields would
always be fast - as there is no need to deserialise the positions. However,
tests indicate that when the cache size exceeds 1M positions (say 1000
portfolios, 1000 positions), the queries become very slow - at least an
order of magnitude slower than would be the case with small numbers of
positions.

Is the above a valid usecase? Are we getting something wrong, or is it
necessary to split the Portfolio into two separate caches?

Thanks in advance,
Colin.



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

Re: Query performance affected by record size?

[afedotov <al...@gmail.com>]

Hi,

Probably you have pretty much data that satisfies the condition.
Try running EXPLAIN command to check the query plan.

In general, it's better to move child objects into a separate cache and
establish
a relation via  affinity
<https://apacheignite.readme.io/v2.2/docs/affinity-collocation#section-collocate-data-with-data>  

Kind regards,
Alex



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

Re: Query performance affected by record size?

[colinc <co...@yahoo.co.uk>]

isBenchmark is indexed by the way:

    @QuerySqlField(index = true)
    private boolean isBenchmark;



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

Re: Query performance affected by record size?

[colinc <co...@yahoo.co.uk>]

One like this:

select portCode, benchCode, shortName, benchShortName, fullName, currency,
operatingCurrency
from PORTFOLIO.Portfolio 
where ISBENCHMARK = true




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

Re: Query performance affected by record size?

[afedotov <al...@gmail.com>]

Hi,

Could you please share the queries you run against the cache?



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