Hi,
Queries: Select by Id Select Where ColumnValue = X Group By OrderId Group By Date
The SP List will be 6 columns wide: Id, Date, OrderId (Lookup), Quanity, ItemName, Title
Kind regards, Ashish Shharma
The SharePoint lists will be slower.
More overhead = worse performance.
Don't do it. SharePoint is not good at handling transactional data and will perform badly.
Any abilities you might have to improve performance at the database level (like adding indexes) may have detrimental effects on the SharePoint installation (although columns in lists can be "indexed" through SharePoint.
Essentially SharePoint is designed for a specific purpose (content/documents) and trying to get it to do something out of the ordinary means you have to fight the application tooth and nail.
Fortunately SharePoint has several means of integrating transactional data into it.
First off (if you have the more expensive Enterprise licence) you have the Business Data Catalog that allows you to import database values that will appear similar to list items.
If you do not have the Enterprise licence, I can recommend either custom controls/webparts or the Data View Web Part to allow that data to be "shown" on the relevant pages within SharePoint.
In summary: You will be setting yourself up for a lot of uneccesary work by storing transactional data within SharePoint compared to other application designs hosting the data in traditional database applications and integrating to SharePoint.
+1 No
SharePoint primarily function is collaboration. In your case you will just list the data as read-only. In your situation I would recommend to store the data into SQL DB, if you need to display it in SharePoint portal you can use BDC or something like Bamboo Data View web part. http://store.bamboosolutions.com/p-71-data-viewer-web-part.aspx
I concur with all of the above comments. I've had extensive experience with customers who wanted to use SharePoint lists for things where they didn't fit. If you're worried about performance at all, then SharePoint lists are not the way to go. If it's simply for archival purposes and you are doing infrequent searches against the data and the SharePoint search features are sufficient for you, I might consider it and not dismiss it out of hand (if you're using MOSS).
But I would consider all aspects of this carefully. It's not too difficult through Data Form Web Parts, and the BDC to get SQL server data into the SharePoint environment, but it is more difficult to get SharePoint data into other platforms or applications.
And again, if performance is at all a requirement, then don't do it.
For more SharePoint scalability and performance best practice information see: http://technet.microsoft.com/en-us/library/cc287790.aspx
The rule of thumb is to limit SharePoint lists to 2000 items for performance reasons.
At 100k, the performance would go "from suck to blow".
The only way that this could work is if you could segment the data set into multiple lists with less than 2000 in each.
I also agree with the guys above However - a lot of the performance problems discussed in blogs are due to the fact that the SharePoint Object Model is not used correctly.
You can checkout my blog series about SharePoint List Performance at dynaTrace Blog. This series looks into the SharePoint Object Model to highlight what is actually going on between the SharePoint Servers and the Content Database
Having done this myself, I would say try to avoid it if possible! It's a minefield, especially after about 100,000 rows.
Something that can end up biting you as well, is that the search crawler can start timing out trying to crawl really large lists - you can increase the time outs, but it's the beginning of a loosing battle.