Method to measure stored procedure execution statistics

• US 7,340,650 B2
• Filed: 01/14/2003
• Issued: 03/04/2008
• Est. Priority Date: 10/30/2002
• Status: Active Grant

• Current Assignee: JP Morgan Chase Bank, N.A. (JP Morgan Chase & Co)
• Original Assignee: JP Morgan Chase & Co
• Inventors: Westmacott, Jago R.
• Primary Examiner(s): Wilson; Yolanda L
• Application Number:
  US10/341,841
• Publication Number:
  US 20040088278A1
• Time in Patent Office: 1,876 Days
• Field of Search: 714/38
• US Class Current: 714/38.14
• CPC Class Codes: G06F 11/3409 for performance assessment
• CPC Class Codes: G06F 11/3452 Performance evaluation by statistical analysis
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409

Abstract

The invention is a method to obtain actual stored procedure execution statistics while an overlying application is running. The method is accomplished by a software module running in background and acquires the execution statistics, including database writes and reads, for every stored procedure run. Statistics, such as the number of database writes and reads, are reported by the application'"'"'s underlying database supervisory program. The resulting execution statistics are analyzed and selected information is saved to a stored procedure statistics database. Because the module can be run continuously in background, it can develop statistics for all procedure calls made under varying computing conditions with varying calling parameter sets. A statistical analysis of such acquired stored procedure execution statistics is useful for evaluating new versions of the complex software application, new stored procedures, modified stored procedures, and for locating a specific cause of application failure or reduced performance as caused by a malformed or malfunctioning stored procedure. Additionally, the method can flag anomalous performance, warning of previously undetected failure.

Claims

1. A method of evaluating the performance of a business or financial software application comprising the steps of:

providing a stored procedure to interact with a database;

executing the stored procedure in a stored procedure execution time;

querying the database for the stored procedure execution statistics including reads and writes of the database;

analyzing the stored procedure execution statistics to generate a set of stored procedure execution statistics based on many runs;

evaluating the analysis by comparison to a set of predicted or previously recorded execution statistics; and

modifying the stored procedure when the evaluation shows the performance has degraded as compared to the predicted or previously recorded statistics.

2. The method according to claim 1 further comprising the step of recording the stored procedure execution statistics to the database.

3. The method according to claim 1 wherein the stored procedure statistics include the reads and writes of the database.

4. The method according to claim 1 further comprising the step of flagging a problematic stored procedure when its number of reads and writes exceeds a computed value derived from past statistical analysis.

5. The method according to claim 1 further comprising the step of flagging a problematic stored procedure when its execution time exceeds a predicted value.

6. The method according to claim 1 wherein the stored procedure statistics include the execution time for the stored procedure.

7. The method according to claim 1 wherein the database is Sybase.

8. The method according to claim 7 further comprising the step of returning the stored procedure execution time from an error handler in the Sybase database.

9. The method according to claim 1 further comprising the step of evaluating the statistical analysis during regression testing.

10. The method according to claim 1 further comprising the step of evaluating the statistical analysis following a change to stored procedure.

11. The method according to claim 1 further comprising the step of evaluating the statistical analysis following the addition of a new stored procedure to the application software.

12. The method according to claim 1 further comprising the step of flagging a problematic stored procedure when its execution time exceeds a computed value derived from past statistical analysis.

13. A method of evaluating the performance of a business or financial software application comprising the steps of:

providing a stored procedure to interact with a database;

starting a timer;

executing the stored procedure in a stored procedure execution time;

stopping the timer;

reading the timer to register the total execution time from call to returned data;

querying the database for the stored procedure execution statistics including the number of reads and writes of the database;

analyzing the stored procedure execution statistics and total execution time to generate a set of stored procedure execution statistics based on many runs;

evaluating the analysis by comparison to a set of predicted or previously recorded execution statistics; and

modifying the stored procedure when the evaluation shows the performance has degraded as compared to the predicted or previously recorded statistics.

14. A method of evaluating the performance of a business or financial software application in a simulated trading environment comprising the steps of:

providing a stored procedure to interact with a database;

providing client terminals programmed to simulate normal trading activities on a network;

performing a regression test caused by simulated trades from multiple terminals;

executing stored procedures according to the simulated trades;

querying the database for the stored procedure execution statistics including the number of reads and writes of the database;

analyzing the stored procedure execution statistics to generate a set of stored procedure execution statistics based on many runs;

evaluating the analysis by comparison to a set of predicted or previously recorded execution statistics; and

modifying the stored procedure when the evaluation shows the performance has degraded as compared to the predicted or previously recorded statistics.

15. The method according to claim 14 further comprising re-running the steps of the method following a change to a stored procedure.

16. A method of evaluating the performance of a business or financial software application comprising the steps of:

providing a stored procedure to interact with a database;

executing the stored procedure in a stored procedure execution time;

querying the database for the stored procedure execution statistics including reads and writes of the database;

returning the stored procedure execution time from an error handler in the database;

analyzing the stored procedure execution statistics to generate a set of stored procedure execution statistics based on many runs;

evaluating the analysis by comparison to a set of predicted or previously recorded execution statistics; and

modifying the stored procedure when the evaluation shows the performance has degraded as compared to the predicted or previously recorded statistics.