Tracy,

1. Did you try to run this query as parameterized query?

2. What indexes do you have on your tables?

3. Did you try to separate children from parents (do two INNER JOINS or a select with UNION)?

>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