[DUG] Why InterBase

[Karl at Work]

> "How well does it scale" is a difficult question. I have
> heard that IB/FB doesnt
> do well with multi-table joins once no. of tables gets above
> 3. I havent tested.

I have a dynamic SQL generator.  With it I can generate some really
horrendous queries if I feel the need.  So I built one to test this idea.
The resultant query is around 20K of raw SQL with numerous subclauses and a
total of 50 or so table references in it.  You can count them: here's the
adapted plan (there were 25 individual plan lines):

Adapted Plan
PLAN JOIN (IIV INDEX (INTEG_1240),ICTV INDEX (INTEG_1040)) PLAN JOIN (ICV
INDEX (INTEG_1231),IEIV INDEX (XIE1INC_ENTITY_ICT)) PLAN (CDETLOAFF INDEX
(INTEG_1205,INTEG_1204)) PLAN (CDETLAT INDEX (INTEG_1205,INTEG_1204)) PLAN
(CDETLOASS INDEX (INTEG_1205,INTEG_1204)) PLAN JOIN (OLA NATURAL,PLA INDEX
(INTEG_1082),ICLA INDEX (INTEG_1231,XIE1INC_CONTACT),IE_CLA INDEX
(XIE1INC_ENTITY_ICT),IILA INDEX (INTEG_1035),ICTLA INDEX (INTEG_1040)) PLAN
(CDETLOD INDEX (INTEG_1205,INTEG_1204)) PLAN JOIN (CDLD INDEX
(INTEG_1204),OLD INDEX (INTEG_1265),IILD INDEX (INTEG_1240)) PLAN (CDETVDC
INDEX (INTEG_1205,INTEG_1204)) PLAN (CDETOW INDEX (INTEG_1205,INTEG_1204))
PLAN JOIN (CCRSH INDEX (INTEG_1212),OSH INDEX (INTEG_1030),OPSH INDEX
(INTEG_1082),IISH INDEX (INTEG_1240),ICTSH INDEX (INTEG_1040),IEISH INDEX
(INTEG_1237,XIE1INC_ENTITY_ICT),VSH INDEX (INTEG_1030),VPSH INDEX
(INTEG_1082)) PLAN JOIN (CDDIRT INDEX (INTEG_1218),CAIRT INDEX (INTEG_1014))
PLAN JOIN (CDDIBT INDEX (INTEG_1218),CAIBT INDEX (INTEG_1014)) PLAN JOIN
(CDDIAC INDEX (INTEG_1218),CAIAC INDEX (INTEG_1014)) PLAN JOIN (CDDIAS INDEX
(INTEG_1218),CAIAS INDEX (INTEG_1014)) PLAN JOIN (III INDEX
(INTEG_1240),ICTI INDEX (INTEG_1040)) PLAN JOIN (CDDRT INDEX
(INTEG_1218),CART INDEX (INTEG_1014)) PLAN JOIN (CDDBT INDEX
(INTEG_1218),CABT INDEX (INTEG_1014)) PLAN JOIN (CDDAC INDEX
(INTEG_1218),CAAC INDEX (INTEG_1014)) PLAN JOIN (CDDAS INDEX
(INTEG_1218),CAAS INDEX (INTEG_1014)) PLAN JOIN (IIH1 INDEX
(INTEG_1240),ICTH1 INDEX (INTEG_1040)) PLAN JOIN (IIH2 INDEX
(INTEG_1240),ICTH2 INDEX (INTEG_1040)) PLAN JOIN (II INDEX (INTEG_1240),ICT
INDEX (INTEG_1040)) PLAN JOIN (IIH1ACR INDEX (INTEG_1240),ICTH1ACR INDEX
(INTEG_1040)) PLAN JOIN (IIH2ACR INDEX (INTEG_1240),ICTH2ACR INDEX
(INTEG_1040)) PLAN (ICIC INDEX (INTEG_1233))

All the tables are given generated aliases by the query builder, that's why
they don't have readable names in the plan.  Anyway, I ran the resultant
query twice.  Here is the performance information the first time (first
query run since restarting the FB server:)

------ Performance info ------
Prepare time = 63ms
Execute time = 19s 578ms
Avg fetch time = 9,789.00 ms
Current memory = 1,572,000
Max memory = 1,788,312
Memory buffers = 2,048
Reads from disk to cache = 17,511
Writes from cache to disk = 3
Fetches from cache = 475,398

Running it a second time (same plan):

------ Performance info ------
Prepare time = 47ms
Execute time = 1s 110ms
Avg fetch time = 555.00 ms
Current memory = 1,571,664
Max memory = 1,792,732
Memory buffers = 2,048
Reads from disk to cache = 17,311
Writes from cache to disk = 0
Fetches from cache = 475,346

Ok, so the first time this abomination was run it was fairly slow - about 20
secs to run.  The second time (and in subsequent runs) it took around 1
second.  I tentatively think that this is reasonable, given the size and
complexity of this query.  Thoughts?

Cheers,
Carl