Template based database analyzer

Patent metadata

Field	Value
Publication number	US-9311345-B2
Application number	US-201213648043-A
Country	US
Kind code	B2
Filing date	Oct 9, 2012
Priority date	Oct 9, 2012
Publication date	Apr 12, 2016
Grant date	Apr 12, 2016

Abstract

An automated database analyzer is uses templates to accommodate multiple database languages, such as SQL and its dialects. The templates are combined with metadata that define a database schema and operations on the database schema. An SQL file instantiates the database schema on a database system being tested. Operations on the database schema may then be performed to assess the performance of the database system being tested.

First claim

What is claimed is:

1. A method for assessing a plurality of database (DB) systems comprising:
identifying a plurality of DB systems, each implemented using a different set of hardware environments and application requirements, the plurality of DB systems include at least one in-memory database;
for each DB system in the plurality of DB systems, storing a plurality of template files, wherein each template file in the plurality of template files describes a different set of grammar and syntax for one or more DB statements of a DB language corresponding to the DB system and each template file comprises a plurality of DB statements expressed in the DB language of its corresponding DB system, wherein at least one of the DB statements is configured to define a DB schema in the corresponding DB system using placeholders in place of actual names of data tables that comprise the DB schema;
for a given DB system:
receiving a template file from among the plurality of template files that corresponds to the given DB system;
receiving a metadata file that contains metadata for a DB schema including metadata for one or more DB tables of the DB schema and metadata for data fields of the DB tables;
generating DB statements using the template file and the metadata file, including substituting placeholders in the template file with actual names from the metadata file according to the grammar and syntax specified in the template file;
creating first DB statements configured to create an instance of the DB schema and second DB statements configured to perform operations on the DB tables of the DB schema;
instantiating the DB schema on the DB system by executing the first DB statements;
executing the second DB statements to perform operations on the DB tables of the DB schema; and
reporting on the DB system's performance of execution of the second DB statements, including memory usage of the DB system when executing the second DB statement; and

comparing performance of each DB system in the plurality of DB systems for analysis by a user.

2. The method of claim 1 wherein the first DB statements insert data values into the DB tables.

3. The method of claim 1 further comprising obtaining data values to be inserted into the DB tables of the DB schema, wherein the first DB statements include DB statements that insert the data values into the DB tables.

4. The method of claim 3 further comprising receiving the data values from an existing database system.

5. The method of claim 3 wherein at least some of the data values are randomly generated.

6. The method of claim 1 further comprising collecting performance data from the DB system during execution of the second DB statements.

7. The method of claim 1 further comprising repeating execution of the second DB statements one or more times and reporting on the DB system's performance of executing the second DB statements multiple times.

8. The method of claim 1 wherein the metadata file is one among a plurality of predefined metadata files.

9. The method of claim 1 wherein the DB statements include third DB statements that delete the DB schema from the DB system, the method further comprising deleting the DB schema from the DB system by executing the third DB statements subsequent to executing the second DB statements.

10. The method of claim 1 , wherein the performance of each DB system in the plurality of DB system is measured during a single run of the second DB statements.

11. A system for assessing a plurality of database (DB) systems comprising:
a computer system; and
a data storage system having stored thereon computer executable program code, which, when executed by the computer system, causes the computer system to:
identify a plurality of DB systems, each implemented using a different set of hardware environments and application requirements, the plurality of DB systems include at least one in-memory database;
for each DB system in the plurality of DB systems, store a plurality of template files, wherein each template file in the plurality of template files describes a different set of grammar and syntax for one or more DB statements of a DB language corresponding to the DB system and each template file comprises a plurality of DB statements expressed in the DB language of its corresponding DB system, wherein at least one of the DB statements is configured to define a DB schema in the corresponding DB system using placeholders in place of actual names of data tables that comprise the DB schema;
for a given DB system:
receive a template file from among the plurality of template files that corresponds to the given DB system;
receive a metadata file that contains metadata for a DB schema including metadata for one or more DB tables of the DB schema and metadata for data fields of the DB tables;
generate DB statements using the template file and the metadata file, including substituting placeholders in the template file with actual names from the metadata file according to the grammar and syntax specified in the template file;
create first DB statements configured to create an instance of the DB schema and second DB statements configured to perform operations on the DB tables of the DB schema;
instantiate the DB schema on the DB system by executing the first DB statements;
execute the second DB statements to perform operations on the DB tables of the DB schema; and
report on the DB system's performance of execution of the second DB statements, including memory usage of the DB system when executing the second DB statement; and

compare performance of each DB system in the plurality of DB systems for analysis by a user.

12. The system of claim 11 wherein the first DB statements insert data values into the DB tables.

13. The system of claim 11 wherein the computer executable program code further causes the computer system to obtain data values to be inserted into the DB tables of the DB schema, wherein the first DB statements include DB statements that insert the data values into the DB tables.

14. The system of claim 13 wherein at least some of the data values are randomly generated.

15. The system of claim 13 wherein the computer executable program code further causes the computer system to collect performance data from the DB system during execution of the second DB statements.

16. A non-transitory computer readable storage medium for assessing a database (DB) system having stored thereon computer executable program code, which, when executed by a computer system, causes the computer system to perform steps of:
identifying a plurality of DB systems, each implemented using a different set of hardware environments and application requirements, the plurality of DB systems include at least one in-memory database;
for each DB system in the plurality of DB systems, storing a plurality of template files, wherein each template file in the plurality of template files describes a different set of grammar and syntax for one or more DB statements of a DB language corresponding to the DB system and each template file comprises a plurality of DB statements expressed in the DB language of its corresponding DB system, wherein at least one of the DB statements is configured to define a DB schema in the corresponding DB system using placeholders in place of actual names of data tables that comprise the DB schema;
for a given DB system:
receiving a template file from among the plurality of template files that corresponds to the given DB system;
receiving a metadata file that contains metadata for a DB schema including metadata for one or more DB table

Assignees

• Sap Ag
• Sap Se

Inventors

• Chen Yingyu
• Xu Xin
• Zhou Xiwei
• Gu Jing

Classifications

• G06F16/217 Primary

  Database tuning (G06F16/2282 takes precedence; database performance monitoring G06F11/3409) · CPC title

• G06F16/2428

  Query predicate definition using graphical user interfaces, including menus and forms (G06F16/2423 takes precedence) · CPC title

• G06F17/30398

  Physics · mapped topic

• G06F17/30306 Primary

  Physics · mapped topic