Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Demo: Modelling Cost of Travel Paths Between Towns

Re: Demo: Modelling Cost of Travel Paths Between Towns

From: Neo <neo55592_at_hotmail.com>
Date: 4 Dec 2004 10:26:24 -0800
Message-ID: <4b45d3ad.0412041026.1e7a4869@posting.google.com>


May 7, 04 - Neo's definition of normalization: See www.dbdebunk.com/page/page/857323.htm , search for "Neo"

May 16, 04 - Neo: Original challenge, copied from 1st post in thread titled "Common Ancestor Report..."

"$1000 to the first person who replicates the equivalent of www.xdb1.com/Example/Ex076.asp using the relational model. To claim the prize, one needs to produce the equivalent Nearest Common Ancestor Report from normalized and NULL-less data and the solution must be as generic, meaning allow the user to create any hierarchy, consisting of different types of things (each type to allow different attributes) and each thing in the hierarchy to have any number of parents. Report generation must not be more than 2X slower than XDb1 on equivalent hardware."

Hugo's solution: http://www.google.com/groups?q=g:thl2529170390d&dq=&hl=en&lr=&c2coff=1&selm=mts4b09m5olm07v0b9ppienrdk8au4md6c%404ax.com&rnum=101

Neo: Your solution doesn't qualify because its too slow, doesn't generate from normalized NULL-less data, isn't as generic, and it doesn't allow the user to create any hierarchy. Among other deficiencies your last RM Solution #2 has redundancy in its schema (schema is data also). It stores the class hierarchy in one table and the remaining hierarchies in a separate table. Because your code only runs against the second table, it cannot generate a report for the class hierarchy.

Also your solutions cannot represent the following cases without NULLs or redundant data (XDb1's solution is normalized down to symbols):

Case1: God is the parent of an unnamed person. God is also the parent of second person with three names (string 'john', integer 100, decimal 3.14).

Case2: john isa person. john's color is brown. mary isa person. mary's color is brown. brown is a person.  

As shown by the measurements made below, even when executed on a 5.6 times slower 233 Mhz Pocket PC, XDb1 generated the small common ancestor report nearly twice as fast as your non-normalized, non-generic SQL Server 2000 Solution running on a 1,300 MHz desktop.

Small Report Generation Summary (provided by Hugo)



Solution Time(ms) Platform Notes
-------------- --------  ----------------- --------------------------
RM#1 SqlSrvr2K  14.3     1.3 Ghz PC        Unnormalized, non-generic
RM#2 SqlSrvr2K  11.0     1.3 Ghz PC        Unnormalized, non-generic


Small Report Generation Summary (provided by Neo)



Solution Time(ms) Platform Notes
-------------  --------  ----------------- --------------------------
RM#1 SqlSrvr7   65.0     500 Mhz Server    Unnormalized, non-generic
RM#2 SqlSrvr7   68.9     500 Mhz Server    Unnormalized, non-generic
XDb1 4.5.7       1.632   500 Mhz Server    Normalized, generic
XDb1 4.5.9       6.561   233 MHz PocketPC  Normalized, generic


Large Report (28,940 rows) Generation Summary (provided by Neo) 200 Goat Hierarchy (5 generations x 40 goats/generation, each goat having two parents, except 1st gen).



Solution Time(sec) Platform Notes
-------------  --------  ----------------- --------------------------
RM#5 SqlSrvr7   40.5     500 Mhz Server    Unnormalized, non-generic
XDb1 4.5.7       2.9     500 Mhz Server    Normalized, generic
XDb1 4.5.9      16.971   233 Mhz PocketPC  Normalized, generic


Larger Report (276,620 rows) Generation Summary (provided by Neo). 400 Goat Hierarchy (10 gen x 40 goats/gen),



Solution Time(ms) Platform Notes
-------------  --------  ----------------- --------------------------
RM#5 SqlSrvr7  105 min   500 Mhz Srvr, NT  Avg of 2 runs, UnNrm,UnGen
XDb1 4.5.10     44 min   500 Mhz Srvr, NT  Avg of 2 runs, Norm, gener
XDb1 4.5.10     57 min   450 Mhz PC, 98    1 run, Normalized, generic
XDb1 4.5.10 195 min 233 Mhz PocketPC 1 run, Normalized, generic Received on Sat Dec 04 2004 - 12:26:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US