[DUG] Why InterBase

[Kyley Harris]

50 tables, holy crap! :D I'd say the first time was just preparation and
1 sec per reuse is probably pretty good considering the amount of effort
required to join and filter so many tables.

Can't think of the last time I ever joined more than 6 tables, so most
queries are probably on par with each different database, and speed is
more along the lines of efficiency of the DB. MSSQL I have noticed can
be very slow the first time a table is accessed in a while, and then
fast afterwords, like its caching or something.

-----Original Message-----
From: delphi-bounces at ns3.123.co.nz [mailto:delphi-bounces at ns3.123.co.nz]
On Behalf Of Karl @ Work
Sent: Thursday, 1 June 2006 10:53 a.m.
To: 'NZ Borland Developers Group - Delphi List'
Subject: RE: [DUG] Why InterBase

> "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

_______________________________________________
Delphi mailing list
Delphi at ns3.123.co.nz
http://ns3.123.co.nz/mailman/listinfo/delphi