 | |||
|
|
||
| |||
| |||
| |||
|
||||||||||||||||||||
| |
|
Home > Static SQL vs. dynamic SQL for database application performance |
| |
|
|
|
|
| Chapter Download: |
|
||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Table of Contents
At the inception of relational database systems and into the 1980s, the only portable interface for applications was embedded SQL. At that time, there was no common function API such as a standards-based database API, for example, ODBC. Embedded SQL is SQL statements written within an application programming language such as C. These statements are preprocessed by a SQL preprocessor, which is database dependent, before the application is compiled. In the preprocessing stage, the database creates the access plan for each SQL statement. During this time, the SQL was embedded and, typically, always static. In the 1990s, the first portable database API for SQL was defined by the SQL Access Group. Following this specification came the ODBC specification from Microsoft. The ODBC specification was widely adopted, and it quickly became the de facto standard for SQL APIs. Using ODBC, SQL did not have to be embedded into the application programming language, and precompilation was no longer required, which allowed database independence. Using SQL APIs, the SQL is not embedded; it is dynamic. What is static SQL and dynamic SQL? Static SQL is SQL statements in an application that do not change at runtime and, therefore, can be hard-coded into the application. Dynamic SQL is SQL statements that are constructed at runtime; for example, the application may allow users to enter their own queries. Thus, the SQL statements cannot be hard-coded into the application. Static SQL provides performance advantages over dynamic SQL because static SQL is preprocessed, which means the statements are parsed, validated, and optimized only once. If you are using a standards-based API, such as ODBC, to develop your application, static SQL is probably not an option for you. However, you can achieve a similar level of performance by using either statement pooling or stored procedures. See "Statement Pooling," page 29, for a discussion about how statement pooling can improve performance.
Stored procedures are database dependent because each relational database system implements stored procedures in a proprietary way. Therefore, if you want your application to be database independent, think twice before using stored procedures. The Network The network, which is a component of the database middleware, has many factors that affect performance: database protocol packets, network packets, network hops, network contention, and packet fragmentation. See "Network," in Chapter 4 (page 86) for details on how to understand the performance implications of the network and guidelines for dealing with them. In this section, let's look at one important fact about performance and the network: database application performance improves when communication between the database driver and the database is optimized. With this in mind, you should always ask yourself: How can I reduce the information that is communicated between the driver and the database? One important factor in this optimization is the size of database protocol packets. The size of database protocol packets sent by the database driver to the database server must be equal to or less than the maximum database protocol packet size allowed by the database server. If the database server accepts a maximum packet size of 64KB, the database driver must send packets of 64KB or less. Typically, the larger the packet size, the better the performance, because fewer packets are needed to communicate between the driver and the database. Fewer packets means fewer network round trips to and from the database. For example, if the database driver uses a packet size of 32KB and the database server's packet size is configured for 64KB, the database server must limit its packet size to the smaller 32KB packet size used by the driver—increasing the number of packets sent over the network to return the same amount of data to the client (as shown in Figure 2-14).
Figure 2-14 Using different packet sizes This increase in the number of packets also means an increase in packet overhead. High packet overhead reduces throughput, or the amount of data that is transferred from sender to receiver over a period of time. You might be thinking, "But how can I do anything about the size of database protocol packets?"You can use a database driver that allows you to configure their size. See "Runtime Performance Tuning Options," page 62, for more information about which performance tuning options to look for in a database driver. The Database Driver The database driver, which is a component of the database middleware, can degrade the performance of your database application because of the following reasons:
See Chapter 3, "Database Middleware: Why It's Important," for a detailed description of how a database driver can improve the performance of your database application. In this section, let's look at one important fact about performance and a database driver: The architecture of your database driver matters. Typically, the most optimal architecture is database wire protocol. Database wire protocol drivers communicate with the database directly, eliminating the need for the database's client software, as shown in Figure 2-15.
Figure 2-15 Database wire protocol architecture Using a wire protocol database driver improves the performance of your database application because it does the following:
We go into more detail about the benefits of using a database wire protocol driver in "Database Driver Architecture," page 55. Know Your Database System You may think your database system supports all the functionality that is specified in the standards-based APIs (such as ODBC, JDBC, and ADO.NET). That is likely not true. Yet, the driver you use may provide the functionality, which is often a benefit to you. For example, if your application performs bulk inserts or updates, you can improve performance by using arrays of parameters. Yet, not all database systems support arrays of parameters. In any case, if you use a database driver that supports them, you can use this functionality even if the database system does not support it, which 1) results in performance improvements for bulk inserts or updates, and 2) eliminates the need for you to implement the functionality yourself. The trade-off of using functionality that is not natively supported by your database system is that emulated functionality can increase CPU use. You must weigh this trade-off against the benefit of having the functionality in your application. The protocol of your database system is another important implementation detail that you should understand. Throughout this chapter, we discussed design decisions that are affected by the protocol used by your database system of choice: cursor-based or streaming. Explanations of these two protocols can be found in "One Connection for Multiple Statements" on page 16. Table 2-3 lists some common functionality and whether it is natively supported by five major database systems.
Table 2-3 Database System Native Support
Using Object-Relational Mapping Tools Most business applications access data in relational databases. However, the relational model is designed for efficiently storing and retrieving data, not for the object-oriented model often used for business applications. As a result, new object-relational mapping (ORM) tools are becoming popular with many business application developers. Hibernate and Java Persistence API (JPA) are such tools for the Java environment, and NHibernate and ADO.NET Entity Framework are such tools for the .NET environment. Object-relational mapping tools map object-oriented programming objects to the tables of relational databases. When using relational databases with objects, typically, an ORM tool can reduce development costs because the tool does the object-to-table and table-to-object conversions needed. Otherwise, these conversions must be written in addition to the application development. ORM tools allow developers to focus on the business application. From a design point of view, you need to know that when you use object relational mapping tools you lose much of the ability to tune your database application code. For example, you are not writing the SQL statements that are sent to the database; the ORM tool is creating them. This can mean that the SQL statements could be more complex than ones you would write, which can result in performance issues. Also, you don't get to choose the API calls used to return data, for example, SQLGetData versus SQLBindCol for ODBC. To optimize application performance when using an ORM tool, we recommend that you tune your database driver appropriately for use with the database your application is accessing. For example, you can use a tool to log the packets sent between the driver and the database and configure the driver to send a packet size that is equal to the packet size of that configured on the database. See Chapter 4, "The Environment: Tuning for Performance," for more information. Summary Many factors affect performance. Some are beyond your control, but thoughtful design of your application and the configuration of the database middleware that connects your application to the database server can result in optimal performance. If you are going to design only one aspect of your application, let it be database connections, which are performance-expensive. Establishing a connection can take up to ten network round trips. You should assess whether connection pooling or one connection at a time is more appropriate for your situation. When designing your database application, here are some important questions to ask: Are you retrieving only the minimum amount of data that you need? Are you retrieving the most efficient data type? Would a prepared statement save you some overhead? Could you use a local transaction instead of a more performance-expensive distributed transaction? Lastly, make sure that you are using the best database driver for your application. Does your database driver support all the functionality that you want to use in your application? For example, does your driver support statement pooling? Does the driver have runtime performance tuning options that you can configure to improve performance? For example, can you configure the driver to reduce network activity? More on accessing data:
|
|
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
About Us | Contact Us | For Advertisers | For Business Partners | Site Index | RSS |
|
|
||
|
|||||||
