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.