Call pattern analysis (CPA) is the mapping of the SQL queries within an application system to the accessed tables and the subsequent analysis of those calls. CPA is a valuable tool for the database administrator (DBA) to utilize when making decisions regarding indexing and maintenance strategies.
Why do it?
As a DBA, one of the most important and challenging jobs is to turn a logical data model into a physical database design. In performing this task, one of the chief concerns is how the design decisions will affect the performance of the system.
One of the tools that a DBA can use is the index, but it is a double-edged sword. Indexes equal performance, which is good; however, indexes also equal overhead, which is bad. There needs to be a balance. Creating a CPA for the application and performing some analysis of the results will highlight where indexes are required, as well as some potential performance drawbacks. This analysis will also guide the DBA in making intelligent design decisions.
How to do it
The first step in creating a CPA is to work with application developers in order to understand the application involved. A DBA cannot work in a void and must understand the application.
Consider the type of application. The same table structures accessed by two different applications will deserve two different treatments. Is the application transactional or a data warehouse? The design decisions made regarding a query in these two environments will be different.
The best source of this information is the developers and analysts who are working closely with the user to determine the application requirements. With every application development effort, there is a wealth of written documentation. Below are some examples of documentation that can assist the DBA in gaining knowledge of the application system:
- Systems requirements documentation: These documents will give you an understanding of the functional specifications of the system.
- Architectural diagrams: These diagrams will give you insight as to how the application components communicate with each other and the database(s).
- Logical data models: These models will give you a better understanding of the database design.
Again, developing a strong relationship with the application developers will result in better information and, ultimately, a better design of the application and a more satisfied customer.
Now that you have some understanding of what the application is meant to do, you can start to document the SQL calls against the database tables. These SQL calls are actual database accesses of the applications. The information you have gathered from your research into the application is applied to these facts in order to make decisions about indexing.
Create a call pattern analysis matrix
Create a CPA by developing one matrix for each table in the application. The matrix is simply a list of the table fields along the x axis and a list of the SQL calls along the y axis. An example is shown in Listing A.
For example, in tab001t1 within this structure, the CPA matrix would look like what you see in Table A.
Gather application SQL
The next step is to gather the list of application SQL. This can be obtained from the developers. For each SQL call, document the activity against the table. For example, the query in Listing B is part of the application SQL.
The query in Listing B would be added to the call pattern analysis matrix for tab001t1 as per the line beside "Query1," highlighted in Table B.
When applying the analysis to the matrix, it is often easier to use codes such as:
- S – select
- W – used in where clause
- D – delete
- U – updated by query
- I – inserted
- J – used in a join
While adding the analysis to the matrix, you can use these codes to document the SQL calls against the table. Of course, any codes you prefer can be used. The main goal of the task is to get a picture of the activity the application will be performing against the table.
Discuss SQL call frequencies with developers
Now that the SQL calls are documented, discuss the SQL calls with the developers. Determine SQL call frequencies. SQL call frequencies are an important factor when making design decisions. For example, let's look at another SQL call on tab001t1, as shown in Listing C.
Table C shows where this call fits into the matrix.
At a glance, a decision might be made to add an index to tab001t1 on columns Col001 and Col004. This would allow the query to use an index with both columns to access the data. Now add the SQL frequency factor.
After speaking to the developer, we are now armed with the information that this query is only run during a yearly maintenance job. Given this information, our decision would change. We wouldn't want to add an index to a table and incur the overhead during the other 364 days of the year when the query isn't being run. Now, if the query was part of a transactional system and ran hundreds of times a day, then the overhead of the index is far outweighed by the improvement in query performance. This shows why frequency is such an important part of the analysis process.
Analyze the results
Armed with a wealth of knowledge in the documentation of the CPAs, your knowledge of the application, and the call frequencies, you can now perform the analysis. During your analysis, determine which columns to index and note any potential SQL inefficiencies. The CPA highlights potential index columns. Look for the columns that are used in the WHERE clauses. Now, apply your knowledge as a DBA to make decisions on the best makeup for the indexes on the table. Keep in mind the call frequencies.
Share the wealth
Once you have completed your call pattern analysis, share your final results with the development team. Developers should understand the database design on some level in order to take advantage of its design and write more efficient SQL. If, during your analysis, you discovered some potential SQL inefficiencies, review these with the development team and work with them to resolve the issues.
Performing a call pattern analysis prior to implementing an application may identify potential problems preventing performance problems, data integrity issues, and costly post-implementation changes. The knowledge you gain of the application and the tables will assist you in determining any physical design tuning that is needed. In addition, the information documented in the CPA can become a basis of knowledge that is helpful when creating the maintenance strategies, including backup and recovery.