3

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.

svick
  • 9,999
  • 1
  • 37
  • 51
Anyname Donotcare
  • 1,364
  • 2
  • 16
  • 29
  • 2
    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 ? – Anyname Donotcare Oct 23 '13 at 07:15
  • possible duplicate of [Stored Procedures a bad practice at one of worlds largest IT software consulting firms?](http://programmers.stackexchange.com/questions/65742/stored-procedures-a-bad-practice-at-one-of-worlds-largest-it-software-consulting) – gnat Oct 15 '14 at 13:11

2 Answers2

7

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
Matthew Flynn
  • 13,345
  • 2
  • 38
  • 57
Morons
  • 14,674
  • 4
  • 37
  • 73
6

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.

mgw854
  • 1,818
  • 10
  • 11
  • Could u clarify what's the alternative to `looping` in DB plz,an example or some links ? – Anyname Donotcare Oct 23 '13 at 07: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 (http://sqlblog.com/blogs/aaron_bertrand/archive/2012/01/26/the-fallacy-that-a-while-loop-isn-t-a-cursor.aspx), 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