I'm trying to build a very high performance application on top of SharePoint 2010. One thing I'm having a hard time deciding is if I should make 2 list queries (1 small and 1 large) or if I should just make 1 larger query. Let me flush out my example a little bit.
First, I'm using caching everywhere I can. But some things may not be in the cache yet, so I'll need to get them from the database. I have two schemes I'm evaluating using.
- Do one "complete" query and get records from the list, this would be all columns needed. Then loop through these items and skip any "processing" of the items that are already in the cache.
- Do one "small" query and get only the id's of the items I need. Then loop through these items and check the cache for them. For items not found in the cache do a second "complete" query to get the items from the list, this would include all columns.
So, I can't seem to decide which I think would be more efficient: Doing one very large query with potentially a lot of items; Or doing 1 very "light" query and then only pulling the full details for items not found in the cache.
Now, if I was doing this via straight SQL and SQL Server, I think I would be inclined to use the second option. This could potentially lead to it being more efficient since we would potentially be sending less data across the network. But, at the same time, it introduces a second query to the db, which could loose any gains we made. Being in SharePoint though, I'm not really sure which approach should yield a better result.
Any help or advice you can provide would be appreciated.