Anmelden
 
HomeLibraryDownloadsSupportWebcasts & EventsCommunityForenBlogs
Ressourcen für IT-Professionals >
> 
> 
Where to store data whether in sharepoint list or sql database ?

Answered Where to store data whether in sharepoint list or sql database ?

  • Freitag, 16. März 2012 07:43
     
     
    Anmelden
    0

    Hello,

    Scenario : I have created one web application with 5 site collection for HR,Account,Finanace,IT and upport.

                      I have centralize data in excel around milions of records.

    Issue : Which approach will be best in practice to store data ?

                Should I store data from Excel to sharepoint list or SQL database ?

                What is Pros and cons for both options ?

    Thanks,

            

    Dipti Chhatrapati


     

Alle Antworten

  • Freitag, 16. März 2012 07:49
     
     
    Anmelden
    0

    It depends :)

    Are you going to work with that data? I mean, update, insert new, delete, ...

    Then you can put it in SharePoint.

    If the data is only there for consulting it, why not store it in a database and link to it via BCS or SharePoint Designer DataSource.

    Are you going to need all that data in all 5 site collections? Or can it be split amongst the different site collections ?

    http://www.balestra.be || @marijnsomers

     
  • Freitag, 16. März 2012 08:13
     
     
    Anmelden
    0

    Thanks Marijn for your reply :)

    Well, I need to mostly work with manipulation of data like update,delete,insert also data will get split amongst the different site collection, however I am not sure If I store millions of records in list then will it be safe performance wise ?

    I want to store in sharepoint list so I don't be in need to go for connection through BCS and any more complications..

    gradually data will get increase and hence I need an perfect medium to store data where I get better performance of application.

    Please give your suggestions and opinions.

    Thanks a lott,

    Dipti Chhatrapati

     
  • Freitag, 16. März 2012 08:51
     
     
    Anmelden
    0

    Hi Dipti,

    In my opinion its always better to store data in SharePoint List rather than depending on External Data Source.

    In SharePoint List you can have all the common options like update/delete/add/views to perform on List.

    Additionally you can perform List operations using code and fetch or send data easily from list.

    SharePoint 2010 doesn't have much performance issues on list as you have option of "Resource Throttling" available now.

    On the other hand sometimes using BCS is trouble, most of the times you have connection problems there while accessing from different sources.

    Hope this helps!!

    Thanks, Suraj

     
  • Freitag, 16. März 2012 09:29
     
     
    Anmelden
    0

    Thanks Suraj,

    I would love to store data in sharepoint list, however by setting resource throttling how many items I can store in list at max, as I have milions of records in one excel sheet ?

    Do I need to store all excel sheet by importing spreadsheet into sharepoint list ? or is there any other simple ways to store more than 150 tables as sharepoint list ?

    Thanks,

    Dipti

    Dipti Chhatrapati


     
  • Freitag, 16. März 2012 09:34
     
     
    Anmelden
    2
    I have a far different opinion from the other approaches mentioned. You're working with millions of rows of data. Don't store them in a SharePoint list (check out http://sharepointdragons.com/2011/12/05/sharepoint-capacity-planning/ for limits regarding this area). Don't lift the resource throttling limits, it may severely compromise your server performance. Don't use BCS either. I'd say that this is a perfect job for PowerPivot for SharePoint: http://msdn.microsoft.com/en-us/library/ee210682.aspx , it's great when it comes to performance when handling huge amounts of data (as opposed to the other approaches mentioned), and the technology is very closely related to where the data is stored now.

    Kind regards,
    Margriet Bruggeman

    Lois & Clark IT Services
    web site: http://www.loisandclark.eu
    blog: http://www.sharepointdragons.com





     
  • Freitag, 16. März 2012 09:49
     
     
    Anmelden
    0

    Hi Dipti,

    You can store millions of records in SharePoint list, "Resource Throttling" makes it easy to query items without affecting the performance.

    Here is an nice article on it to refer  Resource Throttling

    Regarding the data from Excel Sheet, you can do this with number of ways -

    1) Importing spreadsheet as list( The easiest way to achieve this)

    2) Using Powershell script you can map column names and cells(You can try this option too).

    3) Writing custom code( Keep this as your last option, only if you need to perform some additional operations that is not possible using earlier two options).

    Hope this might be of your help !!!

    Thanks, Suraj - LinkedIn - http://in.linkedin.com/pub/suraj-bangera/45/a56/38a

     
  • Freitag, 16. März 2012 10:10
     
     
    Anmelden
    0

    You can increase the thresh hold.

    I guess you 150 tables means each sheet in file.I would recommend to go with each excel sheet as "Table" and "Custom List" instead of "Import Excel sheet type List".I faced lot inconvenient to use "Import Excel sheet" template some time its was not support the certain excel version on exporting to SP Foundation  and SP Server Enterprise edition.
    Not only that bulk updating the list using DataSheetView also not user friendly with "Import Excel".

    But one thing I would suggest to use the "OpenXML" concept to upload the excel data into "Custom List" through code.Its hassle free method if you uploading the excel file into server where some of the COM or Excel Reading drivers are not been installed for some security reasons.

    I am not sure how you are going to do operation like "update/insert/and deletion" I mean UI to deal with these operation.

    If you are using GridView with buttons,you can simply pass the Id parameter and open up the "built in NEW/EDIT" form for operation.

    Murugesa Pandian.,MCTS|App.Devleopment|Configure



     
  • Freitag, 16. März 2012 10:13
     
     
    Anmelden
    0

    Thanks Suraj for the Kind reply, however by going with the option of sharepoint list rather than sql data table performance will get slow or down with resource throttling settings ?

    I need answer of above question just for the confirmation as I doubt that performance will get slow if we store large data in sharepoint list !!!

    Thanks again :)

    Dipti Chhatrapati

     
  • Freitag, 16. März 2012 10:59
     
     Beantwortet
    Anmelden
    0

    Hi Dipti,

    I suggest you to go through the below articles before you proceed.

    This will give you an clear idea on limitations and advantages of storing data in SQL Database or SharePoint List.

    Article 1

    Article 2

    Thanks, Suraj || LinkedIn - http://linkd.in/xXf8nk

     
  • Freitag, 16. März 2012 11:23
     
     Beantwortet
    Anmelden
    0

    Hi Dipti,

                 Based o the scenario 

    1) The data will be spitted to the sub sites

    2) The data will be added, deleted and updated

    The possible solution will be

    1) If the data in HR,Account,Finance,IT and Support are not related means the data shown in the Finance are not related to Account, create the list in the respective sub sites and export the data.

    2) If the data on the respective sub sites are related use the Excel services to show the excel file

    Or

    Create a list for the respective data in the sub sites (the data that can be updated, deleted and created). Use the excel file to get the data from the content data base and show it in the excel sheet. This would help you to manage the data respective to the sites using list and excel sheet can be used to display the the data.

    Let me know if it helps!!!

    Ajeet

     
  • Freitag, 16. März 2012 12:44
     
     
    Anmelden
    0

    Thanks to all of you !!!

    I get my answer from following link..

    http://msdn.microsoft.com/en-us/library/ff647105.aspx

    Thanks Suraj for your time !!

    Dipti Chhatrapati

     
  • Montag, 19. März 2012 09:19
     
     
    Anmelden
    0
    Actually, you didn't. You're basing your decision based on info about SharePoint 2007, PowerPivot didn't exist back then, BCS played a role that was far minor to the current situation, external lists didn't exist either. Those should be key factors in your decision making. Sometimes you know so sure you're giving the right advice and see somebody take a different corner anyway... Oh well, that's life.

    Kind regards,
    Margriet Bruggeman

    Lois & Clark IT Services
    web site: http://www.loisandclark.eu
    blog: http://www.sharepointdragons.com


     
  • Montag, 19. März 2012 11:10
     
     
    Anmelden
    0

    I agree with Margriet,
    The Article links given here to compare the SPList Vs SQL Table seems to be a date back.May be it was written for WSS 3.0.
    Here I suspect,The thread initiator's roles under "Farm Administrator" or "Developer" because "Power Pivot" requires the Enterprise edition of SP 2010.

    My answer for this thread was fully based on Developer prospective.Even we are not sure about the UI to push the excel data into SQL either SPList.

    And it requires Farm Administrator role and have full access permission on SQL Server to play around this features.

    I don't know how the end user going to do CRUD operation over these record and clueless.

    So my conclusion ends here with thread initiator was  a Farm Administrator,not having of Enterprise edition of SP 2010,No UI for pushing the records into SPList or SQL and not ready to adopt the latest approach [Power Pivot].

    So the Article Links will be an answer for theoretically not for practically.

    Murugesa Pandian.,MCTS|App.Devleopment|Configure