calling Philip Morris, errrrrr, Mr. Adamanche

[BobTheDatabaseBoy]

i grab quotes now and again for use as footers to e-mail
here in CubeLand. found this one in the stash:

the logic exercised by the database to maintain referential
integrity is not free, but if the rdbms does not do
it, it gets done in the application, where it costs the
same if not more - just now the application becomes
"slower" versus the rdbms.
-- Blair Adamanche/2003

while i certainly agree, i've been looking, without success,
for some numbers that confirm the assertion. if Mr. A is
lurking, can you provide a reference?

thx,
btdb

[Serge Rielau]

BobTheDatabaseB oy wrote:

i grab quotes now and again for use as footers to e-mail
here in CubeLand. found this one in the stash:

the logic exercised by the database to maintain referential
integrity is not free, but if the rdbms does not do
it, it gets done in the application, where it costs the
same if not more - just now the application becomes
"slower" versus the rdbms.
-- Blair Adamanche/2003

while i certainly agree, i've been looking, without success,
for some numbers that confirm the assertion. if Mr. A is
lurking, can you provide a reference?

thx,
btdb

That really doesn't require numbers, just horse sense.
Teh app sits on the client. Why make another network trip to confirm
soemthing that thr DBMS can confirm directly?
Also if more than one row is affected the DBMS can parallelize the effort.
In general the app is single threaded...

What soemtimes happes is that the App verifies check and RI "anyway" or
certain properties ar eenforced on a different plane.
In this case DB2 can be taught to trust the app using NOT ENFORCED
ENABLE QUERY OPTIMIZATION properties for check and RI constraints.

Cheers
Serge
Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[BobTheDatabaseBoy]

Serge Rielau wrote:

BobTheDatabaseB oy wrote:

i grab quotes now and again for use as footers to e-mail
here in CubeLand. found this one in the stash:

the logic exercised by the database to maintain referential
integrity is not free, but if the rdbms does not do
it, it gets done in the application, where it costs the
same if not more - just now the application becomes
"slower" versus the rdbms.
-- Blair Adamanche/2003

while i certainly agree, i've been looking, without success,
for some numbers that confirm the assertion. if Mr. A is
lurking, can you provide a reference?

thx,
btdb
That really doesn't require numbers, just horse sense.
Teh app sits on the client. Why make another network trip to confirm
soemthing that thr DBMS can confirm directly?
Also if more than one row is affected the DBMS can parallelize the

effort. In general the app is single threaded...

What soemtimes happes is that the App verifies check and RI "anyway" or certain properties ar eenforced on a different plane.
In this case DB2 can be taught to trust the app using NOT ENFORCED
ENABLE QUERY OPTIMIZATION properties for check and RI constraints.

Cheers
Serge
Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

the arguement i most often get, without numbers on either side, is
that the DB2 engine is an interpreter, while the COBOL code is fully
compiled, and thus "must" be faster. clearly, RI has to be faster
than app code. the question comes down to db code vs. app code for
check constraints, triggers, SP and the like. since, presumably, a
cycle is a cycle is a cycle, the closer to the metal the better. and
since the SPs are compiled through C, they should be just as close
to the metal as compiled COBOL. i have this recollection that some
COBOL compilers are now just C preprocessors. probably not on z/OS,
though.

it sure would make for a fascinating study. CubeLand does have more
of some horse stuff than horse sense.

thx,
btdb

[Serge Rielau]

BobTheDatabaseB oy wrote:

Serge Rielau wrote:

BobTheDatabas eBoy wrote:

i grab quotes now and again for use as footers to e-mail
here in CubeLand. found this one in the stash:

the logic exercised by the database to maintain referential
integrity is not free, but if the rdbms does not do
it, it gets done in the application, where it costs the
same if not more - just now the application becomes
"slower" versus the rdbms.
-- Blair Adamanche/2003

while i certainly agree, i've been looking, without success,
for some numbers that confirm the assertion. if Mr. A is
lurking, can you provide a reference?

thx,
btdb

That really doesn't require numbers, just horse sense.
Teh app sits on the client. Why make another network trip to confirm
soemthing that thr DBMS can confirm directly?
Also if more than one row is affected the DBMS can parallelize the

effort.

In general the app is single threaded...

What soemtimes happes is that the App verifies check and RI "anyway"

or

certain properties ar eenforced on a different plane.
In this case DB2 can be taught to trust the app using NOT ENFORCED
ENABLE QUERY OPTIMIZATION properties for check and RI constraints.

Cheers
Serge
Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

the arguement i most often get, without numbers on either side, is
that the DB2 engine is an interpreter, while the COBOL code is fully
compiled, and thus "must" be faster. clearly, RI has to be faster
than app code. the question comes down to db code vs. app code for
check constraints, triggers, SP and the like. since, presumably, a
cycle is a cycle is a cycle, the closer to the metal the better. and
since the SPs are compiled through C, they should be just as close
to the metal as compiled COBOL. i have this recollection that some
COBOL compilers are now just C preprocessors. probably not on z/OS,
though.

it sure would make for a fascinating study. CubeLand does have more
of some horse stuff than horse sense.

thx,
btdb

It is tru that computing an expression in COBOL or C will be faster that
doing so in SQL PL. Whiel DB2 for zOS SQL PL is cross compiled SQL PL in
DB2 for LUW is indeed interpreted.
The major problem here si not so much teh qualit of the compiler. It is
teh complexity of SQL semantics. E.g. three state logic, error handing,
etc. If all this baggage were added to C or Cobol that too woudl slow down.
Having said that. This may be an argument fro checking CHECK constraints
in the APP, but it sure isn't one for RI constraints because now we
are doing joins beween parents rows and children rows and that the DBMS
can definitely do better close to the iron than the App via cursors.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab