At 02:14 PM 2/9/01 -0800, Lee Rodgers wrote:

>
>Like the difference between the UPDATE and INSERT
>grammars.
>That we have a good simple UPDATE command:
>UPDATE <table> SET <name=value pairs>
>and a prone-to-error INSERT command:
>INSERT (name1 name2 name3) (value1 value2 value3)
>is stupid and pointless. The INSERT syntax should look
>like UPDATE's so you avoid common placement mistakes
>with INSERT like:
>INSERT (name1 name2 name3) (value2 value1 value3)
>

The basic reason is that insert has to accomodate input
from a select statement and update doesn't.

If you don't mind incompatibility with the standard, use
GDML. The GDML syntax for the batch insert case would
be:

for x in table1
store y in table2
y.field1 = x.field2
end_store
end_for

Or, if the fields name matches (which they usually do):

table2 = table1

But believe me, having a better language than the standard
doesn't win you any points. Or a chorus of approval.

>
>Another idea I heard recently from a proj lead who
>wondered why DBMS's don't check the row's values (via
>a checksum or some such) to see if the UPDATE would
>actually modify any data or not. If the UPDATE stream
>was indentical to the current data, or wouldn't change
>the row, to not perform the update so to save I/O.. I
>agreed, esp. from a DBA's perspective, it'd be great
>for UPDATE to have a "CHECK DISTINCT" clause to
>prevent unnecessary logged operations.. in a busy OLTP
>DB more than half the I/O and most of the bottleneck
>is sitting on the hotspot at the end of the tran. log.
>

Hey, I know one that does (Netfrastructure). Seriously,
however, there are sometimes perfectly good reasons to
do a dummy update. And in Firebird, because versions
are delta-ed, the update is about as close to free as
possible (in Firebird).

A place where this is particularly useful is a (non-standard)
REPLACE verb, syntactically parallel to INSERT but does
an UPDATE if a record with the same primary key exists,
a non-op if it exists and nothing has changed, otherwise
an INSERT. Trivial to implement, saves a bundle of
application level code, and fast to boot.

>
>I guess it'd go like this: Before the write, the
>record still needs to put into some working space's
>page in memory, regardless. To me, it'd be simple,
>match checksums & check *before* writing to the log,
>and the page isn't marked as dirty, and checkpoint has
>one less row to write to the table. It'd be better
>suited to systems with lots of RAM so the buffer/cache
>page would be checked before having to move/steal
>cache pages to double-check the record.
>

Uh, you've got some homework to do. Firebird doesn't
work that way.

Jim Starkey