CODEWITHSUNDEEP
c#sqlrecursionoptimization
user11793520
user11793520

Reputation:

Using Recursive with CTE in SQL Query vs. Recursive in C#

I have been researching which approach will better optimize my system whether it is to use Recursive in my Stored Procedure (SQL) or to use Recursive in the C#.

I have read two points of view where one explained that using recursive in c# is more complex and slower but solved using a tail-call optimization but C# does not support this.

On the other hand, I have read a thread where it is used in their program written in C# to reduce the Database burden.

I want to know which one is better in a system that'll store and retrieve thousands of records.

Upvotes: 0

Views: 626

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

In general, you are better off writing a single query and avoiding the overhead of going back-and-forth to the database.

You haven't specified what you are trying to do. But I would expect a recursive CTE to work better in the database rather than in the application for the following reasons:

  • You don't have the overhead of multiple calls to the database.
  • The intermediate results can be saved in internal database format.
  • You don't have to return data to the application and passing it back into the database.

In addition, some things that you might approach using recursion in C# might be better implemented using SQL constructs, such as window functions.

Upvotes: 1

Related Questions