"Mark A" <noone (AT) nowhere (DOT) com> дÈëÏûÏ¢
news:hk7apt$bm3$1 (AT) news (DOT) eternal-september.org...
it has been years from the first time I saw this article. Absolutely expert
sql and it made me feel very puzzled when it was marked as "basic" level in
its Chinese version.

On Feb 1, 8:41*pm, "Mark A" <no... (AT) nowhere (DOT) com> wrote:
Very nice article!

While I was reading it, a few questions about "best practices" came up
in my mind:

1) The table HISTORY has no primary key or index at all. Is this still
a clean approach, even if the table may hardly ever be read (I
suppose), save be searched?

2) The table STOCK (others do it similarly) uses the ITEM PK and the
WAREHOUSE PK as it's primary key. I've seen approaches where a
surrogate key is used for every table in the database. Is the use of
surrogate keys everywhere just a matter of taste or can I clearly say
it incurs noticeable costs?

3) The implementation makes heavy use of table functions. We try to
avoid functions and stored procedures where we can to avoid having the
code split into application and database parts. Would you recommend
functions and stored procedures? Maybe depending on the SQL skills of
the programmers?

When doing an insert, maintaining the index on a primary key (or other
indexed column) takes extra resources, and if the TPC benchmark does not
require any retrieval of the rows, then that would explain why the
keys/indexes were left off. TPC is not about best practices, it is about
best speed.

Surrogate keys do usually incur some extra costs, unless the natural keys
start getting too large (too many columns). Surrogate keys do have some
advantages, but usually performance is not one of them.

Stored procedures and functions will improve the performance of most
applications. There are the tradeoffs you mentioned of having logic spread
across both the application code and in the SP/functions. You just have to
decide how important it is have the best possible performance.

In addition to what Mark says in the case of this benchmark the table
functions are all inline. That is there is 0-overhead to invoking them
and 0 overhead in context switching between the SQL runtime and the
procedural logic. The code is very, very tight.
You would not write code like this for your average app. Only for that
extra performance sensitive batch or process or OLTP query.

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab