Re: observation...

Bricklen <bricklen_at_shaw.ca> wrote in message news:<3C605504.ABEAC98D_at_shaw.ca>...
> Does anyone else find it irritating when someone posts a question to one
> of these oracle newsgroups and makes some inane statement like the
> following (culled from a post a week or so ago):
>
> 'I have an application that runs lot's of queries and i'm finding
> that a lot
> of these queries take between 100-500 ms each where on ms sql server
> few
> querys are over 100ms
> Why is it that oracle is so slow? What can I do to speed it up.'
>
> On the other hand, I find it amusing when one of us oracle folks gets
> our dander up about those statements and replies thusly:
> 'Are you using bind variables? Probably not. Use bind variables.'
>
>
> Just an observation that I felt like sharing.
>
> ;-D

You sound like someone who should have the answer I am seeking. I have just ported an app to Oracle which runs fine under SQL Server and Sybase. It uses SQLBindCol and Blocking (SQL_ATTR_ROW_ARRAY_SIZE) to maximise retrieval performance on multi-record selects. With this technology I can typically retrieve between 5k and 10k records/second of my (c. 30 column) tables against other databases. With Oracle, against the same tables with the same data, opening the recordset takes the same time, a few milliseconds, but retrieval is then *very* slow, at best around 400 records per second. I have tried this with both 8i and 9i on a Intel server box superior in specification to the one I am using for MS SQL Server. Just in case there was something wrong with the server box I stuck MS SQL Server on it and got my normal multi-k records/second out of SQL Server. I also tried Oracle on a different box with the same (slow) results. Altering the ODBC driver Prefetch Count makes only a tiny difference to performance. If I turn off blocking things get even worse, around 150 records/second (with binding still in place)! Unfortunately because I have some CLOBS in my queries the Oracle ODBC driver bug I have to run in the production environment without blocking which means that Oracle is suffering a performance hit factor of 40-1 against other db technologies. tried Can you suggest what am I doing wrong and how I might tune Oracle better, or tune my code better for Oracle?

Kind Regards

Tom Received on Fri Feb 08 2002 - 02:53:48 CST