Make sure you do EXPLAIN PLAN to see if it’s using an index. I don’t think it is. If you put the composite index order the other way around it I suspect it would use the index.
> On 18 Jun 2020, at 16:01, R S S Aditya Harish <[email protected]> > wrote: > > Okay. What I meant is we don't want to give QueryEntities in XML way. We want > to give it via Client side Java application way. I understood your point. > > I want to give further explanation > > Following is the value class. We wanted a composite index on score and > appname_id so we created in this way. > > class Value{ > > ---- > > @QuerySqlField > private String url; > > @QuerySqlField (orderedGroups={@QuerySqlField.Group( > name = "score_app_idx", order = 0)})//No I18N > private double score; > > @QuerySqlField (index = true, orderedGroups={@QuerySqlField.Group( > name = "score_app_idx", order = 1)})//No I18N > private long appname_id; > > ----- > > } > > I am inserting as you mentioned in previous mail for "AppDetails" class using > datastreamer. > > How I am querying is by using SqlFieldsQuery as following: > > -> new SqlFieldsQuery("SELECT URL FROM URLS WHERE APPNAME_ID = ? ORDER BY > SCORE LIMIT ?"); > > We have checked by doing this way and found that it is giving an average of > 1.5-2 msec for a run of 20 minutes on 0.7 to 1 million record data. > > Now we changed the value class in this format > > class Value{ > > ---- > > @QuerySqlField > private String url; > > @QuerySqlField > private double score; > > @QuerySqlField > private long appname_id; > > ----- > > } > > We have checked by doing this way and even now for the same record count we > are getting read times of about 2msec. > > Am I missing something? Please shed some light on the way we need to create > index for the above read query. > > Thank you, > R S S Aditya Harish > +91-8106278997 | Ext No. 4433 > <1571817034225000_1337245575.jpeg> > > > > ---- On Thu, 18 Jun 2020 20:14:44 +0530 Stephen Darlington > <[email protected]> wrote ---- > > 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 <[email protected] > <mailto:[email protected]>> 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 > <[email protected] <mailto:[email protected]>> > 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 <[email protected] > <mailto:[email protected]>> 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 > <[email protected] <mailto:[email protected]>> > 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 <[email protected] > <mailto:[email protected]>> 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 > <[email protected] <mailto:[email protected]>> > 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 <[email protected] > > <mailto:[email protected]>> 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/> > > > > > > > > > > > > > > > >
