Re: Optimizing fetch of big result set - HELP!

200,000 rows is a fairly generous haul - particularly if it is across a network to a Windows front-end. How confident
are you that the problem is not in the
network layer, or the multiple layers
of Windows DLLs at the client.

You might like to check how much work
Oracle is doing in that 6 seconds, and
how much time it is losing on waits.
(See v$session_wait for the connect
session - look especially for waits
with names like

    'sqlnet message from client' or
    'sqlnet more data to client'.

How big is the arraysize in your bulk fetch ?

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.




Philippe Laflamme wrote in message ...


>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....

>

>