Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82

    Unanswered: Foreach - best and most effective rewrite in SQL?

    I am coding application according design and many time there is need to rewrite commands like these:

    foreach ( select @x = col ... from ... )
    update, insert, exec...etc

    endforeach

    I d have some philosophical questions...

    1) what do you use to rewrite this ? E.g. you automative, without thinking, write cursor according your template - it is fastest, easy and effective way how to re-write this

    2) you use other way cos you concern about amount of written code and his simply fast re-reading in the future by other programmers ? E.g. cursor are heavy, lot of code etc. so we use simply temp table with indexed column and counter...

    3) what concerns of effectivity and resource cost? You mostly use cursors/ temporary tables/db work tables cos ...... .

    Conlusion first we use .... and if we see during testing problems we rewrite and use this ...

    Thanks for your hints and points of view...

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    The basic thumb rule for databases is the more you batch, the better your performance is. Which translates to

    a) Temp tables are more batch based.
    b) Cursors are more row based, so less throughput. Wherever you can avoid cursors, it would be prefereble.

  3. #3
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    is temp tables based solution better solution even if I am going through temp table row-by-row with counter variable holding index column...?

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Cursors are always going to be slow and difficult to read but if the application works and it's fast enough then why bother changing it? At best it will be wasted effort and at worst you'll just be introducing new errors.

    If the application too slow then only change those cursors that affect the performance most.

    If money is no object then sure - get rid of all the cursors

  5. #5
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    Thanks for tips and hints

    Problem is that I am almost ever in transaction (where DDL is prohibited, so create table is disabled in tran). Moreover working on just part of large application in deep level on one called stored procedure.

    So, if I implement new code with foreach... it seems for me most simple/fast to change just one procedure and implement cursor... otherwise I d have to look for all callings of my procedure and spoting where transaction starts in these procedures and implement create temp table before begin tran in every calling...

    And instead one stored proc simple test all these changed procedures I d have to test subseqently...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •