1 2 Previous Next 21 Replies Latest reply on Mar 5, 2012 11:07 AM by steve.winter

    Need input - Performance issue

    Mike_Mitchell

      Good day. I have a search feature on my site, based on a single input field from the user. What I've implemented is intended to allow the user to input a search term, and then the page searches some number of fields in the database with that term and returns any record in which the search term appears in any of the fields. I've done this by writing a PHP function, thus:

       

      function fetch_result_array_by_multiple_values($findRequestArray,$fetchLayout,$recordTypeDescription,$DB,$errorBaseData,$sortField=null,$sortDirection=null,$skip=0,$range=null,$logEmptyResult='no') {

       

       

      // Find result object using compound find by identifiers in $findRequestArray

      // Each element in $findRequestArray is a find request in this form: array('fetchValue'=>{value to search for},'fetchFieldName'=>{name of field},'omit'=>{'' or 'TRUE'})

      // The layout is $fetchLayout

      // $recordTypeDescription is used for the error message, e.g. "articles", "topics", etc.

      // If sorting is desired, populate $sortField with name of field to sort on

      // $sortDirection can be "DESCEND" if descending order is desired; otherwise, default is ascending

      // If only a range is desired, populate $range

      // Other parameters are used for error processing

       

       

      $recordFind = $DB->newCompoundFindCommand($fetchLayout);

       

      $count = 0;

       

      foreach($findRequestArray as $key=>$findRequest) {

       

      $request[$key] = $DB->newFindRequest($fetchLayout);

      $request[$key]->addFindCriterion($findRequest['fetchFieldName'],$findRequest['fetchValue']);

      if($findRequest['omit'] == 'TRUE') {

      $request[$key]->setOmit(TRUE);

      }

       

      $recordFind->add(($count+1),$request[$key]);

       

      $count++;

       

      }

       

      unset($count);

       

      if($sortField != '') {

      if($sortDirection == 'DESCEND') {

      $recordFind->addSortRule($sortField,1,FILEMAKER_SORT_DESCEND);

      }else{

      $recordFind->addSortRule($sortField,1);

      }

      }

       

      if($range != '') {

      $recordFind->setRange($skip,$range);

      }

       

      $recordResult = $recordFind->execute();

       

      if(FileMaker::isError($recordResult)) {

      $returnedErrorMessage = $recordResult->getMessage();

      if($logEmptyResult == 'yes' or ($logEmptyResult == 'no' and $returnedErrorMessage != 'No records match the request')) {

      $errorBaseData['errMsg'] = 'Error fetching '.$recordTypeDescription.': '.$recordResult->getMessage();

      $errorBaseData['displayMsg'] = 'Error fetching '.$recordTypeDescription.'. Contact tech support.';

      log_error($DB,$errorBaseData);

      }

      }

       

       

      // If there is an error, calling function will need to test for it

      return $recordResult;

       

      }

       

      It's a bit much, I know. But basically, when I call this function, it looks like this (as an example):

       

      $findRequestArray[0] = array('fetchValue'=>$searchTerm,'fetchFieldName'=>'articleTitle','omit'=>'');

      $findRequestArray[1] = array('fetchValue'=>$searchTerm,'fetchFieldName'=>'author','omit'=>'');

      $findRequestArray[2] = array('fetchValue'=>$searchTerm,'fetchFieldName'=>'articleTeaser','omit'=>'');

      $findRequestArray[3] = array('fetchValue'=>$searchTerm,'fetchFieldName'=>'articleBody','omit'=>'');

      $articleResult = fetch_result_array_by_multiple_values($findRequestArray,'articleWebSearch','articles',$DB01,$errorBaseData,'datePosted','DESCEND',$skip,$range);

       

      Where $DB01 is the database object, $errorBaseData is the error logging object, $findRequestArray is the array you see defined here, and the rest are just text strings or numbers.

       

      The problem I'm having is ... this thing is slow. Really slow. Is there something really obvious I'm missing here that's slowing it down, or are compound Finds just dog slow?

       

      Thanks.

       

      Mike

        1 2 Previous Next