SQL performance: Alpha Five vs. Filemaker 9 benchmarks

Alpha Five Version 8 sorts over 1 million records more than 600 times faster than Filemaker 9.

If database performance matters to you, this is a blog post you won't want to miss.

You know the old saying that the best laid plans always go awry? Well they certainly did. A few weeks ago, Richard promised a continuation of the Alpha Five vs. Filemaker comparison.

So he asked me to set up a series of tests to pin Alpha Five Version 8 against Filemaker 9. It took a bit longer than expected because (besides the fact that we have a million things going on at Alpha Software right now) we wanted to make sure the tests were solid. And I believe the facts I am about to present to you are solid as rock.

When FileMaker 9 was released this summer, and described as the "Most Significant Upgrade" in the history of their company, we were, as a competitor, naturally curious to see what they were up to. We were especially taken with their strong claims in the area of SQL support.

To wit:

The new version also offers breakthrough, easy-to-use tools so FileMaker users and workgroups easily can connect to the world of company and Web data residing in external SQL data sources: MySQL, Oracle SQL, and Microsoft SQL Server.

Their original PR is here.

To investigate this claim, we decided to run some tests. The results were stunning, to put it mildly. Let's start with sorting.

When you build an application in Alpha Five in a SQL environment, and you use one of the Web component builders, Alpha Five is smart enough to let the back-end data source do the work for you.

And to a degree, FileMaker is smart, too, when it comes to searching for particular records. But when it comes to sorting records (for some unfathomable reason) FileMaker downloads ALL of the records to the client (i.e. the local machine). Then the local FileMaker database performs the sort.

It's as if FileMaker took the server out of client-server computing, and replaced it with an invention dubbed client-client computing. Clearly this is the wrong approach, and performance and scalability suffer considerably.

This, of course, is not an issue with tiny databases. But when people are connecting to Microsoft SQL Server, MySQL, Oracle, etc., they're typically working with thousands to millions of records.

We performed three benchmarks with FileMaker Pro 9 Advanced and Alpha Five Version 8.

Benchmark I:
We connected to a SQL Server database that contained zip codes, cities, and states for 42,000 locations in the United States. We then built a FileMaker form and an Alpha Five Web form that connected to this source. Both programs were then used to sort the records by city name. The processing took Alpha Five two seconds, and took FileMaker more than 15 seconds. That makes Alpha over seven times faster.

Benchmark II:
We sorted the same data set by the two-character state field. This should be an easier, higher-performing test for both databases. Indeed, Alpha Five finished the sort in just 1.33 seconds. It took FileMaker 20.66 seconds. Alpha comes out even faster --15.5 times faster.

I recorded these benchmark tests in Camtasia Studio, so I could perform exact measurements by playing back the video in slow motion. If you'd like to see the video, contact us at Marketing@AlphaSoftware.com for a look.

By dividing the number of records processed by the number of seconds it took to process, I was able to calculate and graph the results in terms of records processed by second. Check out the data:



Benchmark III:
Just for fun, I decided to run a third benchmark to see what would happen if we tried to process over 1 million records. I used our Web traffic logs for this, which are quite large.

I asked both FMP9 and A5V8 to sort by visitor ID, a 10-digit number. It took Alpha Five 8.86 seconds to process over 1 million records. FileMaker required more than an hour and a half; 5,400 seconds. That's over 600 times longer than Alpha Five.



In Alpha, the number of records per second actually increased when dealing with a larger set of records. In previous tests with the smaller data set, Alpha Five was processing 32,000 records per second. In this test with over 1 million records, Alpha processed 132,000 records per second.

These results illustrate fundamental architectural, design, and performance differences between the two popular database platforms. FileMaker is a wonderful product, and we respect them as a competitor, as we do Microsoft and many other desktop database vendors.

While Alpha Five is every bit as easy to use for beginners as FileMaker, the two products are not in the same league when it comes to tackling robust business requirements. FileMaker doesn't scale, in part because of its odd "client-client" architecture. Alpha Five scales, in part because it is a true client-server platform.

Now, in fairness to FileMaker, their technical documentation (unlike their press release) does offer a caveat:

It is important to note that "increasing the potential size and scalability of FileMaker Pro based solutions" is not an explicit design goal of the ESS (External SQL Sources) feature set. It may be possible to use ESS in that way, but the feature set is designed to favor transparency and ease of use, not raw speed. So it is probably not wise to expect that a good use of ESS would be to replace your largest and most complex FileMaker Pro based tables with SQL-based tables. This might work perfectly well, and might even solve certain problems for certain systems, but it would be wise to proceed with caution and to remember that the feature designers were trying to achieve "smooth, FileMaker Pro-like integration" not "bigger, faster FileMaker Pro-based systems." ESS is not designed as a means to allow a FileMaker Pro solution to scale beyond the limits of a purely FileMaker Pro based solution. The ESS feature set is designed to emphasize the seamless integration of SQL-based tables into a FileMaker Pro solution, rather than to take specific advantage of the high-scalability features of SQL back ends.

Clearly, the marketing and tech teams at FileMaker are not on the same page. The tech guys and gals are straight with developers, but the marketers are overpromising on FileMaker Pro 9's SQL abilities.

But that's not even the point. The point is, with FileMaker, SQL support appears to be a bolted-on afterthought. With Alpha Five, it's integral to the platform. And it shows, with far better runtime performance that developers, users, and clients alike will appreciate.

Now, perhaps FileMaker's abysmal SQL sorting performance is just the result of a bad design decision, or even a bug. I, for the life of me, can't figure out why they would design it to behave as a "client-client" application instead of client-server.

Then again, maybe there is some internal limitation that prevents FileMaker from taking full advantage of SQL server processing. Regardless, in its current incarnation, the SQL sorting performance is, put plainly, awful.