4

I want to keep the records of registered user for course registration. There are maximum 100,000 users, 1 user can registered 25 courses. Which means the maximum records can be 2,500,000. One item cannot hold more than course's details.

How can I achieve this? Should I create a separate SQL Server Database (External Data) and fetch in list using BCS?

What will be best approach.

Improve this question

4 Answers 4

Reset to default
4

If you intend to use the BCS External List to fetch data you should be aware of this important point

If the retrieving items from the LOB database are more than 2000 items You will face performance issues so I advise you to check External List Limitation Before you try to use BCS even if you increase its default max (2000 items)to absolute max via PowerShell which will increase it to only 25.000 items.

So I agree with you to create a separate SQL Server Database and work with the Visual web Part with CSOM or Server Object model via C#.

Improve this answer
3

Storing this much data is not an issue with SharePoint. But actual problem is presenting the data( reporting). In SharePoint their is thing called "List View Threshold" which had limit of 5000 items in a view.

Even you stored the data in the SQL bring in Sharepoint using the BCS but again LVTH will cause the issue.

What you do, create index columns, use the filters in the view and keep the number under 5k.

You can increase the LVTH but you have to pay for the performance. You have to test the performance if you increase the LVTH.

UseFul reference: https://technet.microsoft.com/en-us/library/cc262813.aspx?f=255&MSPPError=-2147217396#Throttling

https://technet.microsoft.com/en-us/library/cc262813(v=office.14).aspx

http://www.enjoysharepoint.com/p/Working-with-large-lists-in-sharepoint-2013-list-throttling.aspx

Improve this answer
2
  • "You can increase the LVTH" but it doesn't increase infinitely, right? There is some cap over which you cannot exceed. And it's not like every query just returns the TOP LVTH number of records either. If you try to return LVTH+1 then you actually get an error and no records.
    – Brad
    Commented Jul 11, 2016 at 17:09
  • 1
    Right, That's why i said test it. Sometime increasing from 5000 to 5100 cause the performance issue...sometime even increase to 20k not causing the issue. I have customer where we increase it to 20k. Every envrinoment is different.
    – Waqas Sarwar MVP ♦
    Commented Jul 11, 2016 at 17:22
0

Unless you are using other functionality of SharePoint to collect the registrations or initiating an approval workflow, it might be best to store this list in an Excel document that is stored on SharePoint. There are a lot of reporting options in Excel as well as using the document as a source.

Improve this answer
2
0

We utilized a custom webpart to write to SQL to record many entries like this. Then, we then used SSRS to report on it. Links to the reports were posted on the SharePoint site along with the form. Most users didn't realize it was two separate systems.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.