Slow Performance of FM-PHP API

[Deepak Kumar]

hi guys please help in this situation:

Scenario: I want to retrieve the records of a database having 90,000 records via PHP, but unable to do it.

How can i retrieve the complete records without any performance issue ?

I am using the code:

<?php

$layout='web';

$fetchLayout=$fm->newFindAllCommand($layout);

print_r($result=$fetchLayout->execute());

$values=$result->getRecords();

foreach($values as $recValues)

{

echo $test = $recValues->getField('make');

echo $instructorlist1=$recValues->getField('model');

echo $instructorlist2=$recValues->getField('year');

}

?>

[dansmith65]

Reduce the number of fields on the layout to only those you need for that specific query.

Do you know the max memory that PHP can use, on your server? I'm not sure if you're reaching this limit, but that's the only other thing I can think of.

[Deepak Kumar]

Thanks for concern.

Table consists of 53 fields, but i am only using 2 fields which is needed.

Although, it is slow.

[Richard DarkMantis Clifford]

Well if you create a new layout just for those fields then it will increase the performance greatly as the way that FM Works is that it will retrieve ALL Fields and filter through the ones that you need (in a round about way).

[dansmith65]

The next step would be to time each section of you script so you know for sure where the delay comes from.

[webko]

Depending on how much data is in the two fields on the special layout that has only those two fields... It still may be too much data to handle in one hit...

Is there a reason you need all 90K rows at once?

[Deepak Kumar]

  On 2/29/2012 at 9:37 PM, webko said:

Depending on how much data is in the two fields on the special layout that has only those two fields... It still may be too much data to handle in one hit...

Is there a reason you need all 90K rows at once?

I want to create a value list from the field, which has 90k records.

[webko]

How many values are unique?

I'd probably create a value list in FileMaker and then call that - not sure how that's done in the API but it's fairly easy in FX.php

[cbishop]

If ExecuteSQL is available in your version of PHP, use PerformFMScript instead, and call ExecuteSQL, delivering the results in a flat table format.  This is much faster and more efficient than using the API to get the individual records, as each record will include metadata and bloat the overall return.

You can use a layout with one field on it (a global text field) to save and output the results for your PHP file to read.