Re: Any ODBC and performance issues

On Wed, 31 Jan 2001 20:26:44 GMT, buckeye714_at_my-deja.com wrote:

>Scenario: Third party application. Run's on Windows (Various
>versions). It was written for MS Access. We use Oracle 8.1.6 on
>Solaris. (The vendor modified the code for use with Oracle). A process
>that reads and updates 32,000 records takes 32 hours to run. Vendor
>says it runs fine with Access. I have monitored the database and it is
>running well. Buffer Cache, Dictionary Cache and SQL cache hit ratios
>are all above 98%. No I/O problems. No wait events.
>
>Desired: Information on possible ODBC problems. We are not
>experiencing network problems. The dml is using indexes as expected.
>The transactions don't take long on the database side. I would like to
>rule out the ODBC connection as the problem, but have not worked with
>ODBC very much. Can any one recommend a source for information on ODBC?
>
>Thanks in advance.
>
>Patrick

From experience, I can almost guarantee that this is a problem with their code and how they're using ODBS, not ODBC itself. Run whatever logs you can to see if they are

1. fetching data to the client to perform work, and then performing such things as updates, etc. one row at a time., rather than using appropriate SQL to get things done on the server (that's what it's there for after all!)
2. check what kind of cursors are in use. You can pretty much guarantee they either a) haven't even thought of this, or b) have defaulted to client-side cursors. My little mantra to our programmers is "Server cursors are your best friend".
3. Are they using SQLFetch or SQLExtendedFetch to retrieve results? The first uses one round trip per row of results ... which means really bad network behaviour. The latter is much better, bundling multiple result rows into transmissions.
4. Check to see if they've gone crazy with "Object-oriented" approaches, which can often translate into needless repeating the same SQL statements. (I've seen this on a few client sites)
5. Check to see if they're using pessimistic locking ... especially the "select / select again just in case / change" approach. Oracle has one of the best locking regimes ... they should let it do it's job.

I'm sure there's plenty more to this ... but that's a start.

Ciao
Fuzzy
:-) Received on Thu Feb 01 2001 - 17:25:36 CST