[thelist] Tip - ColdFusion and Datasources

Jeff Howden jeff at jeffhowden.com
Sat Aug 16 15:47:05 CDT 2003


rudy,

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> From: rudy
>
> slag mysql all you want
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

ok

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> but listen up -- they do some things really well, e.g.
> they support some stuff that other databases don't
>
> for example, if you have a form with multiple entries on
> it, like an order form with ten order items, and you
> want to insert the multiple entries into a table, you
> can do this --
>
>   insert into orderitem
>     (orderno,orderitemno,qty,price)
>   values (27,342,5,5.98)
>      , (27,613,1,7.98)
>      , (27,42,2,12.00)
>      , (27,887,1,14.99)
>      , (27,211,1,5.95)
>      , (27,61,1,12.50)
>      , (27,57,1,7.98)
>      , (27,712,1,5.00)
>      , (27,74,1,2.00)
>      , (27,5012,1,14.99)
>
> in other words, you call the database once, instead of
> ten times in a loop
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

the expense of an insert (or multiple inserts) is not in the connection to
the database, but in the insert operation itself (and logging said insert).

therefore, i suspect that there isn't any real gain with the syntax above
compared to sending the following in a single connection.

INSERT INTO orderitem (orderno, orderitemno, qty, price)
               VALUES (27, 342, 5, 5.98)
INSERT INTO orderitem (orderno, orderitemno, qty, price)
               VALUES (27, 613, 1, 7.98)
INSERT INTO orderitem (orderno, orderitemno, qty, price)
               VALUES (27, 42, 2, 12.00)
INSERT INTO orderitem (orderno, orderitemno, qty, price)
               VALUES (27, 887, 1, 14.99)
INSERT INTO orderitem (orderno, orderitemno, qty, price)
               VALUES (27, 211, 1, 5.95)
INSERT INTO orderitem (orderno, orderitemno, qty, price)
               VALUES (27, 61, 1, 12.50)
INSERT INTO orderitem (orderno, orderitemno, qty, price)
               VALUES (27, 57, 1, 7.98)
INSERT INTO orderitem (orderno, orderitemno, qty, price)
               VALUES (27, 712, 1, 5.00)
INSERT INTO orderitem (orderno, orderitemno, qty, price)
               VALUES (27, 74, 1, 2.00)
INSERT INTO orderitem (orderno, orderitemno, qty, price)
               VALUES (27, 5012, 1, 14.99)

i will agree, however, that the sql3 syntax for doing multiple inserts is
*very* cool, short, readable, etc.  i just don't see it being an issue of
improved performance.

.jeff

——————————————————————————————————————————————————————
Jeff Howden - Web Application Specialist
Résumé - http://jeffhowden.com/about/resume/
Code Library - http://evolt.jeffhowden.com/jeff/code/




More information about the thelist mailing list