Hi, You mentioned that all fields are indexed, does this mean that you have only single-field indexes? Do you consider creating group indexes for your queries? Can you share query plans with us?
If you will have proper group indexes for all queries, maybe it worth to try to avoid joins at all and store everything in one table with flat structure. As for loading - it's too long as for me, how do you insert data? As Ilya mentioned, it worth to enable streaming mode( https://apacheignite-sql.readme.io/docs/set), and, if you use persistence, disable WAL at the moment of data loading. Best Regards, Evgenii вс, 25 нояб. 2018 г. в 23:13, Ilya Kasnacheev <[email protected]>: > Hello! > > I think you should be using Streaming Mode or Data Streamer to insert data > for SQL, which you seem to already do. > > Regards, > -- > Ilya Kasnacheev > > > сб, 24 нояб. 2018 г. в 16:49, joseheitor <[email protected]>: > >> Given the following model structure: >> >> { >> "trans": { >> "cust": { >> "firstname": "Bone", >> "lastname": "Klebes", >> "email": "[hidden email]", >> "gender": "Male" >> }, >> "ipaddress": "104.89.149.184", >> "date": "2017-12-01", >> "amount": 1217, >> "currency": "NOK" >> } >> } >> >> What is the recommended storage strategy for optimum querying? >> (example: date='2018-12-01' and lastname='Klebes' and amount > 1000) >> >> I have tried the following strategies on a partitioned, two-node cluster >> having 1 000 000 documents, with the following results: >> >> 1. - DataGrid: >> One document per record >> Using IgniteBiPredicate in ScanQuery >> Time taken: ~1.5 sec >> Inserts are quite fast (1 000 000 documents = 1 000 000 records = 35 >> min) >> >> 2. - SQL: >> Table structure - [id(PK)], [doc_id], [key], [val] (all columns >> indexed) >> One record for each field (8 records per document) >> Using nested JDBC JOIN query >> Time taken: ~20ms (PostgreSQL comparison = ~10ms) >> Inserts are very slow (1 000 000 documents = 8 000 000 records = 10 >> hours) >> >> Is there another strategy that I should consider? Or any techniques that I >> can use to optimise the queries? >> >> Thanks. >> >> >> >> -- >> Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >> >
