>
>If you can add some filter applied to your main query to the derived table too, I think it would speed up the data retrieval, e.g.
>
>select Header.Data, DerivedTable.Data from Header inner join (select ... group by ..) Derived where Header.ID in (some ID lists).
>
>If we can put this into the derived table where condition, I believe it should speed it up too.

That could be hard, becuase the Where clause is built dynamically based on about 5 fields that the use might or might not supply inputs for. It presently comes in after all the derived tables and is the Where clause for the whole thing, after the joining takes place.

Here is the code for dynamically building the Where clause:

  lcAnd= " WHERE "

  *-- Customer filter -----------------------------------------------------------
  if not empty(lcSrch1)
   lcSQL = lcSQL + lcAnd + Alltrim(Search.cSrch_1) + " = '" + lcSrch1 + "' "
   lcAnd = "AND "
  endif
  *-- Job No filter -------------------------------------------------------------
  if not empty(lcSrch2)
   lcSrch2=Strtran(lcSrch2, '*', '%')
   lcSQL = lcSQL + lcAnd + Alltrim(Search.cSrch_2) + " Like '" + lcSrch2 + "%' "
   lcAnd = "AND "
  endif
  *-- PO filter -----------------------------------------------------------------
  if not empty(lcSrch3)
   lcSQL = lcSQL + lcAnd + Alltrim(Search.cSrch_3) + " Like '%" + lcSrch3 + "%' "
   lcAnd = "AND "
  endif

  *=====================================================================
  *-- Now, handle the keywords from the generic search box
  *=====================================================================
  if not empty(lcSrch41)
   *-- Look for '-' as first character to EXCLUDE items with this search term
   if(Left(lcSrch41,1)='-')
    lcNotLike=' not'
    lcSrch41=Substr(lcSrch41,2)
   Else
    lcNotLike=''
   endif
   lcSQL = lcSQL + lcAnd + Alltrim(Search.cSrch_4) + lcNotLike + " like '%" + lcSrch41 + "%' "
   lcAnd = "AND "
  endif
  if not empty(lcSrch42)
   *-- Look for '-' as first character to EXCLUDE items with this search term
   if(Left(lcSrch42,1)='-')
    lcNotLike=' not'
    lcSrch42=Substr(lcSrch42,2)
   Else
    lcNotLike=''
   endif
   lcSQL = lcSQL + lcAnd + Alltrim(Search.cSrch_4) + lcNotLike + " like '%" + lcSrch42 + "%' "
   lcAnd = "AND "  
  endif
  if not empty(lcSrch43)
   *-- Look for '-' as first character to EXCLUDE items with this search term
   if(Left(lcSrch43,1)='-')
    lcNotLike=' not'
    lcSrch43=Substr(lcSrch43,2)
   Else
    lcNotLike=''
   endif
   lcSQL = lcSQL + lcAnd + Alltrim(Search.cSrch_4) + lcNotLike + " like '%" + lcSrch43 + "%' "
   lcAnd = "AND "  
  endif
  if not empty(lcSrch44)
   *-- Look for '-' as first character to EXCLUDE items with this search term
   if(Left(lcSrch44,1)='-')
    lcNotLike=' not'
    lcSrch44=Substr(lcSrch44,2)
   Else
    lcNotLike=''
   endif
   lcSQL = lcSQL + lcAnd + Alltrim(Search.cSrch_4) + lcNotLike + " like '%" + lcSrch44 + "%' "
   lcAnd = "AND "  
  endif
  if not empty(lcSrch45)
   *-- Look for '-' as first character to EXCLUDE items with this search term
   if(Left(lcSrch45,1)='-')
    lcNotLike=' not'
    lcSrch45=Substr(lcSrch45,2)
   Else
    lcNotLike=''
   endif
   lcSQL = lcSQL + lcAnd + Alltrim(Search.cSrch_4) + lcNotLike + " like '%" + lcSrch45 + "%' "
   lcAnd = "AND "  
  Endif

  *-- Vendor Filter -------------------------------------------------------
  if not empty(lcSrch5) 
   lcSQL = lcSQL + lcAnd + Alltrim(Search.cSrch_5) + " = '" + lcSrch5 + "' "
   lcAnd = "AND "
  endif

  *-- Date Filter ---------------------------------------------------
  if not empty(thisform.date1)
   lcSQL = lcSQL + lcAnd + Alltrim(Search.cSrch_Date) + ">=thisform.date1 "
   lcAnd = "AND "
  endif
  if not empty(thisform.date2)
   lcSQL = lcSQL + lcAnd + Alltrim(Search.cSrch_Date) + "<=thisform.date2 "
   lcAnd = "AND "
  endif

  *-- Status filter (as in Job status) -----------------
  if not empty(lcSrchStatus)
   lcSQL = lcSQL + lcAnd + "job_info.Status = '" + lcSrchStatus + "' "
  endif

  lcSQL = lcSQL + lcGroupBy + lcOrderBy + ' INTO cursor ' + tcSearchResultsCursor + ' READWRITE'

  thisform.cSqlString=lcSQL && Store the SQL search command to a form property as a reference that can be used to review.

  &lcSQL &&  Execute the SQL statement