On 17/02/06, Richard Gaywood <[EMAIL PROTECTED]> wrote: > On 2/16/06, sebb <[EMAIL PROTECTED]> wrote: > > > > On 16/02/06, Richard Gaywood <[EMAIL PROTECTED]> wrote: > > > If you are planning on using JMeter to test stored procedures, you > > should > > > read > > > http://issues.apache.org/bugzilla/show_bug.cgi?id=38682 > > > first. This bit me in the past quite badly. > > > > Why does the server recompile the stored procedure for executeQuery, > > and not prepareCall? > > I'd only expect the SP to be compiled once when it is initially created. > > > Me too, actually, but I'm no DBA. I know our senior DBA here recolied in > horror when he looked through the SQL Profiler and saw all the calls. I've > actually seen this in effect: with standard JMeter, the recompiles/sec > counter in perfmon.msc on the database server was through the roof. With a > special hacked version I produced which hard-codes prepareStatement() to > call the SP we were testing, it was zero. > > In any event, prepareStatement() offers performance advantages as it passes > the SQL server parameters of known types and doesn't make it do the parsing > donkey work. > > More details here: > http://www-db.stanford.edu/~ullman/fcdb/oracle/or-jdbc.html#0.1_prepare > > Reading that now makes it sound like the recompile actually *is* parsing the > incoming SQL, rather than compiling the SP itself. That makes more sense, > actually.
Yes. > Seems like a limitation in MS SQL server rather than JMeter ... > Not really a limitation - what else can it do if the SQL may change? > > I'm informed it also effects Oracle, although I haven't seen this with my > own eyes. Reading the link above suggests it would effect all DBMSs. It affects Ingres - in the embedded SQL language there is a REPEATED keyword which is used to tell the pre-processor to generate a prepared statement. This needs to be used judiciously, as the prepared statements require storage. Also any optimisation is only done once, with the initial set of values. So for some data distributions you may be unlucky and get a bad query plan. But I agree it would be useful to be able to use prepared statements in JMeter. This could be done as in Ingres, which just "names" the prepared statement according to its position in the source file [and the file name]. JMeter could perhaps use a checksum of the query text to identify the SQL. This would allow them to be shared across threads. This is similar to the behaviour in Ingres, where multiple processes running the same code will use the same plan. Or perhaps we need a statement pool, with names provided by the user, like the connection pool. Thoughts? --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
