Re: Was: what does "serialization" mean?

From: Nick Landsberg (SPAMhukolauTRAP_at_SPAMworldnetTRAP.att.net)
Date: 07/10/04 

Date: Sat, 10 Jul 2004 04:36:29 GMT

Edward G. Nilges wrote:

> Nick Landsberg <SPAMhukolauTRAP@SPAMworldnetTRAP.att.net> wrote in message news:<BmBHc.222121$Gx4.16309@bgtnsc04-news.ops.worldnet.att.net>...
>

[ MUCH MORE SNIPPAGE SO AS NOT TO REPLOW
THE SAME FIELD ]

>
> I apologize for what was the appearance of brusqueness. The point I
> was trying to make was that hardware speeds are only part of the
> story.
>

OK ... I won't snarl at you if you don't snarl at me :)

[ MORE SNIPPAGE ]

>>
>>In addition, the sales and inventory database has
>>to support such queries as:
>>
>>Report the sales trends week by week
>>for the last 6 months on the top and bottom
>>200 selling products ordered by product category,
>>total sales, geographic region and store.
>>(No, I'm not going to write the SQL for this.)
>

I should have said "ad-hoc" queries in the above
since the customers are usually loathe to define
the specific queries up front.

>
>
> Let me try extempore without having the time to check syntax:
> corrections welcome:
>
> SELECT FIRST 6 MONTHLY_TOTAL_SALES FROM WHATEVER ORDER BY
> (PRODUCT_CATEGORY, TOTAL_SALES, REGION, STORE)
>
> SELECT LAST 6 MONTHLY_TOTAL_SALES FROM WHATEVER ORDER BY
> (PRODUCT_CATEGORY, TOTAL_SALES, REGION, STORE)
>
> WHATEVER would probably be a separate query to summarize total sales.

I will revisit WHATEVER a little later. Bear with me.

[ SNIP ]

>
> But a second irony is that around SQL a number of urban legends have
> grown up in communities of scribes who have in fact been assigned the
> task of using SQL.
>
> One is the privileging of "table" over "relation" when in fact the
> concept of "relation" is more general and more powerful than "table",
> subject to the caveat that your queries should not result in the
> continual creation and re-creation, of relations.

I agree with the comment about "relations" vs. "tables"
however, I am ambivalent about the last part of that
paragraph, the caveat. There are two conflicting
forces at work in any database. The need for
data integrity and the need for speed. (To oversimplify
it drastically.) Those theorists who are in the camp of
data integrity would claim that no permanent relations
should exist which can be derived from other relations.
There are those who will go so far as to claim that
no summary tables should exist permanently because they
can always be derived from the base tables and thus are
replicating data. Those who are solely concerned with speed of
access create other anomalies within the database
where the consistency and integrity of the data
is compromised. For any given database, there is
a "happy medium" which is struck by a series of
design tradeoffs.

So, getting back to your WHATEVER above. The first
camp would fight you tooth and nail, while the
second camp would accept it while possibly ignoring
its side-effects. If this were a "canned query"
I would personally go with the summary table
approach, but in that world, not all queries
are canned and one cannot tell ahead of time
what query some corporate droid might dream up.

>
>
>>And yes, that's a real query, and I was told
>>third hand, that the results were used, in part,
>>to establish price and inventory levels of
>>cold medications during a flu epidemic somewhere
>>in the midwest.
>>
>>In order to satisfy such a query in most
>>database systems (including the ones you mention),
>>one must visit a vast majority of the records in
>>the sales and inventory database. Thus, the response
>>time to such a query is dominated by disk access
>>times and data transfer rates between the disk
>>and the CPU's.
>>
>
> ...Only if the system is poorly designed. The above query can be
> optimized into straight line code as long as a stored procedure exists
> for "whatever".

See my comments about "whatever" above.

>
> The whole purpose of effective data base design is to AVOID having to
> visit a vast majority of records in cases like the above.
>
> I won't say "what part of 'indexing' don't you understand" because you
> understand indexing.
>
> Let's assume WalMart doesn't have a single record for total sales but
> instead a single table of all sales, completely normalized, at one
> item for each distinct sale of each distinct item (the actual sale, of
> multiple products as the shopper makes, we hope, a series of impulsive
> purchases of dreck she does not need, is broken up into distinct
> items).
>
> If the designers don't put aggregate information including total sales
> somewhere as the sales come in, they should be WalMart greeters and
> not designers, but even if they don't, the total sales has only to be
> developed once.

This is the tradeoff I would make with most DBMS systems today
if the queries were known up front.
Note that this would require the application (or a trigger)
to update the aggregate as part of the same atomic operation
as entering the sale. Yet another tradeoff betwen speed
up update vs. speed of reports.

>
> The single query in most cases therefore won't visit all records in
> any halfway decent system.

The Teradata "dinosaur" actually was able to do a decent
job on ad-hoc queries because of specialized hardware and
software without having to rely on aggregate information
being updated as the individual records came in. And, for
ad-hoc queries it did visit (almost) all the data.

[SNIP, it's getting late]

NPL. 

-- 
"It is impossible to make anything foolproof
because fools are so ingenious"
  - A. Bloch

Relevant Pages

  • Re: access 2003
    ... I removed the parameters from the form query source. ... boxes from the form header, events, code, etc and ran the form query source ... forms queries and the SQL because syntax of the SQL will change randomly. ... the Access 97 database, I wouldn't have thought any expressions would be ...
    (microsoft.public.access.conversion)
  • Re: access 2003
    ... I removed the parameters from the form query source. ... synchronize combo boxes to the detail section or the parameter form query ... forms queries and the SQL because syntax of the SQL will change randomly. ... the Access 97 database, I wouldn't have thought any expressions would ...
    (microsoft.public.access.conversion)
  • Re: Query In BE database
    ... that would give the users "live" access to the underlying queries. ... When selected from the drop-down list box, I what the the query that is ... What Is A Remote Query? ... point your local query to a query that was in another Access database. ...
    (microsoft.public.access.queries)
  • Re: merge multiple databases
    ... You need to create a new Access datafile (mdb) to store all in. ... Open the newly created Access database. ... Click on the Queries icon. ... "Create new query", then select lnkDrive as the table to ...
    (microsoft.public.vb.database.dao)
  • Re: merge multiple databases
    ... You need to create a new Access datafile (mdb) to store all in. ... Open the newly created Access database. ... Click on the Queries icon. ... "Create new query", then select lnkDrive as the table to ...
    (microsoft.public.vb.general.discussion)