Data Storage: SharePoint v. SQL
Many years ago we worked on a project that would allow the client to perform inspections digitally on the various restaurants in their franchise. One of the goals of these inspections was to make sure the restaurants were following the client’s procedures and policies. These procedures and policies are constantly being reviewed and updated, so one of the main requirements of this inspection project was to allow the client to update the inspection on the fly without requiring development updates each time. In other words, it needed to be reasonably configurable by the client. We ultimately created a dynamic form that would be constructed in real-time based on data provided by the client. The idea was that as the client updated their procedures and policies, they would add/remove items from the form to keep it up-to-date.
The people responsible for maintaining this inspection form weren’t technical in the slightest, so in order to allow them to maintain it we needed to provide them a user-friendly way to manipulate the data used to construct the form. This client already had an existing SharePoint environment, and their entire user-base was familiar with the basics of using SharePoint lists so in order to make it easier for the people maintaining the inspection form we decided to keep all the data for the form (configuration and results) in SharePoint.
After long-term use of this project, we wish we hadn’t.
SharePoint lists are definitely not made for intense data crunching. As the years went by, the lists holding the results of each inspection grew larger and larger, resulting in an application that was getting progressively slower and slower. To make matters worse, the client rarely made updates to the inspection configuration as originally intended – each time they wanted to update the inspection form, the changes were either so drastic as to require developer intervention anyway, or they simply didn’t have the time to do it themselves and so had us make the updates. This made the main reason we had decided to go with SharePoint to host the data for this project moot!
We did spot fixes as time went by, trying to keep the inspection form from grinding to a halt. These kept the project limping along for a while, until the last large change the client requested: auditing. They wanted audit trails for all activity performed on any given inspection. We implemented this (still in SharePoint) but it didn’t take long until the new massive amount of data flowing in and out of the system made it clear that we couldn’t utilize SharePoint anymore.
So we moved all the data into SQL.
SQL is much better suited for handling large amount of data and querying for subsets of the data. Plus, as a bonus, now the client could run their own reports against the data. Without SharePoint choking the data access, performance improved greatly and now there’s little concern about the sheer amount of data the system is handling. The only downside is the client can no longer maintain the inspection form on their own (at least not until we write an interface to allow them to do so), but they weren’t really doing that anyway.
In conclusion, storing data in SharePoint can be useful when your user base needs to access the data frequently and is not very technologically literate. However, if you ever suspect your data will grow unwieldy (say, in the 10s or 100s of thousands), SharePoint will probably end up hindering more than helping. SQL isn’t very user-friendly but for handling large data sets it is by far the better option.