Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 667
  • Last Modified:

Large list in SP 2007 or SP 2010.

Hi,

we have a scenario, where we may end up populating a SharePoint list with over 100,000 records. In this case, what is the best way to handle these in a SharePoint list? Or is there any other better solution ? which gives me the benefit of SharePoint without affecting the performance of the list?


Thanks,

0
Techsavy
Asked:
Techsavy
3 Solutions
 
geowrianCommented:
The basic rule of thumb is to keep the number of list items shown on a single page to about 2,000 or less. This can be accomplished by ensuring all views have the "Item Limit" set to show items in batches of 2,000 or less. In Sharepoint 2010, you can configure an option to force per-list/view limits (including different limits for administrative users).

You can also use folders to partition the items.

If you have any custom coding that retrieves the data, ensure they also follow the same practice of not pulling more than ~2,000 items at once (pull the items in chunks if necessary to go over this limit). The major performance issue is that while the SQL server is retrieving the items, it locks the entire table, making any other read/writes to any other lists wait for the very large query to complete.

Microsoft has a white paper on this exact subject:
http://go.microsoft.com/fwlink/?LinkId=95450&clcid=0x409
0
 
einarbrownCommented:
You can also index columns to increase performance in Sharepoint 2010.

http://www.sharepointsecurity.com/sharepoint/sharepoint-development/sharepoint-2010-indexing-best-practices/
0
 
mangesh004Commented:
Hi,

Recommended approach is not to have more than 2000 items in a single container / view. If you need to have 100,000 records in a list (which is actually a huge no.) then i would recommend that create folders in the list and make sure that you are not crossing more than 2000 items in a single folder. You have some event handers to manage those folder creating quarterwise folders.

If the data is just read only then you can have it in SQL table and implement BDC to view the data. For write operations you can have some web parts / usercontrols.

If you have attachments in the list or item level permissions and you know that its gonna grow in futre then just avoid list. It will put too much pressure on the search crawing as well but that decision you can take based on the hardware and other application function.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TechsavyAuthor Commented:
Hi Mangesh,

will your approach improve performance by any chance?i.e if I have 100,000 items in a list without folders in one case, but then I have another case where i split these 100,000 items into different folders. will the performance differ? i.e in the second case, where have 100 folders that contain all 100, 000 items in it.? if this is true then i like this approach.


can you please clarify?

thanks
0
 
MontoyaProcess Improvement MgrCommented:
all queries in SharePoint are going to happen on demand. If you want to improve performance, start with the suggestions above...and here are a couple of other things:

Set a rule to archive older items to another list. Chances are that you won't be accessing 100,000 records all the time. You can set up a worfklow that archives older items or items not modified in a long time to another list, therefore maintining a streamlined list.

Group your items in all your views. Grouping the items will dramatically increase your performance as it does not have to display each item on the screen. Just note that once they hit that plus (+) symbol, the natural course of things will take effect as it tries to display all items in that group.

Try to use a filter through a data view web part. That way the user is looking for specific filtered items which would only return a very small subset of the 100k items.

Hope that helps...
Cheers

0
 
TechsavyAuthor Commented:
Hi Tammontoya,


Thanks for the brilliant idea of workflow.

I like the idea of setting up a workflow. However I am still waiting to clarify from mangesh 004 on using folders.

0
 
GreatGermCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Tackle projects and never again get stuck behind a technical roadblock.
Join Now