Hi Tim (et al),

Before I experiment any more, is 45 seconds (with empty cache) about the
best I can expect from this query? A second run gets down to around 25
seconds, I assume due to caching.

The setup is non-client/server, the data is coming across a gigabit
connection.  Both machines are P4+XP.  All joined fields are indexed, as are
"_Group" and "_LName. All indexes are case-sensitive and uncompressed.

Would I see a HUGE improvement by going to C/S?

As I am no expert in this, any advice would be appreciated!

Thanks,

Monte

------------SQL Statement
SELECT Roma._Group, Roma._ID, Roma._CustID, Roma._VehcID, Roma._InsID,
Roma._InvAmt, Roma._BHrs, Roma._RHrs,
Roma._JobMgr, Roma._BO, Roma._FinishDate, Roma._PickupDate,  Roma._ClaimNo,
Vehc._VCol, Vehc._VModYr, Vehc._VMfg, Vehc._VMod, Vehc._VIN,
Cust._LName, Cust._FName, Cust._Phone1,
Inco._ShortName

FROM Roma
JOIN Cust ON (Roma._CustID=Cust._ID)
JOIN Vehc ON (Roma._VehcID=Vehc._ID)
JOIN Inco ON (Roma._InsID=Inco._ID)
WHERE LEFT(UPPER(Cust."_LName") FOR 6) = 'MILLER'
AND ((Roma."_Group" >= 0) AND (Roma."_Group" <= 9))
ORDER BY Roma."_Group", Roma."_ID"

-------------Generated Plan
================================================================================
SQL statement (Executed with 4.22 Build 4)
================================================================================

SELECT Roma._Group, Roma._ID, Roma._CustID, Roma._VehcID, Roma._InsID,

Roma._InvAmt, Roma._BHrs, Roma._RHrs,

Roma._JobMgr, Roma._BO, Roma._FinishDate, Roma._PickupDate,  Roma._ClaimNo,

Vehc._VCol, Vehc._VModYr, Vehc._VMfg, Vehc._VMod, Vehc._VIN,

Cust._LName, Cust._FName, Cust._Phone1,

Inco._ShortName



FROM Roma

JOIN Cust ON (Roma._CustID=Cust._ID)

JOIN Vehc ON (Roma._VehcID=Vehc._ID)

JOIN Inco ON (Roma._InsID=Inco._ID)

WHERE LEFT(UPPER(Cust."_LName") FOR 6) = 'MILLER'

AND ((Roma."_Group" >= 0) AND (Roma."_Group" <= 9))

ORDER BY Roma."_Group", Roma."_ID"

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

Roma (Roma) table opened shared, has 17067 rows
Cust (Cust) table opened shared, has 11012 rows
Vehc (Vehc) table opened shared, has 13238 rows
Inco (Inco) table opened shared, has 133 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-sensitive
temporary index:

_Group ASC
_ID ASC

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

The expression:

Roma."_Group" >= 0 AND Roma."_Group" <= 9

is OPTIMIZED, covers 17031 rows or index keys, costs 810770 bytes, and will
be
applied to the Roma table (Roma) before any joins

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

The driver table is the Roma table (Roma)



The Roma table (Roma) is joined to the Cust table (Cust) with the INNER JOIN
expression:



Roma._CustID = Cust._ID



The Roma table (Roma) is joined to the Vehc table (Vehc) with the INNER JOIN
expression:



Roma._VehcID = Vehc._ID



The Roma table (Roma) is joined to the Inco table (Inco) with the INNER JOIN
expression:



Roma._InsID = Inco._ID

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

Optimized Join Ordering
-----------------------

The driver table is the Cust table (Cust)



The Cust table (Cust) is joined to the Roma table (Roma) with the INNER JOIN
expression:



Cust._ID = Roma._CustID



The Roma table (Roma) is joined to the Inco table (Inco) with the INNER JOIN
expression:



Roma._InsID = Inco._ID



The Roma table (Roma) is joined to the Vehc table (Vehc) with the INNER JOIN
expression:



Roma._VehcID = Vehc._ID

The expression:

LEFT(UPPER(Cust."_LName"),6) = 'MILLER'

is UN-OPTIMIZED and will be applied to each candidate row in the Cust table
(Cust) as the result set is generated

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:

Cust._ID = Roma._CustID

is OPTIMIZED

The expression:

Roma._InsID = Inco._ID

is OPTIMIZED

The expression:

Roma._VehcID = Vehc._ID

is OPTIMIZED

================================================================================
>>>>> 96 rows affected in 45.375 seconds
================================================================================

Monte, the main issue appears to be:

LEFT(UPPER(Cust."_LName"),6) = 'MILLER'
is UN-OPTIMIZED and will be applied to each candidate row in the Cust table
(Cust) as the result set is generated

If "All indexes are case-sensitive and uncompressed"
perhaps LName is already uppercase ... then

WHERE Cust."_LName" LIKE 'MILLER%' will take advantage of the index

You should consider full compression on text based fields / try / compare
result.

HTH.  Donat.

Monte,

<< Would I see a HUGE improvement by going to C/S? >>

Well, you'll definitely get a boost due to not having the query engine pull
across the index data to process the query.  The main problem I see with the
query is that your WHERE  clause does nothing to the Roma table and
essentially selects all of the records.  That means that DBISAM has to
perform a join between all tables in their entirety, and then filter the
result down by the Cust._LName filter.  As Donat suggested, I would change
that condition to be a LIKE condition that can use an index for the
partial-length match.  That might speed things up a bit by kicking out some
more records *before* the joins take place.

--
Tim Young
Elevate Software
www.elevatesoft.com

WOW!  Thanks for the suggestion!
================================================================================
SQL statement (Executed with 4.22 Build 4)
================================================================================
..
..
..
WHERE Clause Execution
----------------------
The expression:

UPPER(Cust."_LName") LIKE UPPER('MILLER%')

is OPTIMIZED, covers 58 rows or index keys, costs 3920 bytes, and will be
applied to the Cust table (Cust) before any joins
..
..
..
================================================================================
WAS >>>>>96 rows affected in 45.375 seconds
NOW>>>>> 96 rows affected in 0.484 seconds
================================================================================
And as Tim said, this starts with a much smaller set of records...

Now, a couple of more questions.  To get this to work, I had to change the
indexes case insensitivity to "Yes" on the only index that used "_LName".
This index was a multifield index leading with "_LName".  (Note: The field
values are in propercase, and many of my users think you  turn the keyboard
on with "capslock" .

I thought I had read it is "better" to have case sensitive indexes?

Is there any reason to have a separate single field index that not case
sensitive-just for use by my "search" routines?

The other thing I learned (I think) is that the SQL interpreter doesn't look
to see if the comparison value has been uppercased (like by me, the
programmer), but sure seems to know what to do when it sees the UPPER
function on *both* sides of the "LIKE".

UPPER(Cust."_LName") LIKE UPPER('Miller%')
< >
UPPER(Cust."_LName") LIKE 'MILLER%'

Is that correct?

thanks again,

Monte



"Donat Hebert (WSI)" <dhebert@worldsoftwareinc.com> wrote in message
news:BD432C74-A856-41AC-AE0E-5BE8B678D421@news.elevatesoft.com...
> Monte, the main issue appears to be:
>
> LEFT(UPPER(Cust."_LName"),6) = 'MILLER'
> is UN-OPTIMIZED and will be applied to each candidate row in the Cust
> table
> (Cust) as the result set is generated
>
> If "All indexes are case-sensitive and uncompressed"
> perhaps LName is already uppercase ... then
>
> WHERE Cust."_LName" LIKE 'MILLER%' will take advantage of the index
>
> You should consider full compression on text based fields / try / compare
> result.
>
> HTH.  Donat.
>
>

Monte


>I thought I had read it is "better" to have case sensitive indexes?

If its a case sensitive index, and the field values are always uppercased then just use

Cust."_LName" LIKE 'MILLER%'

Making sure you've converted the name to uppercase yourself.

Once you've figured out the UPPER trick the only difference is how MILLER and Miller will sort / display. Case insensitive is more "forgiving" of the use of the caps lock or shift key

>The other thing I learned (I think) is that the SQL interpreter doesn't look
>to see if the comparison value has been uppercased (like by me, the
>programmer), but sure seems to know what to do when it sees the UPPER
>function on *both* sides of the "LIKE".
>
>UPPER(Cust."_LName") LIKE UPPER('Miller%')
>< >
>UPPER(Cust."_LName") LIKE 'MILLER%'
>
>Is that correct?

Absolutely!

Roy Lambert

Monte,

<< The other thing I learned (I think) is that the SQL interpreter doesn't
look to see if the comparison value has been uppercased (like by me, the
programmer), but sure seems to know what to do when it sees the UPPER
function on *both* sides of the "LIKE". >>

Correct.  That is the only reliable way for the optimizer to know for sure
that the condition is entirely case-insensitive.

--
Tim Young
Elevate Software
www.elevatesoft.com