Tracy,
update: just saw, no vfp table... ok time for first coffee...
(assuming index on tow.child_id and probably on tow.vehi_id and canccode to be a field of tow)
usually I hesitate to mention the option of creating a complex index as this habit can increase write times.
tow.towid > 0 AND tow.vehi_id > 0 AND canccode = ' '
resolves to a binary result (asssuming nulls are not allowed for these 3 fields or isnull()-clauses may be added).
The write-penalty for a binary index is much smaller
and more constant,
as no re-arranging for the index tree is needed in case of imbalanced structures.
If reccount("tow") is rather large and currently
any filter is rushmore-used to filter tow before joining,
testing with an added binary index for selecting and writing speed might be warranted:
reading times differ as the cdx-reads differ based on the selectivity of each normal index
vs. the sectors needed to fill the bitmap for all records in case of binary.
If more than one index is used to filter I'd expect a good chance for binary index being faster.
And it gives you a chance to play a bit with the vfp-optimizer <g>.
regards
thomas
>>
>>SELECT tow.* FROM tow
>> INNER JOIN inhistory ih ON tow.child_id = ih.child_id
>> WHERE tow.towid > 0 AND tow.vehi_id > 0 AND canccode = ' '
>> AND
>> (ih.child_id IN ('<<lcchild_id>>', '<<lcParent_Id>>')
>> OR ih.parent_id IN ('<<lcchild_id>>', '<<lcParent_Id>>'))
>>
>>
>>>On systems with hundreds of thousands of records, these two queries take 17 seconds. Can anyone see a way to optimize them without changing the results? It's a strange query which was modified in the past to add the >0 section because sqlserver was returning errors without out it.