Accessing FileMaker Server Advanced 11:
PHP vs. Lasso 9

This article discusses the various methods of accessing FileMaker Server Advanced 11 (FMSA) for online web applications, and compares the resulting performance under various database calls.

The FileMaker data access methods assessed are:

• FMSA's bundled PHP connector;
• Lasso 9's built in FM Datasource connector;
• Lasso 9's ODBC connector.

Hardware & software configuration

All tests were conducted on the same hardware and software:

• Mac Mini, 2.5GHz Core 2 Duo running OS X Server 10.6.8, 4Gb RAM;
• FileMaker Server Advanced 11.0.3.309;
• Lasso Server 9.1.2;
• PHP 5 as installed by FMSA installer, with FMSA 11's included PHP connector;
• ODBC connector uses FileMaker's ODBC driver and Apple's ODBC Administrator to configure access.

Database

The FileMaker database consists 1 table of 4 columns: 

• id (number)
• txt (text)
• deci (number)
• theDate (date)

Round 1 contained 143 total records. The txt field contains the same phrase in a different language for each record as a UTF-8 test.

Round 2 contained 10,000 total records. The txt field contains a random 32 character string.

Round 3 contained 100,000 total records. The txt field contains a random 32 character string.

The Tests

Each round consisted of three tests:

1. Find All rows and display them in a table.
2. Find a random record by id.
3. Find 20 random records by id.

PHP benchmarking

This was setup using FileMaker's PHP SiteBuilder, and used to set a benchmark time for each test execution. A timer function was added to the recordlist.php file which autogenerated timing specifically for the query and results generation. When just 25 rows were returned the timer function reported an average of 0.107s.

It was observed that when the max records value was overridden (or omitted) this time jumped to an average of 0.2135s.

The next phase of investigation was to extract uneeded autogenerated code in the event of a generic approach skewing the statistics, and adding more tests. The results of this phase are used in the statistics.

Lasso 9's FM Datasource

Lasso has provided a dedicated FileMaker datasource connector since it's inception. The current FM datasource connector uses FileMaker's XML publishing engine for data read & write functions.

The database access and access credentials are defined in the web based Lasso 9 Administration. This approach encourages datasource abstraction and and security - the datasource access URL's, usernames and passwords are not exposed even in page level code.

No additional configuration is required for this datasource to be used within Lasso inline code blocks.

Care was taken to ensure the equivalent queries were executed on the same dataset under the same conditions.

A microseconds timer was used to detemine the speed of each query.

Lasso 9's ODBC Datasource

For the sake of completeness we included Lasso 9's ODBC datasource in our testing.

FMSA 11 is distributed with an ODBC driver for Mac OS X and Windows. After loading this driver the Apple OS X ODBC Administrator application was installed and a System DSN set up to provide access to the FileMaker database used for the tests.

Lasso 9's ODBC datasouce was then configured to access this DSN via localhost / port 2399. A unique database alias was chosen for the test db to avoid conflicting with the same database set up with the FM Datasource.

Note: The only code change required for the tests was to alter the db name used in the inline access config.

Results

For purposes of comparison, Lasso 9 benchmarks are presented as % difference from the PHP access method results for each test. Average times presented above are compiled from two sets of 20 "runs".

Round 1 (143 records)

	Test 1		Test 2		Test 3
Access Method	Avg Time	%	Avg Time	%	Avg Time	%
PHP	0.2982		0.0161		0.4468
Lasso 9: FM Datasource	0.1434	208%	0.0327	49%	0.5748	78%
Lasso 9: ODBC	0.043	693%	0.01083	149%	0.2438	183%

Round 2 (10,000 records)

	Test 1		Test 2		Test 3
Access Method	Avg Time	%	Avg Time	%	Avg Time	%
PHP	11.1421		0.0696		0.3228
Lasso 9: FM Datasource	2.6118	427%	0.0210	331%	0.266	121%
Lasso 9: ODBC	2.5079	444%	0.0208	335%	0.267	121%

Round 3 (100,000 records)

	Test 1		Test 2		Test 3
Access Method	Avg Time	%	Avg Time	%	Avg Time	%
PHP	Did not complete		0.0752		0.3445
Lasso 9: FM Datasource	7.8447	-	0.0211	356%	0.4534	76%
Lasso 9: ODBC	7.1729	-	0.0133	565%	0.3733	92%

Note: Test 1 was amended to only return found count to the browser due to logistical issues returning the data to the browser even while hidden.

Note: The PHP connector could not complete Test 1 on the 100,000 record data set. 

Summary

Round 1:
While Lasso 9's FM Datasource is faster than the PHP connector at the larger data set, the results swing in favour of the PHP connector for small, discrete queries.

The surprise however is in the performance of the Lasso 9 ODBC connector. The results were re-checked to ensure there was no cache-effect skewing results, and while the PHP connector out-performed the Lasso 9 FM connector in Tests 2 & 3 the relevance of this was a contrasting result to the Lasso 9 ODBC connector results, in particular the 693% speed advantage in Test 1.

Round 2:
Round 2 delivered another surprise. With the larger 10,000 record dataset, the PHP connector's advantage in Tests 2 & 3 over Lasso 9's FM Datasource was wiped out, with the FM Datasource gaining parity with the ODBC connector.

Round 3:
Round 3 increased the data set size to 100,000. Interestingly the return times for Test 1 only increased 3x for Lasso 9 datasources, however the PHP connector consistently failed to complete this test.

Tests 2 and 3 saw all connectors return similar times to Round 2, with Lasso connectors handing a small advantage to the PHP connector in Test 3 of Round 3.

Conclusion

Accessing FileMaker data using either of Lasso 9's connectors provides a solid performance advantage over larger database queries. Where access to FileMaker's unique traits are required - such as portal functionality, the Lasso 9 FileMaker Datasource provides clear benefits, especially where larger data sets are involved. Lasso 9's ODBC datasource provides the greatest performance edge and would be the logical choice if native FileMaker functionality is not required for web application logic.

Given the trend for superior performance for Lasso 9 connectors, the limited scenario where Lasso 9 does not outperform the PHP connector warrants further investigation and analysis.

View more articles about Lasso and Filemaker

Author: Jonathan Guthrie
Created: 29 Jul 2011
Last Modified: 2 Aug 2011