Writing Efficient Application Code

If you have taken the time to design your database with performance in mind (including setting up proper indexes and defining queries correctly), you are a long way toward your goal of a fast MySQL-based solution. If you have also factored concurrency considerations into your application design, so much the better.

In fact, at the point when you actually need to write code, there is very little left to be done regarding performance from a purely database perspective. Of course, if you don't understand your application development technology, and introduce new performance problems at the application code level, then all bets are off.

The remainder of this chapter provides some development technology-neutral ideas about how your application code can take advantage of several MySQL server-based performance features. Most of these ideas are explored elsewhere in the book: This bolsters the just- stated argument that by the time you reach the software development phase, most of the optimization work is finished.

Finally, because ODBC is an extremely popular way of connecting database-neutral applications with a MySQL server, this chapter closes with a review of how to configure the MySQL ODBC Connector for maximum performance.

General Suggestions

To provide language-specific ideas for only one or two of the myriad MySQL APIs or connectors would shortchange the developers that use the others. To supply language-specific ideas for all of these major MySQL-friendly development technologies would expand this book substantially beyond its scope. Happily, most performance-enhancing suggestions happen to be independent of your choice of API or connector.

Persistent Connections

Chapter 14, "Operating System, Web Server, and Connectivity Tuning," calls out the high costs of repeatedly creating and dropping connections to the database.

When designing your application, consider its typical database usage pattern. Will the application frequently access the database, or will it periodically fire a burst of information at the MySQL server and otherwise not need its services? The answer to this question has a fundamental impact on how you should structure your database connectivity.

For those applications that need constant access to the database, try to employ persistent connections. From the point of view of the application logic, this simply means not closing a session only to reopen it a few moments later. From the server perspective, Chapter 10, "General Server Performance Parameters and Tuning," describes several variables that you can set to prevent idle sessions from being disconnected.

Prepared Statements

When you send a SQL statement to a MySQL database server, it must parse the statement to make sure that it contains valid syntax and is acceptable for use. This parsing exercise incurs overhead, whether the same statement is run once, twice, or three million times. To help reduce this overhead, MySQL 4.1 saw the introduction of prepared statement support.

When you issue a PREPARE directive combined with a candidate SQL statement, MySQL analyzes and validates its syntax. Assuming that all is well, you can then invoke that statement as often as you want via the EXECUTE statement. Because the statement has already been parsed, there is no need for MySQL to conduct the same exercise again.

For example, suppose you are writing an application that will update the purchases table that was shown previously. You expect the application to be run frequently by a large number of users, with a common activity being an update of the municipal_tax field, consistently using the following SQL statement:

 UPDATE purchases SET municipal_tax = some value WHERE purchase_id = some value; 

This is an excellent candidate for statement preparation, using the following steps. Note that although it is displayed here in standard SQL, it can also be handled through most of the development technologies currently used to build MySQL-based applications:

 PREPARE prep_update FROM 'UPDATE purchases SET municipal_tax = ?  WHERE purchase_id = ?'; SET @new_muni_tax = 12.22; SET @purchase_identifier = 200112; EXECUTE prep_update USING @new_muni_tax, @purchase_identifier; 

From this point forward, the only necessary steps are to change the variables and rerun the EXECUTE statement. This reduces overhead, as well as takes advantage of a faster, binary-based client/server protocol that is available to clients using prepared statements. You should note, however, that MySQL still must generate a query plan; this extraneous work should be addressed in an upcoming version.

Multirow INSERT Statements

Large-scale data export and import operations are the focus of Chapter 15, "Improving Import and Export Operations." For now, it's worth mentioning that grouping INSERT statements into multirow batches can deliver significant speed improvements, especially for larger data sets.

Again referring to the previous purchases table, the following is how a five-row INSERT would look when batched into a multirow statement:

 INSERT INTO purchases(iata_code,amount,federal_tax, province_tax,municipal_tax,vat) VALUES ('SLC',17.42,.21,.03,0,0),('YYC',19.12,.33,.12,0,.09), ('ANC',33.99,1.12,.88,0,0),('ABQ',102.00,8.02,5.77,0,0), ('ARN',218.31,22.55,13.98,.74,11.00); 

Although tedious to type out by hand, an application should have no trouble programmatically generating, and then benefiting from, this kind of statement.

ODBC Configuration

Before the introduction of the Open Database Connectivity (ODBC) standard in the early 1990s, application developers faced the odious choice of either developing specialized versions of their products that worked with databases from different vendors or creating their own database-neutral connectivity layer.

Fortunately, the ODBC standard, along with ancillary products that conformed to this new approach, greatly improved the lives of developers by freeing them from having to worry about the idiosyncrasies of each database platform. However, differences do remain among these platforms, and the combination of these disparities and the ODBC standard can affect responsiveness for applications that use the MySQL ODBC driver.

As a MySQL developer or administrator, you have the ability to affect some of these ODBC performance aspects via the MyODBC driver. While most of its parameters are aimed at compatibility issues, some of them do affect performance, as shown in Table 9.1.

Table 9.1. MySQL ODBC Driver Performance-Related Settings

Setting	Performance Implication
Use Compressed Protocol	This book explores the benefits of compressing the communication between database clients and the MySQL server in Chapters 10 and 14. Enable this setting if you want your ODBC-based applications to enjoy the same benefits.
Don't Cache Results	Generally, MySQL attempts to cache the results from your forward-only cursor so that they remain resident in memory. This is usually faster than forcing a call to the server to return rows. However, if your goal is a dynamic cursor or your resultset is likely to be very large, disabling result caching can conserve client-side memory.
Force Use of Named Pipes	Named pipes (for Windows-based servers) or sockets (for Linux/Unix-based servers) offer a potentially faster alternative to standard TCP/IP for communication between a MySQL database server and its associated client when the two are running on the same machine.

One final word about ODBC and MySQL performance: Be sure that ODBC driver tracing is turned off. Otherwise, the cost of generating the extensive diagnostic information provided during tracing can easily translate into an orders-of-magnitude response degradation.