JP2002149652A - Method for processing database retrieval, its execution device and recording medium stored with its processing program - Google Patents

Abstract

PROBLEM TO BE SOLVED: To provide a technique capable of detecting whether an SQL defined while masked like a script exerts influence on retrieval performance before performing its database retrieval processing to inform a user of this. SOLUTION: The name of a table and the name of a column being retrieving objects are obtained from an SQL generated from an API to be used for database retrieval. When the name of the table and the name of the column being the obtained retrieving objects exist in a table structure information file showing the name of a column with an index, the absence of influence on retrieval performance under the retrieving condition of the SQL generated from the input value of the API is outputted to an output device. When the name of the table and the name of the column being the obtained retrieving objects never exist in the table structure information file, the presence of influence on retrieval performance under the retrieving condition of the SQL generated from the input value of the API is outputted to the output device.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a database search processing apparatus for searching a relational database using SQL, and more particularly to a technique which is effective when applied to a database search processing apparatus for specifying a performance deterioration factor in a database search. is there.

[0002]

2. Description of the Related Art In a conventional relational database system, an SQL (Structured) which is a language or a command for operating the relational database system is used.
QueryLanguage) is used to search the database.

[0003] This SQL is a data definition language DDL (Dat
a Description Language), DML (Da
ta Manipulation Language), Data Control Language DCL
(Data Control Language), and commands and the like corresponding to each process are prepared. This S
One of the features is that the grammar of QL is similar to English, and the meaning of the command is relatively easy to understand.
Since grammar must be learned in order to use QL, various scripts in which SQL is hidden by an application programming interface (API) are used to reduce the burden.

[0004] For example, it is possible to search a database using SQL that specifies search contents as a script,
A database search method and a database management system for improving the search efficiency are disclosed in JP-A-11-32.
No. 8190. The outline is that, in order to improve the search efficiency, a plurality of SQL contents used for searching the relational database are defined as scripts, and the user selects a script suitable for the purpose from the group of scripts, and the user can transfer the contents to the relational database. A search is performed.

[0005]

In the above prior art, the search efficiency is improved by allowing the user to select a script. However, the SQL defined in the selected script affects the search performance. Since there is no consideration of a method of detecting in advance and giving a notice to the user, the user is required to define the SQL unless the search is actually performed on the relational database using a script. It may not be possible to detect that the search performance is affected, and the user may unconsciously execute a search process with a high processing load such as a search that does not use an index.

SUMMARY OF THE INVENTION An object of the present invention is to solve the above-mentioned problem and detect whether or not SQL defined in a concealed manner like a script affects search performance before executing a database search process, and provide a user with the information. It is to provide a technology capable of notifying.

[0007]

According to the present invention, there is provided a database search processing apparatus for specifying a performance deterioration factor in a database search.
This is to output to the output device whether or not the SQL generated from the template SQL corresponding to the PI affects the search performance of the database search.

[0008] In the database search processing device of the present invention,
First, an input value required for calling an API used in a database search is input from a user, set as an input value to a process corresponding to the API, and a process corresponding to the API is called.

When a process corresponding to the API is called,
SQL of the template SQL corresponding to the called API
An SQL corresponding to the API is generated from the set input values obtained from the information file and the obtained template SQL.

Then, a table name and a column name to be searched are obtained from the generated SQL, and a table structure information file indicating a column name with an index is obtained.
Check whether the obtained table name and column name of the search target exist, and if the table name and column name of the search target exist in the table structure information file, from the input value of the API, Generated SQL
Output to the output device that there is no effect on the search performance due to the search condition. If the table name and the column name of the search target do not exist in the table structure information file, the search condition is generated from the input value of the API. The fact that there is an effect on the search performance due to the SQL search condition is output to the output device.

As described above, according to the database search processing device of the present invention, the input value set in the API and the API
Is output to the output device whether or not the SQL generated from the template SQL corresponding to the XML has an influence on the search performance of the database search, so that the SQL defined by being hidden like a script affects the search performance. It is possible to detect whether or not the database search process is executed before notifying the user.

[0012]

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS A database search processing apparatus according to an embodiment for specifying a performance deterioration factor in a database search will be described below.

FIG. 1 is a diagram showing a schematic configuration of a database search processing device according to the present embodiment. As shown in FIG. 1, the database search processing device of the present embodiment
SQL generation unit 106, search condition analysis unit 107,
An improvement plan search unit 108 is provided.

The business processing unit 103 inputs an input value required for calling an API used in a database search, sets the input value as an input value to a process corresponding to the API, and calls a process corresponding to the API. It is.

The SQL generation unit 106 includes the business processing unit 103
Template SQL corresponding to the API called from SQL
The information corresponding to the API is obtained from the information file 109 and obtained from the set input values and the obtained template SQL.
This is a processing unit that generates QL.

The search condition analyzer 107 obtains a table name and a column name to be searched from the generated SQL, and stores the table name of the obtained search target in a table structure information file 110 indicating a column name having an index. And when the column name exists, it outputs to the monitor device 102 that there is no influence on the search performance due to the SQL search condition generated from the API and the input value, and the table structure information file 110 If the search target table name and column name do not exist,
This is a processing unit that outputs to the monitor device 102 that the search performance of the SQL search condition generated from the PI and the input value affects search performance.

The improvement plan search unit 108 generates the generated S
An improvement possibility information corresponding to the API is stored in an improvement proposal information file 111 that stores an improvement possibility determination condition, which is a condition for determining whether QL improvement is possible, and an improvement plan when the improvement possibility determination condition is satisfied. A processing unit that acquires a determination condition and an improvement plan, and outputs the improvement plan corresponding to the improvement possibility determination condition to the monitor device 102 when the acquired table name and column name match the improvement possibility determination condition. is there.

The database search processing device is a business processing unit 1
03, a program for functioning as the SQL generation unit 106, the search condition analysis unit 107, and the improvement plan search unit 108 is recorded on a recording medium such as a CD-ROM, stored on a magnetic disk or the like, and then loaded into a memory. Shall be executed. The recording medium for recording the program is C
A recording medium other than the D-ROM may be used.

The database search processing apparatus according to the present embodiment includes a business processing unit 103 created by a user and an evaluation library unit 104 in which an API called from the business processing unit 103 is defined. The evaluation library unit 104
An interface unit 105 that is an API called by the business processing unit 103, an SQL generation unit 106 that generates SQL from the API called by the business processing unit 103, a search condition analysis unit 107 that analyzes search conditions of the generated SQL,
It comprises an improvement plan search unit 108 for searching for an improvement plan for the API called by the business processing unit 103.

[0020] Further, the database search processing device includes a SQ
L information file 109 and table structure information file 1
10 and an improvement plan information file 111, an input operation device 101 such as a keyboard and a mouse for giving an operation instruction, and a CRT (Cathode Ray) for displaying analysis results of search conditions and improvement plans in particular on a screen. Tube) and a monitor device 102 using a liquid crystal display or the like.

FIG. 2 is a flowchart showing a processing procedure of the business processing unit 103 of this embodiment. As shown in FIG. 2, when calling the API (here, func_A) used in the database search, the business processing unit 103 according to the present embodiment executes the func_A in step 201.
After the user inputs and sets the input values necessary for calling the function, the processing for func_A defined in the interface unit 105 is called in step 202.

FIG. 3 shows the interface unit 10 of the present embodiment.
9 is a flowchart showing a processing procedure of No. 5; As shown in FIG. 3, the interface unit 105 of the present embodiment performs the processing of the SQL generation unit 106 in step 301, and executes the SQ from the API name func_A called from the business processing unit 103.
Generate L. Next, in step 302, the search condition analysis unit 1
07, the SQL generated in step 301
Analyze the search condition of. Then, in step 303, the improvement plan search unit 108 performs processing to search for an improvement plan for func_A.

FIG. 4 is a flowchart showing a processing procedure of the SQL generation unit 106 of the present embodiment. As shown in FIG. 4, the SQL generation unit 106 of the present embodiment acquires the template SQL corresponding to the API name func_A from the SQL information file 109 in step 401, and then proceeds to step 402, where the input value set in step 201 is Step 402
Corresponding to func_A from the template SQL acquired in step
Generate L.

FIG. 5 shows the SQL information file 1 according to this embodiment.
It is a figure showing the example of contents of 09. As shown in FIG.
The L information file 109 includes an API name 701 called from the business processing unit 103 and a template SQL corresponding thereto.
702 are stored. For example, “f” of the API name 701
In “unc_A”, “select” is added to the template SQL 702.
Input value 1 from Table 1 where Input value 2; "is stored.

FIG. 6 shows a search condition analysis unit 107 of this embodiment.
6 is a flowchart showing the processing procedure of FIG. In the processing of the search condition analysis unit 107 shown in FIG. 6, it is determined whether or not the search performance is affected by checking whether or not the table name and the column name of the search target exist in the table structure information file 110. The following describes a processing procedure for determining whether or not there is an effect on search performance by checking whether or not the condition for improvement is determined according to FIG. 9 and outputting the determination result to the monitor device 102. It is good to do.

First, at step 501, the search condition analysis unit 107 of the present embodiment generates the func generated at step 402.
The table name and the column name to be searched are acquired from the SQL corresponding to _A.

In step 502, the table name and the column name of the search target acquired in step 501 are the table structure information file 1 indicating the indexed column name.
10 to check whether the acquired table name and column name are in the table structure information file 11.
If the number is 0, the process proceeds to step 503; otherwise, the process proceeds to step 504.

FIG. 7 is a diagram showing an example of the contents of the table structure information file 110 according to this embodiment. As shown in FIG. 7, the table structure information file 110 stores a table name 801 to be searched and an indexed column name 802 indicating an indexed column among the columns belonging to the table. For example, “Table 1” of the table name 801 stores “ID” in the column name with index 802, which indicates that the column “ID” of “Table 1” has an index.

In step 503, the fact that the search condition of the SQL generated from func_A and the input value does not affect the search performance is output to the monitor device 102. That is, the fact that the acquired table name and column name exist in the table structure information file 110 means that the search using the index is performed, and the index is more efficient (the performance is not deteriorated). This is because it is determined that the search is performed.

At step 504, the fact that the search condition of the SQL generated from func_A and the input value has an effect on the search performance is output to the monitor device 102. This means that the absence of the acquired table name and column name in the table structure information file 110 means that a search is performed without using an index, and that the processing load is high and the efficiency is low (performance degradation is low). This is because it is determined that the search will be performed.

FIG. 8 is a flowchart showing the processing procedure of the improvement plan search unit 108 of this embodiment. As illustrated in FIG. 8, the improvement plan search unit 108 of the present embodiment acquires the improvement possibility determination condition and the improvement plan corresponding to func_A from the improvement plan information file 111 in the process of step 601. In step 602, it is checked whether or not the input value to func_A matches the improvement possibility determination condition. If the input value matches the improvement possibility determination condition, the process proceeds to step 603, and func_A
The improvement plan corresponding to _A is output to the monitor device 102.

FIG. 9 shows an improvement plan information file 1 according to this embodiment.
FIG. 11 is a diagram showing an example of contents of the eleventh embodiment. As shown in FIG. 9, the improvement plan information file 111 includes an API name 901 called from the business processing unit 103, an improvement possibility determination condition 902 indicating a condition for determining whether improvement is possible, and an improvement possibility. An improvement plan 903 in the case where it is determined that there is an improvement plan is stored. For example, “func_A” of the API name 901 includes
“No ID column exists in input value 2” and “Include ID column in input value 2” are stored in the improvement possibility determination condition 902 and “Include ID column in input value 2” in improvement proposal 903, and “ID column does not exist in input value 2”. If the improvement possible / impossible judgment condition 902 is satisfied, it indicates that the improvement plan 903 of "Include an ID column in the input value 2" is displayed.

As described above, according to the present embodiment, the effect on the performance of the SQL used by the API called from the business processing unit 103 for the search is detected in advance without actually searching the relational database, and the improvement is achieved. If a proposal exists, the user can also be notified.

As described above, according to the database search processing device of this embodiment, the SQ generated from the input value set in the API and the template SQL corresponding to the API
Before the execution of the database search process, whether or not SQL defined by concealing like a script affects the search performance is output to the output device as to whether or not L affects the search performance of the database search. And notify the user.

[0035]

According to the present invention, whether the SQL generated from the input value set in the API and the template SQL corresponding to the API affects the retrieval performance of the database retrieval is output to the output device. Before the execution of the database search process, it is possible to detect whether or not SQL defined in a hidden manner such as a script affects search performance and notify the user.

[Brief description of the drawings]

FIG. 1 is a diagram illustrating a schematic configuration of a database search processing device according to an embodiment.

FIG. 2 is a flowchart illustrating a processing procedure of a business processing unit 103 according to the embodiment.

FIG. 3 is a flowchart illustrating a processing procedure of an interface unit according to the embodiment.

FIG. 4 is a flowchart illustrating a processing procedure of an SQL generation unit 106 according to the embodiment.

FIG. 5 is a diagram showing an example of the contents of an SQL information file 109 according to the embodiment.

FIG. 6 is a flowchart illustrating a processing procedure of a search condition analysis unit 107 according to the embodiment.

FIG. 7 is a table structure information file 110 according to the embodiment;
It is a figure showing the example of contents of.

FIG. 8 is a flowchart illustrating a processing procedure of an improvement plan search unit according to the present embodiment.

FIG. 9 is a diagram showing an example of the contents of an improvement plan information file 111 according to the embodiment.

[Explanation of symbols]

101: input operation device, 102: monitor device, 104:
Evaluation library unit, 105 ... Interface unit, 109
... SQL information file, 110 ... table structure information file, 111 ... improvement plan information file, 103 ... business processing unit, 106 ... SQL generation unit, 107 ... search condition analysis unit,
108: improvement plan search unit, 701: API name, 702: template SQL, 801: table name, 802: column name, 9
01: API name, 902: Improvement availability judgment condition, 903:
Improvement plan.

 ────────────────────────────────────────────────── ─── Continuing on the front page (72) Inventor Masahiro Yamase 5030 Totsuka-cho, Totsuka-ku, Yokohama-shi, Kanagawa Prefecture F-term in the Software Division, Hitachi, Ltd. F-term (reference) 5B075 PP23 PQ02 QT06 5B082 GA03 GA08 GC04

Claims (5)

[Claims] In a database search processing method for specifying a performance deterioration factor in a database search, an input value required to call an API used in a database search is input and set as an input value to a process corresponding to the API. Calling a process corresponding to the API, and calling a template SQL corresponding to the called API
Acquiring from the information file, generating an SQL corresponding to the API from the set input value and the acquired template SQL, acquiring a table name and a column name to be searched from the generated SQL, If the acquired table name and column name of the search target exist in the table structure information file indicating the column name having the index,
It outputs to the output device that there is no influence on the search performance due to the SQL search condition generated from the input value of the PI, and the obtained table name and column name of the search target do not exist in the table structure information file. In that case, the AP
Outputting to the output device that the search performance of the SQL generated from the input value of I has an influence on the search performance. 2. An improvement plan information file storing an improvement possibility determination condition as a condition for determining whether the generated SQL can be improved and an improvement plan when the improvement possibility determination condition is satisfied. From the API, and if the obtained table name and column name match the above-mentioned condition, output the improvement plan corresponding to the condition. 2. The database search processing method according to claim 1, further comprising the step of outputting to a device. 3. The database search processing method according to claim 2, wherein the improvement plan indicates that an input value includes a column having an index. 4. In a database search processing device for specifying a performance deterioration factor in a database search, an input value required to call an API used in the database search is input and set as an input value to a process corresponding to the API. A business processing unit that calls a process corresponding to the API, and a template SQL corresponding to the called API,
An SQL generation unit that obtains an SQL corresponding to the API from the set input values and the obtained template SQL, which is obtained from the information file, and obtains a table name and a column name to be searched from the generated SQL If the obtained table name and column name of the search target exist in the table structure information file indicating the column name having the index,
It outputs to the output device that there is no influence on the search performance by the SQL search condition generated from the PI and the input value, and the obtained table name and column name of the search target do not exist in the table structure information file. In that case, the AP
A database search processing device comprising: a search condition analysis unit that outputs to an output device that the search performance of SQL generated from I and an input value has an effect on search performance. 5. A computer-readable recording medium on which a program for causing a computer to function as a database search processing device for identifying a cause of performance degradation in database search is provided. Is input as an input value to a process corresponding to the API, and a business processing unit that calls a process corresponding to the API, and a template SQL corresponding to the called API is SQL
An SQL generation unit that obtains an SQL corresponding to the API from the set input values and the obtained template SQL, which is obtained from the information file, and obtains a table name and a column name to be searched from the generated SQL If the obtained table name and column name of the search target exist in the table structure information file indicating the column name having the index,
It outputs to the output device that the search performance of the SQL generated from the PI and the input value does not affect the search performance, and the obtained table name and column name of the search target do not exist in the table structure information file. In that case, the AP
A recording medium characterized by recording a program for causing a computer to function as a search condition analysis unit for outputting to an output device that an SQL search condition generated from I and an input value has an effect on search performance.