CODEWITHSUNDEEP
c#sql-server
Ranadeera Kantirava
Ranadeera Kantirava

Reputation: 324

Which is better performance wise, C# for loop or cursor(or while loop) in sql?

Which will perform better while loop or cursor? After lots of research, I came to know they both are equally bad for performance and may sometime out perform each other based on situation, and should be used only when it is not possible to use set based operation.

Now question is Which is better performance wise, loop in C# or cursor(or while loop) in sql? and I searched in web, but found no definitive result...

anybody have any idea?

Upvotes: 4

Views: 4876

Answers (2)

Kris Vandermotten
Kris Vandermotten

Reputation: 10201

When you say "a for loop in C#", do you mean that you intend to first load all the results from the query, and then subsequently loop over them? The downside of that approach is of course that you need the memory to hold all those results. But you don't have to do that. There are many mechanisms in C# that allow you to loop over the results as they come in, which avoids the need to hold all results in memory. It depends of course on your database access technology.

If you use some technology based on IQueryable<T>, just use a foreach loop over the result, and avoid calling materialization functions on it, such as .ToList().

As for cursors, always avoid them if possible. For performance, always prefer set based operations over cursors. In fact, the same is true for a foreach loop in C#. If your processing of each result involves querying the database again, use a SQL join that returns you the needed data in a single query, instead of a new query for each result row retrieved.

Upvotes: 0

Morix Dev
Morix Dev

Reputation: 2744

Based on my experience I would say: it depends on which operations you perform on every item...

In a scenario it happened to me to use cursor loop in SQL for performing bit-wise operation on some data read from the DB and it was very poor in performance (SQL is not intended to operate such kind of stuff)... in that case I obtained a better result looping in C# on a cursor opened on the DB...

On the other side, if you have to perform some other complex data-mining task for every item of the loop, then it is much more convenient to do that in SQL, so that your data do not have to go back and forth from DB to C# and viceversa.

Have you a specific application scenario you can talk about, so that we can give you an idea about that?

Upvotes: 5

Related Questions