Prepared Statements / JDBC 
Author Message
 Prepared Statements / JDBC

We're running performance tests - 50,100,200 user simulations, on a
relatively small ASE 12.5.01 database (1 GB).

We've found that using the SybStatement class is much faster than
the PreparedStatement class ... at times 3 times faster.

When the PreparedStatment class is used in a 50 user simulation, the
CPUs on the Solaris box are put under a lot more stress - noticable
using the vmstat command.  

Many Java developers default to the PrepareStatement class - perhaps
this is the standard when working with Oracle ?

Now to the question:  Are there any specific sp_configure options to
adjust, knowing that an application is using prepared statements ?
We've already made any adjustments proposed by sp_sysmon.

Have any other DBAs experienced similar results ?



Mon, 01 Nov 2004 22:25:45 GMT
 Prepared Statements / JDBC

Dear John,

     I am not using jdbc, but I see similar results (although not so
drastic) in odbc.   Ad-hoc SQL can run faster than prepared statements.   My
question to you is "How complex are your prepared statements?"     If your
prepared statements have joins, SARGS, etc., I estimate that the prepared
version of these would be faster than the ad-hoc equivalent.   Depending on
the level of preparedness, Sybase will create a stored procedure for each
statement.   There is overhead involving the execution of a stored
procedures in the ODBC layer.   Simple ad-hoc queries will out perform their
stored procedure counterparts.

Brian
http://www.talusmusic.com/BriansTools


Quote:
> We're running performance tests - 50,100,200 user simulations, on a
> relatively small ASE 12.5.01 database (1 GB).

> We've found that using the SybStatement class is much faster than
> the PreparedStatement class ... at times 3 times faster.

> When the PreparedStatment class is used in a 50 user simulation, the
> CPUs on the Solaris box are put under a lot more stress - noticable
> using the vmstat command.

> Many Java developers default to the PrepareStatement class - perhaps
> this is the standard when working with Oracle ?

> Now to the question:  Are there any specific sp_configure options to
> adjust, knowing that an application is using prepared statements ?
> We've already made any adjustments proposed by sp_sysmon.

> Have any other DBAs experienced similar results ?



Fri, 05 Nov 2004 21:16:24 GMT
 Prepared Statements / JDBC

Quote:

> Dear John,

>      I am not using jdbc, but I see similar results (although not so
> drastic) in odbc.   Ad-hoc SQL can run faster than prepared statements.
>  My question to you is "How complex are your prepared statements?"
> If your prepared statements have joins, SARGS, etc., I estimate that the
> prepared version of these would be faster than the ad-hoc equivalent.
> Depending on the level of preparedness, Sybase will create a stored
> procedure for each statement.   There is overhead involving the
> execution of a stored procedures in the ODBC layer.   Simple ad-hoc
> queries will out perform their stored procedure counterparts.

There's an additional variable in the puzzle - how many times will the
prepared statement be executed?

I've just come across a case where the client was using dynamic SQL to
execute two queries. As this is a conversion script it ran 40 parallel
instances of the script, and it never reused the prepared statements.
This essentially brought an E4500 to its knees because of execessive
locking in tempdb.

Rewriting the script to use two very simple stored procs changed the
behavior completely.

Note that prepared statements create "temporary" stored procs, and in
11.9.x and later these are "lightweight" (i.e. are not added to
sysobjects). However the server this was running on was a production
server with fairly heavy use of tempdb, and the additional CPU cycles
needed for the creation of these temporary stored procs was enough to
cause the locking problems mentioned above.

Michael

Quote:


>> We're running performance tests - 50,100,200 user simulations, on a
>> relatively small ASE 12.5.01 database (1 GB).

>> We've found that using the SybStatement class is much faster than the
>> PreparedStatement class ... at times 3 times faster.

>> When the PreparedStatment class is used in a 50 user simulation, the
>> CPUs on the Solaris box are put under a lot more stress - noticable
>> using the vmstat command.

>> Many Java developers default to the PrepareStatement class - perhaps
>> this is the standard when working with Oracle ?

>> Now to the question:  Are there any specific sp_configure options to
>> adjust, knowing that an application is using prepared statements ?
>> We've already made any adjustments proposed by sp_sysmon.

>> Have any other DBAs experienced similar results ?

--
Michael Peppler                              Data Migrations, Inc.

http://www.mbay.net/~mpeppler
International Sybase User Group: http://www.isug.com


Fri, 05 Nov 2004 23:15:24 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. JDBC Driver, Prepared Statements and Performance

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

3. JDBC-ODBC does not work for long datatypes using prepared statements

4. JDBC Support - prepared Statements?

5. JDBC Support - prepared Statements?

6. JDBC Driver - Batch Prepared Statements

7. Problems with Prepared Statement and SUN's JDBC

8. SQLServer/JDBC:ODC/prepare statement - can't set dates

9. help!!- jdbc prepared statement=error ora-01008

10. jdbc prepared statements with unknown no. of parameters

11. Insert into char(1) using JDBC prepared statement?

12. JDBC-Prepared Statements


 
Powered by phpBB® Forum Software