0

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.

  1. 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.
  2. 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.

Thanks.

Improve this question

2 Answers 2

Reset to default
1

The logic ends up being fairly similar to direct SQL queries. There is a lot of overhead, but essentially, the list queries still end up being SQL queries. An exception to this can be if items have individual permissions. In this case a lot of additional processing takes place.

So... you will have to determine the best strategy based on the amount of data concerned and the number of potential secondary queries. Remember that it's not always the amount of data transferred that is the problem. With overhead, a large number of smaller round trips can kill you.

Would it be possible to do a single full query (all the columns you need) but adapt that query to only retrieve the data you know won't be cached already. Perhaps querying based on modification date?

Improve this answer
1
  • Thanks for the reply. The point about security trimming is a good point. There will be security placed on some individual items. A lot of the data won't have security, but some will. I will be using the modification date to only pull new items for "partial updates". But when I need to pull the full view again I need to pull the most recent 20 (or more or less) items in. Some of those items will already be in cache, so I don't need to fetch the full dataset for them from the db, I just need their ids to check against cache. Then for items not in cache, I need to go back for the full dataset.
    – TehOne
    Jun 14, 2012 at 20:10
0

Ideally the solution would be to query for the items needing to be updated. So if you don't expect/allow deletions that would be a query for items with Created or Modified > last check.

If you need to catch deletions I'd combine that with a request for all IDs if LastItemDeletedDate of the list is after the last check.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.