Sunday, March 22, 2009

Working with large list

Hi all,

Every time we start designing any project on sharepoint, we or I can say all, are having same problem.

Where to store the data in the sharepoint? (Which can be used as data table? in the language of dumb client who want to use sharepoint but don’t know why)

This is the million dollar question for starting any sharepoint project.

And billion dollar answer is none other than SharePoint Custom Lists.

And after deciding to go with the list another question will come about the taxonomy and structure of list.

You may also read so many article that more that 2000 item is hitting performance in the list. And that is true (that is what Microsoft says)

We found one of a very good document from Microsoft regarding the same Issue with title “Working with large lists” here is the link.


It really worth to read this article to decide architecture.

So we found one way to over come this situation.

Scenario: we are analysis SharePoint solution for one of the manufacturing plant.
And data is been stored in SharePoint list. For an example order list is containing a large number of data. So our first concern is how to implement this much data in one list.
One of the best solution we found is we created taxonomy for SharePoint list item. We decided to store order data in list with folder structure.

What we thought is that create a solution which will help to over come this 2k item problem.

First a folder is created for year like “2009” then month like “3” and then we are storing item in that folder. ( according to client they will not get orders more then 2k per month) other wise you keep segregating this with weeks, or days or may be hours according to your need.
So in our case if I’m adding an order today then my folder structure is
Order list/2009/3/my item

Note: we are storing/inserting item in the list programmatically and will post the same thing (how to do it programmatically?) asap

So this will solve our one problem.

And at the same time another problem is how to store other data or other related things like ….. history for an example. And each order is having multiple records in history.

So to over come this problem we user same structure with one more node.
Year/month/ordered/item in the list. and this date is form order created date not history created.

So we can store multiple items almost 2k item related to one particular order.
So our order history list we will have following structure.

Order History List/2009/3/OrderID#27/history item.
Again repeating the same that if history table updated in April even that structure will be the same as we are creating structure according to order.

So this way we can over come form SharePoint view limitation of 2ooo items.

There are a lot of other ways like creating subsite of Order (Main Item) and store related info in that subsite only. And many more are there according to our need and time.

But this is one of the way that we had done and working fine so we thought that better to share the same to every one so that we can get impact if some one had other idea for similar kind of implementation or some draw back or side effect of this implementation so that we can share the same with community. Please fill free to comment and share your idea regarding the same.

4 comments:

Andreas Grabner said...

Hi
Please check out my blog series about SharePoint Performance Considerations when working with SharePoint Lists @ http://blog.dynatrace.com/tag/sharepoint-net

This should also help you to find some answers about how to correctly access SharePoint data via the SharePoint API

Parth Patel said...

Hi Andreas Grabner,

great research on performance.

definitely helpful.

will try your tricks with my folder structure with lots of data soon.

and will publish the results if possible.

thanks again.

Anonymous said...

But even with this folder structure , data will be saved in sharepoint list only..Then what is the benefit?

SharePoint Kings said...

benefit is that even data in same list, your performance will not hit




Share your SharePoint Experiences with us...
As good as the SharePointKings is, we want to make it even better. One of our most valuable sources of input for our Blog Posts comes from ever enthusiastic Visitors/Readers. We welcome every Visitor/Reader to contribute their experiences with SharePoint. It may be in the form of a code stub, snippet, any tips and trick or any crazy thing you have tried with SharePoint.
Send your Articles to sharepointkings@gmail.com with your Profile Summary. We will Post them. The idea is to act as a bridge between you Readers!!!

If anyone would like to have their advertisement posted on this blog, please send us the requirement details to sharepointkings@gmail.com