subject:"Multi\-threaded JDBC driver issue\/concern"

Multi-threaded JDBC driver issue/concern

2020-06-15 Thread adipro

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-1 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/

Re: Multi-threaded JDBC driver issue/concern

2020-06-15 Thread Stephen Darlington

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  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-1 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/

Re: Multi-threaded JDBC driver issue/concern

2020-06-15 Thread R S S Aditya Harish

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 
 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  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-1 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/

Re: Multi-threaded JDBC driver issue/concern

2020-06-16 Thread Stephen Darlington

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 = 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 cacheConfiguration = new 
CacheConfiguration<>();
cacheConfiguration.setSqlSchema("PUBLIC")
.setName("APPDETAILS")
.setIndexedTypes(AppDetailsKey.class, AppDetails.class);

IgniteCache 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 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  > 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 
> 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  > > 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-1 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/ 
> >  
> 
> 
> 
>

Re: Multi-threaded JDBC driver issue/concern

2020-06-16 Thread adipro

Hi stephen. Thanks so much for reply.

This is my SQL table schema

ID (Long), URL (Varchar), SCORE (Double), APPNAME_ID (Long)

We have 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 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?



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

Re: Multi-threaded JDBC driver issue/concern

2020-06-16 Thread adipro

Hi stephen. Thanks so much for reply.

This is my SQL table schema

ID (Long), URL (Varchar), SCORE (Double), APPNAME_ID (Long)

We have 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 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?



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

Re: Multi-threaded JDBC driver issue/concern

2020-06-16 Thread adipro



This is my SQL table schema

ID (Long), URL (Varchar), SCORE (Double), APPNAME_ID (Long)

We have 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 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?



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

Re: Multi-threaded JDBC driver issue/concern

2020-06-18 Thread R S S Aditya Harish

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

 On Tue, 16 Jun 2020 19:24:44 +0530 Stephen Darlington 
 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) {
        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 cacheConfiguration = new 
CacheConfiguration<>();
cacheConfiguration.setSqlSchema("PUBLIC")
        .setName("APPDETAILS")
        .setIndexedTypes(AppDetailsKey.class, AppDetails.class);

IgniteCache 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 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 
 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 
 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  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-1 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/

Re: Multi-threaded JDBC driver issue/concern

2020-06-18 Thread Stephen Darlington

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

> On 18 Jun 2020, at 11:57, R S S Aditya Harish  
> 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 
>  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  = 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 cacheConfiguration = new 
> CacheConfiguration<>();
> cacheConfiguration.setSqlSchema("PUBLIC")
> .setName("APPDETAILS")
> .setIndexedTypes(AppDetailsKey.class, AppDetails.class);
> 
> IgniteCache 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 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  > 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 
> 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  > > 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-1 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 
> > connecti

Re: Multi-threaded JDBC driver issue/concern

2020-06-18 Thread R S S Aditya Harish

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

 On Thu, 18 Jun 2020 16:42:54 +0530 Stephen Darlington 
 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

On 18 Jun 2020, at 11:57, R S S Aditya Harish 
 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 
 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) {
        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 cacheConfiguration = new 
CacheConfiguration<>();
cacheConfiguration.setSqlSchema("PUBLIC")
        .setName("APPDETAILS")
        .setIndexedTypes(AppDetailsKey.class, AppDetails.class);

IgniteCache 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 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 
 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 
 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  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-1 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. 
>

Re: Multi-threaded JDBC driver issue/concern

2020-06-18 Thread Stephen Darlington

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  
> 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 
>  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 
> 
> 
> On 18 Jun 2020, at 11:57, R S S Aditya Harish  > 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 
> 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  = 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 cacheConfiguration = new 
> CacheConfiguration<>();
> cacheConfiguration.setSqlSchema("PUBLIC")
> .setName("APPDETAILS")
> .setIndexedTypes(AppDetailsKey.class, AppDetails.class);
> 
> IgniteCache 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 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  > 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 Darlingto

Re: Multi-threaded JDBC driver issue/concern

2020-06-18 Thread R S S Aditya Harish

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










 On Thu, 18 Jun 2020 20:14:44 +0530 Stephen Darlington 
 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 
 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 
 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



On 18 Jun 2020, at 11:57, R S S Aditya Harish 
 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 
 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) {
        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;

Re: Multi-threaded JDBC driver issue/concern

2020-06-18 Thread Stephen Darlington

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  
> 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 
>  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  > 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 
> mailto: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 
> 
> 
> On 18 Jun 2020, at 11:57, R S S Aditya Harish  > 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 
> mailto:stephen.darling...@gridgain.com>> 
> wrote 
> 
> There’s not one, right way of doing it. In Java it’s someth

Multi-threaded JDBC driver issue/concern

Re: Multi-threaded JDBC driver issue/concern

Re: Multi-threaded JDBC driver issue/concern

Re: Multi-threaded JDBC driver issue/concern

Re: Multi-threaded JDBC driver issue/concern

Re: Multi-threaded JDBC driver issue/concern

Re: Multi-threaded JDBC driver issue/concern

Re: Multi-threaded JDBC driver issue/concern

Re: Multi-threaded JDBC driver issue/concern

Re: Multi-threaded JDBC driver issue/concern

Re: Multi-threaded JDBC driver issue/concern

Re: Multi-threaded JDBC driver issue/concern

Re: Multi-threaded JDBC driver issue/concern

13 matches

Site Navigation

Mail list logo

Footer information