Solved

Large list in SP 2007 or SP 2010.

Posted on 2010-11-16
8
652 Views
Last Modified: 2012-08-13
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
Comment
Question by:Techsavy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 12

Accepted Solution

by:
geowrian earned 168 total points
ID: 34152280
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
 
LVL 6

Assisted Solution

by:einarbrown
einarbrown earned 166 total points
ID: 34152891
You can also index columns to increase performance in Sharepoint 2010.

http://www.sharepointsecurity.com/sharepoint/sharepoint-development/sharepoint-2010-indexing-best-practices/
0
 
LVL 2

Assisted Solution

by:mangesh004
mangesh004 earned 166 total points
ID: 34153158
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Techsavy
ID: 34180721
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
 
LVL 19

Expert Comment

by:Montoya
ID: 34180734
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
 

Author Comment

by:Techsavy
ID: 34183959
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
 
LVL 17

Expert Comment

by:GreatGerm
ID: 36135903
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I recently came across an issue with a MOSS 2007 deployment where access into some sub-sites were denied, even for the MOSS farm administrators. A bit of background to the setup of this MOSS farm; this was a three server setup, consisting of a fr…
In case you ever have to remove a faulty web part from a page , add the following to the end of the page url ?contents=1
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question