We have been developing a DB web app which constantly queries an SQL store of objects (about 80,000 of them) which are related to each other in a hierarchy. (i.e. many objects have parentid which relates to another object in the same table)
I have been thinking that loading the entire object tree into memory and querying the objects using LINQ may be a better approach. The objects are 1-5K each mostly
Does anyone have any thoughts?
"LINQ" includes LINQ to Objects, LINQ to SQL, etc. You can certainly query in memory, but maybe you should wait until you've done a performance analysis before optimizing. You may optimize the wrong thing.
Ad hoc, loading up to 250 megabytes of data into client memory seems not to be such an good idea. If you have it memory, you can use LINQ to Object and save the database roundtrips.
The problem is that your data is hierarchical and you have to fetch one row, examin it, and retrieve the releated rows and this causes so many roundtrips.
You could use lazy loading to retrieve more and more rows into client memory during the runtime of your program and perform more and more queries in memory when the working set stabilizes. But this will only help if rows are accessed multiple times and not only once or twice.
Another solution might be to create recursive views or stored procedure using common table expression that retrieve releate rows up to some distance from a given row.