Re: Another angle on this....

Not the same problem I'm afraid. Pity.

With me it's not ignoring the SQL_ATTR_R_A_S value, I get the huge surge in performance but its useless, because later calls corrupt the value for the number of rows actually returned (presumably because of some kind of internal driver shennanigans). This is critical if you have to reposition to the odd row to retrieve the rest of a CLOB or large varchar that you didn't assign enough space for initially to avoid huge wastage in the memory array. I could code round this by copying the rowcount, but it makes me really nervous because it makes me wonder if anyone else is using this stuff. I hadn't noticed that setting the driver prefetch to 1 would kill this performance but I guess its kind of predictable, though really the SARAS should reset it for you automatically.

I had a lightning look at the MS driver but for me it doesn't install easily on a modern Oracle client and I couldn't be arsed to try properly. For some reason I don't have a lot of faith that it would be better, there seem to be plenty of issues on MS KB with it.

Regards

Tom

"Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message news:E2F6A70FE45242488C865C3BC1245DA7F71F1E_at_lnewton.leeds.lfs.co.uk...
> Morning,
>
> I've been folloiwng this discussion and note that you mention a bug in
> the Oracle ODBC driver relating to array fetches.
>
> >> I have to turn off ODBC array-fetches because of an
> >> obscure bug in the Oracle ODBC driver.
>
> I too have an app which uses the ODBC driver to access Oracle (or any
> other database) and we have been having problems in that it fetches a
> single row from the database on each request. After setting up a test
> program, I've been able to deduce that the Oracle ODBC driver (up to
> verion 817) ignores the setting for SQL_ATTR_ROW_ARRAY_SIZE and always
> returns a single row, regardless of how big you make this.
>
> I did find a workaround, in the ODBC setup, check out the setting for
> Prefetch - it usually defaults to 1 (or maybe 10) and this does have the
> noted results. By increasing this setting to 1000 I was then able to
> retrieve 999 (!) rows per fetch and this has made a vast improvement to
> the app. As the app is a thrid party one, I have no access to the source
> code, so making the driver go faster was all I could do.
>
> I have a TAR open with Oracle on this problem, but further testing on
> the Microsoft supplied ODBC driver for Oracle shows that the default
> buffer size is 65KB and adjusting that down to 1024 shows that the
> SQL_ATTR_ROW_ARRAY_SIZE does have an affect on the number of rows
> returned per fetch.
>
> I'd be interested to know if this is the problem you are finding with
> your 'obscure bug'.
>
>
> Regards,
> Norman.
>
> ------------------------------------------------------------------------
> -----
> Norman Dunbar EMail: Norman.Dunbar_at_LFS.co.uk
> Database/Unix administrator Phone: 0113 289 6265
> Fax: 0113 289 3146
> Lynx Financial Systems Ltd. URL: http://www.Lynx-FS.com
> ------------------------------------------------------------------------
> -----
>
Received on Fri Feb 22 2002 - 03:54:31 CST