Re: Nearest Common Ancestor Report (XDb1's $1000 Challenge)

On 2 Jun 2004 17:09:24 -0700, Neo wrote:

>Small Report Generation Summary (provided by Hugo)
>---------------------------------------------------------------------
>Solution Time(ms) Platform Notes
>---------- -------- ---------- ----------------------------
>RM's #1 14.3 1.3 Ghz PC Apple-to-orange
>RM's #2 11.0 1.3 Ghz PC A step closer to apples
>XDb1 4.4.7 16 1.3 Ghz PC Unoptimized, debug version

Hi Neo,

You keep talking about apples and oranges. Yet, in this whole BIG (already 186 messages in .theory in my newsreader, 157 on google - no idea what causes the difference) discussion, I have found no single mention of XDb1's capacities regarding logging and recovery, locking for multiuser use, permission checking, etc. There's no way to remove the overhead of these features in SQL Server, so unless you have similar features in XDb1, we won't even get close to an apple-to-cucumber comparison.

>Small Report Generation Summary (provided by Neo)
>---------------------------------------------------------------------
>Solution Time(ms) Platform Notes
>---------- -------- ----------------- -----------------------------
>RM's #1 65.0 500 Mhz Server Apple-to-orange
>RM's #2 68.9 500 Mhz Server A step closer to apples
>RM's #4 152.13 500 Mhz Server Another step closer to apples
>XDb1 4.4.7 16 500 Mhz Server Unoptimized, debug version
>XDb1 4.5.7 1.632 500 Mhz Server Optimized version
>XDb1 4.5.9 6.561 233 MHz Pocket PC 32 MB

I never provided a third or even fourth model. Are you refering to the disaster you wrote yourself?

I tried it on my computer. Results were about the same as for my RM#2 entry (alternating between 107 and 115 ms for 10 executions). I've already seen several possible improvements, though I doubt that the difference will be notable on this small set of data.

If you want to, I will take some time to really get into it, see what you have changed and why and give you a much better version. I'm prepared to do that on a "no cure no pay" basis. Of course, you will have to pay the $1000 you already owe me first - I'm not in the habit of accepting new assignments with non-paying customers and I'm not prepared to make an exception for you.

>Large Report (28,940 rows) Generation Summary (provided by Neo)
>---------------------------------------------------------------------
>Solution Time(sec) Platform Notes
>---------- -------- ----------------- ----------------------------
>RM's #1 15.2 500 Mhz Server Apple-to-orange
>XDb1 4.5.7 2.9 500 Mhz Server Optimized version

I have no idea what data you used, so I can't try it on my database. Your database is still an outdated version (SQL Server 7.0 as opposeed to SQL Server 2000) and is probably still using a case insensitive character set that incurs a lot of extra overhead.

>Since RM Solution #2, didn't generate a class hierarchy report and
>didn't allow things with no or multiple names, etc... I have updated
>it to RM Solution#4 (see script below) which is another step closer to
>making an apple-to-apple comparision.

Apple-to-cucumber, methinks.

> The class hierarchy which had
>been stored in redundant tables T_Classes, T_ClassOfThings and
>T_ClassHierarchy have been dropped and the data moved to
>T_hierarchies, thus allowing a report on the class hierarchy also. In
>addition, the name attribute of things was moved to T_attributes_char,
>thus allowing a thing with no name or multiple names. However,
>T_attributes_char will have redundant data if a thing's property has
>multiple values. The next RM Sol should normalize by adding
>T_AttribChar_Value. As one can see from the measurements above, RM's
>solution gets slower as it is begins to approach the level of
>genericness and normalization in XDb1 (which is normalized down to
>atomic symbols).

The only thing I see from the measurements above and the script below (well, not exactly - I snipped it :-) is that you are lousy at SQL, even worse at configuring and optimizing your SQL Server settings and that you are still clueless when it comes to normalization in the context of the relational model.

Pay me your current debt, sign a contract for a no-cure-no-pay assignment and give me proper and concise specifications (which I can help with, BTW) for what you want and a will model a database and write the SQL to get you what you want with the best performance I can manage to squeeze out of MS SQL Server.

>If someone can also run the below script on SQL Server for PocketPC, I
>would be interested in comparing its performance.

Why would you be interested in the performance of a script that even joins some extra unneeded tables? (to name just the most striking ugly-thing).

(snip good example of why SQL courses are still necessary)

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)