On 11/12/05, sebb <[EMAIL PROTECTED]> wrote:
>
> Just at thought - could the server be running out of cache memory?
>
> It might reuse the cache containing the stored procedure and would
> therefore need to recreate it.
OK, we've now gotten to the bottom of this issue.
I had scripted JMeter to run the stored procedure as plan text in a SQL
query, e.g.
"exec sp_name('foo', 1, 'bar')"
entered in the "SQL Query" box in JMeter. This, it turns out, is not the
Right Way way to run a stored procedure. If you do that, the SQL server has
to parse the SP call out of the string it has received and doing so prevents
the server from storing the compiled stored procedure in its cache (at least
on MS SQL Server, I can't talk for other databases). Hence, constant
compilation of SPs and terrible performance. Interesting, MS's Query
Analyzer exhibits the same behaviour as it also sends SP invocations through
as plain text.
The proper way is to invoke the SP as an RPC call and parse the arguments on
the client side. Here's a Java snippet that implements this:
CallableStatement callstmt = conn.prepareCall("{call sp_name (?, ?, ?, ?, ?,
?, ?, ?, ? )}");
callstmt.setString(1,xml);
for (int i=2; i<10; i++)
callstmt.setNull(i,Types.INTEGER);
rs = callstmt.executeQuery();
(the SP in question takes a bunch of arguments; the first is an XML string,
and the rest are used as placeholders for output values from the SP
recording performance of various stages of the query execution. I have left
those as null in my load test).
I've verified (with SQL Profiler) that this is recorded on the server side
as an RPC invocation and not a SQL Batch Query. I've also verified (with
permon.msc) that when I hit on the server with the above code, we no longer
see SQL compilations.
Now: how do I go about integrating this into JMeter? My first thought was to
hack it into the JDBCSampler but the GUI config might be awkward. Because of
the variable number of arguments, I'll need some sort of name/value control
(like User Defined Variables uses) to configure it. Is that a lot of work
for me to fiddle the GUI into a semi-workable shape? I haven't written any
Swing in years, and even then it was only toy stuff, and unfortunately my
time is tight :o( But if I can more-or-less cut and paste some things
together that won't be too bad.
My next though was the BeanShell sampler; I wondered if I could write code
in there that would look like:
DataSourceComponent pool = (DataSourceComponent)
getThreadContext().getVariables().getObject(getDataSource());
Connection conn = pool.getConnection();
and so on. But I suspect that scoping and suchlike are going to get in the
way. Seems unlikely BeanShell's interpreter is going to be able to get out
into JMeter's singletons to get the DB pool. Am I right in thinking this
approach won't work?
Alternatively, can anyone suggest a third approach I haven't thought of yet?
Advice is welcomed!
