We're have performance problems with inserts into MSSQL (7.0)
tables when using Weblogic Type 4 JDBC drivers.  MS SQL performance is
about half that of equivalent Oracle 8i performance.

I first prepare a Query of the form:

INSERT INTO blah (x,y,z) VALUES (?,?,?)

I then loop to repeatedly insert several thousand rows
via the JDBC PreparedStatement statement (I know
bulk loading would be faster).  I commit
every 1000 rows or so (the rows are quite small).

While this is going on, the MS SQL performance monitor
indicates that there are dozens or even hundreds of
"SQL Compilations" per second.

As the query is prepared, isn't it true that there should
be essentially no SQL compilations (there is no other server
activity at this time)?  Doesn't this smell like a JDBC
or MSSQL bug?

Any ideas?

Thanks

Jim

Sent via Deja.com http://www.***.com/
Before you buy.

Hi Jim.

The issue is that MS SQLServer (to my knowledge) provides
no light weight way of preparing SQL for re-execution in
the DBMS. Stored procedures are not appropriate because
they are heavy-weight objects involving system table entries
and logging. These would affect transactions and concurrency,
as well as mandating that every JDBC user have the permissions
to create procedures. This also means the JDBC driver would
have to be able to remove any stored procedures left by a
client who was terminated abruptly.
   You observe correctly that every execution of a prepared
statement is the functional equivalent of a fresh execution,
with SQL parsing etal.
  For 12.0, Sybase has implemented lightweight stored procedures,
which would be appropriate, as they are in a connection-private
name space and go away automatically on log-out. I wish MS
had such a feature.
   As such, I would characterize it as an MS SQLServer weakness,
but I may be wrong. I have to cover a lot of bases and several
DBMSes, so I may well stand to be educated. If there's a way
to just send parameter values to re-execute prepared SQL in the
DBMS, without generating and using stored procedures, I would be
grateful to be informed of such, and would implement the feature in
the driver. In the meantime, the fastest way to do a bunch of inserts
is to batch them, not needing JDBC 2.0 either:

String myBatch = null;

while(not tired of batching)
{
  // if possible, make the insert into a string rather than having the driver
  // substitute for variables. It's faster.
  myBatch += "insert into myTable values(" + val1 + ", " + val2 + ")" + "\n";

// execute the whole shebang. 64k chars of inserts is fine.
stmt.executeUpdate(myBatch);

I hope this helps, Let me know...
Joe Weinstein at BEA, the home of WebLogic

I first prepare a Query of the form:

--
PS: Hey folks, we're hiring Java engineers for our WebLogic
    Engineering group in downtown S.F. Send me your resume.
--------------------------------------------------------------------------------
                    The Weblogic Application Server from BEA
         JavaWorld Editor's Choice Award: Best Web Application Server
  Java Developer's Journal Editor's Choice Award: Best Web Application Server
     Crossroads A-List Award: Rapid Application Development Tools for Java
Intelligent Enterprise RealWare: Best Application Using a Component Architecture
               http://weblogic.beasys.com/press/awards/index.htm