Re: Simple SELECT taking long time...

Hi.

 Check SORT_AREA_SIZE parameter in init.ora file.  Try to increase it.
 ORDER BY and DISTINCT must perform SELECT and SORT.  Only afterwards the rows will be passed to application.

 HTH. Michael.

In article <sbo7jq76jp66_at_corp.supernews.com>,   Bandoba <bandoba_at_yahoo.com> wrote:
> Hi,
>
> I am firing a SQL which looks like
>
> SELECT DISTINCT Col1, Col2, ..., Col10
> FROM TableName
> ORDER BY Col5 DESC, COL2 DESC, Col1 DESC
>
> Basically there is no WHERE clause and there are no joins with any
 other
> table. That table contains 69000 rows. My database server is Oracle 8
> running on Sun Solaris 2.6 (on Sparc Ultra 10). Following are
 different
> connection methods I used and the results.
>
> 1. Using SQL*Plus
> - It takes 2 minues to get 1st record from the server.
>
> 2. Using ODBC
> - I wrote a very simple program that connects to database and it
 has
> a While(!EOF) loop that shows the records.
> - It still takes 2 minutes to show first record.
>
> 3. Using ADO
> - Again using a simple program as explained above
> - I observed that Connection.Execute method returns immediately
> - But it takes long time to retrieve first record. After that it
 is
> quite fast.
>
> I think 2 minues is too much time for such a simple of query. Can
 anybody
> tell me what parameters I should look at to improve the performance?
>
> Also I tried to execute the query with "WHERE rownum <= 30000"
 clause. In
> that case, server replies immediatly. But for any figure greater that
 30000
> it slows down.
>
> I also tried to fire same query with MS SQL Server database (with no
> specific configuration running on NT 4 box) and it takes 2 seconds to
> retrieve first record.
>
> TIA,
> Bandoba
>
> --
> Posted via CNET Help.com
> http://www.help.com/
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Feb 29 2000 - 00:00:00 CST