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/86f067458411d3a98200a0c9449261/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
