Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I want to know what's the suitable method to implement the following case (best practice).

If i make a set of processes like this:

  1. select data from set of DB tables.

  2. loop on the selected result.

  3. Make some checks on each iteration.

  4. Insert the result in another table.


Implementing the previous steps in a stored procedure or in a transaction through my code (asp.net)? Concerning the performance, security and reliability issues.

share|improve this question
1  
Try to avoid loop when working with DB. –  Kirk Broadhurst Oct 22 '13 at 21:21
    
@KirkBroadhurst : Could u clarify what's the alternative to looping in DB plz,an example or some links ? –  just_name Oct 23 '13 at 7:15
add comment

2 Answers 2

up vote 7 down vote accepted

Both will Work, which to choose depends on other factors not specified in your question.

Those factors include (but are not limited to)

  1. Performance requirements, and other related factors (How much data needs to be shuffled back and forth between the server and DB)
  2. Requirements to support multiple Backbends and or change backends
  3. Talent available to maintain the code base
share|improve this answer
    
Don't you mean that the choice DOES depend on other factors not specified in the question? –  Matthew Flynn Oct 22 '13 at 15:43
    
Yes, Fixed..... –  Morons Oct 22 '13 at 16:19
add comment

Any time you have to move data from the server to a client and back, you are going to hit a performance wall. It may be acceptable, it may not be. You will have to do the testing to figure that out.

I personally stick with stored procedures for my database access, because I like keeping the logic surrounding data with the data, rather than in the application layer, which consumes data.

The most concerning thing to me about your question is that you want to loop over rows--typically, that means you aren't using SQL correctly. SQL works best on sets, and database engines are optimized for set-based work. Unless you absolutely cannot work on the data in a set-based approach, I would advise you to avoid loops.

share|improve this answer
    
Could u clarify what's the alternative to looping in DB plz,an example or some links ? –  just_name Oct 23 '13 at 7:16
1  
Well, it all depends on what exactly you mean when you say "make some checks" at step 3. I am assuming that you are using SQL Server, which also affects the options you have. String concatenation can be relieved by FOR XMLPATH (sqlblog.com/blogs/aaron_bertrand/archive/2012/01/26/…), tasks which can be reduced to a recursive loop are easily handled by a recursive common table expression, etc. It depends very heavily on the case you are using it in. –  mgw854 Oct 23 '13 at 15:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.