Simple rules for fetching large amount of data

First rule:

• Don't fetch huge amounts of data back to the application.

Unless you are going to display every single one of the items in the huge amount of data, do not fetch it. Communication between the DBMS and the application is (relatively) slow, so avoid it when possible. It isn't so slow that you shouldn't use the DBMS or anything like that, but if you can reduce the amount of data flowing between DBMS and application, the overall performance will usually improve.

Often, one easy way to do this is to list only those columns you actually need in the application, rather than using 'SELECT *' to retrieve all columns when you'll only use 4 of the 24 that exist.

Second rule:

• Try to ensure that the DBMS does not have to look at huge amounts of data.

To the extent possible, minimize the work that the DBMS has to do. It is busy, and typically it is busy on behalf of many people at any given time. If you can reduce the amount of work that the DBMS has to do to process your query, everyone will be happier.

Consider things like ensuring you have appropriate indexes on the table - not too few, not too many. Designed judiciously, indexes can greatly improve the performance of many queries. Always remember, though, that each index has to be maintained, so inserts, deletes and updates are slower when there are more indexes to manage on a given table.

(I should mention: none of this advice is specific to Oracle - you can apply it to any DBMS.)

A few points to remember:

• Fetch only the columns you need to use on the client side.
• Ensure you set up the correct indexes that are going to help you find records. These can be done later, but it is better to plan for them if you can.
• Ensure you have properly accounted for column widths and data sizes. Don't use an INT when a TINYINT will hold all possible values. A row with 100 TINYINT fields will fetch faster than a row with 100 INT fields, and you'll also be able to fetch more rows per read.
• Depending on how clean you need the data to be, it may be permissable to do a "dirty read", where the database fetches data while an update is in progress. This can speed things up significantly in some cases, though it means the data you get might not be the absolute latest.
• Give your DBA beer. And hugs.

(I should mention: some points are specific to Oracle and some are not.)

Cheers,

Ujjwal Soni