Re: Optimizing fetch of big result set - HELP!

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 25 Jul 2001 18:56:18 +0200
Message-ID: <996080181.15128.0.pluto.d4ee154e_at_news.demon.nl>


[Quoted] "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
[Quoted] 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. [Quoted] [Quoted] If this doesn't work find out what the Max Transmission Unit of your network [Quoted] [Quoted] card is (usually 1508 bytes) and lower the session data unit in listener.ora and tnsnames.ora to this number.
[Quoted] [Quoted] 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 - 18:56:18 CEST

Original text of this message