Performance question
Lets say I need to retrieve around 3000 records from a server database (LAN). Now, on the form there are some record filtering options. My question is: do I fetch all the records at once, and filter data based on user selection, or should I pull data from database whenever the user changes filtering options?
And another question: since these are read-only data, then the choice for returning data should be datareader. But if I choose the first option (fetch all data at once), then how can I filter data, since when I retrieve data with datareader, I always put data in arraylist, and as far as I know, arraylist data cant be filtered?
Thanks in advance,
Goran
[679 byte] By [
Goran] at [2007-12-17]
Goran,
I think the reason that nobody responded is because it's such an opened ended question. The most likely answer is it depends! Does the data that you are working with change often or rarely? Is the data uniform or does it vary by user?
Also, for your second question you can always return it as a datareader and then if you want to cache it fill a DataSet/DataTable by calling Load.
Thanks,
Carl Perry
Program Manager ADO.NET
cperry@microsoft.com
From a performance point of view, if you're going to show all 3000 rows first and THEN allow filtering, you're better off pulling all 3000 rows then filtering locally.
However, if you plan to enable paging of any type (e.g. only show 10-100 records per page), then you're probably better off repulling the data each time the user requests or filters. It is probably unlikely that a user will look at more than 10% or so of the records each time they use your application, so why tie up the server and network by retrieving 3000?
This also answers your last question, since each refresh of the UI would correspond with a refresh from the database (hence a datareader would be the best means to fetch/display your records).
Hope this helps,
Josh Lindenmuth
