You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@jmeter.apache.org by Steve Miller <st...@openwave.com> on 2008/03/03 16:41:40 UTC
JDBC Batch insert
I've got some JDBC samplers running, and for one of them what I really
want to do is a prepared statement that inserts many (from 10 to 100)
rows at once. JDBC has the PreparedStatement.addBatch() method, along
with PreparedStatement.executeBatch(). Is there a convenient way to do
the equivalent with JMeter? I've seen an Oracle 'kludge' that allows you
to do a 'batch insert' with an sql statement like:
INSERT INTO table (column 1, column 2) VALUES (
select value1a, value2a from dual union all
select value1b, value2b from dual union all
...
select value1...,value2... from dual
);
but that would clearly get very awkward at best using the parameter
values and parameter type input fields on the JDBC sampler.
thanks,
Steve
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
Re: JDBC Batch insert
Posted by Steve Miller <st...@openwave.com>.
Hi Ian,
Not I. There are lots of 'us' around ;-)
Steve
Ian Blavins wrote:
> G'day
>
> Most unlikely but you wouldn't be the Steve Miller who used to work for Clark County NV would you ?
>
>
> Ian Blavins
> Software performance specialist
> .
> TEMENOS
> The Banking Software Company
> .
> PeopleBuilding 2, Maylands Av
> Hemel Hempstead UK HP2 4NW
> .
> T: +44 (0) 1442 431 106
> E: iblavins@temenos.com
> .
> www.temenos.com
> .
> Consider the environment. Please don't print this e-mail unless you really need to.
>
> -----Original Message-----
> From: Steve Miller [mailto:steve.miller@openwave.com]
> Sent: Monday, 3 March 2008 3:42 PM
> To: jmeter-user@jakarta.apache.org
> Subject: JDBC Batch insert
>
> I've got some JDBC samplers running, and for one of them what I really
> want to do is a prepared statement that inserts many (from 10 to 100)
> rows at once. JDBC has the PreparedStatement.addBatch() method, along
> with PreparedStatement.executeBatch(). Is there a convenient way to do
> the equivalent with JMeter? I've seen an Oracle 'kludge' that allows you
> to do a 'batch insert' with an sql statement like:
> INSERT INTO table (column 1, column 2) VALUES (
> select value1a, value2a from dual union all
> select value1b, value2b from dual union all
> ...
> select value1...,value2... from dual
> );
>
> but that would clearly get very awkward at best using the parameter
> values and parameter type input fields on the JDBC sampler.
>
> thanks,
>
> Steve
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
RE: JDBC Batch insert
Posted by Ian Blavins <ib...@temenos.com>.
G'day
Most unlikely but you wouldn't be the Steve Miller who used to work for Clark County NV would you ?
Ian Blavins
Software performance specialist
.
TEMENOS
The Banking Software Company
.
PeopleBuilding 2, Maylands Av
Hemel Hempstead UK HP2 4NW
.
T: +44 (0) 1442 431 106
E: iblavins@temenos.com
.
www.temenos.com
.
Consider the environment. Please don't print this e-mail unless you really need to.
-----Original Message-----
From: Steve Miller [mailto:steve.miller@openwave.com]
Sent: Monday, 3 March 2008 3:42 PM
To: jmeter-user@jakarta.apache.org
Subject: JDBC Batch insert
I've got some JDBC samplers running, and for one of them what I really
want to do is a prepared statement that inserts many (from 10 to 100)
rows at once. JDBC has the PreparedStatement.addBatch() method, along
with PreparedStatement.executeBatch(). Is there a convenient way to do
the equivalent with JMeter? I've seen an Oracle 'kludge' that allows you
to do a 'batch insert' with an sql statement like:
INSERT INTO table (column 1, column 2) VALUES (
select value1a, value2a from dual union all
select value1b, value2b from dual union all
...
select value1...,value2... from dual
);
but that would clearly get very awkward at best using the parameter
values and parameter type input fields on the JDBC sampler.
thanks,
Steve
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
Re: JDBC Batch insert
Posted by Steve Miller <st...@openwave.com>.
Thanks Abram,
Yes, I'm trying to test batch insert performance, and yes, so far I have
worked around it by individual insert statements for 1 row each.
Folklore says batch insert is considerably more efficient, and it would
be appropriate for the application we are using. I'll have to do the
batch insert testing using our application code instead of jmeter, and
using the Oracle performance reports as well. Too bad ;-)
Steve
Abram Catalano wrote:
> Thats what I was looking for too, but worked around it by the
> individual insert statements.
> It depends what type of test you are writing. Why do you need
> batching? Are you loading sample data for tests to run against, or
> are you actually testing the batch insert performance of the DB?
> If the latter, there are probably better ways to test DB performance.
> If the former, who cares if data insert is a little slow?
>
> These are just questions to ask yourself (that I asked myself) - I
> don't know what your specific requirements are. :)
>
> -Abram
>
>
> Steve Miller wrote:
>> Thanks. Perhaps I'm misinterpreting, but what I am really looking for
>> is a single INSERT operation that adds 100 rows in a single
>> transaction, then run this single Insert of 100 rows repeatedly at
>> some rate or as fast-as-possible.
>>
>> Steve
>>
>> Abram Catalano wrote:
>>> Steve, what sebb describes is what I have done, btw.
>>>
>>> a Loop Controller, with a JDBC sampler child (with a single insert
>>> statement with ${variables} defined in the CSV Data Config Set),
>>> which has a child of "CSV Data Set Config"
>>> works quite well - you just don't get batch performance.
>>>
>>> -Abram
>>>
>>>
>>> sebb wrote:
>>>> On 03/03/2008, Steve Miller <st...@openwave.com> wrote:
>>>>
>>>>> Thanks Sebb,
>>>>>
>>>>> I would expect the input data to be defined with either:
>>>>> - some columns random and some columns fixed for each row
>>>>> or
>>>>> - ~reading next n CSV or XML rows from a file for each batch, but
>>>>> use
>>>>> same file each time invoked, thus 'marching-thru' the file data
>>>>> over time.
>>>>>
>>>>
>>>> That's how the CSV Dataset works already.
>>>>
>>>> I've now had a look at the docs, and AFAICS the JDBC "batch" feature
>>>> is not intended for batch loading, it seems to be intended to process
>>>> multiple different SQL statements as a single batch that either
>>>> succeeds or fails as a whole.
>>>>
>>>>
>>>>> One convenient approach, at least from the GUI perspective, could
>>>>> be to
>>>>> add another variable, something like 'repeatCount', to the JDBC
>>>>> Sampler,
>>>>> at least for inserts, default to 1, and if set to >1 then
>>>>> whatever was
>>>>> specified for the parameter values, e.g. mix of random and fixed,
>>>>> would
>>>>> be repeated for each row, though separate instantiations of the
>>>>> 'random'
>>>>> per row/column.
>>>>>
>>>>
>>>> Just enclose the sampler in a Loop Controller, and use a function to
>>>> generate the 'random' data.
>>>>
>>>>
>>>>> This isn't critical to my testing, I'm just doing more single row
>>>>> inserts now as a first approximation, though I need to be
>>>>> cautious about
>>>>> potential differences in latency to concurrent read operations.
>>>>>
>>>>>
>>>>> Steve
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> sebb wrote:
>>>>> > On 03/03/2008, Steve Miller <st...@openwave.com> wrote:
>>>>> >
>>>>> >> I've got some JDBC samplers running, and for one of them what
>>>>> I really
>>>>> >> want to do is a prepared statement that inserts many (from 10
>>>>> to 100)
>>>>> >> rows at once. JDBC has the PreparedStatement.addBatch()
>>>>> method, along
>>>>> >> with PreparedStatement.executeBatch(). Is there a convenient
>>>>> way to do
>>>>> >> the equivalent with JMeter?
>>>>> >>
>>>>> >
>>>>> > Not at present.
>>>>> >
>>>>> > One could put the insert values in a file and loop over them
>>>>> with an
>>>>> > Insert statement.
>>>>> >
>>>>> > If the batch feature was to be added, how would you expect the
>>>>> input
>>>>> > data to be defined?
>>>>> >
>>>>> >
>>>>> >> I've seen an Oracle 'kludge' that allows you
>>>>> >> to do a 'batch insert' with an sql statement like:
>>>>> >> INSERT INTO table (column 1, column 2) VALUES (
>>>>> >> select value1a, value2a from dual union all
>>>>> >> select value1b, value2b from dual union all
>>>>> >> ...
>>>>> >> select value1...,value2... from dual
>>>>> >> );
>>>>> >>
>>>>> >> but that would clearly get very awkward at best using the
>>>>> parameter
>>>>> >> values and parameter type input fields on the JDBC sampler.
>>>>> >>
>>>>> >> thanks,
>>>>> >>
>>>>> >
>>>>> > If there are a lot of rows to be added, I would probably do this
>>>>> > before starting the test using whatever utility is most suitable.
>>>>> >
>>>>> >
>>>>> >> Steve
>>>>> >>
>>>>> >>
>>>>> ---------------------------------------------------------------------
>>>>> >> To unsubscribe, e-mail:
>>>>> jmeter-user-unsubscribe@jakarta.apache.org
>>>>> >> For additional commands, e-mail:
>>>>> jmeter-user-help@jakarta.apache.org
>>>>> >>
>>>>> >>
>>>>> >>
>>>>> >
>>>>> >
>>>>> ---------------------------------------------------------------------
>>>>> > To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>>>>> > For additional commands, e-mail:
>>>>> jmeter-user-help@jakarta.apache.org
>>>>> >
>>>>> >
>>>>> >
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>>
>>>>> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>>>>> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>>>>>
>>>>>
>>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>>>> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>>>>
>>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>>> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
Re: JDBC Batch insert
Posted by Abram Catalano <ab...@adaptx.com>.
Thats what I was looking for too, but worked around it by the individual
insert statements.
It depends what type of test you are writing. Why do you need batching?
Are you loading sample data for tests to run against, or are you
actually testing the batch insert performance of the DB?
If the latter, there are probably better ways to test DB performance.
If the former, who cares if data insert is a little slow?
These are just questions to ask yourself (that I asked myself) - I don't
know what your specific requirements are. :)
-Abram
Steve Miller wrote:
> Thanks. Perhaps I'm misinterpreting, but what I am really looking for
> is a single INSERT operation that adds 100 rows in a single
> transaction, then run this single Insert of 100 rows repeatedly at
> some rate or as fast-as-possible.
>
> Steve
>
> Abram Catalano wrote:
>> Steve, what sebb describes is what I have done, btw.
>>
>> a Loop Controller, with a JDBC sampler child (with a single insert
>> statement with ${variables} defined in the CSV Data Config Set),
>> which has a child of "CSV Data Set Config"
>> works quite well - you just don't get batch performance.
>>
>> -Abram
>>
>>
>> sebb wrote:
>>> On 03/03/2008, Steve Miller <st...@openwave.com> wrote:
>>>
>>>> Thanks Sebb,
>>>>
>>>> I would expect the input data to be defined with either:
>>>> - some columns random and some columns fixed for each row
>>>> or
>>>> - ~reading next n CSV or XML rows from a file for each batch, but use
>>>> same file each time invoked, thus 'marching-thru' the file data
>>>> over time.
>>>>
>>>
>>> That's how the CSV Dataset works already.
>>>
>>> I've now had a look at the docs, and AFAICS the JDBC "batch" feature
>>> is not intended for batch loading, it seems to be intended to process
>>> multiple different SQL statements as a single batch that either
>>> succeeds or fails as a whole.
>>>
>>>
>>>> One convenient approach, at least from the GUI perspective, could
>>>> be to
>>>> add another variable, something like 'repeatCount', to the JDBC
>>>> Sampler,
>>>> at least for inserts, default to 1, and if set to >1 then whatever
>>>> was
>>>> specified for the parameter values, e.g. mix of random and fixed,
>>>> would
>>>> be repeated for each row, though separate instantiations of the
>>>> 'random'
>>>> per row/column.
>>>>
>>>
>>> Just enclose the sampler in a Loop Controller, and use a function to
>>> generate the 'random' data.
>>>
>>>
>>>> This isn't critical to my testing, I'm just doing more single row
>>>> inserts now as a first approximation, though I need to be cautious
>>>> about
>>>> potential differences in latency to concurrent read operations.
>>>>
>>>>
>>>> Steve
>>>>
>>>>
>>>>
>>>>
>>>> sebb wrote:
>>>> > On 03/03/2008, Steve Miller <st...@openwave.com> wrote:
>>>> >
>>>> >> I've got some JDBC samplers running, and for one of them what I
>>>> really
>>>> >> want to do is a prepared statement that inserts many (from 10
>>>> to 100)
>>>> >> rows at once. JDBC has the PreparedStatement.addBatch()
>>>> method, along
>>>> >> with PreparedStatement.executeBatch(). Is there a convenient
>>>> way to do
>>>> >> the equivalent with JMeter?
>>>> >>
>>>> >
>>>> > Not at present.
>>>> >
>>>> > One could put the insert values in a file and loop over them
>>>> with an
>>>> > Insert statement.
>>>> >
>>>> > If the batch feature was to be added, how would you expect the
>>>> input
>>>> > data to be defined?
>>>> >
>>>> >
>>>> >> I've seen an Oracle 'kludge' that allows you
>>>> >> to do a 'batch insert' with an sql statement like:
>>>> >> INSERT INTO table (column 1, column 2) VALUES (
>>>> >> select value1a, value2a from dual union all
>>>> >> select value1b, value2b from dual union all
>>>> >> ...
>>>> >> select value1...,value2... from dual
>>>> >> );
>>>> >>
>>>> >> but that would clearly get very awkward at best using the
>>>> parameter
>>>> >> values and parameter type input fields on the JDBC sampler.
>>>> >>
>>>> >> thanks,
>>>> >>
>>>> >
>>>> > If there are a lot of rows to be added, I would probably do this
>>>> > before starting the test using whatever utility is most suitable.
>>>> >
>>>> >
>>>> >> Steve
>>>> >>
>>>> >>
>>>> ---------------------------------------------------------------------
>>>> >> To unsubscribe, e-mail:
>>>> jmeter-user-unsubscribe@jakarta.apache.org
>>>> >> For additional commands, e-mail:
>>>> jmeter-user-help@jakarta.apache.org
>>>> >>
>>>> >>
>>>> >>
>>>> >
>>>> >
>>>> ---------------------------------------------------------------------
>>>> > To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>>>> > For additional commands, e-mail:
>>>> jmeter-user-help@jakarta.apache.org
>>>> >
>>>> >
>>>> >
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>>>> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>>>>
>>>>
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>>> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>>>
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
Re: JDBC Batch insert
Posted by Steve Miller <st...@openwave.com>.
Thanks. Perhaps I'm misinterpreting, but what I am really looking for is
a single INSERT operation that adds 100 rows in a single transaction,
then run this single Insert of 100 rows repeatedly at some rate or as
fast-as-possible.
Steve
Abram Catalano wrote:
> Steve, what sebb describes is what I have done, btw.
>
> a Loop Controller, with a JDBC sampler child (with a single insert
> statement with ${variables} defined in the CSV Data Config Set), which
> has a child of "CSV Data Set Config"
> works quite well - you just don't get batch performance.
>
> -Abram
>
>
> sebb wrote:
>> On 03/03/2008, Steve Miller <st...@openwave.com> wrote:
>>
>>> Thanks Sebb,
>>>
>>> I would expect the input data to be defined with either:
>>> - some columns random and some columns fixed for each row
>>> or
>>> - ~reading next n CSV or XML rows from a file for each batch, but use
>>> same file each time invoked, thus 'marching-thru' the file data
>>> over time.
>>>
>>
>> That's how the CSV Dataset works already.
>>
>> I've now had a look at the docs, and AFAICS the JDBC "batch" feature
>> is not intended for batch loading, it seems to be intended to process
>> multiple different SQL statements as a single batch that either
>> succeeds or fails as a whole.
>>
>>
>>> One convenient approach, at least from the GUI perspective, could
>>> be to
>>> add another variable, something like 'repeatCount', to the JDBC
>>> Sampler,
>>> at least for inserts, default to 1, and if set to >1 then whatever was
>>> specified for the parameter values, e.g. mix of random and fixed,
>>> would
>>> be repeated for each row, though separate instantiations of the
>>> 'random'
>>> per row/column.
>>>
>>
>> Just enclose the sampler in a Loop Controller, and use a function to
>> generate the 'random' data.
>>
>>
>>> This isn't critical to my testing, I'm just doing more single row
>>> inserts now as a first approximation, though I need to be cautious
>>> about
>>> potential differences in latency to concurrent read operations.
>>>
>>>
>>> Steve
>>>
>>>
>>>
>>>
>>> sebb wrote:
>>> > On 03/03/2008, Steve Miller <st...@openwave.com> wrote:
>>> >
>>> >> I've got some JDBC samplers running, and for one of them what I
>>> really
>>> >> want to do is a prepared statement that inserts many (from 10
>>> to 100)
>>> >> rows at once. JDBC has the PreparedStatement.addBatch() method,
>>> along
>>> >> with PreparedStatement.executeBatch(). Is there a convenient
>>> way to do
>>> >> the equivalent with JMeter?
>>> >>
>>> >
>>> > Not at present.
>>> >
>>> > One could put the insert values in a file and loop over them with an
>>> > Insert statement.
>>> >
>>> > If the batch feature was to be added, how would you expect the input
>>> > data to be defined?
>>> >
>>> >
>>> >> I've seen an Oracle 'kludge' that allows you
>>> >> to do a 'batch insert' with an sql statement like:
>>> >> INSERT INTO table (column 1, column 2) VALUES (
>>> >> select value1a, value2a from dual union all
>>> >> select value1b, value2b from dual union all
>>> >> ...
>>> >> select value1...,value2... from dual
>>> >> );
>>> >>
>>> >> but that would clearly get very awkward at best using the
>>> parameter
>>> >> values and parameter type input fields on the JDBC sampler.
>>> >>
>>> >> thanks,
>>> >>
>>> >
>>> > If there are a lot of rows to be added, I would probably do this
>>> > before starting the test using whatever utility is most suitable.
>>> >
>>> >
>>> >> Steve
>>> >>
>>> >>
>>> ---------------------------------------------------------------------
>>> >> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>>> >> For additional commands, e-mail:
>>> jmeter-user-help@jakarta.apache.org
>>> >>
>>> >>
>>> >>
>>> >
>>> >
>>> ---------------------------------------------------------------------
>>> > To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>>> > For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>>> >
>>> >
>>> >
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>>> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>>>
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
Re: JDBC Batch insert
Posted by Abram Catalano <ab...@adaptx.com>.
Steve, what sebb describes is what I have done, btw.
a Loop Controller, with a JDBC sampler child (with a single insert
statement with ${variables} defined in the CSV Data Config Set), which
has a child of "CSV Data Set Config"
works quite well - you just don't get batch performance.
-Abram
sebb wrote:
> On 03/03/2008, Steve Miller <st...@openwave.com> wrote:
>
>> Thanks Sebb,
>>
>> I would expect the input data to be defined with either:
>> - some columns random and some columns fixed for each row
>> or
>> - ~reading next n CSV or XML rows from a file for each batch, but use
>> same file each time invoked, thus 'marching-thru' the file data over time.
>>
>
> That's how the CSV Dataset works already.
>
> I've now had a look at the docs, and AFAICS the JDBC "batch" feature
> is not intended for batch loading, it seems to be intended to process
> multiple different SQL statements as a single batch that either
> succeeds or fails as a whole.
>
>
>> One convenient approach, at least from the GUI perspective, could be to
>> add another variable, something like 'repeatCount', to the JDBC Sampler,
>> at least for inserts, default to 1, and if set to >1 then whatever was
>> specified for the parameter values, e.g. mix of random and fixed, would
>> be repeated for each row, though separate instantiations of the 'random'
>> per row/column.
>>
>
> Just enclose the sampler in a Loop Controller, and use a function to
> generate the 'random' data.
>
>
>> This isn't critical to my testing, I'm just doing more single row
>> inserts now as a first approximation, though I need to be cautious about
>> potential differences in latency to concurrent read operations.
>>
>>
>> Steve
>>
>>
>>
>>
>> sebb wrote:
>> > On 03/03/2008, Steve Miller <st...@openwave.com> wrote:
>> >
>> >> I've got some JDBC samplers running, and for one of them what I really
>> >> want to do is a prepared statement that inserts many (from 10 to 100)
>> >> rows at once. JDBC has the PreparedStatement.addBatch() method, along
>> >> with PreparedStatement.executeBatch(). Is there a convenient way to do
>> >> the equivalent with JMeter?
>> >>
>> >
>> > Not at present.
>> >
>> > One could put the insert values in a file and loop over them with an
>> > Insert statement.
>> >
>> > If the batch feature was to be added, how would you expect the input
>> > data to be defined?
>> >
>> >
>> >> I've seen an Oracle 'kludge' that allows you
>> >> to do a 'batch insert' with an sql statement like:
>> >> INSERT INTO table (column 1, column 2) VALUES (
>> >> select value1a, value2a from dual union all
>> >> select value1b, value2b from dual union all
>> >> ...
>> >> select value1...,value2... from dual
>> >> );
>> >>
>> >> but that would clearly get very awkward at best using the parameter
>> >> values and parameter type input fields on the JDBC sampler.
>> >>
>> >> thanks,
>> >>
>> >
>> > If there are a lot of rows to be added, I would probably do this
>> > before starting the test using whatever utility is most suitable.
>> >
>> >
>> >> Steve
>> >>
>> >> ---------------------------------------------------------------------
>> >> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>> >> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>> >>
>> >>
>> >>
>> >
>> > ---------------------------------------------------------------------
>> > To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>> > For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>> >
>> >
>> >
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
Re: JDBC Batch insert
Posted by sebb <se...@gmail.com>.
On 03/03/2008, Steve Miller <st...@openwave.com> wrote:
> Thanks Sebb,
>
> I would expect the input data to be defined with either:
> - some columns random and some columns fixed for each row
> or
> - ~reading next n CSV or XML rows from a file for each batch, but use
> same file each time invoked, thus 'marching-thru' the file data over time.
That's how the CSV Dataset works already.
I've now had a look at the docs, and AFAICS the JDBC "batch" feature
is not intended for batch loading, it seems to be intended to process
multiple different SQL statements as a single batch that either
succeeds or fails as a whole.
>
> One convenient approach, at least from the GUI perspective, could be to
> add another variable, something like 'repeatCount', to the JDBC Sampler,
> at least for inserts, default to 1, and if set to >1 then whatever was
> specified for the parameter values, e.g. mix of random and fixed, would
> be repeated for each row, though separate instantiations of the 'random'
> per row/column.
Just enclose the sampler in a Loop Controller, and use a function to
generate the 'random' data.
> This isn't critical to my testing, I'm just doing more single row
> inserts now as a first approximation, though I need to be cautious about
> potential differences in latency to concurrent read operations.
>
>
> Steve
>
>
>
>
> sebb wrote:
> > On 03/03/2008, Steve Miller <st...@openwave.com> wrote:
> >
> >> I've got some JDBC samplers running, and for one of them what I really
> >> want to do is a prepared statement that inserts many (from 10 to 100)
> >> rows at once. JDBC has the PreparedStatement.addBatch() method, along
> >> with PreparedStatement.executeBatch(). Is there a convenient way to do
> >> the equivalent with JMeter?
> >>
> >
> > Not at present.
> >
> > One could put the insert values in a file and loop over them with an
> > Insert statement.
> >
> > If the batch feature was to be added, how would you expect the input
> > data to be defined?
> >
> >
> >> I've seen an Oracle 'kludge' that allows you
> >> to do a 'batch insert' with an sql statement like:
> >> INSERT INTO table (column 1, column 2) VALUES (
> >> select value1a, value2a from dual union all
> >> select value1b, value2b from dual union all
> >> ...
> >> select value1...,value2... from dual
> >> );
> >>
> >> but that would clearly get very awkward at best using the parameter
> >> values and parameter type input fields on the JDBC sampler.
> >>
> >> thanks,
> >>
> >
> > If there are a lot of rows to be added, I would probably do this
> > before starting the test using whatever utility is most suitable.
> >
> >
> >> Steve
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> >> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
> >>
> >>
> >>
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
> >
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
Re: JDBC Batch insert
Posted by Steve Miller <st...@openwave.com>.
Thanks Sebb,
I would expect the input data to be defined with either:
- some columns random and some columns fixed for each row
or
- ~reading next n CSV or XML rows from a file for each batch, but use
same file each time invoked, thus 'marching-thru' the file data over time.
One convenient approach, at least from the GUI perspective, could be to
add another variable, something like 'repeatCount', to the JDBC Sampler,
at least for inserts, default to 1, and if set to >1 then whatever was
specified for the parameter values, e.g. mix of random and fixed, would
be repeated for each row, though separate instantiations of the 'random'
per row/column.
This isn't critical to my testing, I'm just doing more single row
inserts now as a first approximation, though I need to be cautious about
potential differences in latency to concurrent read operations.
Steve
sebb wrote:
> On 03/03/2008, Steve Miller <st...@openwave.com> wrote:
>
>> I've got some JDBC samplers running, and for one of them what I really
>> want to do is a prepared statement that inserts many (from 10 to 100)
>> rows at once. JDBC has the PreparedStatement.addBatch() method, along
>> with PreparedStatement.executeBatch(). Is there a convenient way to do
>> the equivalent with JMeter?
>>
>
> Not at present.
>
> One could put the insert values in a file and loop over them with an
> Insert statement.
>
> If the batch feature was to be added, how would you expect the input
> data to be defined?
>
>
>> I've seen an Oracle 'kludge' that allows you
>> to do a 'batch insert' with an sql statement like:
>> INSERT INTO table (column 1, column 2) VALUES (
>> select value1a, value2a from dual union all
>> select value1b, value2b from dual union all
>> ...
>> select value1...,value2... from dual
>> );
>>
>> but that would clearly get very awkward at best using the parameter
>> values and parameter type input fields on the JDBC sampler.
>>
>> thanks,
>>
>
> If there are a lot of rows to be added, I would probably do this
> before starting the test using whatever utility is most suitable.
>
>
>> Steve
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
Re: JDBC Batch insert
Posted by sebb <se...@gmail.com>.
On 03/03/2008, Steve Miller <st...@openwave.com> wrote:
> I've got some JDBC samplers running, and for one of them what I really
> want to do is a prepared statement that inserts many (from 10 to 100)
> rows at once. JDBC has the PreparedStatement.addBatch() method, along
> with PreparedStatement.executeBatch(). Is there a convenient way to do
> the equivalent with JMeter?
Not at present.
One could put the insert values in a file and loop over them with an
Insert statement.
If the batch feature was to be added, how would you expect the input
data to be defined?
> I've seen an Oracle 'kludge' that allows you
> to do a 'batch insert' with an sql statement like:
> INSERT INTO table (column 1, column 2) VALUES (
> select value1a, value2a from dual union all
> select value1b, value2b from dual union all
> ...
> select value1...,value2... from dual
> );
>
> but that would clearly get very awkward at best using the parameter
> values and parameter type input fields on the JDBC sampler.
>
> thanks,
If there are a lot of rows to be added, I would probably do this
before starting the test using whatever utility is most suitable.
> Steve
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org