It was close enough to get me in the right direction :o) The 2nd part has remained the same. I still have to test it on systems with > 100,000 records. It does test faster than the original. I don't know yet if it is fast enough.


>>Perfect :o)
>>THANKS
>>
>
>This is not equal to your original query.
>
>>
>>>Try
>>>

>>>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.
>>>>
>>>>

lcParent_Id = ALLTRIM(NVL(parent_id,''))  && 
>>>>lcChild_Id = ALLTRIM(NVL(child_id,''))
>>>>
>>>>TEXT TO lcSQL NOSHOW PRETEXT 15 TEXTMERGE
>>>>
>>>>      SELECT tw.* FROM tow AS tw
>>>>
>>>>       INNER JOIN
>>>>
>>>>            (SELECT inhistory.child_id FROM inhistory
>>>>
>>>>             WHERE  inhistory.child_id = '<<lcchild_id>>'
>>>>
>>>>             OR inhistory.parent_id = '<<lcchild_id>>'
>>>>
>>>>             OR (LEN(inhistory.child_id)>0 AND inhistory.child_id = '<<lcParent_Id>>')
>>>>
>>>>             OR (LEN(inhistory.parent_id)>0 AND inhistory.parent_id = '<<lcParent_Id>>')) qry1
>>>>
>>>>      ON tw.child_id = qry1.child_id WHERE tw.towid > 0 AND tw.vehi_id > 0 AND canccode='    '
>>>>
>>>>ENDTEXT
>>>>
>>>>IF SQLProc("SP",m.lcSQL,,,"cTowCT") > 0 && Return any Tow records
>>>>
>>>>      TEXT TO lcSQL2 NOSHOW PRETEXT 15 TEXTMERGE
>>>>
>>>>            SELECT qry2.towid, vh.* FROM vhtable as vh 
>>>>
>>>>      INNER JOIN
>>>>
>>>>            (<<m.lcSQL>>) qry2
>>>>
>>>>            ON vh.vehi_id = qry2.vehi_id ORDER BY vh.vehi_id
>>>>
>>>>      ENDTEXT
>>>>
>>>>      IF SQLProc("SP",m.lcSQL2,,,"cvhtableIn") > 0 && Return the vhtable Records
>>>>
>>>>            SELECT cvhtableIn
>>>>
>>>>      ENDIF
>>>>
>>>>ENDIF