[thelist] DB increments without an auto increment

rudy r937 at interlog.com
Tue Apr 9 09:47:01 CDT 2002


> As this is a multi-user system I'm not entirely sure
> whether I can prevent anyone else from updating
> something else in the system during this time.   Hmmmm.

hmmmm indeed -- may i stress again, then, that you should not be using

    select max(ID)+1

without a transaction block

> Oh well, back to the drawing board!

you mentioned it's a legacy database -- what database system is it running
on?  do you have the ability to define a transaction block?  if so, maybe
there's still something i can do with two or three statements that handle
the rows of table selected from incrementally...

setting a transaction block on two or three statements would certainly be
better for performance than looping over an insert statement, which just
makes me shudder [*]  -- and selects in a loop make me ill

how many rows are you looking to insert at a time?  a dozen?  a hundred?


[*] yes, inserts in a loop are very, very bad, but mostly unavoidable

those of you who have web forms that collect multiple rows, e.g. line items
on an order, have a look at this, it's really neat

as far as i know only mysql has the following syntax

<tip type="sql">
in standard sql, you insert a row of values into a table using
     INSERT
          INTO tbl_name [(col_name,...)]
             VALUES (expression,...)
in mysql, you can insert multiple rows using
    INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
          [INTO] tbl_name [(col_name,...)]
         VALUES (expression,...)
                          , (...),...
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#IN
SERT
so, for example,
    insert into OrderItems (OrderNo, ItemNo, OrderQty)
      values
         (  212937, 'helmet', 1 )
       , (  212937, 'safe-T-vest', 1 )
       , (  212937, 'gloves', 1 )
       , (  212937, 'pant clips', 2 )
       , (  212937, 'water bottle', 2 )
       , (  212937, 'glasses', 1 )
       , (  212937, 'pannier', 2 )
       , (  212937, '700x36 tube', 3 )
sending one sql sentence from your web script to the database engine is
always faster than sending in a loop
</tip>

if your mysql app is written with a loop, and it works, don't go back and
fix it unless you need the performance improvement

rudy
http://rudy.ca/






More information about the thelist mailing list