Hi, seems to me that the join transition isn't good enough, i.e. you have to create some additional indexes on some columns. Every join transition should be the start of an index or an primary key. So if objectid is the only primary key column in your tables you should create some indexes on TXobjectid columns. HTH.
Kind regards, Holger SAP Labs Berlin > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Freitag, 4. Oktober 2002 17:48 > To: Becker, Holger > Cc: [EMAIL PROTECTED] > Subject: RE: Slow joins > > > On Fri, 4 Oct 2002, Becker, Holger wrote: > >Perhaps a update of your table statistics could help. > >Try "update stat <tabname>" for every join table. > >For more information about the update stat command see: > >http://www.sapdb.org/htmhelp/3b/86f067458411d3a98200a0c944926 > 1/frameset.htm > >If this doesn't help feel free to contact me directly. > > This helped a lot (double speed), but still not enough. > It took down the 5-table join to 180ms each (from 350), and > the 6-table join > to 350 (from 700). > Besides, we can't afford to run 'update stat' after every > serious modification > to a table. It takes 100-600ms, as I could see right now. > > I also ran EXPLAIN commands to find out what's wrong. Here it > is, for the > 5-join query: > > dba@sapdb:road> explain SELECT T1.objectid, T2.objectid, T3.objectid, > T4.objectid, T5.objectid FROM T3,T1,T2,T4,T5 WHERE > T2.T1objectid = T1.objectid > AND T3.T2objectid = T2.objectid AND T4.T3objectid = T3.objectid AND > T5.T4objectid = T4.objectid AND T3.objectid = 345; > > -------+----------------+-----------------+------------------- > ---------------------+------------+---+---+---+---+ > OWNER | TABLENAME | COLUMN_OR_INDEX | > STRATEGY | PAGECOUNT | O | D | T | M | > -------+----------------+-----------------+------------------- > ---------------------+------------+---+---+---+---+ > DBA | T3 | | EQUAL CONDITION > FOR KEY COLUMN | 75 | | | | | > | | OBJECTID | (USED KEY > COLUMN) | | | | | | > DBA | T4 | | TABLE SCAN > | 75 | | | | | > DBA | T5 | | TABLE SCAN > | 75 | | | | | > DBA | T2 | OBJECTID | JOIN VIA KEY > COLUMN | 75 | | | | | > DBA | T1 | OBJECTID | JOIN VIA KEY > COLUMN | 75 | | | | | > DBA | JDBC_CURSOR_26 | | RESULT IS > COPIED , COSTVALUE IS | 465 | | | | | > -------+----------------+-----------------+------------------- > ---------------------+------------+---+---+---+---+ > > Note the TABLE SCANs. Before I used 'update stat', it looked > a little bit > different: it started off with a 'table scan T6' at the first > place, then all > other tables went with 'join via key column'. That's the one > that took 350ms > for 5 joins. > > The interesting part is that when I modify the 'T3.objectid = > 345' to another > table's objectid field (it's only there because otherwise it > would be normal > to use table scan, since all records should be returned anyway) to > 'T5.objectid', which obviously modifies the query plan, it > goes like this: > > -------+----------------+-----------------+------------------- > ---------------------+------------+---+---+---+---+ > OWNER | TABLENAME | COLUMN_OR_INDEX | > STRATEGY | PAGECOUNT | O | D | T | M | > -------+----------------+-----------------+------------------- > ---------------------+------------+---+---+---+---+ > DBA | T5 | | EQUAL CONDITION > FOR KEY COLUMN | 75 | | | | | > | | OBJECTID | (USED KEY > COLUMN) | | | | | | > DBA | T4 | OBJECTID | JOIN VIA KEY > COLUMN | 75 | | | | | > DBA | T3 | OBJECTID | JOIN VIA KEY > COLUMN | 75 | | | | | > DBA | T2 | OBJECTID | JOIN VIA KEY > COLUMN | 75 | | | | | > DBA | T1 | OBJECTID | JOIN VIA KEY > COLUMN | 75 | | | | | > DBA | JDBC_CURSOR_28 | | RESULT IS > COPIED , COSTVALUE IS | 2 | | | | | > -------+----------------+-----------------+------------------- > ---------------------+------------+---+---+---+---+ > > No table scans at all. But if I use 'T1.objectid' instead of > 'T3.objectid', > all are table scans: > > -------+---------------+-----------------+-------------------- > --------------------+------------+---+---+---+---+ > OWNER | TABLENAME | COLUMN_OR_INDEX | > STRATEGY | PAGECOUNT | O | D | T | M | > -------+---------------+-----------------+-------------------- > --------------------+------------+---+---+---+---+ > DBA | T1 | | EQUAL CONDITION > FOR KEY COLUMN | 75 | | | | | > | | OBJECTID | (USED KEY > COLUMN) | | | | | | > DBA | T2 | | TABLE SCAN > | 75 | | | | | > DBA | T3 | | TABLE SCAN > | 75 | | | | | > DBA | T4 | | TABLE SCAN > | 75 | | | | | > DBA | T5 | | TABLE SCAN > | 75 | | | | | > DBA | JDBC_CURSOR_2 | | RESULT IS > COPIED , COSTVALUE IS | 927 | | | | | > -------+---------------+-----------------+-------------------- > --------------------+------------+---+---+---+---+ > > Interesting. > > Even more interesting, before I used 'update stat' on all > joined tables, it > went like this: all join was via key column when used > 'T5.objectid' at the end > of the query, while there was a table scan at the first place > at any other. > > Changing the order of the conditions, tables at the from > clause, or anything > else does not affect the execution plan, so I didn't do tests > for these. > > Again, the test database was 5 tables, each having a field > 'objectid' as > primary key, a TXobjectid as a foreign key (wasn't declared > as a foreign key > though, just used as one in the query) and a 'data' field, > just for fun. All > are FIXED(38). Each table holds 10000 records, and all > objectid and TXobjectid > fields go from 1 to 10000. > > I would really appreciate any ideas. This database was a test > only, but it's > very much like the real one, including the query. Except for > that the real one > uses much more joins (up to the limit, 64). > > Agoston Horvath > _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
