ok, that makes sense. I haven't tried calling stored procs from jmeter, but
perhaps others can provide an example.
peter
On 11/17/05, Richard Gaywood <[EMAIL PROTECTED]> wrote:
>
> 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!
>
>
