I was doing some testing and was curious if the time I am getting to complete the select/joins for my situation is optimal. In other words can I speed it up any? All of the UniqueID values are
integers and indexed. Any input would be appreciated. Thanks!

================================================================================
SQL statement (Executed with 4.21 Build 10)
================================================================================

SELECT A.KeyField1, A.KeyField2, A.Name, A.Address, B.KeyField3, B1.Desc1,
B1.Desc2, B1.Desc3, B2.PDesc1, B3.KeyField4, B3.KeyField5, B3.KeyNumber FROM TableA AS A
LEFT OUTER JOIN TableB As B ON (B.BUniqueID=A.AUniqueID)
LEFT OUTER JOIN TableB1 AS B1 ON (B1.BUniqueID=B.BUniqueID)
LEFT OUTER JOIN TableB2 AS B2 ON (B2.BUniqueID=B.BUniqueID)
LEFT OUTER JOIN TableB3 AS B3 ON (B3.BUniqueID=B.BUniqueID)
WHERE (KeyField1='001111')
AND (upper(Name) LIKE upper('ANDERSON R%'))
ORDER BY Name NOCASE

Tables Involved
---------------

TableA (A) table opened shared, has 458254 rows
TableB (B) table opened shared, has 471921 rows
TableB1 (B1) table opened shared, has 59103 rows
TableB2 (B2) table opened shared, has 415482 rows
TableB3 (B3) table opened shared, has 713652 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

Result set will be ordered by the following column(s) using a case-insensitive
temporary index:

Name ASC

WHERE Clause Execution
----------------------

The expression:

KeyField1 = '001111' AND upper(Name) LIKE upper('ANDERSON R%')

has been rewritten and is PARTIALLY-OPTIMIZED, covers 89 rows or index keys,
costs 109416 bytes, and will be applied to the TableA table (A) before any joins

Join Ordering
-------------

The driver table is the TableA table (A)

The TableA table (A) is joined to the TableB table (B) with the LEFT OUTER JOIN
expression:

A.AUniqueID = B.BUniqueID

The TableB table (B) is joined to the TableB1 table (B1) with the LEFT OUTER
JOIN expression:

B.BUniqueID = B1.BUniqueID

The TableB table (B) is joined to the TableB2 table (B2) with the LEFT OUTER
JOIN expression:

B.BUniqueID = B2.BUniqueID

The TableB table (B) is joined to the TableB3 table (B3) with the LEFT OUTER
JOIN expression:

B.BUniqueID = B3.BUniqueID

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

The joins are already in optimal order and cannot be optimized any further

Join Execution
--------------

Costs ARE NOT being taken into account when executing this join
Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the
optimizer to consider costs when optimizing this join

The expression:

A.AUniqueID = B.BUniqueID

is OPTIMIZED

The expression:

B.BUniqueID = B1.BUniqueID

is OPTIMIZED

The expression:

B.BUniqueID = B2.BUniqueID

is OPTIMIZED

The expression:

B.BUniqueID = B3.BUniqueID

is OPTIMIZED

================================================================================
>>>>> 63 rows affected in 3.188 seconds
================================================================================

Vincent,

<< I was doing some testing and was curious if the time I am getting to
complete the select/joins for my situation is optimal. In other words can I
speed it up any? All of the UniqueID values are integers and indexed. Any
input would be appreciated. >>

Is there an index available on the KeyField1 field ?  If so, is it
case-sensitive ?  Judging by the query plan, it looks like the LIKE is
optimized, but the KeyField1 condition is not.

--
Tim Young
Elevate Software
www.elevatesoft.com

That brings up another question. The table has several indexes that begin with the field in question. Some of the indexes are marked as case-insensitive, others are not. So I am not sure
which it would use in the query, is there a way to know? However,  the keyfield1 value of '001111' in this example would yield over 100,000 records by itself.

That brings up another question. The table has several indexes that begin with the field in question. Some of the indexes are marked as case-insensitive, others are not. So I am not sure
which it would use in the query, is there a way to know? However,  the keyfield1 value of '001111' in this example would yield over 100,000 records by itself.

Vincent,

<< That brings up another question. The table has several indexes that begin
with the field in question. Some of the indexes are marked as
case-insensitive, others are not. So I am not sure which it would use in the
query, is there a way to know? >>

As long as there is at least one index that matches the case-sensitivity of
the condition, DBISAM will use it.

<< However,  the keyfield1 value of '001111' in this example would yield
over 100,000 records by itself. >>

Actually, I misread the original cost of the WHERE clause as being 10 megs
instead of 100k, so the WHERE clause is pretty much fine as is.  DBISAM is
smart enough to choose the most selective of the two conditions if both are
optimized, and then just do a record scan on the resultant records if that's
cheaper than doing the index scan on the 100,000 records.  Which, in this
case, it would be.

The majority of the time that you're seeing is probably wrapped up in the
rest of the joins and ordering.

--
Tim Young
Elevate Software
www.elevatesoft.com