System and method for SQL performance assurance services

• US 8,930,918 B2
• Filed: 05/18/2011
• Issued: 01/06/2015
• Est. Priority Date: 05/18/2010
• Status: Active Grant

• Current Assignee: TATA Consultancy Services Limited (Tata Sons Pvt Ltd.)
• Original Assignee: TATA Consultancy Services Limited (Tata Sons Pvt Ltd.)
• Inventors: Tendulkar, Dattatraya Mohan
• Primary Examiner(s): Das, Chameli
• Application Number:
  US13/697,619
• Publication Number:
  US 20130067440A1
• Time in Patent Office: 1,329 Days
• US Class Current: 717/134
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 8/70 Software maintenance or management

Abstract

The present invention relates to a field of evaluating the performance of a Structure Query Language (SQL) in information system. In particular, the invention proposes system and method of integration of a tool in Software Development Life Cycle (SDLC) which assures performance of SQL by estimating an execution time and remote installation of the same in a cloud based testing services. So, it can be used to detect the queries which may not perform well in the production environment.

Claims

1. A system for assuring performance of a plurality of structure query language (SQL) statements, the system comprising:

a memory comprising instructions;

a processor coupled to the memory, where the processor is configure to execute the instructions to perform a method comprising;

capturing a volume baseline from a production database, wherein the volume baseline indicates a plurality of production database statistics;

retrieving an empty schema of the production database;

creating an emulated environment based on the production database statistics and the empty schema, wherein the emulated environment is created for testing the performance of each of the plurality of SQL statements to be executed in the production database;

executing the plurality of SQL statements in the emulated environment to forecast an execution time required by each of the plurality of SQL statements in the production database, wherein the execution time is forecasted using a formula; and

optimizing an execution plan of at least one of the plurality SQL statements by tuning the at least one of the plurality of SQL statements, wherein the at least one of the plurality of SQL statements is tuned based on the execution time thereby assuring the performance of each of the plurality of SQL statements.

2. The system of claim 1, wherein the execution time is forecasted based on test results of the emulated environment.

3. The system of claim 1, wherein the assuring the performance identifies performance problems before deployment in the production database.

4. The system of claim 1, wherein the emulated environment is a virtual testing platform created by means of a cloud computing.

5. The system of claim 1, wherein the at least one of the plurality of SQL statements is tuned by making necessary changes in the at least one of the plurality of SQL statements.

6. The system of claim 1, wherein the plurality of production database statistics comprises CPU speed, multi block read count, single block read count number of rows, average row length, and distinct values.

7. The system of claim 1, wherein the formula comprises

8. A method for assuring performance of a plurality of structure query language (SQL) statements, the method comprising:

capturing, by a processor, a volume baseline from a production database, wherein the volume baseline indicates a plurality of production database statistics;

retrieving, by the processor, an empty schema of the production database;

creating, by the processor, an emulated environment based on the production database statistics and the empty schema, wherein the emulated environment is created for testing the performance of each of the plurality of SQL statements to be executed in the production database;

executing, by the processor, the plurality of SQL statements in the emulated environment to forecast an execution time required by each of the plurality of SQL statements in the production database, wherein the execution time is forecasted using a formula; and

optimizing, by the processor, an execution plan of at least one of the plurality of SQL statements by tuning the at least one of the plurality of SQL statements, wherein the at least one of the plurality of SQL statements is tuned based on the execution time thereby assuring the performance of the plurality of SQL statements.

9. The method of claim 8, wherein the emulated environment is a virtual testing platform created by means of a cloud computing.

10. The method of claim 8, wherein the assuring the performance identifies performance problems before deployment in the production database.

11. The method of claim 8, wherein the at least one of the plurality of SQL statements is tuned by making necessary changes in the at least one of the plurality of SQL statements.

12. The method of claim 8, wherein the plurality of production database statistics comprises CPU speed, multi block read count, single block read count number of rows, average row length, and distinct values.

13. The method of claim 8, wherein the formula comprises