[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