I'm working on an application that imports video files and lets the user browse them and filter them based on various conditions. By importing I mean creating instances of my
VideoFile model class and storing them in a DB table. Once hundreds of files are there, the user wants to browse them.
Now, the first choice they have in the UI is to select a
DateRecorded, which calls a
GetFilesByDate(Date date) method on my data access class. This method will query the SQL database, asking only for files with the given date.
On top of that, I need to filter files by, let's say,
UserRating. This would place additional criteria on the files already filtered by their date. I'm deciding which road to take:
DateRecordedchanges. Handle all subsequent filtering manually in C# code, by iterating over the stored collection of
_filesForSelectedDayand testing them against current additional rules.
Which one would you choose, or even better, any thoughts on pros and cons of either of those?
Some additional points:
GetFilesByDateis expected to return tens of items, so it's not very expensive to store the result in a collection always sitting in memory.
Personally I'd always go the DB every time until it proves impractical. If it's a small amount of data then the overhead should also be small. When it gets larger then the DB comes into its own. It's unlikely you will be able to write code better than the DB although the round trip can cost. Using the DB your data will always be consistent and up to date.
If you find you are hitting the BD too hard then you can try caching your data and working out if you already have some or all of the data being requested to save time. However then you have aging and consistency problems to deal with. You also then have servers with memory stuffed full of data that could be used for other things!
Basically, until it becomes an issue, just use the DB and use your energy on the actual problems you encounter, not the maybes.
If you've already gotten a bunch of data to begin with, there's no need to query the db again for a subset of that set. Just store it in an object which you can query on refinement of the search query by the user.