I am building an application in Winforms. This will talk to DB(oracle) and load huge amount of data (only for viewing). Apart from caching and paging, is there any other point to consider? Performance is an issue, but so is the consideration of limited memory on user machine.
Thanks.
EDIT - Additional info: I also have an option to build a java soap service to act as middle tier. Will that help?
If you are paging, there's no reason for a lot of data to come "over the wire". You only ever need to query the data that is being viewed at that instant, and possibly the next page to cache for user performance.
For example: let's say you have a table with 1,000,000 rows and 20 columns that are visible to the user, and that you want to display 50 rows on the screen at once. When you display the data grid, you should only bring over a portion of those rows (somewhere between 50 and a few hundred). This is a negligible amount of data to transfer on an intranet, and certainly wouldn't impact user memory. Even if you cache 200 rows, this is still <200KB of data to store locally and transfer over the intranet, which would take milliseconds.
You might also want to consider reporting, auditing & security.
Typically users won't be paging through a million rows, but using some sort of report to summarize or query information.
Depending on your requirements you might need thorough audit trails on how data got into your database, and even who has looked at it.
Finally, you will probably need to ensure only valid users can see relevant data. This may have an impact on your database structure, and almost certainly will have an impact on your application architecture.
Only request what you need at the moment, and only send back what has really changed. People often use ORMs and just pull the entire object down from the server. Then they send the whole object back to the server, when only a small part of it has changed.
Some of this depends on your ORM, but a lot of it depends on you, and how you use the ORM. Speculative caching can be useful, but only when you are pretty sure you are going to want the data. Start by requesting as little as possible, then work up.
If you are going to need to do multiple selects to build up your data model rather do all the work in a stored procedure. That will cut down the network traffic and also let Oracle do what it is good at. Good luck.