Problem with Prepared Statements

Hi
we are developing a java application, which connects to a MS SQL Server 2000
via the Microsoft JDBC Drive Service Pack 3.
Due to the fact that we have massiv performance problems with MS SQL. I
started profiling our MS SQL Server.
I've recognized during analyzing the traces, that the variables of every
prepared Sql command are defined as nvarchar(4000) or Decimal(38,0). Quite
confusing by the fact that the lenght of the fields is not exceeding 50
signs in the tables as well as in the variable used within our application.
I not understand the behaviour of the JDBC Driver. Is there a problem
defining the length of prepared parameters ?
Cause within ODBC i am able to define the length of input values in order to
reduce the amount of data being in access by the sql engine.
kind regards
chris
Christian Billig wrote:

> Hi
> we are developing a java application, which connects to a MS SQL Server 2000
> via the Microsoft JDBC Drive Service Pack 3.
> Due to the fact that we have massiv performance problems with MS SQL. I
> started profiling our MS SQL Server.
> I've recognized during analyzing the traces, that the variables of every
> prepared Sql command are defined as nvarchar(4000) or Decimal(38,0). Quite
> confusing by the fact that the lenght of the fields is not exceeding 50
> signs in the tables as well as in the variable used within our application.
> I not understand the behaviour of the JDBC Driver. Is there a problem
> defining the length of prepared parameters ?
> Cause within ODBC i am able to define the length of input values in order to
> reduce the amount of data being in access by the sql engine.
> kind regards
> chris
Hi. The declared length of the varchar variables are not crucial, but
if you have real VARCHAR columns, and the driver is sending data as
NVARCHARs, then your varchar indexes won't be used, so you will have
slow table scans. The solution for that is to set the JDBC connection
property SendStringParametersAsUnicode=false. The driver by default
sends data as 16-bit characters so any Java char is sent correctly.
As long as you are using standard English 8-bit ASCII characters,
you don't need to send as UNICODE.
Let me know if this takes care of the problem.
Joe
|||Hi Joe
setting the property SendStringParametersAsUnicode=false did not change the
performance.
What still is confusing are the Trace Results.
Executing a SQL Statement as prepared statement in a loop just changing the
binded values for 50 times,
is resulting for Java in a trace with 50 statements exec sp_cursoropen.
Doing the same in c# und using an OleDB Connection with the exact same SQL
and value is leading to a different Trace
which contains one statement with "exec sp_prepexec" and 49 statements of
"exec sp_execute".
May i'm doing something wrong ? Or is that really the right behaviour ? It
looks to me that in our java application the PrepareStatement is not really
executed.
bye
chris
|||Seems to be a problem of the JDBC Driver even prepare Insert and Update
statements are being executed as sp_executesql
where as using the jdbc 2005 beta 1 driver they are executed as sp_prepexec
and sp_execute.
Even Select statements which were executed as cursor-open, -fetch and -close
are now executed as sp_prepexec and sp_execute as long as the resultset is
text_forward_only.
Due to the fact that we need scrollable resultsets the former behaviour is
ok
During testing the JDBC 2005 Beta 1 we recognized a bug in this
constellation.The SQL Statement used for opening the cursor is not prepared
any longer although we are using the preparedstatement method. That causes
an immens memory usage until the SQL Server reachs the allowed maximum
memory usage and begins to use the swap file.
|||forgot the log
Both Statements were executed as PreparedStatement with an insensitive
Cursor ResultSet.
JDBC 2000 on SQL Server 2000 SP4
declare @.P1 int
set @.P1=0
declare @.P2 int
set @.P2=4104
declare @.P3 int
set @.P3=8193
declare @.P4 int
set @.P4=0
exec sp_cursoropen @.P1 output, N'SELECT "LVORLP".* FROM "LVORLP" WHERE
("LVBNKZ" = @.P1 AND "LVFIRM" = @.P2 AND ("LVABRJ" > @.P3 OR "LVABRJ" = @.P4 AND
"LVABRM" > @.P5 OR "LVABRJ" = @.P6 AND "LVABRM" = @.P7 AND "LVABGR" > @.P8 OR
"LVABRJ" = @.P9 AND "LVABRM" = @.P10 AND "LVABGR" = @.P11 AND "LVSABE" > @.P12
OR "LVABRJ" = @.P13 AND "LVABRM" = @.P14 AND "LVABGR" = @.P15 AND "LVSABE" =
@.P16 AND "LVPRNR" >= @.P17)) ORDER BY "LVABRJ", "LVABRM", "LVABGR", "LVSABE",
"LVPRNR"', @.P2 output, @.P3 output, @.P4 output, N'@.P1 varbinary(8000) ,@.P2
varbinary(8000) ,@.P3 decimal(38,0) ,@.P4 decimal(38,0) ,@.P5 decimal(38,0)
,@.P6 decimal(38,0) ,@.P7 decimal(38,0) ,@.P8 varbinary(8000) ,@.P9
decimal(38,0) ,@.P10 decimal(38,0) ,@.P11 varbinary(8000) ,@.P12
varbinary(8000) ,@.P13 decimal(38,0) ,@.P14 decimal(38,0) ,@.P15
varbinary(8000) ,@.P16 varbinary(8000) ,@.P17 decimal(38,0) ', 0x42,
0x303031, -9999, -9999, -99, -9999, -99, 0x00, -9999, -99, 0x00,
0x000000, -9999, -99, 0x00, 0x000000, -999999
select @.P1, @.P2, @.P3, @.P4
JDBC 2005 Beta 1 on SQL Server 2000 SP4
RPC Starting
declare @.P1 int
set @.P1=0
declare @.P2 int
set @.P2=0
exec sp_cursoropen @.P1 output, 'SELECT "LVORLP".* FROM "LVORLP" WHERE
(("LVBNKZ" > 0x42) OR ("LVBNKZ" = 0x42 AND "LVFIRM" > 0x303031) OR ("LVBNKZ"
= 0x42 AND "LVFIRM" = 0x303031 AND "LVABRJ" > 2005) OR ("LVBNKZ" = 0x42 AND
"LVFIRM" = 0x303031 AND "LVABRJ" = 2005 AND "LVABRM" > 9) OR ("LVBNKZ" =
0x42 AND "LVFIRM" = 0x303031 AND "LVABRJ" = 2005 AND "LVABRM" = 9 AND
"LVABGR" > 0x20) OR ("LVBNKZ" = 0x42 AND "LVFIRM" = 0x303031 AND "LVABRJ" =
2005 AND "LVABRM" = 9 AND "LVABGR" = 0x20 AND "LVSABE" > 0x202020) OR
("LVBNKZ" = 0x42 AND "LVFIRM" = 0x303031 AND "LVABRJ" = 2005 AND "LVABRM" =
9 AND "LVABGR" = 0x20 AND "LVSABE" = 0x202020 AND "LVPRNR" >= 0)) ORDER BY
"LVBNKZ", "LVFIRM", "LVABRJ", "LVABRM", "LVABGR", "LVSABE", "LVPRNR"', 8, 1,
@.P2 output
select @.P1, @.P2