1

A project I'm working on has me pulling lots of data from another SQL database (via a c# webpart) and I'm curious about the best approach to make it as speedy as possible. The two thoughts I had were to A) Run queries of the DB every time I want information from it, or B) Pull the data into a few lists ahead of time and run statements against those lists.

My first instinct is to go the list route (I'm not a SQL expert but I assume constantly opening extraneous connections to it will be bad for performance), but to be honest I have been surprised in the past by the truth of these situations so I thought it best to ask for advice.

Which of the two methods is faster? Is there another option entirely? What factors make one better/worse than the other?

Edit: For the record, the databases exist on the same SQL server. Also some further clarification... I basically need to search a very large collection of data for items matching a certain pattern and then perform a calculation based on several of their fields and update another list item with this calculation. This has to be repeated for some 8500 items that need updating, searching about 16,000 rows in a table (or 16,000 items in a list if I go that route) for each instance and performing the calculation/update as described. So the question deals mostly with which performs better: Searching an SPListItemCollection or running a SQL query using LIKE statements to find my data through a new connection?

An alternative that just occurred to me is to put the data in lists as described and then have the list that needs updating use calculated fields to run the calculations itself, instead of running an actual webpart that does it. Then the webpart would simply be responsible for sorting/displaying the data instead of querying anything. However, I have NO idea what the impact would be of having a list with 8500 items run multiple calculated fields for each item against a list of 16k other items... or if that's even possible. Thoughts?

  • 1
    Even if you save the data in lists, the would still live in SQL though (in the content database)! So it is very dependent on which SQL Server you want to add the load to – Robert Lindgren Mar 11 '14 at 14:17
  • It's actually on the same SQL server, guess I should have mentioned that. What I'm really thinking about though, is which method would be faster at indexing and looping through the collections of data? If I run a foreach against an SPListItemCollection searching for specific data will it perform differently than if I used a LIKE statement in a new SQL query? – thanby Mar 11 '14 at 14:29
1

Performance for querying a SharePoint list will be much, much worse than a sql table. Well, there could be some use case where the list would provide a benefit. Perhaps the query is a very expensive query, and you're caching aggregated results in a list? Perhaps. But even that would be an odd use of a list, as you could cache results in another table just as easily.

need to search a very large collection of data for items matching a certain pattern and then perform a calculation based on several of their fields and update another list item with this calculation.

This sounds like a join or a subquery, which are things that SQL excels at, and SharePoint does not.

  • Ah thanks for the info. I'll build the webpart that way and see how it performs. – thanby Mar 11 '14 at 17:35
  • Seems SQL handles things well enough that I don't need the extra lists to store the items. I was wondering if running queries like this would disrupt operations but so far it doesn't. Performance-wise there is a bit of a benefit to using queries instead of list item collections, so as long it's not hurting the SQL server I'll stick with that method. – thanby Mar 11 '14 at 20:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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