Bill & Ted's Excellent Adventure, Part 3

DB2 9.7 is up and running. Woo hoo. The next steps are to build three copies of Birchall's test database (I'll be using the standard DB2 Sample scripts, in that I misremembered what Birchall did; my recollection was that he had expanded the size of some tables, but in fact he did the contrary): one on SSD using his schema (ssdtest1), one on rotating rust using his schema (ssdtest2), and one on rotating rust using a flat file version of the most complex joined tables (ssdtest3, not yet, though).

In order to test the rust/SSD/join aspect (which is the goal, after all of BCNF datastores), the bufferpools will be kept to very small (5 8K pages), thus forcing I/O through storage as much as possible.

I did install DB2 into the SSD, so the engine is running lickety split.

So, for a first approximation, here's the result of a straight select on EMPMDC (10,000 rows):

on ssdtest1: .014 seconds
on ssdtest2: .083 seconds

I'm using RazorSQL, since my beloved AQT will run in wine, but I've not yet been able to get the CLI driver to work again since the upgrade. Razor is almost AQT, just jdbc based, and without many of the DBA focused features that the Extended version of AQT has. I don't get any coin from New Zealand if folks take my advice and adopt AQT, and being that it's windoze (and .NET at that) hosted, the *nix fans in the audience likely couldn't care less.

So, let's run one of Graeme's joins. The code samples are here. This is an html file, so Save As, and just strip the beginning and ending html wrapper stuff. I'll be including the text here, in any case, just to add clarity (well, and make these missives longer and more impressive). Note: you may need to do a search and replace on html entities; gt, lt, and such.

Let's run a complex join (Figure 675):

SELECT eee.empno
,aaa.projno
,aaa.actno
,ppp.photo_format AS format
FROM employee eee
LEFT OUTER JOIN
emp_act aaa
ON eee.empno = aaa.empno
AND aaa.emptime = 1
AND aaa.projno LIKE 'M%1%'
LEFT OUTER JOIN
emp_photo ppp
ON eee.empno = ppp.empno
AND ppp.photo_format LIKE 'b%'
WHERE eee.lastname LIKE '%A%'
AND eee.empno < '000170'
AND eee.empno <> '000030'
ORDER BY eee.empno;

ssdtest1: .016
ssdtest2: .141

I'm getting impressed.


If we remove the join constraints,

SELECT eee.empno
,aaa.projno
,aaa.actno
,ppp.photo_format AS format
FROM employee eee
LEFT OUTER JOIN
emp_act aaa
ON eee.empno = aaa.empno
AND aaa.emptime = 1
AND aaa.projno LIKE 'M%1%'
LEFT OUTER JOIN
emp_photo ppp
ON eee.empno = ppp.empno
--AND ppp.photo_format LIKE 'b%'
--WHERE eee.lastname LIKE '%A%'
-- AND eee.empno < '000170'
-- AND eee.empno <> '000030'
ORDER BY eee.empno


we get:

ssdtest1: .005
ssdtest2: .072

So, for these tests, SSD joins are 10 times as fast, modulo buffering. It will take some time to gin up the flat file schema; that's next on the agenda. So far, no surprises.