Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer
    
+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. –  Stuart Pegg Jan 6 '12 at 12:35
    
If you need soft copy, you can just download (no need to buy) –  Amit Kumawat Jan 6 '12 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. –  Stuart Pegg Jan 6 '12 at 14:41

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).

share|improve this answer
    
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. –  Stuart Pegg Jan 6 '12 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 '12 at 15:25
    
I'll look into both those options, thanks. –  Stuart Pegg Jan 6 '12 at 16:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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