Searching records in FileMaker Custom Web Publishing(CWP) using PDO(PHP Data Objects) can become pretty frustrating as a table grows much bigger, say the table has one million records.
A table with one million records is not THAT huge when it is of a MySQL table, but it is obviously too much to handle for FileMaker if you want to do some CWP using PDO.
FileMaker is compatible with ODBC, and PDO supports ODBC. This means you can perform SQL queries via PDO to retrieve data from FileMaker databases.
We will be talking about this issue using PDO in this post.
Here are a few things to consider if your tables have thousands of records:
- Do not use SELECT COUNT (*)
select count(*) causes a server time out, and worse yet, the existing fmxdbc_listener.exe process eats up CPU usage and RAM while counting records, and then goes unresponsive.
This is serious, because you will end up killing and starting fmxdbc_listener.exe process manually, or restarting the FileMaker Server.
SELECT COUNT ( fieldname ) would not make the situation any better. :( - Do not use the ORDER BY clause
This also causes a server timeout and fmxdbc_lisner.exe turns into a CPU/RAM eating monster if you use the ORDER BY clause for a large volume of recordset. - Try not to retrieve a large recordset by using SELECT.
The query response becomes worse if the found recordset is huge, even if indexed fields are specified in a WHERE clause.
How would you navigate records in a found set?
However, when it comes to FileMaker CWP, your options are quite limited after you consider those don't's we have already explained above.
Method 1: Use PDO scrollable cursors
Here is a sample php script for retrieving the last record in a found set using a PDO scrollable cursor.
<?php
//sets up a connection
try {
$dsn= "odbc:Driver={FileMaker ODBC};host=127.0.0.1;Database=test";
$pdo = new PDO( $dsn, "cgi", "pwd" );
$pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
$pdo->setAttribute( PDO::ATTR_CASE, PDO::CASE_NATURAL );
} catch ( PDOException $e ) {
exit( "Database connection failed.". $e->getMessage() );
}
//builds a query string
$sql = "select recId, invoiceNo from invoice";
//prepares a PDO statement and runs it
$stmt = $pdo->prepare( $sql, array( PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL ) );
$stmt->execute();
//retrieves the last record in a found set
$record = $stmt->fetch( PDO::FETCH_ASSOC, PDO::FETCH_ORI_LAST );
//script goes on using $record....
?>
PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL defines a scrollable cursor, and PDO::FETCH_ORI_LAST moves the cursor to the last row in a found set.
You may also consider using the following predefined PDO constants:
PDO::FETCH_ORI_FIRST --- moves the cursor to the first row in a found set.
PDO::FETCH_ORI_ABS --- moves the cursor to the specified row in a found set. The absolute position is specified in the third argument.
Example: $record = $stmt->fetch( PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS,$absPosi);
For more information, please visit http://php.net/manual/en/pdo.constants.php
Method 2: Use FileMaker's OFFSET n ROWS and FETCH FIRST n ROWS Clauses
Here is a sample php script for retrieving the 5000th record in a found set using FileMaker's OFFSET n ROWS and FETCH FIRST n ROWS clauses.
<?php
//sets up a connection
try {
$dsn= "odbc:Driver={FileMaker ODBC};host=127.0.0.1;Database=test";
$pdo = new PDO( $dsn, "cgi", "pwd" );
$pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
$pdo->setAttribute( PDO::ATTR_CASE, PDO::CASE_NATURAL );
} catch ( PDOException $e ) {
exit( "Database connection failed.". $e->getMessage() );
}
//defines the offset value of 4999
$offset = 4999;
//builds a query string
$sql = "select recId, invoiceNo from invoice OFFSET ".$offset." ROWS";$sql .= " FETCH FIRST 1 ROWS ONLY";
//prepares a PDO statement and runs it
$stmt = $pdo->prepare( $sql );
$stmt->execute();
//retrieves a record
$record = $stmt->fetch( );
//script goes on using $record....
?>$offset = 4999 defines to go to the 4999th row in a found set.
FETCH FIRST 1 ROWS ONLY defines to retrieve one row.
Thus, the 5000th row will be set to $record variable.
The Response is SO Slow Whatever the Method is....
Practically speaking, requiring five seconds to retrieve JUST one record is simply ridiculous.
But unfortunately this happens with FileMaker CWP when tables get bigger.
We have created sample scripts using those two data retrieving methods to navigate records in a found set, in order to show you how slow the FileMaker server response gets.
Method 1: Using PDO scrollable cursors
First button
Next button
It took 5.3859 seconds just to show the second record out of 939,623 records.
This outcome is pretty disappointing but this happens when the query has a WHERE clause and the found set is huge.
In this example, cRecId > 17538 results in 922,084 records and FileMaker Server's response goes slow, even though cRecId field is indexed.
Previous button
It took 5.4028 seconds when clicking Previous button to retrieve the previous record from the very last position.
This is another disappointing outcome because this huge found set caused the slow response.
In this example, cRecId < 957160 results in 939,622 records and FileMaker Server's response goes slow, even though cRecId field is indexed.
Last button
The response is alright, since the query does not require a WHERE clause.
Method 2: Using FileMaker's OFFSET n ROWS and FETCH FIRST n ROWS Clauses
This method requires two separate queries:
- A query to get the record count from a table with SELECT clause.
- A query to set the starting position (offset) in a found set and fetch the specified number of rows of records.
Performing two queries does not appear to be a very smart, however, there is no better way to know how many records there are in a table(especially when you want to know the total record count), and then perform OFFSET and FETCH ONLY n ROWS clauses.
First button
Please note the first query (QUERY1 in the figure below) does not have a WHERE clause.
Next button
In this example, the first query's (QUERY1 in the figure below) cRecId > 17538 results in 922,084 records and FileMaker Server's response goes slow, even though cRecId field is indexed.
Previous button
It took 6.854 seconds when clicking Previous button to retrieve the previous record from the very last position.
In this example, the first query's (QUERY1 in the figure below) cRecId < 957160 results in 939,622 records and FileMaker Server's response goes slow, even though cRecId field is indexed.
Last button
It took 2.0451 seconds to get the very last row.
The first query (QUERY1 in the figure below) does not have a WHERE clause, so the response is faster than the Next and Previous buttons.
(亀/turtle)
0 件のコメント:
コメントを投稿