You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@trafodion.apache.org by Eric Owhadi <er...@esgyn.com> on 2017/01/13 23:52:27 UTC
jdbc rowset usage?
Hello,
I am struggling to find the jdbc syntax to set an dynamic array parameter:
Assuming I prepared this statement s with "Insert into t (a,b,c) values( ?[1000], ?[1000], ?[1000])"
How do I set each parameter?
Assuming a is INT, b is CHAR[10], c is INT?
Am I doing something not really supported? Should I use AddBatch instead?
Thanks in advance for the help,
Eric
RE: jdbc rowset usage?
Posted by Eric Owhadi <er...@esgyn.com>.
Hi Suresh,
Awesome. Thanks for this detailed explanation. This helps a lot. And thank Arvin and Selva for you help.
Eric
From: Suresh Subbiah [mailto:suresh.subbiah60@gmail.com]
Sent: Saturday, January 14, 2017 11:40 PM
To: user@trafodion.incubator.apache.org
Subject: Re: jdbc rowset usage?
Hi Eric,
1) The shape of the plan is always the same these rowset inserts. There is no esp parallelism. If upsert is used it will be of vsbb type.It is independent of the size of the rowset.
tuple_flow(unpack(values), insert)
2) The plan includes a max rowset size. At runtime another rowset size is provided (the actual size for that execution). We can compile once with a maximum size and then use the same prepared plan to execute repeatedly with different actual sizes that are smaller.
Max size is set with the statement attribute SQL_ATTR_INPUT_ARRAY_MAXSIZE (please see $MY_SQROOT/../conn/odbc/src/odbc/nsksrvrcore/sqlinterface.cpp), prior to prepare. Actual size is given at runtime through the CLI call SetRowsetDescPointers (declared in sql/cli/sqlcli.h)
3) The use case described (inserting arrays of rows, but with different number of elements in the array each time), should not require a recompile (even through a query cache hit). This can be verified by creating a PreparedStatement and executing once with 10 rows and next with 5 rows in the batch. Through offender or by selecting from query cache virtual table we should be able to confirm that there was only one compile (i.e. there was no recompile).
Code may have changed from the time I worked in this area and it could behave differently now. It will be good to test. However we have trickle loading applications ODB that are inserting thousands of rows per second using rowsets, through a single connection, with a rowset size of a few thousand. Usually we can get several executions to complete in a second. Compile time seems to be negligible compared to execution time in the instances I have seen. Rowset size typically does not change in ODB, but the last insert in a connection usually has less than the previous one. I don't think the last insert is getting a recompile, though even if it did, with various cache hits, it should only take a few milliseconds.
Thanks
Suresh
PS The CLI calls and statement attributes are code level details. A JDBC program writer is not exposed to them and cannot use them to change behaviour directly.
On Fri, Jan 13, 2017 at 11:20 PM, Eric Owhadi <er...@esgyn.com>> wrote:
Hi Suresh, Arvin and Selva,
I understand the addBatch method. But I have a hard time understanding how compiler can do a good job not knowing the cardinality of the rowset at prepare time.
Since we support prepare statement like this:
Insert into t (a,b,c) values( ?[1000], ?[1000], ?[1000])
Where cardinality of the rowset is known at compile time, and I can see a very nice associated plan, I am wondering how efficient could be a process where a iterate over a prepare statement like this:
Insert into t (a,b,c) values( ?, ?, ?)
Where at compile time the prepare have no clue if a rowset plan is appropriate or a non rowset plan is better.
Suresh has explained me over the phone that compiler would assume rowset, and recompile in case it is single value… but then if I keep calling this prepared statement with various random number of items, how is the compiler going to successfully prepare, and keep reuse the same plan? (this is really the use-case I am dealing with: variable length rowset at every calls, can go from 1 to 10000)
That is why I was hoping to force a fixed cardinality plan, to avoid this difficult situation with varying cardinality that I suspect will result in many recompiles?
I know that this syntax would not be jdbc standard (so not portable)… But how about if I am ready to pay the price of using non-standard feature? Since there is no DB like ours anyway ☺…
I guess this question is more for dev list, as I am inquiring about possible non standard stuff, given I see half of it is already working (successful compile of Insert into t (a,b,c) values( ?[1000], ?[1000], ?[1000]) – I suspect there might be way to do the other half with some magic words … ?
Eric
From: Arvind GMAIL [mailto:narain.arvind@gmail.com<ma...@gmail.com>]
Sent: Friday, January 13, 2017 10:52 PM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: RE: jdbc rowset usage?
Hi Eric, Suresh
You could also look at some the jdbc tests under the following for addbatch and related examples:
https://github.com/apache/incubator-trafodion/tree/master/dcs/src/test/jdbc_test/src/test/java/org/trafodion/jdbc_test
Thanks
Arvind
From: Suresh Subbiah [mailto:suresh.subbiah60@gmail.com]
Sent: Friday, January 13, 2017 7:50 PM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: Re: jdbc rowset usage?
Hi Eric,
The steps Selva mentioned are shown in the examples at https://examples.javacodegeeks.com/core-java/sql/jdbc-batch-insert-example/
Section 4. in this example should be relevant. We should have similar examples in JDBC tests, but I am not able to find them.
Thanks
Suresh
On Fri, Jan 13, 2017 at 6:40 PM, Selva Govindarajan <se...@esgyn.com>> wrote:
Just prepare the statement with '?' for parameters
Do
PreparedStatement.setXXX () for all parameters
PreparedStatement.addBatch()
Do the above in a loop, when you reach the required rowset size
PreparedStatement.executeBatch().
Selva
________________________________
From: Eric Owhadi <er...@esgyn.com>>
Sent: Friday, January 13, 2017 3:52 PM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: jdbc rowset usage?
Hello,
I am struggling to find the jdbc syntax to set an dynamic array parameter:
Assuming I prepared this statement s with “Insert into t (a,b,c) values( ?[1000], ?[1000], ?[1000])”
How do I set each parameter?
Assuming a is INT, b is CHAR[10], c is INT?
Am I doing something not really supported? Should I use AddBatch instead?
Thanks in advance for the help,
Eric
Re: jdbc rowset usage?
Posted by Suresh Subbiah <su...@gmail.com>.
Hi Eric,
1) The shape of the plan is always the same these rowset inserts. There is
no esp parallelism. If upsert is used it will be of vsbb type.It is
independent of the size of the rowset.
tuple_flow(unpack(values), insert)
2) The plan includes a max rowset size. At runtime another rowset size is
provided (the actual size for that execution). We can compile once with a
maximum size and then use the same prepared plan to execute repeatedly with
different actual sizes that are smaller.
Max size is set with the statement attribute SQL_ATTR_INPUT_ARRAY_MAXSIZE
(please see $MY_SQROOT/../conn/odbc/src/odbc/nsksrvrcore/sqlinterface.cpp),
prior to prepare. Actual size is given at runtime through the CLI call
SetRowsetDescPointers (declared in sql/cli/sqlcli.h)
3) The use case described (inserting arrays of rows, but with different
number of elements in the array each time), should not require a recompile
(even through a query cache hit). This can be verified by creating a
PreparedStatement and executing once with 10 rows and next with 5 rows in
the batch. Through offender or by selecting from query cache virtual table
we should be able to confirm that there was only one compile (i.e. there
was no recompile).
Code may have changed from the time I worked in this area and it could
behave differently now. It will be good to test. However we have trickle
loading applications ODB that are inserting thousands of rows per second
using rowsets, through a single connection, with a rowset size of a few
thousand. Usually we can get several executions to complete in a second.
Compile time seems to be negligible compared to execution time in the
instances I have seen. Rowset size typically does not change in ODB, but
the last insert in a connection usually has less than the previous one. I
don't think the last insert is getting a recompile, though even if it did,
with various cache hits, it should only take a few milliseconds.
Thanks
Suresh
PS The CLI calls and statement attributes are code level details. A JDBC
program writer is not exposed to them and cannot use them to change
behaviour directly.
On Fri, Jan 13, 2017 at 11:20 PM, Eric Owhadi <er...@esgyn.com> wrote:
> Hi Suresh, Arvin and Selva,
>
> I understand the addBatch method. But I have a hard time understanding how
> compiler can do a good job not knowing the cardinality of the rowset at
> prepare time.
>
> Since we support prepare statement like this:
>
> Insert into t (a,b,c) values( ?[1000], ?[1000], ?[1000])
>
>
>
> Where cardinality of the rowset is known at compile time, and I can see a
> very nice associated plan, I am wondering how efficient could be a process
> where a iterate over a prepare statement like this:
>
> Insert into t (a,b,c) values( ?, ?, ?)
>
>
>
> Where at compile time the prepare have no clue if a rowset plan is
> appropriate or a non rowset plan is better.
>
> Suresh has explained me over the phone that compiler would assume rowset,
> and recompile in case it is single value… but then if I keep calling this
> prepared statement with various random number of items, how is the compiler
> going to successfully prepare, and keep reuse the same plan? (this is
> really the use-case I am dealing with: variable length rowset at every
> calls, can go from 1 to 10000)
>
>
>
> That is why I was hoping to force a fixed cardinality plan, to avoid this
> difficult situation with varying cardinality that I suspect will result in
> many recompiles?
>
>
>
> I know that this syntax would not be jdbc standard (so not portable)… But
> how about if I am ready to pay the price of using non-standard feature?
> Since there is no DB like ours anyway J…
>
> I guess this question is more for dev list, as I am inquiring about
> possible non standard stuff, given I see half of it is already working
> (successful compile of Insert into t (a,b,c) values( ?[1000], ?[1000], ?[1000])
> – I suspect there might be way to do the other half with some magic words …
> ?
>
>
>
> Eric
>
>
>
>
>
> *From:* Arvind GMAIL [mailto:narain.arvind@gmail.com]
> *Sent:* Friday, January 13, 2017 10:52 PM
> *To:* user@trafodion.incubator.apache.org
> *Subject:* RE: jdbc rowset usage?
>
>
>
> Hi Eric, Suresh
>
>
>
> You could also look at some the jdbc tests under the following for
> addbatch and related examples:
>
>
>
> https://github.com/apache/incubator-trafodion/tree/
> master/dcs/src/test/jdbc_test/src/test/java/org/trafodion/jdbc_test
>
>
>
> Thanks
>
> Arvind
>
>
>
>
>
>
>
> *From:* Suresh Subbiah [mailto:suresh.subbiah60@gmail.com
> <su...@gmail.com>]
> *Sent:* Friday, January 13, 2017 7:50 PM
> *To:* user@trafodion.incubator.apache.org
> *Subject:* Re: jdbc rowset usage?
>
>
>
> Hi Eric,
>
>
>
> The steps Selva mentioned are shown in the examples at https://examples.
> javacodegeeks.com/core-java/sql/jdbc-batch-insert-example/
>
> Section 4. in this example should be relevant. We should have similar
> examples in JDBC tests, but I am not able to find them.
>
>
>
> Thanks
>
> Suresh
>
>
>
>
>
> On Fri, Jan 13, 2017 at 6:40 PM, Selva Govindarajan <
> selva.govindarajan@esgyn.com> wrote:
>
> Just prepare the statement with '?' for parameters
>
> Do
>
>
>
> PreparedStatement.setXXX () for all parameters
>
> PreparedStatement.addBatch()
>
>
>
> Do the above in a loop, when you reach the required rowset size
>
>
>
> PreparedStatement.executeBatch().
>
>
>
> Selva
>
>
> ------------------------------
>
> *From:* Eric Owhadi <er...@esgyn.com>
> *Sent:* Friday, January 13, 2017 3:52 PM
> *To:* user@trafodion.incubator.apache.org
> *Subject:* jdbc rowset usage?
>
>
>
> Hello,
>
> I am struggling to find the jdbc syntax to set an dynamic array parameter:
>
>
>
> Assuming I prepared this statement s with “Insert into t (a,b,c)
> values( ?[1000], ?[1000], ?[1000])”
>
> How do I set each parameter?
>
>
>
> Assuming a is INT, b is CHAR[10], c is INT?
>
>
>
> Am I doing something not really supported? Should I use AddBatch instead?
>
> Thanks in advance for the help,
> Eric
>
>
>
>
>
>
>
RE: jdbc rowset usage?
Posted by Eric Owhadi <er...@esgyn.com>.
Hi Suresh, Arvin and Selva,
I understand the addBatch method. But I have a hard time understanding how compiler can do a good job not knowing the cardinality of the rowset at prepare time.
Since we support prepare statement like this:
Insert into t (a,b,c) values( ?[1000], ?[1000], ?[1000])
Where cardinality of the rowset is known at compile time, and I can see a very nice associated plan, I am wondering how efficient could be a process where a iterate over a prepare statement like this:
Insert into t (a,b,c) values( ?, ?, ?)
Where at compile time the prepare have no clue if a rowset plan is appropriate or a non rowset plan is better.
Suresh has explained me over the phone that compiler would assume rowset, and recompile in case it is single value… but then if I keep calling this prepared statement with various random number of items, how is the compiler going to successfully prepare, and keep reuse the same plan? (this is really the use-case I am dealing with: variable length rowset at every calls, can go from 1 to 10000)
That is why I was hoping to force a fixed cardinality plan, to avoid this difficult situation with varying cardinality that I suspect will result in many recompiles?
I know that this syntax would not be jdbc standard (so not portable)… But how about if I am ready to pay the price of using non-standard feature? Since there is no DB like ours anyway ☺…
I guess this question is more for dev list, as I am inquiring about possible non standard stuff, given I see half of it is already working (successful compile of Insert into t (a,b,c) values( ?[1000], ?[1000], ?[1000]) – I suspect there might be way to do the other half with some magic words … ?
Eric
From: Arvind GMAIL [mailto:narain.arvind@gmail.com]
Sent: Friday, January 13, 2017 10:52 PM
To: user@trafodion.incubator.apache.org
Subject: RE: jdbc rowset usage?
Hi Eric, Suresh
You could also look at some the jdbc tests under the following for addbatch and related examples:
https://github.com/apache/incubator-trafodion/tree/master/dcs/src/test/jdbc_test/src/test/java/org/trafodion/jdbc_test
Thanks
Arvind
From: Suresh Subbiah [mailto:suresh.subbiah60@gmail.com]
Sent: Friday, January 13, 2017 7:50 PM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: Re: jdbc rowset usage?
Hi Eric,
The steps Selva mentioned are shown in the examples at https://examples.javacodegeeks.com/core-java/sql/jdbc-batch-insert-example/
Section 4. in this example should be relevant. We should have similar examples in JDBC tests, but I am not able to find them.
Thanks
Suresh
On Fri, Jan 13, 2017 at 6:40 PM, Selva Govindarajan <se...@esgyn.com>> wrote:
Just prepare the statement with '?' for parameters
Do
PreparedStatement.setXXX () for all parameters
PreparedStatement.addBatch()
Do the above in a loop, when you reach the required rowset size
PreparedStatement.executeBatch().
Selva
________________________________
From: Eric Owhadi <er...@esgyn.com>>
Sent: Friday, January 13, 2017 3:52 PM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: jdbc rowset usage?
Hello,
I am struggling to find the jdbc syntax to set an dynamic array parameter:
Assuming I prepared this statement s with “Insert into t (a,b,c) values( ?[1000], ?[1000], ?[1000])”
How do I set each parameter?
Assuming a is INT, b is CHAR[10], c is INT?
Am I doing something not really supported? Should I use AddBatch instead?
Thanks in advance for the help,
Eric
RE: jdbc rowset usage?
Posted by Arvind GMAIL <na...@gmail.com>.
Hi Eric, Suresh
You could also look at some the jdbc tests under the following for addbatch and related examples:
https://github.com/apache/incubator-trafodion/tree/master/dcs/src/test/jdbc_test/src/test/java/org/trafodion/jdbc_test
Thanks
Arvind
From: Suresh Subbiah [mailto:suresh.subbiah60@gmail.com]
Sent: Friday, January 13, 2017 7:50 PM
To: user@trafodion.incubator.apache.org
Subject: Re: jdbc rowset usage?
Hi Eric,
The steps Selva mentioned are shown in the examples at https://examples.javacodegeeks.com/core-java/sql/jdbc-batch-insert-example/
Section 4. in this example should be relevant. We should have similar examples in JDBC tests, but I am not able to find them.
Thanks
Suresh
On Fri, Jan 13, 2017 at 6:40 PM, Selva Govindarajan <selva.govindarajan@esgyn.com <ma...@esgyn.com> > wrote:
Just prepare the statement with '?' for parameters
Do
PreparedStatement.setXXX () for all parameters
PreparedStatement.addBatch()
Do the above in a loop, when you reach the required rowset size
PreparedStatement.executeBatch().
Selva
_____
From: Eric Owhadi <eric.owhadi@esgyn.com <ma...@esgyn.com> >
Sent: Friday, January 13, 2017 3:52 PM
To: user@trafodion.incubator.apache.org <ma...@trafodion.incubator.apache.org>
Subject: jdbc rowset usage?
Hello,
I am struggling to find the jdbc syntax to set an dynamic array parameter:
Assuming I prepared this statement s with “Insert into t (a,b,c) values( ?[1000], ?[1000], ?[1000])”
How do I set each parameter?
Assuming a is INT, b is CHAR[10], c is INT?
Am I doing something not really supported? Should I use AddBatch instead?
Thanks in advance for the help,
Eric
Re: jdbc rowset usage?
Posted by Suresh Subbiah <su...@gmail.com>.
Hi Eric,
The steps Selva mentioned are shown in the examples at
https://examples.javacodegeeks.com/core-java/sql/jdbc-batch-insert-example/
Section 4. in this example should be relevant. We should have similar
examples in JDBC tests, but I am not able to find them.
Thanks
Suresh
On Fri, Jan 13, 2017 at 6:40 PM, Selva Govindarajan <
selva.govindarajan@esgyn.com> wrote:
> Just prepare the statement with '?' for parameters
>
> Do
>
>
> PreparedStatement.setXXX () for all parameters
>
> PreparedStatement.addBatch()
>
>
> Do the above in a loop, when you reach the required rowset size
>
>
> PreparedStatement.executeBatch().
>
>
> Selva
>
>
> ------------------------------
> *From:* Eric Owhadi <er...@esgyn.com>
> *Sent:* Friday, January 13, 2017 3:52 PM
> *To:* user@trafodion.incubator.apache.org
> *Subject:* jdbc rowset usage?
>
>
> Hello,
>
> I am struggling to find the jdbc syntax to set an dynamic array parameter:
>
>
>
> Assuming I prepared this statement s with “Insert into t (a,b,c)
> values( ?[1000], ?[1000], ?[1000])”
>
> How do I set each parameter?
>
>
>
> Assuming a is INT, b is CHAR[10], c is INT?
>
>
>
> Am I doing something not really supported? Should I use AddBatch instead?
>
> Thanks in advance for the help,
> Eric
>
>
>
>
>
Re: jdbc rowset usage?
Posted by Selva Govindarajan <se...@esgyn.com>.
Just prepare the statement with '?' for parameters
Do
PreparedStatement.setXXX () for all parameters
PreparedStatement.addBatch()
Do the above in a loop, when you reach the required rowset size
PreparedStatement.executeBatch().
Selva
________________________________
From: Eric Owhadi <er...@esgyn.com>
Sent: Friday, January 13, 2017 3:52 PM
To: user@trafodion.incubator.apache.org
Subject: jdbc rowset usage?
Hello,
I am struggling to find the jdbc syntax to set an dynamic array parameter:
Assuming I prepared this statement s with "Insert into t (a,b,c) values( ?[1000], ?[1000], ?[1000])"
How do I set each parameter?
Assuming a is INT, b is CHAR[10], c is INT?
Am I doing something not really supported? Should I use AddBatch instead?
Thanks in advance for the help,
Eric