To be clear: query entities is how the SQL engine work (missing out some details but that’s broadly correct as I understand it!). Using CREATE TABLE and IgniteCache#setIndexTypes() with the annotations are both shortcuts that generate the query entities for you. You can verify this for yourself by creating your cache and then viewing the configuration of the created table with IgniteCache#getConfiguration().
I can’t explain your results. It depends on the number of rows in your table, the number of nodes, what query you were using... > On 18 Jun 2020, at 14:46, R S S Aditya Harish <aditya.har...@zohocorp.com> > wrote: > > Thanks for the reply. > > I understand. I have one more doubt. I don't want to go with QueryEntities > way. I want to go via Annotation based way using java classes. In that case, > I want to create index on score and appnameid columns as a composite index. > But what I found is, without creating index at all for fields in that class, > I am getting good results for sql order by query. How is that possible? I am > using whatever way you suggested for inserting rows.. And for querying i'm > just using normal SqlFieldsQuery api. I am getting good result without index > on fields. > > Thank you, > R S S Aditya Harish > +91-8106278997 | Ext No. 4433 > <1571817034225000_1337245575.jpeg> > > > > ---- On Thu, 18 Jun 2020 16:42:54 +0530 Stephen Darlington > <stephen.darling...@gridgain.com> wrote ---- > > You don’t need the CREATE statement as well. If you want to do it instead of > the Query Entities you can do: > > CREATE TABLE IF NOT EXISTS URLS > (ID LONG PRIMARY KEY, URL VARCHAR, SCORE DOUBLE, APPNAME_ID LONG) > WITH ‘key_type=AppDetailsKey,value_type=AppDetails’ > > You still need the Java classes but you can skip the annotations. > > https://apacheignite-sql.readme.io/docs/create-table > <https://apacheignite-sql.readme.io/docs/create-table> > > On 18 Jun 2020, at 11:57, R S S Aditya Harish <aditya.har...@zohocorp.com > <mailto:aditya.har...@zohocorp.com>> wrote: > > Hi Stephen, > > Thank you so much for giving the snippet. I followed and it worked. But I > have one more doubt. In the below section the query is insertion query and it > works fine. We are using the JDBC for Create statement also. We are having > trouble understanding how to execute create a statement using the above > QueryEntity way. Can you please shed some light on it? An example snippet for > the statement "CREATE TABLE IF NOT EXISTS URLS ID LONG PRIMARY KEY, URL > VARCHAR, SCORE DOUBLE, APPNAME_ID LONG)" would be grateful. > > Thank you, > R S S Aditya Harish > +91-8106278997 | Ext No. 4433 > <1571817034225000_1337245575.jpeg> > > > > ---- On Tue, 16 Jun 2020 19:24:44 +0530 Stephen Darlington > <stephen.darling...@gridgain.com <mailto:stephen.darling...@gridgain.com>> > wrote ---- > > There’s not one, right way of doing it. In Java it’s something like this. > > Define your classes: > > public class AppDetailsKey { > @QuerySqlField > private Long id; > > public AppDetailsKey(Long id) { > this.id <http://this.id/> = id; > } > } > > public class AppDetails { > @QuerySqlField > private String url; > @QuerySqlField > private Double score; > @QuerySqlField > private Long app_name; > > public AppDetails(String url, Double score, Long app_name) { > this.url = url; > this.score = score; > this.app_name = app_name; > } > } > > (I didn’t define your secondary index but you can do that with the > annotations, too.) > > Create your cache: > > CacheConfiguration<AppDetailsKey, AppDetails> cacheConfiguration = new > CacheConfiguration<>(); > cacheConfiguration.setSqlSchema("PUBLIC") > .setName("APPDETAILS") > .setIndexedTypes(AppDetailsKey.class, AppDetails.class); > > IgniteCache<AppDetailsKey, AppDetails> cache = > ignite.getOrCreateCache(cacheConfiguration); > > The annotations and the IndexedTypes tell Ignite to make it available to the > SQL engine. > > And then insert stuff into it: > > IgniteDataStreamer<AppDetailsKey,AppDetails> ds = > ignite.dataStreamer("APPDETAILS"); > ds.addData(new AppDetailsKey(1L), new AppDetails("localhost", 1.0, 10L)); > ds.addData(new AppDetailsKey(2L), new AppDetails("localhost", 1.0, 10L)); > ds.addData(new AppDetailsKey(3L), new AppDetails("localhost", 1.0, 10L)); > ds.flush(); > > On 16 Jun 2020, at 06:35, R S S Aditya Harish <aditya.har...@zohocorp.com > <mailto:aditya.har...@zohocorp.com>> wrote: > > This is my SQL table schema > > ID (Long), URL (Varchar), SCORE (Double), APPNAME_ID (Long) > > We have a composite index on Score, Appname_Id. > > Based on your answer I've two questions. > > 1. How can I insert SQL rows using JCache data streamer API (if possible, > with example)? Currently, I'm using jdbc thin with STREAMING ON. But the > issue is mentioned above. > 2. Each row data is -> ID (Long), URL (Varchar), SCORE (Double), APPNAME_ID > (Long). How this data is stored as Key-Value? I mean what will be the key and > what will be the value? > > Can you please answer these two questions? > > > ---- On Mon, 15 Jun 2020 21:44:38 +0530 Stephen Darlington > <stephen.darling...@gridgain.com <mailto:stephen.darling...@gridgain.com>> > wrote ---- > > Do you need the sorting as part of the loading process? If not, the best > route would be to use the data streamer to load the data. You can still use > the SQL engine and access your sorted data afterwards — remember that SQL and > key-value are two different ways of accessing the same underlying data. > > > On 15 Jun 2020, at 15:46, adipro <aditya.har...@zohocorp.com > > <mailto:aditya.har...@zohocorp.com>> wrote: > > > > We have an SQL table which we need because for normal JCache K-V we cannot > > sort on some column's data. We need that sort feature. That's why we chose > > SQL table representation. > > > > Our application is heavily multi-threaded. > > > > Now when trying to insert rows in that table, each thread simultaneously > > sends 5000-10000 rows in bulk. Now if we use, SqlFieldsQuery, it's taking > > so > > much of time as we cannot do it in bulk and have to do it in loop one by > > one. > > > > For this case, we are using JDBC thin driver. > > > > But since it's multi-threaded we can't use single connection to execute in > > parallel as it is not thread safe. > > > > So, what we did is, we added a synchronisation block which contains the > > insertion of those rows in bulk using thin driver. The query performance is > > good, but so many threads are in wait state as this is happening. > > > > Can someone please suggest any idea on how to insert those many rows in > > bulk > > efficiently without threads waiting for so much time to use JDBC > > connection. > > > > > > > > -- > > Sent from: http://apache-ignite-users.70518.x6.nabble.com/ > > <http://apache-ignite-users.70518.x6.nabble.com/> > > > > > > > > > > > >
