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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 [email protected] http://ns3.123.co.nz/mailman/listinfo/delphi _______________________________________________ Delphi mailing list [email protected] http://ns3.123.co.nz/mailman/listinfo/delphi
