You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by alex kamil <al...@gmail.com> on 2014/03/11 20:30:35 UTC
resultSet.next() is slow
running the below query in sqlline takes less than 4 sec but when calling
from Java client the first resultSet.next() is much slower (>20 sec), any
ideas?
client code
String JDBC_DRIVER = "jdbc:phoenix:myip";
String query = "SELECT ROWKEY FROM mytable LIMIT 10000";
//ROWKEY size=32bytes, mytable size=4M rows (128 regions on 3 servers with
40GB heap)
Class.forName("com.salesforce.phoenix.jdbc.PhoenixDriver");
DriverManager.getConnection(JDBC_DRIVER);
Connection con = DriverManager.getConnection(JDBC_DRIVER);
Statement stmt = con.createStatement();
ResultSet resultSet =stmt.executeQuery(query);
Object o = null; int i=0;boolean isFirst = true;
long start = System.currentTimeMillis();
* while(resultSet.next())*{
if (isFirst){
System.out.println("*Time taken for first getNext() *: " +
(System.currentTimeMillis() - start));
isFirst = false;
}
for (i = 0; i < colmax; ++i)
{
o = resultSet.getObject(i + 1);
if (o != null){
//rowKeys.add(o.toString());
}
}
}
System.out.println("Time taken to loop through : " +
(System.currentTimeMillis() - start));
Re: resultSet.next() is slow
Posted by alex kamil <al...@gmail.com>.
right, that would explain why running the query from sqlline (on the same
box with hbase) was faster than from a java client (on a remote machine)
still not sure why reducing the number of salt buckets also reduced the
time, I'd expect it to be the opposite
On Wed, Mar 12, 2014 at 5:10 PM, James Taylor <ja...@apache.org>wrote:
> Phoenix uses the regular hbase.client.scanner.caching property, but we
> default it to 1000. The first next call does most of the work - pulling
> over the batch to the client in parallel. Given you query has no where
> clause, your more or less measuring your network speed.
>
> Thanks,
> James
>
>
> On Wed, Mar 12, 2014 at 1:37 PM, Vladimir Rodionov <vladrodionov@gmail.com
> > wrote:
>
>> Your first call to ResultSet.next does all the job (loading all 10000
>> rows from HBase) and probably not in a optimal way? One row at a time?
>> I do not know if default scan caching setting affects Phoenix or Phoenix
>> uses its own scan caching - this is question to Phoenix developers.
>>
>> -Vladimir
>>
>>
>> On Wed, Mar 12, 2014 at 8:21 AM, alex kamil <al...@gmail.com> wrote:
>>
>>> same result
>>>
>>> Execute Query : 401ms
>>>
>>> meta.getColumnCount : 1ms
>>>
>>> Time taken for first getNext() : 22593ms
>>>
>>> Time taken to loop through : 22871ms
>>>
>>> Execute Query : 14ms
>>>
>>> meta.getColumnCount : 0ms
>>>
>>> Time taken for first getNext() : 21892ms
>>>
>>> Time taken to loop through : 22041ms
>>>
>>>
>>> On Tue, Mar 11, 2014 at 7:15 PM, James Taylor <ja...@apache.org>wrote:
>>>
>>>> What does the time look like if you run your query twice in the same
>>>> JVM, as I suspect you may be hitting a one-time cost:
>>>>
>>>> for (int i =0; i < 2; i++) {
>>>>
>>>> Statement stmt = con.createStatement();
>>>>
>>>> ResultSet resultSet =stmt.executeQuery(query);
>>>>
>>>> Object o = null; int i=0;boolean isFirst = true;
>>>>
>>>> long start = System.currentTimeMillis();
>>>>
>>>> * while(resultSet.next())*{
>>>>
>>>> if (isFirst){
>>>>
>>>> System.out.println("*Time taken for first getNext() *: " +
>>>> (System.currentTimeMillis() - start));
>>>>
>>>> isFirst = false;
>>>>
>>>> }
>>>>
>>>> for (i = 0; i < colmax; ++i)
>>>>
>>>> {
>>>>
>>>> o = resultSet.getObject(i + 1);
>>>>
>>>> if (o != null){
>>>>
>>>> //rowKeys.add(o.toString());
>>>>
>>>> }
>>>>
>>>> }
>>>>
>>>> }
>>>>
>>>> System.out.println("Time taken to loop through : " +
>>>> (System.currentTimeMillis() - start));
>>>>
>>>> }
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> On Tue, Mar 11, 2014 at 12:57 PM, alex kamil <al...@gmail.com>wrote:
>>>>
>>>>> running the query on a table with 3 salt buckets (previously 128)
>>>>> brought down the time to single digits sec, what could be the possible
>>>>> explanation?
>>>>>
>>>>>
>>>>> On Tue, Mar 11, 2014 at 3:30 PM, alex kamil <al...@gmail.com>wrote:
>>>>>
>>>>>> running the below query in sqlline takes less than 4 sec but when
>>>>>> calling from Java client the first resultSet.next() is much slower (>20
>>>>>> sec), any ideas?
>>>>>>
>>>>>>
>>>>>> client code
>>>>>>
>>>>>> String JDBC_DRIVER = "jdbc:phoenix:myip";
>>>>>>
>>>>>> String query = "SELECT ROWKEY FROM mytable LIMIT 10000";
>>>>>>
>>>>>> //ROWKEY size=32bytes, mytable size=4M rows (128 regions on 3
>>>>>> servers with 40GB heap)
>>>>>>
>>>>>> Class.forName("com.salesforce.phoenix.jdbc.PhoenixDriver");
>>>>>>
>>>>>> DriverManager.getConnection(JDBC_DRIVER);
>>>>>>
>>>>>> Connection con = DriverManager.getConnection(JDBC_DRIVER);
>>>>>>
>>>>>> Statement stmt = con.createStatement();
>>>>>>
>>>>>> ResultSet resultSet =stmt.executeQuery(query);
>>>>>>
>>>>>> Object o = null; int i=0;boolean isFirst = true;
>>>>>>
>>>>>> long start = System.currentTimeMillis();
>>>>>>
>>>>>> * while(resultSet.next())*{
>>>>>>
>>>>>> if (isFirst){
>>>>>>
>>>>>> System.out.println("*Time taken for first getNext() *: " +
>>>>>> (System.currentTimeMillis() - start));
>>>>>>
>>>>>> isFirst = false;
>>>>>>
>>>>>> }
>>>>>>
>>>>>> for (i = 0; i < colmax; ++i)
>>>>>>
>>>>>> {
>>>>>>
>>>>>> o = resultSet.getObject(i + 1);
>>>>>>
>>>>>> if (o != null){
>>>>>>
>>>>>> //rowKeys.add(o.toString());
>>>>>>
>>>>>> }
>>>>>>
>>>>>> }
>>>>>>
>>>>>> }
>>>>>>
>>>>>> System.out.println("Time taken to loop through : " +
>>>>>> (System.currentTimeMillis() - start));
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>
Re: resultSet.next() is slow
Posted by James Taylor <ja...@apache.org>.
Phoenix uses the regular hbase.client.scanner.caching property, but we
default it to 1000. The first next call does most of the work - pulling
over the batch to the client in parallel. Given you query has no where
clause, your more or less measuring your network speed.
Thanks,
James
On Wed, Mar 12, 2014 at 1:37 PM, Vladimir Rodionov
<vl...@gmail.com>wrote:
> Your first call to ResultSet.next does all the job (loading all 10000 rows
> from HBase) and probably not in a optimal way? One row at a time?
> I do not know if default scan caching setting affects Phoenix or Phoenix
> uses its own scan caching - this is question to Phoenix developers.
>
> -Vladimir
>
>
> On Wed, Mar 12, 2014 at 8:21 AM, alex kamil <al...@gmail.com> wrote:
>
>> same result
>>
>> Execute Query : 401ms
>>
>> meta.getColumnCount : 1ms
>>
>> Time taken for first getNext() : 22593ms
>>
>> Time taken to loop through : 22871ms
>>
>> Execute Query : 14ms
>>
>> meta.getColumnCount : 0ms
>>
>> Time taken for first getNext() : 21892ms
>>
>> Time taken to loop through : 22041ms
>>
>>
>> On Tue, Mar 11, 2014 at 7:15 PM, James Taylor <ja...@apache.org>wrote:
>>
>>> What does the time look like if you run your query twice in the same
>>> JVM, as I suspect you may be hitting a one-time cost:
>>>
>>> for (int i =0; i < 2; i++) {
>>>
>>> Statement stmt = con.createStatement();
>>>
>>> ResultSet resultSet =stmt.executeQuery(query);
>>>
>>> Object o = null; int i=0;boolean isFirst = true;
>>>
>>> long start = System.currentTimeMillis();
>>>
>>> * while(resultSet.next())*{
>>>
>>> if (isFirst){
>>>
>>> System.out.println("*Time taken for first getNext() *: " +
>>> (System.currentTimeMillis() - start));
>>>
>>> isFirst = false;
>>>
>>> }
>>>
>>> for (i = 0; i < colmax; ++i)
>>>
>>> {
>>>
>>> o = resultSet.getObject(i + 1);
>>>
>>> if (o != null){
>>>
>>> //rowKeys.add(o.toString());
>>>
>>> }
>>>
>>> }
>>>
>>> }
>>>
>>> System.out.println("Time taken to loop through : " +
>>> (System.currentTimeMillis() - start));
>>>
>>> }
>>>
>>>
>>>
>>>
>>>
>>> On Tue, Mar 11, 2014 at 12:57 PM, alex kamil <al...@gmail.com>wrote:
>>>
>>>> running the query on a table with 3 salt buckets (previously 128)
>>>> brought down the time to single digits sec, what could be the possible
>>>> explanation?
>>>>
>>>>
>>>> On Tue, Mar 11, 2014 at 3:30 PM, alex kamil <al...@gmail.com>wrote:
>>>>
>>>>> running the below query in sqlline takes less than 4 sec but when
>>>>> calling from Java client the first resultSet.next() is much slower (>20
>>>>> sec), any ideas?
>>>>>
>>>>>
>>>>> client code
>>>>>
>>>>> String JDBC_DRIVER = "jdbc:phoenix:myip";
>>>>>
>>>>> String query = "SELECT ROWKEY FROM mytable LIMIT 10000";
>>>>>
>>>>> //ROWKEY size=32bytes, mytable size=4M rows (128 regions on 3 servers
>>>>> with 40GB heap)
>>>>>
>>>>> Class.forName("com.salesforce.phoenix.jdbc.PhoenixDriver");
>>>>>
>>>>> DriverManager.getConnection(JDBC_DRIVER);
>>>>>
>>>>> Connection con = DriverManager.getConnection(JDBC_DRIVER);
>>>>>
>>>>> Statement stmt = con.createStatement();
>>>>>
>>>>> ResultSet resultSet =stmt.executeQuery(query);
>>>>>
>>>>> Object o = null; int i=0;boolean isFirst = true;
>>>>>
>>>>> long start = System.currentTimeMillis();
>>>>>
>>>>> * while(resultSet.next())*{
>>>>>
>>>>> if (isFirst){
>>>>>
>>>>> System.out.println("*Time taken for first getNext() *: " +
>>>>> (System.currentTimeMillis() - start));
>>>>>
>>>>> isFirst = false;
>>>>>
>>>>> }
>>>>>
>>>>> for (i = 0; i < colmax; ++i)
>>>>>
>>>>> {
>>>>>
>>>>> o = resultSet.getObject(i + 1);
>>>>>
>>>>> if (o != null){
>>>>>
>>>>> //rowKeys.add(o.toString());
>>>>>
>>>>> }
>>>>>
>>>>> }
>>>>>
>>>>> }
>>>>>
>>>>> System.out.println("Time taken to loop through : " +
>>>>> (System.currentTimeMillis() - start));
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>
Re: resultSet.next() is slow
Posted by Vladimir Rodionov <vl...@gmail.com>.
Your first call to ResultSet.next does all the job (loading all 10000 rows
from HBase) and probably not in a optimal way? One row at a time?
I do not know if default scan caching setting affects Phoenix or Phoenix
uses its own scan caching - this is question to Phoenix developers.
-Vladimir
On Wed, Mar 12, 2014 at 8:21 AM, alex kamil <al...@gmail.com> wrote:
> same result
>
> Execute Query : 401ms
>
> meta.getColumnCount : 1ms
>
> Time taken for first getNext() : 22593ms
>
> Time taken to loop through : 22871ms
>
> Execute Query : 14ms
>
> meta.getColumnCount : 0ms
>
> Time taken for first getNext() : 21892ms
>
> Time taken to loop through : 22041ms
>
>
> On Tue, Mar 11, 2014 at 7:15 PM, James Taylor <ja...@apache.org>wrote:
>
>> What does the time look like if you run your query twice in the same JVM,
>> as I suspect you may be hitting a one-time cost:
>>
>> for (int i =0; i < 2; i++) {
>>
>> Statement stmt = con.createStatement();
>>
>> ResultSet resultSet =stmt.executeQuery(query);
>>
>> Object o = null; int i=0;boolean isFirst = true;
>>
>> long start = System.currentTimeMillis();
>>
>> * while(resultSet.next())*{
>>
>> if (isFirst){
>>
>> System.out.println("*Time taken for first getNext() *: " +
>> (System.currentTimeMillis() - start));
>>
>> isFirst = false;
>>
>> }
>>
>> for (i = 0; i < colmax; ++i)
>>
>> {
>>
>> o = resultSet.getObject(i + 1);
>>
>> if (o != null){
>>
>> //rowKeys.add(o.toString());
>>
>> }
>>
>> }
>>
>> }
>>
>> System.out.println("Time taken to loop through : " +
>> (System.currentTimeMillis() - start));
>>
>> }
>>
>>
>>
>>
>>
>> On Tue, Mar 11, 2014 at 12:57 PM, alex kamil <al...@gmail.com>wrote:
>>
>>> running the query on a table with 3 salt buckets (previously 128)
>>> brought down the time to single digits sec, what could be the possible
>>> explanation?
>>>
>>>
>>> On Tue, Mar 11, 2014 at 3:30 PM, alex kamil <al...@gmail.com>wrote:
>>>
>>>> running the below query in sqlline takes less than 4 sec but when
>>>> calling from Java client the first resultSet.next() is much slower (>20
>>>> sec), any ideas?
>>>>
>>>>
>>>> client code
>>>>
>>>> String JDBC_DRIVER = "jdbc:phoenix:myip";
>>>>
>>>> String query = "SELECT ROWKEY FROM mytable LIMIT 10000";
>>>>
>>>> //ROWKEY size=32bytes, mytable size=4M rows (128 regions on 3 servers
>>>> with 40GB heap)
>>>>
>>>> Class.forName("com.salesforce.phoenix.jdbc.PhoenixDriver");
>>>>
>>>> DriverManager.getConnection(JDBC_DRIVER);
>>>>
>>>> Connection con = DriverManager.getConnection(JDBC_DRIVER);
>>>>
>>>> Statement stmt = con.createStatement();
>>>>
>>>> ResultSet resultSet =stmt.executeQuery(query);
>>>>
>>>> Object o = null; int i=0;boolean isFirst = true;
>>>>
>>>> long start = System.currentTimeMillis();
>>>>
>>>> * while(resultSet.next())*{
>>>>
>>>> if (isFirst){
>>>>
>>>> System.out.println("*Time taken for first getNext() *: " +
>>>> (System.currentTimeMillis() - start));
>>>>
>>>> isFirst = false;
>>>>
>>>> }
>>>>
>>>> for (i = 0; i < colmax; ++i)
>>>>
>>>> {
>>>>
>>>> o = resultSet.getObject(i + 1);
>>>>
>>>> if (o != null){
>>>>
>>>> //rowKeys.add(o.toString());
>>>>
>>>> }
>>>>
>>>> }
>>>>
>>>> }
>>>>
>>>> System.out.println("Time taken to loop through : " +
>>>> (System.currentTimeMillis() - start));
>>>>
>>>>
>>>>
>>>>
>>>
>>
>
Re: resultSet.next() is slow
Posted by alex kamil <al...@gmail.com>.
same result
Execute Query : 401ms
meta.getColumnCount : 1ms
Time taken for first getNext() : 22593ms
Time taken to loop through : 22871ms
Execute Query : 14ms
meta.getColumnCount : 0ms
Time taken for first getNext() : 21892ms
Time taken to loop through : 22041ms
On Tue, Mar 11, 2014 at 7:15 PM, James Taylor <ja...@apache.org>wrote:
> What does the time look like if you run your query twice in the same JVM,
> as I suspect you may be hitting a one-time cost:
>
> for (int i =0; i < 2; i++) {
>
> Statement stmt = con.createStatement();
>
> ResultSet resultSet =stmt.executeQuery(query);
>
> Object o = null; int i=0;boolean isFirst = true;
>
> long start = System.currentTimeMillis();
>
> * while(resultSet.next())*{
>
> if (isFirst){
>
> System.out.println("*Time taken for first getNext() *: " +
> (System.currentTimeMillis() - start));
>
> isFirst = false;
>
> }
>
> for (i = 0; i < colmax; ++i)
>
> {
>
> o = resultSet.getObject(i + 1);
>
> if (o != null){
>
> //rowKeys.add(o.toString());
>
> }
>
> }
>
> }
>
> System.out.println("Time taken to loop through : " +
> (System.currentTimeMillis() - start));
>
> }
>
>
>
>
>
> On Tue, Mar 11, 2014 at 12:57 PM, alex kamil <al...@gmail.com> wrote:
>
>> running the query on a table with 3 salt buckets (previously 128) brought
>> down the time to single digits sec, what could be the possible explanation?
>>
>>
>> On Tue, Mar 11, 2014 at 3:30 PM, alex kamil <al...@gmail.com> wrote:
>>
>>> running the below query in sqlline takes less than 4 sec but when
>>> calling from Java client the first resultSet.next() is much slower (>20
>>> sec), any ideas?
>>>
>>>
>>> client code
>>>
>>> String JDBC_DRIVER = "jdbc:phoenix:myip";
>>>
>>> String query = "SELECT ROWKEY FROM mytable LIMIT 10000";
>>>
>>> //ROWKEY size=32bytes, mytable size=4M rows (128 regions on 3 servers
>>> with 40GB heap)
>>>
>>> Class.forName("com.salesforce.phoenix.jdbc.PhoenixDriver");
>>>
>>> DriverManager.getConnection(JDBC_DRIVER);
>>>
>>> Connection con = DriverManager.getConnection(JDBC_DRIVER);
>>>
>>> Statement stmt = con.createStatement();
>>>
>>> ResultSet resultSet =stmt.executeQuery(query);
>>>
>>> Object o = null; int i=0;boolean isFirst = true;
>>>
>>> long start = System.currentTimeMillis();
>>>
>>> * while(resultSet.next())*{
>>>
>>> if (isFirst){
>>>
>>> System.out.println("*Time taken for first getNext() *: " +
>>> (System.currentTimeMillis() - start));
>>>
>>> isFirst = false;
>>>
>>> }
>>>
>>> for (i = 0; i < colmax; ++i)
>>>
>>> {
>>>
>>> o = resultSet.getObject(i + 1);
>>>
>>> if (o != null){
>>>
>>> //rowKeys.add(o.toString());
>>>
>>> }
>>>
>>> }
>>>
>>> }
>>>
>>> System.out.println("Time taken to loop through : " +
>>> (System.currentTimeMillis() - start));
>>>
>>>
>>>
>>>
>>
>
Re: resultSet.next() is slow
Posted by James Taylor <ja...@apache.org>.
What does the time look like if you run your query twice in the same JVM,
as I suspect you may be hitting a one-time cost:
for (int i =0; i < 2; i++) {
Statement stmt = con.createStatement();
ResultSet resultSet =stmt.executeQuery(query);
Object o = null; int i=0;boolean isFirst = true;
long start = System.currentTimeMillis();
* while(resultSet.next())*{
if (isFirst){
System.out.println("*Time taken for first getNext() *: " +
(System.currentTimeMillis() - start));
isFirst = false;
}
for (i = 0; i < colmax; ++i)
{
o = resultSet.getObject(i + 1);
if (o != null){
//rowKeys.add(o.toString());
}
}
}
System.out.println("Time taken to loop through : " +
(System.currentTimeMillis() - start));
}
On Tue, Mar 11, 2014 at 12:57 PM, alex kamil <al...@gmail.com> wrote:
> running the query on a table with 3 salt buckets (previously 128) brought
> down the time to single digits sec, what could be the possible explanation?
>
>
> On Tue, Mar 11, 2014 at 3:30 PM, alex kamil <al...@gmail.com> wrote:
>
>> running the below query in sqlline takes less than 4 sec but when
>> calling from Java client the first resultSet.next() is much slower (>20
>> sec), any ideas?
>>
>>
>> client code
>>
>> String JDBC_DRIVER = "jdbc:phoenix:myip";
>>
>> String query = "SELECT ROWKEY FROM mytable LIMIT 10000";
>>
>> //ROWKEY size=32bytes, mytable size=4M rows (128 regions on 3 servers
>> with 40GB heap)
>>
>> Class.forName("com.salesforce.phoenix.jdbc.PhoenixDriver");
>>
>> DriverManager.getConnection(JDBC_DRIVER);
>>
>> Connection con = DriverManager.getConnection(JDBC_DRIVER);
>>
>> Statement stmt = con.createStatement();
>>
>> ResultSet resultSet =stmt.executeQuery(query);
>>
>> Object o = null; int i=0;boolean isFirst = true;
>>
>> long start = System.currentTimeMillis();
>>
>> * while(resultSet.next())*{
>>
>> if (isFirst){
>>
>> System.out.println("*Time taken for first getNext() *: " +
>> (System.currentTimeMillis() - start));
>>
>> isFirst = false;
>>
>> }
>>
>> for (i = 0; i < colmax; ++i)
>>
>> {
>>
>> o = resultSet.getObject(i + 1);
>>
>> if (o != null){
>>
>> //rowKeys.add(o.toString());
>>
>> }
>>
>> }
>>
>> }
>>
>> System.out.println("Time taken to loop through : " +
>> (System.currentTimeMillis() - start));
>>
>>
>>
>>
>
Re: resultSet.next() is slow
Posted by alex kamil <al...@gmail.com>.
running the query on a table with 3 salt buckets (previously 128) brought
down the time to single digits sec, what could be the possible explanation?
On Tue, Mar 11, 2014 at 3:30 PM, alex kamil <al...@gmail.com> wrote:
> running the below query in sqlline takes less than 4 sec but when calling
> from Java client the first resultSet.next() is much slower (>20 sec), any
> ideas?
>
>
> client code
>
> String JDBC_DRIVER = "jdbc:phoenix:myip";
>
> String query = "SELECT ROWKEY FROM mytable LIMIT 10000";
>
> //ROWKEY size=32bytes, mytable size=4M rows (128 regions on 3 servers
> with 40GB heap)
>
> Class.forName("com.salesforce.phoenix.jdbc.PhoenixDriver");
>
> DriverManager.getConnection(JDBC_DRIVER);
>
> Connection con = DriverManager.getConnection(JDBC_DRIVER);
>
> Statement stmt = con.createStatement();
>
> ResultSet resultSet =stmt.executeQuery(query);
>
> Object o = null; int i=0;boolean isFirst = true;
>
> long start = System.currentTimeMillis();
>
> * while(resultSet.next())*{
>
> if (isFirst){
>
> System.out.println("*Time taken for first getNext() *: " +
> (System.currentTimeMillis() - start));
>
> isFirst = false;
>
> }
>
> for (i = 0; i < colmax; ++i)
>
> {
>
> o = resultSet.getObject(i + 1);
>
> if (o != null){
>
> //rowKeys.add(o.toString());
>
> }
>
> }
>
> }
>
> System.out.println("Time taken to loop through : " +
> (System.currentTimeMillis() - start));
>
>
>
>