Cross-platform and Portable Development With PHP Page 6

Other niggles we encountered were:

• Needing to select only a part of the resultset from a query (see `Performance’ below) requires different SQL clauses to be used for each database.
• A day was wasted tracking down a difference between the databases where SQL Server stores DECIMAL types with a set of trailing NUL characters tacked on the end. When retrieved from the database by the version of PHP we were using, it would ignore the fractional part. A price like $1.23 was being retrieved as $1 from SQL Server; using MySQL we got the right value back.
• A small difference between PHP’s handling of strftime resulted in the %e format being expanded as a blank on the NT implementation. We had to use %d instead and adjust the result.

The goal of building code which would run without caring which database product it was accessing simply couldn’t be done cleanly enough for our taste. Extending it to even more databases would not be a pleasant prospect.

Performance

We’d love to say that we have done a lot of performance testing, but we haven’t. Apart from noticing that MySQL is a good deal nippier than SQL Server at most of the tasks, database performance hasn’t been a big concern. Our biggest performance problem was having to to deal (on the deployed system) with a WAN separating the web front-end from the database. Customers searching for particular types of component could easily generate queries that returned record sets with thousands of entries and the first version of the software was appallingly slow when that happened. By paging through record sets using MySQL’s limit and SQL Server’s equivalent of it we got up to an acceptable level of performance and that was enough.