Re: Optimizing fetch of big result set - HELP!

"Philippe Laflamme" <plaflamme_at_konova.com> wrote in message news:cIC77.22126$eY6.2501085_at_news20.bellglobal.com...
> Hello,
> Does anyone have any clues as how to optimize fetching of data from a
 result
> set on one table?
>
> Explanation:
> I have a select statement that returns 3 columns and >200000 rows.
> Fetching all these rows (through OLEDB or ODBC) takes ~= 6 seconds...Which
> is too much. I'm already using bulk fetching with no luck (without bulk
> fetching it takes about 40 seconds). The select statement is only on one
> table.
>
> What would be the possible ways to optimize the fetching of theses
 results?
>
> Help please....
>
>

1 Please don't crosspost
2 make sure your number of records times the number of bytes for a single record does NOT exceed the session data unit of sqlnet, usually 2048 bytes. If this doesn't work find out what the Max Transmission Unit of your network card is (usually 1508 bytes) and lower the session data unit in listener.ora and tnsnames.ora to this number.
You should avoid segmenting by Oracle or by the TCP/IP protocol at all costs. I have been in a situation where the optimum was to fetch 20 records, as this was smaller than MTU. As soon as you enlarged the array, performance collapsed again. These tests where conducted at a customer in a similar situation.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Wed Jul 25 2001 - 11:56:18 CDT