Hi all

I found that using PreparedStatement and ? placeholder with the MSSQL server will result in serious performance degradation - factor of 5 - 8 slower. The query run much faster if you are not using the ? placeholder and assemble the query manually every time you make the query.


I have performed the test with various DB drivers - MS provided JDBC driver, DataDirect JDBC 3.0, Sprinta 2000 and jTDS, however, all of them suffer from performance degradation to a similar extent.


Surely, I have also tested it Hibernate and because Hibernate generates queries with ? placeholder and that make Hibernate also suffers from the performance degradation. Does any know what's wrong with it? I really want to replace my existing old code with Hibernate. I think the problem is really obvious and hope someone have already found out the solution and could give some suggestions.


Thanks!

In your tests, how many times did you execute the query?

There is some overhead in preparing a statement and thus PreparedStatements only become more effecient after a certain number of executions. The exact number depends on the driver implementation - for Oracle I read it was +- 30-40. (From the Oracle JDBC book)

This is of course a large number or queries - but in an enterprise system, queries are typically executed thousands of times - and you realise the performance benefit.

There are of course other reasons too (security, automatic string escaping ...) why you should be using preparedstatements or their Hibernate equivalents.

I would suggest executing the test again - running the same (perhaps with different bind parameters) query with and without a prepared statement, say 500 times each, and get back to us on the results.

Justin

It is not a problem.
Implement wrapper for driver and convert prepared statements to strings, if ps is too slow for you.

Are you using the Microsoft supplied driver? If yes, try using an alternative one (there are various I heard). There are many subtile issuse with the Microsoft driver and also with MsSQL, like for example this one: http://www.hibernate.org/74.html#A15

Thank you very much!!

>>What gets even more annoying (and potentially confusing) is that this >>does not happen with direct queries, only prepared statements (hence >>the issue with hibernate). It might be wise to make note of this fact in >>the FAQ or documentation.


It is why the query run 5-8 times slower with hibernate and when I run the test the database does consume a lots of CPU power.


However, do you know that how to solve the problem if I do use nvarchar in my database table? Becuase using

sendStringParametersAsUnicode=false

will corrupt the character if it is not english char (according to the google forum - the link in the FAQ).


Many thanks!

I am not sure it will help, but try leading N
( SELECT N'my unicode string' FROM DUAL )

Thanks again.

Fortunately, I don't have many searches on nvarchar col. Because the solution you suggested require the use of direct SQL and I really want to use pure HQL or the new constraint queries in my application. I will try to find out a solution that do not require direct SQLs first and use your suggestion if my attempt's failed.

Oh no, I have tested adding SendStringParametersAsUnicode=false in the database connection string property, however, the speed of the query is still very slow and the sqlserver still consumes lots of CPU power.

I have tested the server with different table - table which has a PK of int type and the queries run very fast this time.


Is the way I set the SendStringParametersAsUnicode correct? The following is the Connection String I pass to the DriverManager.

dbConnectionString + dbIp + ":" + dbPort + ";DatabaseName=dnasql;SendStringParametersAsUnicode=false"


I have tried on MS provided driver and the jTDS but both are still very slow.


One more thing I have tried is changed the pk column from type char to nchar and the queries run very fast again. Therefore, I still think the problem is related to Unicode.

Many thanks !

Cheers

Edmond,

If you want to work around the issue and still be able to send strings as unicode (to preserve the characters sent) you can issue the following command:

EXECUTE sp_dbcmptlevel '%DATABASENAME%', 70

This makes SQL Server 2000 ignore the NCHAR parameter and CHAR index differences and allows the CBO to utilize the index properly (as it did in SQL Server 7.0). There may be a few side effects as well depending on what you are expecting as your default behavior but the documentation explains what these differences are.

You can reference this topic in the jTDS forum.
http://sourceforge.net/forum/forum.php? ... _id=129584

Also, I have recently spent a significant amount of time optimizing the jTDS query parsing (for handling JDBC escapes and callable/prepared statement parameters). You should consider trying the post 0.6 release of the driver (it should be released this month) or check out the current development code. The new driver includes a number of performance improvements and will easily out perform the Microsoft driver (especially if you are using prepared statements with parameters). One warning though. I ran the development driver against the hibernate 2.1.2 tests and 10 tests of the 197 are failing still.

-Brian

The issue is because your database is using varchar, but your database drivers are all using nvarchar parameters. Java supports unicode, so invariably all the TDS-related drivers default to using nvarchar. (This really has nothing to do with Hibernate.)

The problem is when you use an nvarchar parameter to search a varchar column, SQL Server does the convert the wrong way. Instead of converting your parameter to varchar to match the column, it converts the column to nvarchar. This means you lose any indexes and the system does a full table scan. You can pull out SQL Trace to watch the queries come through. Copy and paste these exact queries into SQL Query tool, and you'll see the table scans.

The trick is (as mentioned earlier) is to set your database driver to pass parameters as varchar. I know for Inet software drivers, you add an "a" (to mean ascii), so your JDBC URL would look like "jdbc:inetdae7a:<host>"

I think we're running into this problem as well. Can anyone point me to the documentation on how to do this for the Sybase driver (sybase_jconnect.jar)? (converting nchars to varchars before it hits the database). We're using it with MS SQL Server 2000.

I've been doing a websearch, but I'm not really clear on the best keywords to find the information.

Thanks.

Yore,

I have not used the Sybase JDBC driver. However, many SQL Server drivers have a "SendStringParametersAsUnicode" parameter that will make the driver send varchar parameters instead of nvarchar parameters. If you believe this may be your problem, you can try the 0.9 release of the jTDS driver (still in development) to determine if this is indeed the problem you are experiencing.http://jtds.sourceforge.net

-Brian

Thanks!

I've done more research and learned that Sybase uses the inet drivers. So I was able to use the information here. That change reduced our query times by about 90%.

Yore,

What exactly did you do to correct this? I am running into the same problem you are and it is unclear on your exact resolution.

~Mike

Thank you so much for your valuable information.
maison de credit