Abstract: I've done some performance testing on JDBC drivers for SQL server, and the results were a bit surprising.
As we develop our application, we are using MS SQL Server as our database. It will probably be deployed on DB2/400 or Oracle at client sites. I was recently asked to improve performance on a particular section of code, as we were going to be giving a demonstration to an important potential client. I analysed the given section of code, looking at our algorithms and so on and managed to effect a fairly significant performance gain by performing one complex query, as opposed to lots of small simple ones. No surprise there (although it was a huge performance gain; 75 seconds down to 8). I still wanted to improve the performance more, as 8s was also fairly unacceptable. There wasn't much more significant improvements I could make without a significant re-design, so I ran the application through a profiler - and found that a big chunk of time was being spent in the jdbc driver itself. Again, this wasn't greatly surprising, as our app is very database intensive. So, I tried experimenting with different JDBC drivers for MSSql. I knew there would be differences in performance, but I didn't expect the difference to be significant. I was wrong. I ran the same piece of code consecutively 5 times, against the same database (restored from a backup before each test) with a different driver each time. The code is part of our app, so I can't release it, but it consists of many queries, and a fair few inserts and updates - i.e. it covers a reasonable amount of different things a jdbc driver would be required to do. The Results (in milliseconds, view in a fixed font): Driver Run 1 Run 2 Run 3 Run 4 Run 5 Average Total Microsoft 7844 3625 3562 3547 3765 4469 22343 DataDirect 6343 2172 2156 2171 2234 3015 15076 JSQLConnect 5531 1828 1844 1782 1985 2594 12970 Opta2000 5375 1469 1719 1422 1328 2263 11313 Conclusions from this test : Microsoft SQL Server is the slowest driver DataDirect is 32.52% faster than Microsoft SQL Server JSQLConnect is 41.95% faster than Microsoft SQL Server Opta2000 is 49.37% faster than Microsoft SQL Server In other words, if we changed our JDBC driver to Opta2000, we would get a significant performance gain for this particular piece of code. Note : to get the Opta 2000 driver to work, I had to write my own ConnectionFactory that used Opta2000 pooled data source driver. I encountered the same problems as found in the following thread : http://www.mail-archive.com/[EMAIL PROTECTED]/msg01483.html I don't think I should have had to write my own ConnectionFactory, especially as we often recommend the Opta2000 driver in the mailing list. How have other people resolved this issue ? Second Note : This evaluation tested one area of our code base. It was actually a fair representation of our typical usage throughout the application, but it might not be the case for you. In other words, do not rely on these test results for your application, as you may have completely different results. Third Note : We are restricted to testing just JDBC 2.0 drivers, as we need to run our code on the IBM iSeries which - until recently - did not have a 1.4 JVM. I think that has changed recently, but I wouldn't swear to it. Summary : It is well worth investigating different JDBC drivers for your application - you could achieve significant performance gains without changing your code. I suspect we will do the same kind of investigation for each database that we plan to support. Driver References : Microsoft : http://www.microsoft.com/sql/downloads/jdbcregister.asp Data Direct : http://www.datadirect-technologies.com/products/jdbc/jdbcindex.asp JSQLConnect : http://www.j-netdirect.com/JSQLConnect/JSQLFeatures.html Opta2000 : http://www.inetsoftware.de/English/produkte/OPTA/default.htm This email and any attachments are strictly confidential and are intended solely for the addressee. If you are not the intended recipient you must not disclose, forward, copy or take any action in reliance on this message or its attachments. If you have received this email in error please notify the sender as soon as possible and delete it from your computer systems. Any views or opinions presented are solely those of the author and do not necessarily reflect those of HPD Software Limited or its affiliates. At present the integrity of email across the internet cannot be guaranteed and messages sent via this medium are potentially at risk. All liability is excluded to the extent permitted by law for any claims arising as a re- sult of the use of this medium to transmit information by or to HPD Software Limited or its affiliates. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
