Pagination and Data buffering in Windows Application using C# 2005

Question

Requirement
.NET Windows application using C# interacts with Oracle DB for retrieving and saving data

Issue
With huge volume of data, the performance is slow and the memory usage is high, the application displays the entire data in the screen. Response time is high due to database call and client side data processing

Proposed Solution
Using pagination (from Oracle DB) to display partial data in the screen, response time of the application will be faster; however, it will make DB calls for each page. We are looking at a solution to get the 1st page data from DB and start the application, after which there will be a background job to get the rest of the data from DB to a local XML DB. So, in case of next page, the data will be loaded from XML instead of making a DB call.

Is this design possible?
Is synchronization possible between local XML DB and the Oracle DB?

Answer 1

Personally I am not sure you really want to go that far, as synchronoization, and overall disk IO could be very "interesting" at best.

Typically what I have found to be good in the past if you REALLY must have "pre-fetched" records for more of the result set is that you can cache say the next 2 and previous 2 pages in memory, that way the users transition is smooth, and after you navigate the page, a backend thread will go out and pre-fetch the next page so taht you have it.

Otherwise, if you do what you are talking about, you are only deferring the performance impacts and introducing data synchronization and other issues.