MS SQL compilations on prepared queries 
Author Message
 MS SQL compilations on prepared queries

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.



Tue, 23 Apr 2002 03:00:00 GMT
 MS SQL compilations on prepared queries

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";

Quote:
}

// 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

Quote:

> 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:

Quote:
> 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.deja.com/
> Before you buy.

--
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


Tue, 23 Apr 2002 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Preparing SQL Queries (Query1.Prepare??)

2. Converting MS Access Query to MS SQL Query

3. Prepared statements and MS Access/SQL Server

4. Prepared Statements & MS SQL Server

5. Storing Unicode strings in a MS SQL Server table using prepared statements in JDBC

6. MS Query crashes when attempting to set up a pivot query in MS

7. Query from ms-sql to ms-access

8. Calling MS ACCESS 97 Cross-Tab Query from MS SQL 6.5

9. Executing a MS Access Cross-Tab Query from MS SQL 6.5

10. SQL Query equivalent for MS Access Query

11. Converting MS Access Query to SQL server query

12. prepare-execute vs. prepare-cursor-fetch


 
Powered by phpBB® Forum Software