Stored procedure only way to go for big data processes, right?

Question

In the past, I would could big data processes in stored procedure. E.g. Update these records here, insert others there.. sometimes calling one proc from another, using temp tables to iterate, etc.  It worked well. 

But then we got sold on LINQ and began to use. It was/is great for simple CRUD operations, especially selects. But in a big processing operation, we couldn't possibly code all that we needed in a single LINQ statement. So we'd end of coding much of it in our .NET classes making LINQ calls within. 

The big problem with this is that the data is being brought back from SQL to .NET (via LINQ) and then iterated over, often in nested loops. These are killing us performance-wise, especially given that the classes don't reside on the same machine as the database (so all that data is going across the LAN). 

So we're now looking at re-writing these big processes back in stored procedure. Is there a LINQ person out there who would suggest otherwise?  There is no way even a LINQ genius could write this stuff in just LINQ without having to involve .NET code. And once you do, you're then bringing that data from data to business layer for all that processing.

Thanks for responses. 

Answer 1

Hi Coojbs,

Welcome to the MSDN forum.

I am not sure if you load all the data to your local memory or not. If not, I recommend you try this way. In addition, since LINQ query is deferred execution by default, you can try to use these methods to make it execute immediately: ToList(),ToDictionary,ToLookup() and ToArray(). Then iterates them.

Also, you can use compiled query to enhance the LINQ performance:

http://blogs.msdn.com/b/ricom/archive/2007/06/22/dlinq-linq-to-sql-performance-part-1.aspx

http://blogs.msdn.com/b/ricom/archive/2007/06/25/dlinq-linq-to-sql-performance-part-2.aspx

http://blogs.msdn.com/b/ricom/archive/2007/06/29/dlinq-linq-to-sql-performance-part-3.aspx

http://blogs.msdn.com/b/ricom/archive/2007/07/05/dlinq-linq-to-sql-performance-part-4.aspx

http://blogs.msdn.com/b/ricom/archive/2007/07/16/dlinq-linq-to-sql-performance-part-5.aspx

In addition, you can try to use custom sql queries with LINQ to SQL:

http://weblogs.asp.net/scottgu/archive/2007/08/27/linq-to-sql-part-8-executing-custom-sql-expressions.aspx

Good day.

---

Alexander Sun [MSFT]
MSDN Community Support | Feedback to us

Answer 2

I've got a topic about best practices of LINQ TO SQL, take a look, maybe this will help you somehow.

http://msguy.net/post/2012/03/20/LINQ-to-SQL-Practices-and-approaches.aspx

---

Please mark as reply if helped.
Also visit my blog http://msguy.net/

Answer 3

I suppose my question really isn't a LINQ one after all.  It's really more about pulling data from the data tier and processing it in the business tier vs just processing it in the data tier.  I realize if the latter, then that puts business logic in the data tier, but I see performance being better (data not crossing boundaries, etc).