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.

Attorneys trust Alpha Five

Here's a true customer success story featuring Attorneys Trust Document Services, which I hope will whet your appetite over the rapid development capabilities provided by Alpha Five. The story is featured below, or you can check out the PDF version, which features a slick layout.

Customer Profile
Attorneys Trust Service (ATS) was founded in 1991. The company goal was to create and deliver the most highly-customized estate planning documents available for clients of California attorneys and financial advisors. Having succeeded in California, ATS is now expanding nationwide.

Location
San Ramon, Calif.

Industries
Law, Trusts & Estates, Estate Planning

Situation
Attorneys Trust Document Service prepares complex estate planning documents for attorneys and financial professionals, who provide them to clients. But the fast-growing company was facing time and productivity challenges. ATS received too many telephone calls where clients asked the same questions again and again. Company management decided they needed a convenient, self-service Web site that provided stock answers to standard questions, and also offered some valuable services. They also wanted to appeal to younger, Web-savvy professionals. Unfortunately, they quickly found it impossible to building a data-driven Web site using popular applications, such as Dreamweaver and Front Page.

Solution
Attorneys Trust Service turned to an Alpha Software partner, AlphaToGo, in San Ramon, Calif. They asked AlphaToGo to create a Web site that provided a secure, interactive online document request form, as well as an extensive knowledge base to search and find answers to common questions. Using Alpha Five, AlphaToGo designed, developed, debugged, and deployed a state-of-the-art, secure Web site with database-driven support and document services. AlphaToGo also developed a work-in-progress tracking system that allows ATS’ clients to access pending work; find client contact information, get maps and directions, automate progress tracking, and distribute status e-mails to authorized parties.

Benefits
The application built in Alpha Five provides an online experience that’s easy for ATS clients to use, and easy for ATS executives to manage. Unlike other platforms that address narrow aspects of Web development—such as page design, or database, or scripting—Alpha's complete integration between Web design, desktop application, and Web database server allow in-house workers to have a rich desktop application, with all the features Windows programs provide, while clients can tap the database via any Web browser. The resulting database-driven application is secure, meets all of ATS’ initial requirements, and then some. The new Web site immediately reduced the number of inbound phone calls, while attracting exponentially more clients—including a younger demographic who prefer Web-based applications. Lastly, ATS’ site can be customized by anyone, without requiring programming skills. This empowers ATS to provide Web services to third-party organizations that want to use it for their own clientèle.

For More Information
Alpha Software
781-229-4500
AlphaSoftware.com

Attorneys Trust Document Service
800-341-4449
AttorneysTrust.com

AlphaToGo
650-759-4700
AlphaToGo.com

Want to share your story too? Feel free to contact Kate Ritchie from Alpha's PR team, at 610-642-8253 ext. 162, or KateR@GregoryFCA.com.

A PHP developer pops the question: Why do I need Alpha Five?

I received an e-mail from an application developer looking to build a robust, Web-based point-of-sale service. He needed an application that would allow customers to:

1. Enter their credit card details.
2. Complete a form.
3. Upload a small image.
4. Obtain a password and editing rights to their Web record.
5. Have a limited usage period of three months.
6. Have the option to renew for a longer time period.

These functions are fairly basic, and can be created using open source server-side tech such as PHP and MySQL. The question this developer wanted answered was, if he could do this easily with PHP and MySQL, why should he consider Alpha Five?

"Easily" is a relative term. But let's accept for the moment that the developer has the requisite PHP and MySQL coding skills.

The direct answer: speed of development. Former PHP developers among our customer base say they can build database applications with Alpha Five five to 10 times faster than with PHP.

It's all about productivity. Here's an screencast of a secure Web-based contact manager, with full reporting, created from scratch in under an hour using Alpha:

Part one
Part deuce

Can that be done, from scratch, in an hour, using PHP? I think not.

We've also posted a more detailed comparison of Alpha Five v. PHP here.

Speed of development (i.e., productivity) has always been a priority in application development. In today's fast-paced world, where people can barely wait in line for a cup of coffee without glancing at their watch every few seconds, speed is more important than ever.

And it's speed that we've worked like dogs to bake into Alpha Five v.8 -- and will continue to make a priority with every future version.

I have nothing against PHP, and Alpha Five works beautifully with MySQL. But when asked to compare the two, and asked why a developer should choose one over the other, it really does get down to one thing: speed. And the simple truth is, Alpha Five gets developers to the finish line faster.

Check out this video, which shows how full reporting can be added to your Web applications using the extensive power of the built in Alpha Five Reportwriter. Doing this in PHP would be much more difficult and time consuming and would require a third party reporting tool. In Alpha, reports are generated as PDFs, and are streamed to the client's browser.

Desperately Seeking SQL

Our VAR newsletter just ran an article I wrote about troubleshooting some common SQL issues in Alpha Five. I thought our user audience would also find this information useful, so I've posted it here on the blog.

Learning SQL and moving from row-based to set-based queries creates quite a challenge for developers. As if that were not enough, the complexities of connecting to different types of databases, and the possibility of (dare I say it) issues with Alpha Five itself can make the whole experience a challenge.

In this article, I hope to show enough of the query handling process to help the experienced Xbasic developer isolate issues -- saving you frustration, and saving all of us time resolving them.

Before launching into detail, one caveat should be discussed. Alpha Five makes it easy to write a SQL statement that works across databases. This would be hard to do by hand. It is even harder to anticipate every possible permutation of database behavior, but that is what we have attempted to do.

To make the problem simpler for ourselves, we use two sections of code that “know” how to handle a particular aspect of talking to your database. The driver is the software that knows how to call functions that execute SQL commands, and to manage result sets and data. The syntax handler is the software that knows how to retrieve schema information to generate SQL statements for a required vendor, and to map database types to Alpha Five types. When something goes wrong, it is usually in one of these two sections of code.

When using an ODBC driver with a database that does not have a specific syntax supported, there is always the potential that the default syntax handlers (“Generic” and “ODBC”) will encounter issues. Some of these are easily fixed, and others may require a custom syntax handler to resolve.

Key Areas to Look At
Problems can occur at several points along the way. While this article does not have room to cover each and every one, we will cover the flow of a SQL statement, and show some examples of code you can use to isolate the problems you may be experiencing in the more complex dialogs.

· Connecting to the database

· Getting schema information from the database

· Converting portable SQL to native syntax

· Getting the types of data returned from queries

· Reading data returned from queries

· Issues with identifying, handling and updating NULL columns

· Data mismatches in queries

The Flow of a Typical Script
There are several major steps required internally to handle the execution of a single SELECT statement from the point where we connect to the database to the retrieval of the data. Note that when you use portable SQL, an extra set of steps are required to convert the portable SQL to a syntax the database will accept.

1. Connect

2. If we are processing portable SQL:
a. Parse the SQL
b. Generate Native Syntax

3. Execute the Query

4. Describe the result set

5. Advance through the result set rows

6. Get the data for each column in the result set row

When something goes wrong, it is important to isolate the problem by proving what works and what doesn’t. For example, can you connect? Can you execute the query? Can you get a description of the result set? Is the error related to data retrieval?

While we can’t do an exhaustive treatment of troubleshooting techniques, the scripts in this article should give you some idea of how to reproduce problems you might encounter while using genies in the product, and to do some initial troubleshooting.

A few minutes in the interactive window will help you understand how Alpha Five processes SQL better, and can save us hours of trying to reproduce a problem you are encountering. If you are at a customer site, you will look like a hero! Keep this list handy…

Connecting to Your Database
This Xbasic script is all that is required to connect to a database. The reason the connection string is a "string," is that it can be replaced at run time to point to a different database. For this reason, you must tell the ODBC driver which syntax handler you want to use (although the dialog can often infer correctly).

dim c as sql::connection

?c.open(your connection string)

Remember that most SQL functions return .t. if all went well and .f. if there was an error. After each function call, the connection (and other SQL objects) remember the result of the last call. You can get this information by looking at the CallResult property -- which is an object itself. If there is an error, you can get the details by executing the following line of Xbasic:

?c.callresult.text

There are several common problems in connecting to a database. Most of the time, it is an issue with configuring the server or providing the proper information to Alpha Five:

1. Additional software to connect to the database is not installed. In most cases SQL Server, Oracle, MySQL, and Access will not require you to install any other database software to work properly with Alpha Five. With other servers, you must install and verify your database interface.

2. The database server, instance name, or port number do not match the configuration of your server.

3. You do not have a valid user ID, password, or do not have permission to establish a connection.

4. For file based systems, you have an incorrect path.

Getting a List of Tables
Assuming that you can connect to your database, the next question is whether you can get information about the database. This script is useful for getting a list of tables, and verifying that we are looking at the right database.

dim c as sql::connection

?c.open(your connection string)

?c.ListTables(.t., .t., .t., .t., .t.)

Executing a Native SQL Statement
Next, we want to verify that the database will respond to a SQL statement in native syntax -- the syntax the database knows how to interpret. For this example, you will need to have the name of a table with no spaces, and that is case insensitive. Execute the script below replacing the literal MYTABLE with the name of your table.

dim c as sql::connection

?c.open(your connection string here)
?c.Execute(“SELECT * from MYTABLE)

Executing a Portable SQL Statement
Portable SQL is a subset of SQL that Alpha Five understands, and uses syntax handlers to generate syntax required by various databases. Once we know that the database is accepting native SQL syntax, we can check to be sure that the syntax handler is correctly generating native syntax from the portable SQL you provide. While I use a trivial SQL statement in this example, it is likely that your SQL is far more complex, and will exercise the syntax handler in a different way. This is a common source of issues on new syntax handlers.

dim c as sql::connection

?c.open(your connection string)

c.PortableSQLEnabled = .t.

?c.Execute(“SELECT * from MYTABLE)

Note that the only change is the line “c.PortableSQLEnabled = .t.” Be sure not to put a question mark in front of this one. When this property is set to .t., the path on the SQL must be parsed and regenerated using the target syntax you have selected. If you get an error here, the functions that generate SQL may have an issue, or you may have a syntax error in your SQL.

Getting a Description of the Columns in the Query
If all goes well up until this point, it is probably a good idea to make sure that Alpha Five can get a good description of the data before attempting to read data values. You can add the following line to either of the scripts above to make sure that the data values from the query are types that are understood.

?c.ResultSet.DBFRowSyntax

This property of the result set must convert each data description from the query into an Alpha Five table description. If any of the types do not make sense (or if you get an error), then the problem is likely in attempting to get type information.

Getting a Query’s Data Values
Reading the individual columns from the query can often be the source of the problem. If you have made it this far in your testing, there is one more thing to test. Can you read and display the values of each of the columns? In this simple example, we want to read and display the values of two columns in two different rows. I’ll let you experiment for more complicated queries on your own.

dim c as sql::connection

?c.open(your connection string)

c.PortableSQLEnabled = .t.

?c.Execute(“SELECT * from MYTABLE)

?c.CallResult.NextRow()

?c.CallResult.Data(1)

?c.CallResult.Data(2)

?c.CallResult.NextRow()

?c.CallResult.Data(1)

?c.CallResult.Data(2)

As I said before, this list is hardly exhaustive, but it should get you started in working with SQL databases in Xbasic. Sending us the contents of the interactive window can make reproducing your issue here at Alpha a whole lot easier!