You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@jmeter.apache.org by welp <pe...@trivadis.com> on 2009/07/29 12:41:01 UTC
JDBC sampler overhead and the number of records returned
Hoi,
for testing Oracle JDBC responsetimes of "not-very-short" running queries I
recognized, that the size of the resultset (i.e. number of records) impacts
the measured time somehow exponentially when exceeding e.g. 1000 records.
Example:
Query X with a result of 1000 records needs 2 seconds
Query X with a result of 2000 records needs 4 seconds
Query X with a result of 4000 records needs 15 seconds etc.
During the test, I recognized one CPU (Core) beeing completely used by
JMeter while the network connection shows almost no traffic and the Oracle
DB only shows waits for the client ("SQL*Net message from client") which
usually indicates that Oracle waits for the client gathering availiable
results.
For comparison I executed the same queries manually (via sqldeveloper also
using the same JDBC driver and a fetchsize of 5000) just fetching all
records and listing them. It took 1.8 seconds for 4000 records.
First I assumed that this may come from feeding the listeners and checking
contents in assertions but when I completely removed all listeners and
assertions from my JMeter testplan, the query with 4000 records still
required 15 seconds to finish.
Is there any way to overcome this overhead? I do not want to measure
Jmeter's overhead but only DB Query response time and JDBC resultset
fetching. I do not need assertions and can live with less features on this
sampler.
unfortunately, I can not simply reduce the size of my resultsets for various
reasons...
Thanks a lot in advance!
Regards
Peter
--
View this message in context: http://www.nabble.com/JDBC-sampler-overhead-and-the-number-of-records-returned-tp24716684p24716684.html
Sent from the JMeter - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
Re: JDBC sampler overhead and the number of records returned
Posted by sebb <se...@gmail.com>.
On 29/07/2009, welp <pe...@trivadis.com> wrote:
>
> Hoi,
>
> for testing Oracle JDBC responsetimes of "not-very-short" running queries I
> recognized, that the size of the resultset (i.e. number of records) impacts
> the measured time somehow exponentially when exceeding e.g. 1000 records.
> Example:
>
> Query X with a result of 1000 records needs 2 seconds
> Query X with a result of 2000 records needs 4 seconds
> Query X with a result of 4000 records needs 15 seconds etc.
>
> During the test, I recognized one CPU (Core) beeing completely used by
> JMeter while the network connection shows almost no traffic and the Oracle
> DB only shows waits for the client ("SQL*Net message from client") which
> usually indicates that Oracle waits for the client gathering availiable
> results.
>
> For comparison I executed the same queries manually (via sqldeveloper also
> using the same JDBC driver and a fetchsize of 5000) just fetching all
> records and listing them. It took 1.8 seconds for 4000 records.
>
> First I assumed that this may come from feeding the listeners and checking
> contents in assertions but when I completely removed all listeners and
> assertions from my JMeter testplan, the query with 4000 records still
> required 15 seconds to finish.
>
> Is there any way to overcome this overhead? I do not want to measure
> Jmeter's overhead but only DB Query response time and JDBC resultset
> fetching. I do not need assertions and can live with less features on this
> sampler.
>
> unfortunately, I can not simply reduce the size of my resultsets for various
> reasons...
The time is presumably in processing the responses in the sampler; it
has to store them all in the sample response.
Unfortunately there's currently no way to skip this processing.
> Thanks a lot in advance!
> Regards
> Peter
>
> --
> View this message in context: http://www.nabble.com/JDBC-sampler-overhead-and-the-number-of-records-returned-tp24716684p24716684.html
> Sent from the JMeter - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> 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 sampler overhead and the number of records returned
Posted by welp <pe...@trivadis.com>.
sebb-2-2 wrote:
>
> On 04/08/2009, welp <pe...@trivadis.com> wrote:
>>
>> Thanks!
>>
>> However, I could think of limiting the JDBC Sampler resultset looping to
>> 500
>> records (limiting the SQL itself is not possible since some Query
>> Optimizer
>> know to take advantage from this and choose an unadequate execution
>> plan). I
>
> Would the Quey Optimizer change the plan if you changed the query to
> return a count of the number of records instead?
>
>> could live with checking the first 500 records for example and then just
>> close the resultset as if it was completed.
>>
>> Any hint where and how I can change this behaviour e.g. by creating an
>> derived JDBC Sampler "JDBC Sampler Max 500"?
>
> You can just update the existing sampler code.
> E.g. add a 'max results' variable to the GUI (or use a property for
> simplicity) and plug that into the retrieval loop.
>
>> Thanks a lot in advance and Regards
>> Peter
>>
>
Hi,
starting with Oracle 10g, yes, the Query Optimizer is aware of a "ROWNUM <=
100" filter and may adjust the plan (there is a workaround with inline views
and hints but this is a mess and quite intransparent). However, I just
changed the JDBCSampler class (parallel to your suggestion) and it works.
That's OK for me.
Thanks and Regards,
Peter
--
View this message in context: http://www.nabble.com/JDBC-sampler-overhead-and-the-number-of-records-returned-tp24716684p24807439.html
Sent from the JMeter - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
Re: JDBC sampler overhead and the number of records returned
Posted by sebb <se...@gmail.com>.
On 04/08/2009, welp <pe...@trivadis.com> wrote:
>
> Thanks!
>
> However, I could think of limiting the JDBC Sampler resultset looping to 500
> records (limiting the SQL itself is not possible since some Query Optimizer
> know to take advantage from this and choose an unadequate execution plan). I
Would the Quey Optimizer change the plan if you changed the query to
return a count of the number of records instead?
> could live with checking the first 500 records for example and then just
> close the resultset as if it was completed.
>
> Any hint where and how I can change this behaviour e.g. by creating an
> derived JDBC Sampler "JDBC Sampler Max 500"?
You can just update the existing sampler code.
E.g. add a 'max results' variable to the GUI (or use a property for
simplicity) and plug that into the retrieval loop.
> Thanks a lot in advance and Regards
> Peter
>
> --
> View this message in context: http://www.nabble.com/JDBC-sampler-overhead-and-the-number-of-records-returned-tp24716684p24804628.html
>
> Sent from the JMeter - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> 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 sampler overhead and the number of records returned
Posted by welp <pe...@trivadis.com>.
Thanks!
However, I could think of limiting the JDBC Sampler resultset looping to 500
records (limiting the SQL itself is not possible since some Query Optimizer
know to take advantage from this and choose an unadequate execution plan). I
could live with checking the first 500 records for example and then just
close the resultset as if it was completed.
Any hint where and how I can change this behaviour e.g. by creating an
derived JDBC Sampler "JDBC Sampler Max 500"?
Thanks a lot in advance and Regards
Peter
--
View this message in context: http://www.nabble.com/JDBC-sampler-overhead-and-the-number-of-records-returned-tp24716684p24804628.html
Sent from the JMeter - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org