How to Speed up Reading huge data from Database

Reading small amount of data from Database is fast and easy, but as the data size increases the read performance and also managing the data becomes difficult. But in today's world, "Data is the new fuel". So storing and doing operations on such huge data is very common. So let's see how we can deal with this problem in our Application.

Understanding Application Requirements:

Requirement is always the first thing you should consider, when dealing with such problems. For example, if you are creating an ecommerce website which has millions of products and you want to show them to your customer on your website. Now on your website, not all million records will be visible on your screen at all time. You will be displaying only first 10 records and then giving customer option to either go to next page or add a scrollbar for fetching next 10 records. So instead of fetching all millions records at once and we can split our query to fetch first 10 records and then next 10 records based on the page number.

Improving SQL Query:

We often try to fetch data from multiple tables and while doing so although we require some limited data, we write a query which fetches unnecessary data too. For example, we need Employee names whose department is 'HR'. So we write this query-
select * from employee e join department d on e.deptId=d.id where d.departmentName='HR';
But Above query will try to return all columns from Employee and Department table, which we don't require. We can easily optimize our query to return all data that we want, like,
select e.employee_name from employee e join department d on e.deptId=d.id where d.departmentName='HR';
Likewise there are ways to improve sql query too, which will have a very good impact on Query Performance.

Add indexing:

Creating an index on a field in a database table creates another data structure which holds the field value, and a pointer to the data it relates to. This can provide significant improvement while fetching data. But you need to decide which columns to index as per your application requirement. You can refer Indexing Best Practices for better understanding on how to use indexes.

Improve Memory and CPU:

The efficiency of any Database will be affected, if it doesn't have enough memory and computing power for database to work perfectly. So if you can afford higher memory and CPU, I would suggest, just go for it. This will have tremendous impact on Database Performance

JDBC Fetch Size:

For Java Applications only, there might be similar methods in other language too.


If you are making Java Applications, you can use JDBC fetch size parameter, which can significantly improve your read performance. Syntax,
getJdbcTemplate().setFetchSize(200);