How to improve performance of a simple select query in oracle

Question

I recently got into an interview and I was asked a question

We have a table employee(id, name). And in our java code, we are writing a logic to fetch data from this table and display it in UI. The query is

Select id,name from employee

Query was that during debugging, we found that this jdbc call to fire the query and get the output is taking say 20 secs and we want to reduce this to say 5 seconds or to the optimal time. How can we you do that, or how will I tackle this problem?

As there is no where clause in the query, I didn't suggest to index the column. As this logic is taking 20 secs every time, so, some other code getting a lock on this table is also out of question. I suggested that limiting the number of records fetched from the table should help but the interviewer didn't look convinced

Is there anything else we can do as a developer to optimize the call. I guess DBA might tune database setting to improve the performance of this query, but is there any other way

Answer 1

OK, so this is an interview question, so both the problem and the solutions are hypothetical. The interviewer is asking for possible optimizations and / or approaches. Here are some that are most likely to help:

• Modify the query to page the data rather than fetching the whole lot. This looks applicable for the example query. Note that this is not just "limiting the number of rows selected from the table" ... which is probably why the interviewer looked doubtful when you said that!

• If you do need to display the entire selected record set but in a reduced form (e.g. summed, averaged, sorted, collated etc), do the reduction in the query rather than by fetching the records and doing it in the client.

• Tune the fetchSize() as suggested by Ivan.

Here are some other ideas that are less likely to help and / or will require extensive reworking.

• Look at the network configs. For example you may be able to get better throughput by OS-level tuning TCP buffer, or optimizing physical or virtual network paths.
• Run the query on the database server itself (to eliminate network overheads)
• Use an in-memory table
• Query a secondary database server; e.g. a readonly snapshot or a slave