2

I'm designing some lists that may well contain thousands of items, and I'm trying to come up with the best design for efficient querying.

I'm looking for some form of (hopefully Microsoft) documentation on what makes for an efficient table/query design. For example, one of the main filtering columns on a table could be implemented as:

  • Choice: Readable, but my SQL experience tells me string comparisons are likely to be slow.
  • Lookup (with dedicated table): May be slowed by the ";#" string splitting?
  • Number: It won't be immediately obvious what the Number means, but it may be faster?

As you can see, I'm not sure which is the better option in this situation, and several similar circumstances.

P.S. SharePoint is far from a Relational Database, I know. However, setting up a separate SQL database for this would be extraordinarily inconvenient, even with my SQL Server background.

Improve this question

2 Answers 2

Reset to default
2

Check this out:

MSDN: List Patterns

You can also buy a guidance ebook here:

Developing Applications for SharePoint 2010

See the SharePoint List Data Models chapter

Improve this answer
3
  • +1 The 'List Patterns' MSDN pages are interesting, but seem to boil down to "Use indexes and Views". I'll look into buying the ebook; if no-one offers any free alternatives.
    – Stu Pegg
    Jan 6, 2012 at 12:35
  • If you need soft copy, you can just download (no need to buy)
    – Amit Kumawat
    Jan 6, 2012 at 13:12
  • Whereabouts? I can see a link to buy the book ($27.99 for the soft copy), and download link for CHM documentation; which I've downloaded and does not include the book.
    – Stu Pegg
    Jan 6, 2012 at 14:41
1

Stuart, when dealing with really large lists, I think it could be a very good idea to use SharePoint Search to query and display these lists.

The point is, that CoreResultsWebPart inherits from the well-known DataFormWebPart, so actually you can render very complex views with CoreResultsWebPart and its XSLT, and probably you can even create almost the same look and feel, as the XsltListViewWebPart offers.

Also, what about the standard CAML queries, I think the indexed columns and the unique columns will do most impact. Also, as always, try to avoid string comparison queries (like "BeginsWith" and "Contains"). The most quick will be Integers, of course. Other things will not matter much (based on my own experience with MS SQL performance).

Improve this answer
3
  • Interesting point about the SharePoint Search, but unfortunately the access I want to make more efficient is programmatic (via an SPQuery). I'm guessing lookups fall under the "string comparison" category, since they need to be split before the IDs can be checked.
    – Stu Pegg
    Jan 6, 2012 at 14:48
  • 1
    SharePoint Search does have programmatic interface and I promise you, it is much more flexible, than CAML query. And what about lookups, actually I don't know how the search for lookupId is implemented, so best way to determine the performance impact precisely is to use Developer Dashboard and analyse the MSSQL queries generated by SharePoint.
    – Andrey Markeev
    Jan 6, 2012 at 15:25
  • Quite an innovative way of looking at the issue! @AndreyMarkeev
    – Nisarg Shah
    Mar 1, 2016 at 15:51

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.