Performance: SQL query vs SPListItemCollecion

Question

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?

Answer 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.