Consultor Eletrônico

Status: Verified

FACT(s) (Environment):

Progress 9.x
OpenEdge 10.x
All Supported Operating Systems

SYMPTOM(s):

Performance issue with left-outer join in SQL-92 query.

An SQL-92 query, with LEFT OUTER JOIN, runs in roughly 30 seconds. Change
the LEFT OUTER JOIN to INNER JOIN and it returns instantly, as it should.

This version of the query takes about 30 seconds:

SELECT PUB.INVCOST.COSTCODE, PUB.INVDEVICE.ITEMNAME
FROMPUB.INVDEVICE
LEFT OUTER JOIN PUB.INVCOST INVCOST ON INVCOST.INVSEQ =
PUB.INVDEVICE.INVSEQ
AND INVCOST.INVDOSESEQ = -1

This version of the query returns the result set almost instantly:

SELECT PUB.INVCOST.COSTCODE, PUB.INVDEVICE.ITEMNAME
FROM PUB.INVDEVICE
INNER JOIN PUB.INVCOST INVCOST ON INVCOST.INVSEQ = PUB.INVDEVICE.INVSEQ
AND INVCOST.INVDOSESEQ = -1

CAUSE:

The reason the LEFT OUTER JOIN is slow in this case is that SQL-92 chooses to join from the large table to the small table. SQL-92 makes this choice because the database does not have any statistics created. Since there are no statistics, sql assumes that all tables are approximately the same size, and chooses, as the first table, the table with a literal predicate:
AND INVCOST.INVDOSESEQ = -1

FIX:

The solution is to create some statistics which will tell SQL-92 what the sizes of the tables are:
update table statistics for pub.invcost;
update table statistics for pub.invdevice;

For this simple query, just the table statistics for these 2 tables will suffice. For other tables, or for more complex queries, get more statistics, possibly for all tables:
update statistics;
update index statistics;
After getting statistics, both queries run very fast in the same amount of time.