Simple SELECT taking long time...

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/