You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Lucky <wa...@163.com> on 2017/11/24 09:24:48 UTC
Poor performance select query with jdbc thin mode
Hi:
I need to return a ResultSet, So I had to use jdbc thin mode to execute select query.
But the performance is not acceptable!
The sql and explain is like this:
Two tables both have 1,500,000 rows and about 15 columns.
It took 3 seconds in single ignite node ,and took 6 seconds in cluster(3 ignite nodes).
But if i execute the sql with cache.query(new SQLFieldsQuery(sql)) ,it just took 400ms in single ignite node , and took 200ms in cluster(3 ignite nodes).
Ignite version is 2.2.
What suggestions?
Thanks.
Re:Re: Re: Re: Re: Re: Re: Re: Re:Poor performance select query
with jdbc thin mode
Posted by Lucky <wa...@163.com>.
Denis,
Thanks very much!
All data are related ,and there are about 60G data in the heap. And if I add other business module ,the data also are related.We can not split them.
So If data is collocated, then a single node is enough. But it's not high availability.
At 2017-12-06 18:06:05, "Denis Mekhanikov" <dm...@gmail.com> wrote:
Looks like you ran this query on a cluster with more than one node, so you get performance drop because of distributed joins.
Let's tune performance on a single node first. You can expect queries with joins to operate fast only if data is collocated.
Otherwise latency will suffer from network communication.
So, what is performance and plan of query execution on a single node?
Denis
Re: Re: Re: Re: Re: Re: Re: Re: Re:Poor performance select query with
jdbc thin mode
Posted by Denis Mekhanikov <dm...@gmail.com>.
Lucky,
Please provide explain and timing for the same query, executed on a single
node.
Denis
ср, 6 дек. 2017 г. в 16:27, Lucky <wa...@163.com>:
> well,3 node took about double time of single node.
>
Re:Re: Re: Re: Re: Re: Re: Re: Re:Poor performance select query
with jdbc thin mode
Posted by Lucky <wa...@163.com>.
well,3 node took about double time of single node.
Re: Re: Re: Re: Re: Re: Re: Re:Poor performance select query with
jdbc thin mode
Posted by Denis Mekhanikov <dm...@gmail.com>.
Lucky,
You can find information about distributed joins tuning here:
https://apacheignite-sql.readme.io/docs/distributed-joins
It contains explanation of how distributed joins work, and why it's
important to keep data collocated.
Denis
ср, 6 дек. 2017 г. в 13:06, Denis Mekhanikov <dm...@gmail.com>:
> Looks like you ran this query on a cluster with more than one node, so you
> get performance drop because of distributed joins.
>
> Let's tune performance on a single node first. You can expect queries with
> joins to operate fast only if data is collocated.
> Otherwise latency will suffer from network communication.
>
> So, what is performance and plan of query execution on a single node?
>
> Denis
>
> ср, 6 дек. 2017 г. в 12:06, Lucky <wa...@163.com>:
>
>> explain :
>>
>>
>> SELECT
>> T0__Z0.FID AS __C0_0,
>> T0__Z0.FCUSTOMERGROUPSTANDARDID AS __C0_1,
>> T0__Z0.FCUSTOMERGROUPID AS __C0_2,
>> T0__Z0.FCUSTOMERID AS __C0_3,
>> T0__Z0.FCUSTOMERGROUPFULLNAME AS __C0_4
>> FROM "csspGroupStandardCache".CSSPGROUPSTANDARD T1__Z1
>> /* "csspGroupStandardCache".CSSPGROUPSTANDARD_FID_IDX: FID =
>> '00000000-0000-0000-0000-000000000002BC122A7F' */
>> /* WHERE T1__Z1.FID = '00000000-0000-0000-0000-000000000002BC122A7F'
>> */
>> INNER JOIN "customerGroupDetailCache".CUSTOMERGROUPDETAIL T0__Z0
>> /* batched:broadcast
>> "customerGroupDetailCache".CUSTOMERGROUPDETAIL_FCUSTOMERGROUPSTANDARDID_IDX:
>> FCUSTOMERGROUPSTANDARDID = T1__Z1.FID */
>> ON 1=1
>> /* WHERE T0__Z0.FCUSTOMERGROUPSTANDARDID = T1__Z1.FID
>> */
>> INNER JOIN "customerCache".CUSTOMERIGNITEINFO T3__Z2
>> /* batched:broadcast "customerCache".CUSTOMERIGNITEINFO_FID_IDX: FID
>> = '3NsAAABI7bq/DAQO'
>> AND FID = T0__Z0.FCUSTOMERID
>> */
>> ON 1=1
>> WHERE ((T3__Z2.FID = '3NsAAABI7bq/DAQO')
>> AND (T1__Z1.FID = '00000000-0000-0000-0000-000000000002BC122A7F'))
>> AND ((T0__Z0.FCUSTOMERID = T3__Z2.FID)
>> AND (T0__Z0.FCUSTOMERGROUPSTANDARDID = T1__Z1.FID))
>>
>>
>>
>>
>
Re: Re: Re: Re: Re: Re: Re: Re: Re:Poor performance select query with
jdbc thin mode
Posted by Denis Mekhanikov <dm...@gmail.com>.
Lucky,
If you want to run queries with *JOIN*s, you should make sure, that entries
are collocated. It means, that all data for a* JOIN* should be available on
a single node.
You may have many nodes, but each row of the result should be composed of
the data, that is stored within a single data node.
Here is the section in documentation, that explains this approach:
https://apacheignite-sql.readme.io/docs/distributed-joins#section-collocated-joins
To achieve data collocation you can configure affinity keys for your data.
See https://apacheignite.readme.io/docs/affinity-collocation
So, if you want to achieve reasonable performance, you should stop using
*distributedJoins* option, because it has huge impact.
Also refer to this page for more information on SQL performance tuning:
https://apacheignite-sql.readme.io/docs/performance-and-debugging
Denis
вт, 12 дек. 2017 г. в 6:08, Lucky <wa...@163.com>:
> Denis,
> Thanks very much!
> All data are related ,and there are about 60G data in the heap. And if
> I add other business module ,the data also are related.We can not split
> them.
> So If data is collocated, then a single node is enough. But it's not
> high availability.
>
>
>
>
>
>
>
>
>
>
>
>
Re:Re: Re: Re: Re: Re: Re: Re: Re:Poor performance select query
with jdbc thin mode
Posted by Lucky <wa...@163.com>.
Denis,
Thanks very much!
All data are related ,and there are about 60G data in the heap. And if I add other business module ,the data also are related.We can not split them.
So If data is collocated, then a single node is enough. But it's not high availability.
Re: Re: Re: Re: Re: Re: Re: Re:Poor performance select query with
jdbc thin mode
Posted by Denis Mekhanikov <dm...@gmail.com>.
Looks like you ran this query on a cluster with more than one node, so you
get performance drop because of distributed joins.
Let's tune performance on a single node first. You can expect queries with
joins to operate fast only if data is collocated.
Otherwise latency will suffer from network communication.
So, what is performance and plan of query execution on a single node?
Denis
ср, 6 дек. 2017 г. в 12:06, Lucky <wa...@163.com>:
> explain :
>
>
> SELECT
> T0__Z0.FID AS __C0_0,
> T0__Z0.FCUSTOMERGROUPSTANDARDID AS __C0_1,
> T0__Z0.FCUSTOMERGROUPID AS __C0_2,
> T0__Z0.FCUSTOMERID AS __C0_3,
> T0__Z0.FCUSTOMERGROUPFULLNAME AS __C0_4
> FROM "csspGroupStandardCache".CSSPGROUPSTANDARD T1__Z1
> /* "csspGroupStandardCache".CSSPGROUPSTANDARD_FID_IDX: FID =
> '00000000-0000-0000-0000-000000000002BC122A7F' */
> /* WHERE T1__Z1.FID = '00000000-0000-0000-0000-000000000002BC122A7F'
> */
> INNER JOIN "customerGroupDetailCache".CUSTOMERGROUPDETAIL T0__Z0
> /* batched:broadcast
> "customerGroupDetailCache".CUSTOMERGROUPDETAIL_FCUSTOMERGROUPSTANDARDID_IDX:
> FCUSTOMERGROUPSTANDARDID = T1__Z1.FID */
> ON 1=1
> /* WHERE T0__Z0.FCUSTOMERGROUPSTANDARDID = T1__Z1.FID
> */
> INNER JOIN "customerCache".CUSTOMERIGNITEINFO T3__Z2
> /* batched:broadcast "customerCache".CUSTOMERIGNITEINFO_FID_IDX: FID =
> '3NsAAABI7bq/DAQO'
> AND FID = T0__Z0.FCUSTOMERID
> */
> ON 1=1
> WHERE ((T3__Z2.FID = '3NsAAABI7bq/DAQO')
> AND (T1__Z1.FID = '00000000-0000-0000-0000-000000000002BC122A7F'))
> AND ((T0__Z0.FCUSTOMERID = T3__Z2.FID)
> AND (T0__Z0.FCUSTOMERGROUPSTANDARDID = T1__Z1.FID))
>
>
>
>
Re:Re: Re: Re: Re: Re: Re: Re:Poor performance select query with
jdbc thin mode
Posted by Lucky <wa...@163.com>.
explain :
SELECT
T0__Z0.FID AS __C0_0,
T0__Z0.FCUSTOMERGROUPSTANDARDID AS __C0_1,
T0__Z0.FCUSTOMERGROUPID AS __C0_2,
T0__Z0.FCUSTOMERID AS __C0_3,
T0__Z0.FCUSTOMERGROUPFULLNAME AS __C0_4
FROM "csspGroupStandardCache".CSSPGROUPSTANDARD T1__Z1
/* "csspGroupStandardCache".CSSPGROUPSTANDARD_FID_IDX: FID = '00000000-0000-0000-0000-000000000002BC122A7F' */
/* WHERE T1__Z1.FID = '00000000-0000-0000-0000-000000000002BC122A7F'
*/
INNER JOIN "customerGroupDetailCache".CUSTOMERGROUPDETAIL T0__Z0
/* batched:broadcast "customerGroupDetailCache".CUSTOMERGROUPDETAIL_FCUSTOMERGROUPSTANDARDID_IDX: FCUSTOMERGROUPSTANDARDID = T1__Z1.FID */
ON 1=1
/* WHERE T0__Z0.FCUSTOMERGROUPSTANDARDID = T1__Z1.FID
*/
INNER JOIN "customerCache".CUSTOMERIGNITEINFO T3__Z2
/* batched:broadcast "customerCache".CUSTOMERIGNITEINFO_FID_IDX: FID = '3NsAAABI7bq/DAQO'
AND FID = T0__Z0.FCUSTOMERID
*/
ON 1=1
WHERE ((T3__Z2.FID = '3NsAAABI7bq/DAQO')
AND (T1__Z1.FID = '00000000-0000-0000-0000-000000000002BC122A7F'))
AND ((T0__Z0.FCUSTOMERID = T3__Z2.FID)
AND (T0__Z0.FCUSTOMERGROUPSTANDARDID = T1__Z1.FID))
Re: Re: Re: Re: Re: Re: Re:Poor performance select query with jdbc
thin mode
Posted by Denis Mekhanikov <dm...@gmail.com>.
Lucky,
Well, at least we figured out, that JDBC drivers and IgniteCache#query
don't have that huge difference, that you told us about :)
Could you show result of *EXPLAIN *for this query? Did you configure any
indexes?
Denis
ср, 6 дек. 2017 г. в 4:05, Lucky <wa...@163.com>:
> No, the problem is poor performance.
> This query should not take that time.
> That just took 10ms in oracle ,but It took 2200ms in ignite.
> Thanks.
>
>
>
>
Re:Re: Re: Re: Re: Re: Re:Poor performance select query with jdbc
thin mode
Posted by Lucky <wa...@163.com>.
No, the problem is poor performance.
This query should not take that time.
That just took 10ms in oracle ,but It took 2200ms in ignite.
Thanks.
Re: Re: Re: Re: Re: Re:Poor performance select query with jdbc thin mode
Posted by Denis Mekhanikov <dm...@gmail.com>.
So, the problem was in measurement, right?
Denis
вт, 5 дек. 2017 г. в 12:39, Lucky <wa...@163.com>:
>
> now it is about the same as JDBC thin mode.
>
>
>
>
>
> At 2017-12-05 16:52:48, "Denis Mekhanikov" <dm...@gmail.com> wrote:
>
> Lucky,
>
> Thanks for the code.
> And what is the time for cache.query() for the same query?
>
> Denis
>
>
Re:Re: Re: Re: Re: Re:Poor performance select query with jdbc thin
mode
Posted by Lucky <wa...@163.com>.
now it is about the same as JDBC thin mode.
At 2017-12-05 16:52:48, "Denis Mekhanikov" <dm...@gmail.com> wrote:
Lucky,
Thanks for the code.
And what is the time for cache.query() for the same query?
Denis
Re: Re: Re: Re: Re:Poor performance select query with jdbc thin mode
Posted by Denis Mekhanikov <dm...@gmail.com>.
Lucky,
Thanks for the code.
And what is the time for cache.query() for the same query?
Denis
вт, 5 дек. 2017 г. в 11:25, Lucky <wa...@163.com>:
> Denis,
> The code is here:
> public static void main(String[] args){
> try { String sql = "SELECT \"T0\".\"FID\" AS \"ID\",
> \"T0\".\"FCUSTOMERGROUPSTANDARDID\" AS \"T1.ID\",
> \"T0\".\"FCUSTOMERGROUPID\" AS \"T2.ID\", \"T0\".\"FCUSTOMERID\" AS \"
> T3.ID\", \"T0\".\"FCUSTOMERGROUPFULLNAME\" AS \"CUSTOMERGROUPFULLNAME\"
> FROM \"customerGroupDetailCache\".CustomerGroupDetail AS \"T0\" INNER JOIN
> \"csspGroupStandardCache\".CsspGroupStandard AS \"T1\" ON
> \"T0\".\"FCUSTOMERGROUPSTANDARDID\" = \"T1\".\"FID\" INNER JOIN
> \"customerCache\".CustomerIgniteInfo AS \"T3\" ON \"T0\".\"FCUSTOMERID\" =
> \"T3\".\"FID\" WHERE (\"T3\".\"FID\" = '3NsAAABI7bq/DAQO' AND
> \"T1\".\"FID\" = '00000000-0000-0000-0000-000000000002BC122A7F')"; long t1
> = System.currentTimeMillis();
> Class.forName("org.apache.ignite.IgniteJdbcThinDriver"); Connection conn =
> DriverManager.getConnection("jdbc:ignite:thin://
> 192.168.63.36?distributedJoins=true"); long t2 =
> System.currentTimeMillis(); logger.error("IgniteJdbcThinDriver getconn time
> ="+(t2-t1)+"ms"); PreparedStatement preparedStatement =
> conn.prepareStatement(sql); t1 = System.currentTimeMillis();
> logger.error("IgniteJdbcThinDriver preparedStatement time ="+(t1-t2)+"ms");
> ResultSet resultSet = preparedStatement.executeQuery(); while
> (resultSet.next())
> System.out.println("resultSet=="+resultSet.getObject(1)); t2 =
> System.currentTimeMillis();
> logger.error("IgniteJdbcThinDriver execute sql time ="+(t2-t1)+"ms");
> Class.forName("org.apache.ignite.IgniteJdbcDriver");
> Connection conn2 =
> DriverManager.getConnection("jdbc:ignite:cfg://distributedJoins=true:cache=baseUnitCache
> @file:D:/ignite/cof/default-config.xml"); t1 =
> System.currentTimeMillis(); logger.error("IgniteJdbcDriver getconn2 time
> ="+(t1-t2)+"ms"); PreparedStatement preparedStatement2 =
> conn2.prepareStatement(sql); t2 = System.currentTimeMillis();
> logger.error("IgniteJdbcDriver preparedStatement2 time ="+(t2-t1)+"ms");
> ResultSet resultSet2 = preparedStatement2.executeQuery(); while
> (resultSet2.next())
> System.out.println("resultSet2==="+resultSet2.getObject(1)); t1 =
> System.currentTimeMillis(); logger.error("IgniteJdbcDriver execute sql2
> time ="+(t1-t2)+"ms"); } catch (Exception e) { logger.error(e); } }
>
> the log is here:
> [(TestMain.java:417)]IgniteJdbcThinDriver getconn time =245ms
> [(TestMain.java:420)]IgniteJdbcThinDriver preparedStatement time =8ms
> resultSet==3NsAAABJbRloQTCA [(TestMain.java:425)]IgniteJdbcThinDriver
> execute sql time =2275ms [(TestMain.java:430)]IgniteJdbcDriver getconn2
> time =11172ms [(TestMain.java:433)]IgniteJdbcDriver preparedStatement2 time
> =2ms resultSet2===3NsAAABJbRloQTCA [(TestMain.java:438)]IgniteJdbcDriver
> execute sql2 time =2836ms
>
> I have tried many times ,it's the same .
> This is single node. It's took double time in 3 node.
> Thanks.
>
> At 2017-12-05 15:12:45,"Denis Mekhanikov" <dm...@gmail.com> wrote:
>
> Lucky,
>
> I looks strange, that client driver works slower than thin. Normally it
> should work just like cache.query() + network overhead.
> Maybe you included connection time into your evaluation? Or something
> else? You should compare query execution time, when connection is already
> established and all preparatory work is done.
>
> Can you share code of your benchmark? I'd like to make sure, that all
> measurements are correct.
>
>
Re:Re: Re: Re: Re:Poor performance select query with jdbc thin mode
Posted by Lucky <wa...@163.com>.
Denis,
The code is here:
public static void main(String[] args){
try { String sql = "SELECT \"T0\".\"FID\" AS \"ID\", \"T0\".\"FCUSTOMERGROUPSTANDARDID\" AS \"T1.ID\", \"T0\".\"FCUSTOMERGROUPID\" AS \"T2.ID\", \"T0\".\"FCUSTOMERID\" AS \"T3.ID\", \"T0\".\"FCUSTOMERGROUPFULLNAME\" AS \"CUSTOMERGROUPFULLNAME\" FROM \"customerGroupDetailCache\".CustomerGroupDetail AS \"T0\" INNER JOIN \"csspGroupStandardCache\".CsspGroupStandard AS \"T1\" ON \"T0\".\"FCUSTOMERGROUPSTANDARDID\" = \"T1\".\"FID\" INNER JOIN \"customerCache\".CustomerIgniteInfo AS \"T3\" ON \"T0\".\"FCUSTOMERID\" = \"T3\".\"FID\" WHERE (\"T3\".\"FID\" = '3NsAAABI7bq/DAQO' AND \"T1\".\"FID\" = '00000000-0000-0000-0000-000000000002BC122A7F')"; long t1 = System.currentTimeMillis(); Class.forName("org.apache.ignite.IgniteJdbcThinDriver"); Connection conn = DriverManager.getConnection("jdbc:ignite:thin://192.168.63.36?distributedJoins=true"); long t2 = System.currentTimeMillis(); logger.error("IgniteJdbcThinDriver getconn time ="+(t2-t1)+"ms"); PreparedStatement preparedStatement = conn.prepareStatement(sql); t1 = System.currentTimeMillis(); logger.error("IgniteJdbcThinDriver preparedStatement time ="+(t1-t2)+"ms"); ResultSet resultSet = preparedStatement.executeQuery(); while (resultSet.next()) System.out.println("resultSet=="+resultSet.getObject(1)); t2 = System.currentTimeMillis();
logger.error("IgniteJdbcThinDriver execute sql time ="+(t2-t1)+"ms");
Class.forName("org.apache.ignite.IgniteJdbcDriver"); Connection conn2 = DriverManager.getConnection("jdbc:ignite:cfg://distributedJoins=true:cache=baseUnitCache@file:D:/ignite/cof/default-config.xml"); t1 = System.currentTimeMillis(); logger.error("IgniteJdbcDriver getconn2 time ="+(t1-t2)+"ms"); PreparedStatement preparedStatement2 = conn2.prepareStatement(sql); t2 = System.currentTimeMillis(); logger.error("IgniteJdbcDriver preparedStatement2 time ="+(t2-t1)+"ms"); ResultSet resultSet2 = preparedStatement2.executeQuery(); while (resultSet2.next()) System.out.println("resultSet2==="+resultSet2.getObject(1)); t1 = System.currentTimeMillis(); logger.error("IgniteJdbcDriver execute sql2 time ="+(t1-t2)+"ms"); } catch (Exception e) { logger.error(e); } }
the log is here:
[(TestMain.java:417)]IgniteJdbcThinDriver getconn time =245ms
[(TestMain.java:420)]IgniteJdbcThinDriver preparedStatement time =8ms resultSet==3NsAAABJbRloQTCA [(TestMain.java:425)]IgniteJdbcThinDriver execute sql time =2275ms [(TestMain.java:430)]IgniteJdbcDriver getconn2 time =11172ms [(TestMain.java:433)]IgniteJdbcDriver preparedStatement2 time =2ms resultSet2===3NsAAABJbRloQTCA [(TestMain.java:438)]IgniteJdbcDriver execute sql2 time =2836ms
I have tried many times ,it's the same .
This is single node. It's took double time in 3 node.
Thanks.
At 2017-12-05 15:12:45,"Denis Mekhanikov" <dm...@gmail.com> wrote:
Lucky,
I looks strange, that client driver works slower than thin. Normally it should work just like cache.query() + network overhead.
Maybe you included connection time into your evaluation? Or something else? You should compare query execution time, when connection is already established and all preparatory work is done.
Can you share code of your benchmark? I'd like to make sure, that all measurements are correct.
Re: Re: Re: Re:Poor performance select query with jdbc thin mode
Posted by Denis Mekhanikov <dm...@gmail.com>.
Lucky,
I looks strange, that client driver works slower than thin. Normally it
should work just like cache.query() + network overhead.
Maybe you included connection time into your evaluation? Or something else?
You should compare query execution time, when connection is already
established and all preparatory work is done.
Can you share code of your benchmark? I'd like to make sure, that all
measurements are correct.
Denis
вт, 5 дек. 2017 г. в 9:31, Lucky <wa...@163.com>:
> Denis,
>
> This query just only retrieve 2 records.
> As I say in the first message, cache.query() took less time.
>
> Another thing,I did a statistics.
> When I enable enforceJoinOrder option, there are just few queries can
> become faster , but more than 1000 queries become slower.
> And I change the join order some queries, it happened nothing.
>
> Thanks.
>
>
>
>
>
> 在 2017-12-01 23:52:48，"Denis Mekhanikov" <dm...@gmail.com> 写道：
>
> Lucky,
>
> If you enable *enforceJoinOrder* option, then join operations will be
> performed in the exact order, in which they appear in the query. This flag
> prevents an SQL optimizer from rearranging the tables.
> This is why some queries may become faster, and some slower.
>
> So, to find an optimal order, you can try rearranging tables in your
> joins, i.e. changing *a JOIN b* to *b JOIN a*.
>
> Denis
>
>
> пт, 1 дек. 2017 г. в 15:30, Denis Mekhanikov <dm...@gmail.com>:
>
>> Lucky,
>>
>> How much data do you retrieve in this query?
>> There is some overhead, caused by network communication, when you are
>> using JDBC drivers, but it's not expected to be so big.
>> Also do you connect over JDBC to the same node, on which you test
>> IgniteCache#query() ?
>>
>> Denis
>>
>> чт, 30 нояб. 2017 г. в 11:30, Lucky <wa...@163.com>:
>>
>>> Denis,
>>> It's worse!
>>> There just have several sql can be faster, the other took more time.
>>> Thanks.
>>>
>>>
>>>>
Re:Re: Re: Re:Poor performance select query with jdbc thin mode
Posted by Lucky <wa...@163.com>.
Denis,
This query just only retrieve 2 records.
As I say in the first message, cache.query() took less time.
Another thing,I did a statistics.
When I enable enforceJoinOrder option, there are just few queries can become faster , but more than 1000 queries become slower.
And I change the join order some queries, it happened nothing.
Thanks.
在 2017-12-01 23:52:48，"Denis Mekhanikov" <dm...@gmail.com> 写道：
Lucky,
If you enable enforceJoinOrder option, then join operations will be performed in the exact order, in which they appear in the query. This flag prevents an SQL optimizer from rearranging the tables.
This is why some queries may become faster, and some slower.
So, to find an optimal order, you can try rearranging tables in your joins, i.e. changing a JOIN b to b JOIN a.
Denis
пт, 1 дек. 2017 г. в 15:30, Denis Mekhanikov <dm...@gmail.com>:
Lucky,
How much data do you retrieve in this query?
There is some overhead, caused by network communication, when you are using JDBC drivers, but it's not expected to be so big.
Also do you connect over JDBC to the same node, on which you test IgniteCache#query() ?
Denis
чт, 30 нояб. 2017 г. в 11:30, Lucky <wa...@163.com>:
Denis,
It's worse!
There just have several sql can be faster, the other took more time.
Thanks.
Re: Re: Re:Poor performance select query with jdbc thin mode
Posted by Denis Mekhanikov <dm...@gmail.com>.
Lucky,
If you enable *enforceJoinOrder* option, then join operations will be
performed in the exact order, in which they appear in the query. This flag
prevents an SQL optimizer from rearranging the tables.
This is why some queries may become faster, and some slower.
So, to find an optimal order, you can try rearranging tables in your joins,
i.e. changing *a JOIN b* to *b JOIN a*.
Denis
пт, 1 дек. 2017 г. в 15:30, Denis Mekhanikov <dm...@gmail.com>:
> Lucky,
>
> How much data do you retrieve in this query?
> There is some overhead, caused by network communication, when you are
> using JDBC drivers, but it's not expected to be so big.
> Also do you connect over JDBC to the same node, on which you test
> IgniteCache#query() ?
>
> Denis
>
> чт, 30 нояб. 2017 г. в 11:30, Lucky <wa...@163.com>:
>
>> Denis,
>> It's worse!
>> There just have several sql can be faster, the other took more time.
>> Thanks.
>>
>>
>>
>>
>>
>> 在 2017-11-29 21:31:05，"Denis Mekhanikov" <dm...@gmail.com> 写道：
>>
>> Lucky,
>>
>> Try enabling *enforceJoinOrder *parameter in JDBC connection string and
>> let us know the result.
>> Your JDBC connection string should look like this:
>> jdbc:ignite:thin://127.0.0.1?*enforceJoinOrder=true*
>>
>> Denis
>>
>>>
>>>
Re: Re: Re:Poor performance select query with jdbc thin mode
Posted by Denis Mekhanikov <dm...@gmail.com>.
Lucky,
How much data do you retrieve in this query?
There is some overhead, caused by network communication, when you are using
JDBC drivers, but it's not expected to be so big.
Also do you connect over JDBC to the same node, on which you test
IgniteCache#query() ?
Denis
чт, 30 нояб. 2017 г. в 11:30, Lucky <wa...@163.com>:
> Denis,
> It's worse!
> There just have several sql can be faster, the other took more time.
> Thanks.
>
>
>
>
>
> 在 2017-11-29 21:31:05，"Denis Mekhanikov" <dm...@gmail.com> 写道：
>
> Lucky,
>
> Try enabling *enforceJoinOrder *parameter in JDBC connection string and
> let us know the result.
> Your JDBC connection string should look like this:
> jdbc:ignite:thin://127.0.0.1?*enforceJoinOrder=true*
>
> Denis
>
>>
>>
Re:Re: Re:Poor performance select query with jdbc thin mode
Posted by Lucky <wa...@163.com>.
Denis,
It's worse!
There just have several sql can be faster, the other took more time.
Thanks.
在 2017-11-29 21:31:05，"Denis Mekhanikov" <dm...@gmail.com> 写道：
Lucky,
Try enabling enforceJoinOrder parameter in JDBC connection string and let us know the result.
Your JDBC connection string should look like this:
jdbc:ignite:thin://127.0.0.1?enforceJoinOrder=true
Denis
Re: Re:Poor performance select query with jdbc thin mode
Posted by Denis Mekhanikov <dm...@gmail.com>.
Lucky,
Try enabling *enforceJoinOrder *parameter in JDBC connection string and let
us know the result.
Your JDBC connection string should look like this:
jdbc:ignite:thin://127.0.0.1?*enforceJoinOrder=true*
Denis
ср, 29 нояб. 2017 г. в 10:22, Lucky <wa...@163.com>:
> Have you come to a conclusion？
>
>
>
>
>
>
> At 2017-11-27 17:23:16, "Lucky" <wa...@163.com> wrote:
>
> Hi,Denis:
> 2.3 will be better than 2.2.
> But the performance improvement is not obvious.
> It took 2.5 seconds in single ignite node ,and took 5 seconds in
> cluster(3 ignite nodes).
>
> JDBC Client driver is worse than JDBC thin driver. it took about more
> 1 seconds.
>
> Did there any other suggestiones?
> Thanks.
>
>
> 2017-11-24 20:25,Denis Mekhanikov<dm...@gmail.com>
> <dm...@gmail.com> write:
>
> Hi!
>
> Please try running the same test on Ignite 2.3
> Also try changing to JDBC client driver
> <https://apacheignite-sql.readme.io/docs/jdbc-client-driver> and let us
> know the results.
>
> Denis
>
>
Re:Re:Poor performance select query with jdbc thin mode
Posted by Lucky <wa...@163.com>.
Have you come to a conclusion？
At 2017-11-27 17:23:16, "Lucky" <wa...@163.com> wrote:
Hi,Denis:
2.3 will be better than 2.2.
But the performance improvement is not obvious.
It took 2.5 seconds in single ignite node ,and took 5 seconds in cluster(3 ignite nodes).
JDBC Client driver is worse than JDBC thin driver. it took about more 1 seconds.
Did there any other suggestiones?
Thanks.
2017-11-24 20:25,Denis Mekhanikov<dm...@gmail.com> write:
Hi!
Please try running the same test on Ignite 2.3
Also try changing to JDBC client driver and let us know the results.
Denis
Re: Re:Poor performance select query with jdbc thin mode
Posted by "ilya.kasnacheev" <il...@gmail.com>.
Hello Lucky.
Please try setting enforceJoinOrder parameter on connection to true,
retrying the request with various order of tables joined to see if it helps.
It might turn out that one order of joins performs much better than the
other one.
Setting the parameter is documented here:
https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/IgniteJdbcThinDriver.html
Regards,
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re:Poor performance select query with jdbc thin mode
Posted by Lucky <wa...@163.com>.
Hi,Denis:
2.3 will be better than 2.2.
But the performance improvement is not obvious.
It took 2.5 seconds in single ignite node ,and took 5 seconds in cluster(3 ignite nodes).
JDBC Client driver is worse than JDBC thin driver. it took about more 1 seconds.
Did there any other suggestiones?
Thanks.
2017-11-24 20:25,Denis Mekhanikov<dm...@gmail.com> write:
Hi!
Please try running the same test on Ignite 2.3
Also try changing to JDBC client driver and let us know the results.
Denis
Re: Poor performance select query with jdbc thin mode
Posted by Denis Mekhanikov <dm...@gmail.com>.
Hi!
Please try running the same test on Ignite 2.3
Also try changing to JDBC client driver
<https://apacheignite-sql.readme.io/docs/jdbc-client-driver> and let us
know the results.
Denis
пт, 24 нояб. 2017 г. в 12:25, Lucky <wa...@163.com>:
> Hi:
> I need to return a ResultSet, So I had to use jdbc thin mode to execute
> select query.
> But the performance is not acceptable!
> The sql and explain is like this:
> Two tables both have 1,500,000 rows and about 15 columns.
> It took 3 seconds in single ignite node ,and took 6 seconds in
> cluster(3 ignite nodes).
> But if i execute the sql with cache.query(new SQLFieldsQuery(sql))
> ,it just took 400ms in single ignite node , and took 200ms in cluster(3
> ignite nodes).
> Ignite version is 2.2.
> What suggestions?
> Thanks.
>
>
>
>