Re: Nearest Common Ancestor Report (XDb1's $1000 Challenge)
Date: 10 Jun 2004 13:55:20 -0700
Message-ID: <4b45d3ad.0406101255.1a12f6e2_at_posting.google.com>
> > RAM Intensive - It loads the entire database into main memory. The
> > maximum database size is appoximately 4 GB with appropriate hardware.
> > The maximum size will increase on 64-bit processors (ie Intel's Itanium).
>
> Run that by me again? The whole database in RAM? Erm ... hang on a
> minute, wasn't Keanu complaining that SQL server was caching it's
> queries in RAM, thus "cheating" when Hugo so convincingly outpaced
> keanu's so-called databse "XDB1" using SQL Server? Umm ...
Either I misstated or one has misinterpretted my concern which was not whether RM's normalized data is preloaded in RAM, but that on subsequent runs the report was possibly being generated from intermediate results or statistics based on initial run thus not generating "report from normalized ... data". Based on various measurements, it does not appear that the provided solutions were utilizing intermediate results/statistics.
As far as being RAM Intensive, below are the minimum memory requirements to store a 400 Goat Hierarchy (which produces 276,620 rows in report).
Solution Memory Notes
------------- --------- ----------------------------- XDb1 v4.5.10 40 KB Compacted, but functional db.RM#5 SqlSrvr7 8,180 KB Truncated/shrunk db.
As far as being RAM Intensive, below are memory useage according to Window's Task Manager during report generation:
Solution RAM Notes
------------- --------- -----------------------------XDb1 v4.5.10 3,488 KB Steady (using compacted db) RM#5 SqlSrvr7 25,480 KB Initial amount and rising
As far as being RAM Intensive, the db sizes after running report with XDb1 remains the same as before (ie 40 KB) because the 1.3MB report is written to a separate text file on HD. With RM Sol#5, for the first run, I started with 10 MB db. At the end of the report generation, the db had grown to 69 MB. For the second run, I started with a 100 MB db. At the end of the run, it had grown to 150 MB. Shrinking the db brought it down to 40 MB. Truncating logs brought it down to 20 MB. Dropping the report table (T_NCancestors) and intermediate report table (T_Ancestor) brought it down to 8.18 MB.
As far as being outpaced, RM Sol#1 and #2 fail to meet the challenge because, they contain redundant schema (schema is data) to store the class hierarchy which cannot be accessed by the provided report procedure, and stores redundant data (ie "brown"). Deficiencies in the genericness of the solutions can be shown by the inability to handle things without an attribute (ie name) and things with multiple attributes each of different type, etc.
Below are measurements made thus far using RM solutions that aren't as generic or normalized as XDb1's.
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 XDb1 4.4.7 16 1.3 Ghz PC Debug ver, norm and gener
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, genericXDb1 4.5.10 195 min 233 Mhz PocketPC 1 run, Normalized, generic
Note: SQL Server scripts are available from website. Received on Thu Jun 10 2004 - 22:55:20 CEST