Populate large data from database.

Question

I have develop 3-tier application and I have issue about populate data from database. My application has some window that query large data from database and this process take a very long time. (My client is so far from server and connection between it is 128 KBS) Then I have think for the solution to solving this problem. The solution is populate x records of data every y sec. (eg: populate 10 records to display on UI first and then display more 10 row every 5 sec.)

This is a good solution to solving this problem ? Did it have another better way to solving this?

Thanks.

Answer 1

Is it an option to do lazy loading (so you would be loading data on demand, not all at once)?

Answer 2

Maybe if the application UI can allow, show records in multiple pages.

I would also suggest to use asynchronous calls to retrieve data so you dont freez the app while querying (if its desktop app).

If its web app, consider using AJAX.

Answer 3

If it is for display purposes you should be using paging. There is a variation of this, where the UI grabs the next "page" of records when the user tries access more info with the scroll bar. If you need to aggregate data, do it on the query, so the whole data doesn't travels trough the network. If you really/absolutely have to get the whole data and do some processes with it, consider if it or will ever be too big to load all at once. In that case, grab chunks of the data, run related processes, and then grab another chunk (so on). Regardless, you need to do it with an approach that doesn't blocks the UI (threads / asynchronous operations).

Answer 4

Freddy beat me to it, but as an example, you'll notice a lot of database viewers populate only the data required in the field of view, as you use the scroll bars it automatically grabs the next page/view of data.

If you think about websites, they will nearly always Page the data as not to load it all at once.

Answer 5

Paging sounds like the best option.

I suppose another suggestion not yet mentioned would be to check your SQL to see if the code can be optimized better to help reduce load times.

Using SQL Profiler, viewing the Execution Plan for your SQL, as well as checking the Indexes on tables can help you identifying ways to improve your SQL performance.