Very interesting SQL/JDBC Performance Enhancement

Solution ID: 201030108


SOLUTION:
EnterpriseOne Tools Development has tested and evaluated the SQL Server 2005 JDBC driver running against SQL Server 2000 DB and the difference is dramatically faster. SQL Server 2005 JDBC driver utilizes fast forward cursors (similar to ODBC driver) and no longer downgrades to a less efficient cursor on the database connection. If receiving poor performance on Find/Browse application forms that access a table with 750K to 1 million plus records (e.g. transaction tables: F0911, F03B13, etc....), the recommendation is to upgrade to SQL Server 2005 JDBC driver.

Minimum Technical Requirements to utilize SQL Server 2005 JDBC driver are:
* EnterpriseOne 8.9 and above
* EnterpriseOne Tools release 8.96.D1
* Websphere 6.0

Very nice find there Bro

So in effect, using the SQL2005 client against a SQL2000 server - specifically the JDBC client.

But what IS intriguing is the statement "similar to ODBC driver" - does this mean that the same improvement would occur with the SQL2005 ODBC driver?

I'm interested in hearing more about this in the field. I also wonder how much of this negates the need to upgrade to SQL2005 for performance reasons ! Obviously you'd want to eventually update for Support reasons - but is this the entire SQL2005 performance improvement ?

Secondly - where is SP23Q1 in the MTR ? Why EnterpriseOne 8.9 and above ? Surely SP23Q1 gives us SQL2005 support ?

Actually known this for awhile........

Official support for the JDBC 2005 drivers cane at 8.96_E1 or so but I've been using them since 8.96_C1.

Massive difference on the larger tables.

I've got 2 client live on 811 SP1 with SQl Server 2000 but SQL Server 2005 JDBC.

[ QUOTE ]
Very nice find there Bro

So in effect, using the SQL2005 client against a SQL2000 server - specifically the JDBC client.

But what IS intriguing is the statement "similar to ODBC driver" - does this mean that the same improvement would occur with the SQL2005 ODBC driver?

I'm interested in hearing more about this in the field. I also wonder how much of this negates the need to upgrade to SQL2005 for performance reasons ! Obviously you'd want to eventually update for Support reasons - but is this the entire SQL2005 performance improvement ?

Secondly - where is SP23Q1 in the MTR ? Why EnterpriseOne 8.9 and above ? Surely SP23Q1 gives us SQL2005 support ?

[/ QUOTE ]

Denver has had (and known about) a problem with JDBC and very large tables for a long time now. You can tank a SQL server by doing an open find in P0901, P0911, etc. in the web client. I really wish that Oracle would do a better job of communicating this kind of enhancement.

We actually found this little jem just a couple months ago before we went live with 8.12. We'd probably not moved to 8.12 otherwise. Here's a link to the thread I found in February:

Performance Fix

Colin,

You mentioned you are using the new JDBC driver with 8.96C1. Are you using it in the production system. We just upgraded to c1 and wanted to use this driver hwever Oracle said we need to upgrade to higher one off.

We are planning to upgrade ourselves latest TR in 4 months. However if this is usuable today with 8.96c1, I would love to give it a try.

I just set this up in my development environment. I ran an open querry in P42101. It took awhile to come up but I didn't find and SQL Blocks in SQL. I'm giong to have my devlopment crew test this so I'll let you know how it works here.

Hello all,

What is the steps I need to do make changes in the existing instalation, so that It can use the correct driver
1) I have installed the new driver,
2) I have changed jdbj.ini correctly
3) There is some manaul settign which I need to do within Webadmin. I am not sure what this is.
Could soem one confirm this
How can I verify which dirver is actually gettign usilized

Colin,

I am struggling with the Config, Oracle has looked at my config and said every thing is installed correctly, however they said it will not work as we need to be atleast on d1 and I am on C1. I am continuosly gettign the driver not found error.

Do you remeber doing any thing special to get this working

Jeff/Jon/Colin,
Have you (or anyone else reading this) heard anything about the 2005 driver with 2003 DB? We are very familiar here with the performance improvements resulting from 2005 driver with 2000 DB, but haven't been able to find any information on using it with 2003. Have you heard good/bad/indifferent, same as with 2000 DB, any of the above? Haven't been able to find anything so far!

Any thoughts are greatly appreciated!
Matt
---Currently 8.12/8.96_D1 on our way to 8.96.1.1
---MS SQL / OAS