When you create an application, you can improve performance in several ways, including:
For applications that send and receive many commands and replies, network overhead can be significant. Compound SQL and stored procedures are two ways to reduce this overhead.
If an application sends several SQL statements without intervening programming logic, you can use compound SQL. If programming logic is needed within the group of SQL statements, you can use stored procedures.
All executable statements except the following can be contained within a Compound SQL statement:
CALL
FETCH
CLOSE
OPEN
Compound SQL
Connect
Prepare
Release
Describe
Rollback
Disconnect
Set connection
execute immediate
See SQL Reference for more details.
For information about using compound SQL in an application, see NOT ATOMIC Compound SQL. For information about using compound SQL with the import utility, see Using Import and Export Utilities
Stored procedures help to reduce network traffic by placing program logic at the server. In DB2 prior to Version 5.0, a stored procedure could return output parameters only, and a separate commit command had to be issued by the application. This resulted in two network trips. In DB2 Versions 5.0 and above, you can commit automatically when exiting the procedure. You can also return results sets, which minimize application logic at the client.
For information about using stored procedures, see Stored Procedures.
Grouping related database requests (SQL statements) into one database request can reduce the number of requests and responses transmitted across the network. For example, grouping the following statements:
SELECT COL1, COL2, COL5, COL6 FROM TABLEA WHERE ROW_ID=1 SELECT COL1, COL2, COL5, COL6 FROM TABLEA WHERE ROW_ID=2
into
SELECT COL1, COL2, COL5, COL6 FROM TABLEA WHERE ROW_ID=1 OR ROW_ID=2
sends fewer requests across the network.
You can also use keywords such as IN and BETWEEN to reduce the number of rows returned. In addition, you can use WHERE, IN, and BETWEEN keywords on UPDATE and DELETE statements.
You can use predicate logic to request only the rows and columns that are needed. This minimizes the network traffic and CPU overhead for data transmission.
For example, do not use the query:
SELECT * FROM TABLEA
if only the first row of TABLEA with ROW_ID=1 is really needed or if only column 1 and column 2 are needed.
You should use data blocking if you expect large amounts of data from the server. Blocking improves the utilization of the network bandwidth and reduces the CPU overhead of both the host or AS/400 database server and the DB2 Connect workstation.
There is fixed amount of CPU and network overhead for each message sent and received regardless of size. Data blocking reduces the number of messages required for the same amount of data transfer.
With blocking, the first row of data from a query will not be delivered to the application until the first block is received. Blocking increases the retrieval time for the first row, but improves the retrieval time for subsequent rows.
Another consideration is the amount of memory that is used. The memory working set usually increases when blocking is turned on. For a complete discussion of blocking when using SNA connections, see the DRDA Connectivity Guide.
Within DB2 Connect, you can control the amount of data that is transferred within each block, as described in RQRIOBLK.
To invoke blocking, use the BLOCKING option of the prep or bind command. (For more information, see The BIND Command.) Blocking is on, if:
For the definitions of read-only, updateable, and ambiguous cursor, refer to the Application Development Guide.
| Note: | When using dynamic SQL, the cursor is always ambiguous. |
Updateable SELECT statements (using UPDATE/DELETE WHERE CURRENT OF statements) are non-blocking queries, so you should use them only when absolutely necessary.
An updateable SELECT ensures that the row has not changed between the time the SELECT is completed and the UPDATE/DELETE is issued. If this level of concurrency is not important to your application, an alternative is to use a DELETE or UPDATE with search criteria based on the values returned from a non-updateable SELECT.
For read-only SELECT, specify FOR FETCH ONLY (except under VM and VSE, where it is not supported).
Use static SQL as much as possible. It avoids run-time SQL section preparation and ambiguous cursors. If dynamic SQL cannot be avoided, you can do the following to minimize the network traffic and improve performance:
If the SQLDA allocation is not big enough to store the returning SQLDA, the program must issue another DESCRIBE with a big enough SQLDA to store the result again. This would increase the network traffic.
Do not use the PREPARE and DESCRIBE sequence. Using the PREPARE.....INTO statement provides better performance.
Using the command line processor is, in general, slower than having dynamic SQL in the program because the command line processor must parse the input before submitting the SQL to the database engine. The command line processor also formats data when it is received, which may not be necessary for your application.
SQL statements in an interpreted language (such as REXX) are substantially slower than the same SQL statements in a compiled language (such as C).
There are two types of CONNECT statement, called type 1 and type 2. With type 2 connect, connecting to a database puts the previous connection into a dormant state but does not drop it. If you later switch to a dormant connection, you avoid the overhead of loading libraries and setting up internal data structures. For this reason, using type 2 connect may improve performance for applications that access more than one database. For more information about type 2 connects, see the Administration Guide and the SQL Reference.