CODEWITHSUNDEEP
sqloracle-databaseoptimizationoracle-sqldeveloperoracle12c
Asit Kumar
Asit Kumar

Reputation: 1

oracle sql Developer taking time to fetch all records

My query is giving output in less than a second for first 50 records in Oracle SQL Developer. When I select all(Ctrl + A) in Query result then it takes 120 second to fetch all records(165k). Could you please suggest me if there is any scope to optimize this ?

Can you also explain how the query is working for first 50 records of result and then for rest of the records ? Is the SQL engine fetching the rest of records from the data blocks OR from the result cache ?

Many Thanks, Asit

Upvotes: 0

Views: 3509

Answers (3)

Shantanu Kher
Shantanu Kher

Reputation: 1054

SQL developer stops the execution as soon as it fetches first 50 records. However you can increase it upto 200.

In SQL Developer, Go to Preferences —> Database —> Advanced —> SQL Array Fetch Size (between 50 and 200) --> Change the value to 200

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142733

For a simple query (something like select * from some_table), I presume every today's GUI does the same - fetches the first "n" (50, 100, 500) rows fast. It returns an unordered set and says "here you are, look at what you got".

But, if it goes slightly more complex, e.g. select count(*) from some_table or select * From some_table order by col1, then it has to fetch all rows, and for large tables it takes time.

Also, as queries go wild (joins, aggregations, whatnot), it also makes it more complex and more time consuming.

So - how much time would you consider satisfying for 165K rows? Ctrl + A does take time.

Upvotes: 0

Jacobm001
Jacobm001

Reputation: 4539

There's a lot of things that could be a factor here, the most obvious being what the records are. 165,000 records is not a ridiculously oversized ask, but how large is each individual record? Remember, these records must be read from the DB, pass through the network, and then be processed by SQL Developer itself.

When I've run into this problem, you can typically watch the sql developer process spike the CPU and RAM as it's trying to ingest all those records in whatever internal process it utilizes.

Upvotes: 2

Related Questions