Re: JDBC v. native drivers

[chrisd@xxxxxxxxxxxxx]

>I've got a process that runs natively on the 400 (via QSH) that's a
>bit slow.  Currently, it's using JDBC connectivity and I'd like to
>make it run faster (it needs to process about 200K records).
Without seeing your tables, I can't say for sure, but I'm wondering if you
have checked the queries your are using to make sure they are as efficient
as they can be.  We run a fairly intensive eBusiness application using the
Toolkit driver, and most of the time our database performance problems are
related to indices not being built when necessary.  You can use the "Visual
Explain" function inside of the "Run SQL Scripts" function of Operations
Navigator to examine your queries.
In Visual Explain, you can see all of the tables involved in your query.
If you see any of the source tables say that it is using a "Table Scan" to
lookup records, click on that icon.  In the data that comes up on the right
side of the window for that table scan, scroll down until you see a section
labeled "Index advised information".  If it advises that you create an
index, then create on over the columns that it refers to.  Do this for any
of the other table scans that the optimizer recommends indices for.

Try your query again to see if it is any faster.

If it isn't then you may be using a query that can't be optimized very well
(a subselect, or something like that).  That may require a rework of your
query or some other strategy.

>How difficult would it be to convert the JDBC to native?  I'm using
>SQL extensively and not sure if the native drivers
>can support SQL.
The implementation differences between the native driver an toolbox driver
might cause some re-factoring to have to occur.  It's not something you
want to do unless you have to, though.

----------------------------------------------------------------------
Chris DeLashmutt
Senior R&D Analyst
LeasePlan USA
1165 Sanctuary Pkwy., Alpharetta, GA  30004
Phone: 678-202-8695
Fax: 678-566-8551
----------------------------------------------------------------------