what is good,using explicite cursor,while loop on database side OR using loop on front end (application side programm)???
Edited:- Hi,if I want to iterate and i am using ado.net asynchronous(disconnected) connection (ie.I have a datatable and i am applying for loop on datatable in front end) -- In this case,If i want to iterate which is better on DB side OR front end side??
The best approach is to take advantage of the set-based nature of SQL and avoid cursors whenever you can. If you can rewrite a query using a cursor to one that uses JOIN, you should always do so.
If you find a situation where you must iterate, it is usually better to do it on the database server. This is to avoid the overhead of repeated calls to the database that will occur if you are doing it in the application layer.
Update: Regarding whether you should iterate in the front end or not, it all depends on what you want to do within the loop. Display the data? Then sure, you probably want to itereate on the front end. If, however, you are iterating through the data in order to look something else up in the database, do a calculation, etc., you may be better off doing this on the db server. We'll need more specifics to make detailed recommendations, right now we can only speak in generalities with the info you have provided.
The best thing is a set-based operation (like JOIN or APPLY) expressed in SQL.
Sometimes, however, this is not possible (because of the complexity of the algorithm or lack of the SQL dialect support on the DB side).
In this case, it is better to process results on the DB side with a DB procedural language loop. This will save you the roundtrips between the client and the server.
Only if both SQL and procedural language are not powerful enough to process your data, it is worth to download the data to the client (in the bulk manner of both server and client support this) and do the processing there.
It depends on your business case. If you have a huge business logic, I would advise you to put in application. Performance wise - Try to avoid loops in db by using join clauses. If that does not work out then go for while loop on database end. then for loop in application and then cursors.
Iteration is a bad thing in databases and should be avoided if at all possible. Do not use this as your first choice of how to do things when accessing a datbase. Until you have tried to figure out a set-based way to perform the task, you should not be considering iteration.
Read this for ideas on how to do things better: http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them