Ignite query performance

[suds123 <su...@gmail.com>]

I'm evaluating apache ignite for a use case very similar to the setup
described at -
https://dzone.com/articles/apache-ignite-how-to-read-data-from-persistent-sto

1. Instead of Person I'm using Transaction for the proof-of-concept, so I
have followed the above link to create 3 source files - Transaction.java,
TransactionStore.java and TransactionStoreExample.java. 

2. I'm loading cache from an Oracle database. The Transaction table has 2
million rows, each row having 30 fields and average row size is 300 bytes. 

3. This is my query in the TransactionStoreExample.java 

QueryCursor<List&lt;?>> cursor = cache.query(new SqlFieldsQuery("select id,
transactionAmount from Transaction where groupCode = ' ' and scheme = ' '
and transactionDate > '<from date>' and transactionDate < '<from date>' and
mid in ( )  order by transactionDate asc limit 100 offset 100"));

4. groupCode, scheme, transactionDate, and mid are indexed in model
Transaction.java 

5. My infrastructure is a single VM on Google Cloud (4 CPUs and 20 GB RAM)
and as per dzone example I start a server instance with spring config file
and a client instance where I run the TransactionStoreExample class. 

6. I'm timing the query by printing timestamps before and after
cursor.getAll().

7. For 2 million records I get a 1770 millis response time. 

My questions are - 
 - is this response timing expected for given setup? 
 - can this be improved? 
 - is there another way to capture query timings instead of printing
timestamps before and after cursor.getAll()?

Thanks





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

Re: Ignite query performance

[Andrey Mashenkov <an...@gmail.com>]

Hi,

1. Please, check if index is used for field you use in order by clause. You
can check this with EXPLAIN command [1].
If you see wrong index is used, then try hint [2] ignite for proper index.

2. FYI: there is limit and offset in query. For partitioned cache, Ignite
will fetch 200 (limit + offset) from each remote nodes and then merge
results and then apply limits.
So, actually to return 100 entries, Ignite have to fetch 200 entries per
node.

3. IN clause is used in query which may be inefficient due to lack of
underlying H2 optimizer.
Try to replace it with JOIN [3].

4. Also, conditions on Date\Time values can bring a slowdown (seems, due to
lack of H2).
It is recommended to use Long value (e.g. unix timestamp) instead of dates
if possible. It looks ok for historical date\time values.
Of cource, for time in future it is a bad idea as timezones can be changed
later.


[1]
https://apacheignite.readme.io/docs/sql-performance-and-debugging#section-using-explain-statement
[2]
https://apacheignite-sql.readme.io/docs/performance-and-debugging#section-index-hints
[3]
https://apacheignite-sql.readme.io/docs/performance-and-debugging#section-sql-performance-and-usability-considerations



On Sun, Jan 28, 2018 at 3:59 PM, suds123 <su...@gmail.com> wrote:

> I'm evaluating apache ignite for a use case very similar to the setup
> described at -
> https://dzone.com/articles/apache-ignite-how-to-read-
> data-from-persistent-sto
>
> 1. Instead of Person I'm using Transaction for the proof-of-concept, so I
> have followed the above link to create 3 source files - Transaction.java,
> TransactionStore.java and TransactionStoreExample.java.
>
> 2. I'm loading cache from an Oracle database. The Transaction table has 2
> million rows, each row having 30 fields and average row size is 300 bytes.
>
> 3. This is my query in the TransactionStoreExample.java
>
> QueryCursor<List&lt;?>> cursor = cache.query(new SqlFieldsQuery("select id,
> transactionAmount from Transaction where groupCode = ' ' and scheme = ' '
> and transactionDate > '<from date>' and transactionDate < '<from date>' and
> mid in ( )  order by transactionDate asc limit 100 offset 100"));
>
> 4. groupCode, scheme, transactionDate, and mid are indexed in model
> Transaction.java
>
> 5. My infrastructure is a single VM on Google Cloud (4 CPUs and 20 GB RAM)
> and as per dzone example I start a server instance with spring config file
> and a client instance where I run the TransactionStoreExample class.
>
> 6. I'm timing the query by printing timestamps before and after
> cursor.getAll().
>
> 7. For 2 million records I get a 1770 millis response time.
>
> My questions are -
>  - is this response timing expected for given setup?
>  - can this be improved?
>  - is there another way to capture query timings instead of printing
> timestamps before and after cursor.getAll()?
>
> Thanks
>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>



-- 
Best regards,
Andrey V. Mashenkov