ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lucky <wanxing...@163.com>
Subject Re:Re: Re: Fetched result use too much time
Date Wed, 11 Oct 2017 09:32:06 GMT

My ignite version is 2.2.





在 2017-10-10 20:58:55,"Andrey Mashenkov" <andrey.mashenkov@gmail.com> 写道:

Hi Lucky,


Looks like your query selectivity is poor and even with GroupBy large amount of data shoud
be fetched to reduce node.


1. Is it possiblt to coolocate data on field used in OrderBy clause?
2. Looks weird that queryParallelizm cause wrong results. Looks like you have a single node
grid and there is a bug in queryParallelizm feature.
 Also I can find what ignite version you use. Would you try to switch to the latest one?


On Tue, Oct 10, 2017 at 2:48 PM, Lucky <wanxing987@163.com> wrote:

Andrey Mashenkov
    Thank you very much!
    1.query parallelism:this will cause a problem: fetch wrong reslut. 
       I set it to 10,and have table a with 150,000 records, table b with 12,000,000 records.
       when I query single table,the result is correct.
       but when the sql is like this:
           select a.id from a inner join b on a.id = b.tid 
      it got the wrong result. The result should be 11,000,000;but it just return 380,000
records.
      when I remove query parallelism setting,it return correctly. 


    2. I have modified ths property,and restart the server.for the record is too large, it
need 4 hours to load data to ignite.So I have to wait.
    3.Actually, if I remove the group by clause and having condition, it took more time!
    4  and 5: I have try them before ,but it did not work.
Thanks again.
Lucky 





At 2017-09-21 21:28:40, "Andrey Mashenkov" <andrey.mashenkov@gmail.com> wrote:

Lucky,




1. Looks like it make no sense to set query parallelism level higher number of available CPU
on node.


2. Map query use index for field FASSIGCUID type of String and seems values are 16 chars length
strings (32 bytes)
By default, values with size < 10 bytes can be inlined in index, so Ignite doesn't need
to lookup a data page for value data. 
You can try to increase it up to 32 via cacheConfiguration.setSqlIndexMaxInlineSize(32) or
JVM property -DIGNITE_MAX_INDEX_PAYLOAD_SIZE=32.


3. Ignite doesn't know whether your data is collocated by FDATABASEDID (group by clause) or
not collocated.
So, Ignite can't apply HAVING condition instantly on map phase and have to load and merge
all groups from all nodes before check for HAVING.
If it possible to collocate data on GROUP BY condition, you can hint Ignite with setting query
flag:   sqlFieldsQuery.setCollocated(true).
However, I'm not sure it will help much and H2 will be able to make any optimization here.


4. Also, you can force Ignite to use different index. E.g. group index on FDATABASEDID and
FASSIGCUID and same fields in different order.


5. Sometimes, Ignite change join order and it can cause unexcpected slowdown. You can try
to change join order by changing tables positions in query string.
To preserve Ignite join order optimization you may use a flag:  sqlFieldsQuery.setEnforceJoinOrder(true).




Hope, this will help you.




--

Best regards,
Andrey V. Mashenkov
Mime
View raw message