JP2009258913A - Coding check support system, method, and program - Google Patents

Abstract

A coding check support system, a coding support method, and a coding support program capable of easily grasping a description with poor performance and efficiently performing tuning of an SQL sentence are provided.
When a control unit 21 of a processing server 20 determines that it is an SQL sentence, it executes an SQL sentence matching process using the SQL sentence influence pattern data and the influence pattern recorded in the learning data storage part 25. . Here, in the case of matching, error information is recorded in the memory. The control unit 21 generates check result screen data including error information, transmits the check result screen data to the worker terminal 10, and displays the check result screen. When the line number of the error information included in this check result screen is selected, the worker terminal 10 acquires from the processing server 20 check detailed screen data including source code in a predetermined line range before and after the selected line number. To display the check details screen.
[Selection] Figure 1

Description

  The present invention relates to a coding check support system, a coding support method, and a coding support program for supporting a coding check of a processing program that performs processing using data stored in a database.

  Conventionally, when debugging a program, it is generally performed to visually check the coding displayed on a display or printed paper. Furthermore, in order to reduce bugs, a coding rule for creating a program may be defined, and programming may be performed according to this coding rule. In this case, a system for easily grasping an appropriate coding rule corresponding to the program has been developed (see, for example, Patent Document 1).

According to the invention described in this document, when the source code of a program is received from a personal computer, the rules corresponding to the contents of the source code are listed, and a result file including the listed rules is created and personalized. Send to computer.
JP 2006-260165 A (first page)

  By the way, it is known that even when predetermined processing is performed, performance varies depending on the description of the program. For this reason, it is desirable to check a description that deteriorates the performance and make a description that can obtain a good performance. However, in this case, the descriptions to be checked vary depending on the experience and skill level of each person in charge, resulting in variations in performance.

  In particular, when a description with poor performance is found in a program used in one system, it is desirable to verify the influence on the performance of a program used in another system. In addition, when the workers in charge of program checking for each system are different, it is desirable to share information about descriptions that affect performance. In addition, when the worker in charge of each system is changed, it is desirable to take over all shared information.

  The present invention has been made in view of the above-described problems, and its object is to easily grasp a description with poor performance and efficiently perform SQL statement tuning, a coding support method, a coding support method, and a coding support program. Is to provide.

In order to solve the above-mentioned problems, the invention described in claim 1 includes a control unit connected to an operator terminal used by an operator, and a source data storage unit that stores a processing program for executing processing using a database. And a pattern data storage means in which a search pattern for detecting a description of an SQL sentence that affects performance is recorded, wherein the control means records in the source data storage means If the search pattern is detected in the coding, the check means for executing the matching process for comparing the coding of the processed program and the search pattern recorded in the pattern data storage means Information to identify the part and the description to include the relevant part Generating a check result screen data including a coding information acquisition means, and receiving a coding information acquisition request using the coding information acquisition means from the worker terminal, and a first information presentation means for transmitting the check result screen data to the worker terminal A second information presentation unit that extracts the coding including the corresponding portion from the source data storage unit, generates coding information screen data including the coding, and transmits the coding information screen data to the worker terminal. The gist.

  According to a second aspect of the present invention, in the coding check support system according to the first aspect, the control means includes at least a part of the SQL sentence for the information specifying the SQL sentence of the processing program. And an SQL related data storage means for recording the execution time of the SQL statement, and an execution plan data storage means for recording the execution plan of the SQL statement for information for specifying the SQL statement, Means for acquiring an SQL statement execution time threshold; information for identifying an SQL statement whose execution time has exceeded the threshold; from SQL related data storage means; Means for extracting from the execution plan data storage means an execution plan recorded for the information specifying the executed SQL statement, the extracted SQL statement, and this S Candidate screen presenting means for generating search pattern candidate screen data including an execution plan of an L statement and transmitting it to the worker terminal, and a registration instruction instructed via the search pattern candidate screen from the worker terminal The gist of the invention is that it further comprises a new registration means for specifying a search pattern from information for specifying a new search pattern included in the registration instruction and registering it in the pattern data storage means.

  According to a third aspect of the present invention, in the coding check support system according to the second aspect, the pattern data storage means includes information for specifying the search pattern and a case where the search pattern does not affect performance. When confirmed data including a confirmed condition is stored, and the control means obtains a confirmed instruction including information for identifying a search pattern and a confirmed condition from the operator terminal, the search pattern is Further comprising confirmed registration means for recording confirmed data including specific information and the confirmed conditions in the pattern data storage means, wherein the checking means is in a matching process with the coding of the processing program of the confirmed conditions The gist is to perform a comparison with a search pattern not associated with the confirmed condition.

  The invention according to claim 4 is a control means connected to an operator terminal used by an operator, a source data storage means for storing a processing program for executing processing using a database, and an SQL statement affecting performance. A coding check support method using a system including a pattern data storage unit in which a search pattern for detecting the description of the recording program is recorded, wherein the control unit includes a processing program stored in the source data storage unit. Check stage for executing a matching process for comparing the coding and the search pattern recorded in the pattern data storage means, and information for identifying a corresponding portion including the search pattern when the search pattern is detected in the coding And coding information to obtain a description including this part A first information presentation step of generating check result screen data including an obtaining unit and transmitting the check result screen data to the worker terminal, and a coding information obtaining request using the coding information obtaining unit from the worker terminal Extracting the coding including the relevant part from the source data storage means, generating coding information screen data including the coding, and transmitting the coding information screen data to the worker terminal. To do.

The invention according to claim 5 is a control means connected to an operator terminal used by an operator, a source data storage means for storing a processing program for executing processing using a database, and an SQL statement affecting performance. A coding check support program for use in a system comprising a pattern data storage means in which a search pattern for detecting the description of the above is recorded, wherein the control means is a processing program recorded in the source data storage means. Check means for executing a matching process for comparing the coding and the search pattern recorded in the pattern data storage means, and information for identifying a corresponding part including the search pattern when the search pattern is detected in the coding; , Cody to get a description containing this part Receiving the coding information acquisition request using the coding information acquisition means from the first information presentation means that generates check result screen data including the transmission information acquisition means and transmits the check result screen data to the worker terminal. In this case, the coding including the corresponding part is extracted from the source data storage means, and coding information screen data including the coding is generated, and functions as second information presentation means for transmitting to the worker terminal. And

(Function)
According to the present invention, the control means executes a matching process for comparing the coding of the processing program recorded in the source data storage means and the search pattern recorded in the pattern data storage means. When the control means detects a search pattern in coding, the check result screen data includes information for identifying a relevant part including the search pattern and coding information obtaining means for obtaining a description including the relevant part. Is generated and transmitted to the worker terminal. When the control means receives the coding information acquisition request using the coding information acquisition means from the worker terminal, the control means extracts the coding including the corresponding part from the source data storage means, and generates the coding information screen data including this To the worker terminal. For this reason, the operator can easily grasp the presence or absence of an SQL sentence that affects the performance by browsing the check result screen. Moreover, since the coding information screen including the coding including the corresponding part is displayed, the coding before and after the corresponding part can be easily browsed, and the SQL sentence can be tuned efficiently.

  According to the present invention, the control means obtains a threshold value for the execution time of the SQL statement. The control means extracts information specifying the SQL statement whose execution time exceeds the threshold from the SQL related data storage means. The control means extracts the execution plan recorded for the information specifying the extracted SQL statement from the execution plan data storage means. The control unit generates search pattern candidate screen data including the extracted SQL statement and the execution plan of the SQL statement, and transmits the search pattern candidate screen data to the worker terminal. For this reason, the operator terminal displays a search pattern candidate screen including the SQL statement whose performance has deteriorated from the actual execution plan and the execution plan of the SQL statement, so the actual execution time and execution plan are displayed. Therefore, it is possible to easily grasp the SQL sentence with actually poor performance.

  In addition, when the control unit acquires a registration instruction instructed via the search pattern candidate screen from the worker terminal, the control unit specifies a search pattern from information for specifying a new search pattern included in the registration instruction, Register in the data storage means. For this reason, since the control means executes the matching process using the search pattern newly recognized as a description with poor performance, it can quickly share information on the newly discovered description with poor performance. . Therefore, it is possible to perform tuning with no variation depending on the checked operator.

According to the present invention, when the control means obtains the confirmed instruction including the information for identifying the search pattern and the confirmed condition from the worker terminal, the control means confirms that the retrieved pattern includes the specified information and the confirmed condition. Data is recorded in the pattern data recording means. In the matching process with the coding of the processing program of the confirmed condition, the control means performs a comparison with a search pattern not associated with the confirmed condition. For this reason, the description allowed by the operator that the performance is not affected under the confirmed condition of the confirmed data is not detected in the subsequent check processing. Therefore, even for a processing program with the same module name, it is possible to easily grasp the description of the SQL statement that affects the performance by using a more appropriate search pattern according to the confirmed conditions of the system environment and the subsystem. Can do.

  According to the present invention, it is possible to grasp a description of an SQL statement with poor performance before execution, and to efficiently tune the SQL statement.

  Hereinafter, an embodiment embodying the present invention will be described with reference to FIGS. In the present embodiment, a coding check support system used to check the coding of a processing program that executes processing using data recorded in a database will be described.

  As shown in FIG. 1, in the present embodiment, a processing server 20 as a coding check support system is connected to a worker terminal 10. The worker terminal 10 is a computer terminal used by a worker who performs a coding check, and includes a display, a keyboard, a pointing device, and the like.

  On the other hand, the processing server 20 executes business processing such as sales processing in accordance with the processing program, using data recorded in the ORACLE (registered trademark) database 30. The processing server 20 includes a control unit 21, a source data storage unit 22, an SQL related data storage unit 23, an execution plan data storage unit 24, and a learning data storage unit 25. The source data storage unit 22 functions as a source data storage unit, the SQL related data storage unit 23 functions as an SQL related data storage unit, and the execution plan data storage unit 24 functions as an execution plan data storage unit. In the present embodiment, the control unit 21 and the learning data storage unit 25 constitute a pattern data storage unit.

  The control unit 21 functions as a control unit and includes a CPU, a RAM, a ROM, and the like (not shown), and processes to be described later (check stage, first information presentation stage, second information presentation stage, candidate screen presentation stage, new registration stage) And processing including a confirmed registration stage). The control unit 21 functions as the module execution unit 211. Further, the control unit 21 functions as a check processing unit 215 and a learning processing unit 216 by executing the coding check support program. The check processing unit 215 functions as a check unit, a first information presentation unit, and a second information presentation unit. The learning processing unit 216 functions as a candidate screen presenting unit, a new registration unit, and a confirmed registration unit.

  The control unit 21 stores subsystem configuration data for specifying a subsystem that uses the processing program. Specifically, in the system configuration data, the module name of the processing program that configures the subsystem is associated with the subname that identifies the subsystem. In addition, the control unit 21 stores environment configuration data for specifying a system environment that can be used by the subsystem. Specifically, in the environment configuration data, a sub name of an available subsystem is associated with an environment name that identifies the system environment. Further, the control unit 21 stores format data of various screens, which will be described later, and system configuration data and environment configuration data defaults.

The module execution unit 211 executes processing using the processing target data recorded in the database 30 according to the processing program stored in the source data storage unit 22. In this case, the module execution unit 211 generates an SQL sentence in the compilation process of the processing program, and stores the SQL sentence related information regarding the SQL sentence in the SQL related data storage unit 23 as described later.

  The check processing means 215 executes a check process for checking the coding of the processing program. Specifically, the check processing means 215 stores project standardized data. This project standardized data is data relating to a description defined as a standard in the processing program used in this system from the viewpoint of program readability. This project standardized data includes standardized matching data and standardized check data. The standardized matching data is data that defines rules regarding how to display constants and arguments. The standardized check data is data that defines a description definition, for example, whether the description of one line is within a predetermined number of characters.

  Further, the SQL statement influence pattern data and the loop sentence influence pattern data are stored in the check processing means 215. The influence pattern data includes search pattern data relating to a description with poor performance in the description of the SQL sentence or the description of the loop sentence. The SQL sentence influence pattern data includes, for example, a description in which a keyword “SELECT” and a special character of a wild card are combined. The loop statement influence pattern data includes, for example, a description that defines a calling function. The check processing means 215 executes a process for extracting a description of poor performance and displaying it on the operator terminal 10 with respect to the program standardization description, the SQL statement description, and the loop statement description.

Further, the check processing unit 215 stores SQL statement determination condition data for determining whether or not the statement is an SQL statement, and loop statement determination condition data for determining whether or not the statement is a loop statement. . In the SQL statement determination condition data, a keyword specifying the SQL statement (“SELECT”, “UPDATE”, “DELETE”, “INSERT”, etc.) and an end keyword “;” of the SQL statement are recorded. In the loop statement determination condition data, a keyword specifying a loop statement (“LOOP”, “FOR”, “WHILE”, etc.) and a loop statement end keyword “END” are recorded. When the check processing unit 215 detects a keyword specifying the SQL sentence, the check processing unit 215 temporarily stores the SQL sentence determination flag until an end character of the SQL sentence is detected. In addition, when the check processing unit 215 detects a keyword specifying a loop sentence, it temporarily stores a loop sentence determination flag until an end character of the loop sentence is detected.

  The learning processing unit 216 executes processing for newly registering an influence pattern in the learning data storage unit 25. Specifically, the learning processing means 216 acquires data relating to the SQL sentence to be tuned, and based on this, an influence pattern is newly stored in the learning data storage unit 25 in accordance with an instruction received from the worker terminal 10. sign up. Further, the learning processing means 216 acquires a confirmed condition (in this case, a system environment condition and a subsystem condition) when not applying the newly acquired influence pattern, and a confirmation including the confirmed condition is obtained. The completed data is stored in the learning data storage unit 25.

  On the other hand, the source data storage unit 22 stores the source code of the processing program with the module name. This source code is recorded when a new module code is created and registered. Here, line numbers are given to the source code as is well known.

  The SQL related data storage unit 23 stores SQL related data relating to an SQL statement used when executing the processing program. This SQL related data is recorded when the SQL statement is generated and executed. This SQL related data includes data related to the SQL head sentence, address, hash value, and execution time.

In the SQL head sentence data area, data relating to the first 100 characters (SQL head sentence) of the SQL sentence is recorded.
In the address data area and the hash value data area, data relating to the address and hash value of the SQL sentence are recorded, respectively. Using these addresses and hash values, the execution plan data recorded in the execution plan data storage unit 24 is associated with the SQL related data.

In the execution time data area, data relating to the required time when this SQL statement is executed is recorded.
The execution plan data storage unit 24 stores data related to an execution plan, which is an internal procedure when executing an SQL statement. This execution plan is generated and recorded when processing is performed based on SQL. This execution plan data includes data relating to addresses, hash values, operations, options, and object names.

In the address data area and the hash value data area, data relating to the address and hash value of the execution plan are recorded.
In the operation data area, data relating to information for specifying an internal operation to be executed in the execution plan is recorded.

  In the option data area, data related to operation variations corresponding to the operation data is recorded. For example, when the operation is “TABLE ACCESS”, the data regarding this variation includes “FULL”, “INDEX”, and the like.

In the object name data area, data relating to a table name or an index name to be processed corresponding to the operation data is recorded.
In addition to the SQL statement influence pattern data stored in the check processing unit 215, the learning data storage unit 25 records influence pattern data for extracting descriptions of SQL statements that affect performance. This learning data is registered when an influence pattern registration instruction from the worker terminal 10 is acquired. This learning data includes influence pattern data and confirmed data.

The influence pattern data includes data related to the pattern identifier and the search pattern.
In the pattern identifier data area, data relating to an identifier for specifying the influence pattern is recorded.

In the search pattern data area, data relating to the coding description of the SQL sentence that adversely affects performance is recorded.
The confirmed data includes data related to the confirmed condition associated with the pattern identifier. In this embodiment, conditions for specifying the system environment and the subsystem to which the processing program belongs are used as the confirmed conditions. Therefore, the confirmed data includes the pattern identifier, the environment name, and the sub name.

  In the pattern identifier data area, data relating to an identifier (pattern identifier) for specifying an influence pattern confirmed in this subsystem in this system environment is recorded.

In the environment name data area, data related to the environment name for specifying the system environment to be processed by the processing program is recorded.
In the sub-name data area, data relating to a sub-name (sub-name) that identifies the subsystem to which the processing program belongs is recorded.

  Processing using the processing server 20 having the above configuration will be described with reference to FIGS. Here, a description will be given in the order of a check process performed before executing the process program stored in the process server 20, a module execution process executed by the process program, and a learning process performed after executing the process program. In this case, the worker registers data on the allowable time (threshold value) of the execution time of the SQL statement for each processing program in the control unit 21 of the processing server 20 using the worker terminal 10.

(Check processing)
First, the check process will be described with reference to FIG.
The worker performs coding check after the source code of the processing program to be worked has been created and updated and registered. In this case, the worker terminal 10 transmits a check condition screen request for performing a coding check to the processing server 20 in accordance with the worker's instruction.

  The control unit 21 of the processing server 20 that has received the coding check screen request generates default environment system data and check condition screen data including the system environment data, and transmits the check condition screen data to the worker terminal 10. The worker terminal 10 displays a check condition screen on the display. The check condition screen includes a pull-down menu for selectable environments, a pull-down menu for selectable subsystems, a pull-down menu for selectable modules, and a check execution button.

  Here, the operator selects the environment name of the system environment using the processing program to be checked from the pull-down menu. When the environment name is selected from the pull-down menu, the worker terminal 10 transmits data related to the selected environment name to the processing server 20. The control unit 21 of the processing server 20 transmits the sub name of the environment configuration data associated with the acquired environment name to the worker terminal 10. The worker terminal 10 displays a list of sub names associated with the selected environment name in a pull-down menu related to the sub names.

  Then, the worker selects a sub name including a processing program to be checked from the pull-down menu. When a sub name is selected from the pull-down menu, the worker terminal 10 transmits data regarding the selected sub name to the processing server 20. The control unit 21 of the processing server 20 transmits the module name of the system configuration data associated with the acquired sub name to the worker terminal 10. The worker terminal 10 displays the acquired module list list data on a pull-down menu related to the module name.

  Then, the operator selects the module name of the processing program to be checked from the pull-down menu regarding the module, and selects the check button on the check condition screen. In this case, the worker terminal 10 transmits data related to the environment name and module name selected on the check condition screen to the processing server 20.

  The control unit 21 of the processing server 20 temporarily stores the received environment name and module name in the memory. And the control part 21 performs the connection process to a database (step S1-1). Specifically, the check processing means 215 of the control unit 21 specifies the database 30 corresponding to the acquired environment name and makes this database 30 accessible.

Next, the control unit 21 executes a program reading process for each line (step S1-2). Specifically, the check processing means 215 of the control unit 21 sequentially reads the source code corresponding to the temporarily stored module name from the source data storage unit 22 as a check target row for each row from the beginning. If it is determined that the line is commented out, the check processing unit 215 skips this line and reads the next line as a check target line.

  When the read check target line is not the end of the program (in the case of “NO” in step S1-3), the control unit 21 executes a project standardization matching process (step S1-4). Specifically, the check processing unit 215 of the control unit 21 compares the standardized matching data with the description of the check target line, and determines whether or not there is a description that does not match (standardization violation description) in the check target line. Search for.

  Next, the control part 21 performs a project standardization check process (step S1-5). Specifically, the check processing means 215 of the control unit 21 searches whether the description of the check target line is a description that does not conform to the standardized check data (standardization violation description).

  When a standardization violation is detected (in the case of “YES” in step S1-6), the control unit 21 executes a violation information registration process (step S1-7). Specifically, the check processing means 215 of the control unit 21 determines that there is a description that does not match in step S1-4 or a description that does not match the check data in step S1-5. Record data on violation information in memory. This violation information includes the line number of the check target line, the corresponding part, and the content of the standardization that has been violated.

  On the other hand, when the standardization violation is not detected (in the case of “NO” in step S1-6), the control unit 21 executes a determination process as to whether or not the read check target line is an SQL statement ( Step S1-8). Here, when a keyword that matches the SQL sentence determination condition data is in the check target line, the check processing means 215 of the control unit 21 determines that the SQL sentence is an SQL sentence, and temporarily records the SQL sentence determination flag. . The check processing means 215 holds this SQL sentence determination flag until an end keyword of the SQL sentence is detected. When the SQL statement determination flag is recorded, the check processing unit 215 determines that the statement is an SQL statement.

  When it is determined that the sentence is an SQL sentence (“YES” in step S1-6), the control unit 21 executes the SQL sentence matching process (step S1-9). Specifically, the check processing unit 215 of the control unit 21 compares the SQL statement influence pattern data with the description of the check target row, and determines whether or not the check target row has a description that matches the SQL statement influence pattern. Search for.

  Next, the check processing unit 215 performs matching processing with the influence pattern recorded in the learning data storage unit 25. In this case, the matching process is not performed for the search data specified by the pattern identifier of the confirmed data. Specifically, the check processing unit 215 extracts the confirmed data including the environment name and the sub name temporarily stored in the memory from the learning data storage unit 25. The check processing unit 215 extracts influence pattern data other than the influence pattern data including the pattern identifier of the extracted confirmed data from the learning data storage unit 25. Then, the check processing means 215 compares the extracted influence pattern data search pattern with the description of the check target line, and searches for a description that matches the influence pattern in the check target line.

When matching is performed in this SQL sentence matching process (in the case of “YES” in step S1-10), the control unit 21 executes an error information registration process (step S1-11). Specifically, the check processing means 215 of the control unit 21 records error information in a memory. The error information includes a line number of the check target line, a description of the matched search pattern, and a predetermined number of characters before and after the search pattern (for example, 10 characters before and after).
And are included.

  On the other hand, if there is no description that matches the SQL statement determination condition data in the check target line, or if no SQL statement determination flag is recorded, the check processing means 215 determines that the statement is not an SQL statement. In this case (in the case of “NO” in step S1-8) or in the case where matching is not performed in the SQL sentence matching process (in the case of “NO” in step S1-10), the control unit 21 is a loop sentence. Whether or not is determined (step S1-12). Here, when a keyword that matches the loop statement determination condition data is included in the check target line, the check processing unit 215 of the control unit 21 determines that the loop statement is a loop statement, and temporarily sets the loop statement determination flag. To record. The check processing means 215 holds this loop sentence determination flag until the end keyword of the loop sentence is detected. Then, the check processing means 215 determines that it is a loop sentence when the loop sentence determination flag is recorded.

  Here, when it is a loop sentence, the control part 21 performs a loop sentence matching process (step S1-13). Specifically, the check processing unit 215 of the control unit 21 compares the loop sentence influence pattern with the description of the check target line to detect whether or not the loop sentence influence pattern is included in the check target line.

  When matching is performed in this loop sentence matching process (in the case of “YES” in step S1-14), the control unit 21 executes an error information registration process (step S1-11). Specifically, the check processing means 215 of the control unit 21 records error information in a memory. This error information includes the line number of the check target line, the description of the search pattern included in the check target line, and the coding description of a predetermined number of characters (for example, 10 characters before and after) including this description. included.

  On the other hand, when it is not a loop sentence (in the case of “NO” in step S1-12), or when it is not matched in the loop sentence matching process (in the case of “NO” in step S1-14), the control unit 21 The program reading process (step S1-2) is executed for the next line, and the processes after step S1-2 are repeatedly executed.

  If the check target line read in the program reading process is the last (in the case of “YES” in step S1-3), the control unit 21 executes a result output process (step S1-15). Here, the check processing means 215 of the control unit 21 firstly includes the environment name of the system environment connected in the check process, the module name of the processing program, the violation information recorded in step S1-7, and the step S1-11. The check result screen data including the error information recorded in is generated. Furthermore, the check processing means 215 includes hyperlink information including the environment name, module name, and error information line number in the check result screen data. In the present embodiment, this hyperlink information functions as coding information acquisition means for displaying a predetermined range of coding. Then, the check processing unit 215 transmits the generated check result screen data to the worker terminal 10. The worker terminal 10 displays a check result screen on the display.

  This check result screen includes violation information and error information. Furthermore, the line number of the error information is displayed in a clickable manner. Here, when the worker selects the line number of the error information, the worker terminal 10 transmits a coding information acquisition request to the processing server 20. This coding information acquisition request includes the environment name, module name, and selected line number included in the check result screen.

The control unit 21 of the processing server 20 connects to the database 30 specified from the environment name included in the coding information acquisition request. The control unit 21 extracts the source code of the processing program from the source data storage unit 22 from the environment name and the module name included in the coding information acquisition request, and in this source code, a predetermined line before and after the selected line number (for example, , 10 lines before and after) are extracted. And the control part 21 produces | generates the check detailed screen data as coding information screen data containing the extracted source code, and transmits to the operator terminal 10. FIG. The worker terminal 10 displays a check details screen. The operator uses this check details screen to update the source code of the processing program. Then, the updated source code is registered in the source data storage unit 22.

(Module execution processing)
Thereafter, when the worker instructs execution of the processing program, the worker terminal 10 transmits a processing program execution instruction request including the environment name, sub name, and module name of the processing program to be executed to the control unit 21 of the processing server 20. To do. In this case, the control unit 21 of the processing server 20 executes module execution processing.

  In this module execution process, first, the control unit 21 extracts the source code corresponding to the acquired module name from the source data storage unit 22. The control unit 21 compiles the extracted source code and executes the processing program.

  Here, when dynamic SQL is included in the source code, an SQL statement issuance process is executed. In this case, the module execution unit 211 executes the SQL statement execution processing after executing the SQL statement analysis processing, the SQL statement rewriting processing, and the execution plan creation processing, as is well known. Here, in the execution of the SQL statement, the processing target data recorded in the database 30 specified from the environment name acquired from the worker terminal 10 is used. In this case, in the SQL statement execution process, the module execution unit 211 stores the generated SQL head sentence, address, hash value, and execution time required for execution in the SQL related data storage unit 23. Further, when the execution plan is created, the module execution unit 211 stores data related to the execution plan in the execution plan data storage unit 24.

(Learning process)
Next, the learning process will be described with reference to FIG.
In this learning process, first, the control unit 21 of the processing server 20 executes a tuning target SQL sentence extraction process (step S2-1). Specifically, when the learning processing means 216 of the control unit 21 detects the end of the module execution process, it compares the execution time of the SQL related data recorded in the SQL related data storage unit 23 with a threshold value. Then, the learning processing means 216 extracts the SQL related data in which the execution time exceeding the threshold is recorded as a tuning target, and temporarily stores it in the memory.

  Next, the control unit 21 determines whether or not the extracted SQL sentence has already been confirmed (step S2-2). Specifically, the learning processing unit 216 of the control unit 21 extracts the confirmed data associated with the environment name and the sub name acquired in the module execution process from the learning data storage unit 25. The learning processing unit 216 extracts the influence pattern data including the pattern identifier of the extracted confirmed data from the learning data storage unit 25. Then, the learning processing means 216 compares the influence pattern of the extracted influence pattern data with the SQL head sentence of the SQL related data stored in the memory in step S2-1. If the extracted influence pattern is included in the SQL head sentence, it is determined that the SQL sentence has been confirmed. On the other hand, if it does not match the extracted influence pattern data, it is determined that the SQL sentence is unconfirmed, and data relating to the unconfirmed SQL sentence is temporarily stored in the memory.

If all of the extracted SQL sentences have been confirmed (in the case of “YES” in step S2-2), the learning processing means 216 ends the processing assuming that it has already been confirmed.
On the other hand, when the data related to the unconfirmed SQL sentence is temporarily stored in the memory (in the case of “NO” in step S2-2), the learning processing means 216 executes the output process of the unconfirmed tuning target SQL sentence (step S2). S2-3). Specifically, the learning processing unit 216 generates tuning screen data for displaying a list of SQL information to be tuned. In this case, the learning processing unit 216 includes the SQL head sentence and execution time of the extracted SQL related data and the execution plan hyperlink in the tuning screen data as unconfirmed SQL sentence information. Further, the learning processing means 216 includes the address and hash value of the extracted SQL related data in the tuning screen data in association with each execution plan hyperlink.

  Then, the learning processing unit 216 transmits the generated tuning screen data to the worker terminal 10. The worker terminal 10 displays a tuning screen on the display. This tuning screen displays a list of unconfirmed SQL statement information. Further, this tuning screen includes an end button.

  Here, when the operator selects an execution plan hyperlink on the tuning screen, the worker terminal 10 sends an execution plan acquisition request including an address and a hash value associated with the execution plan hyperlink to the processing server. 20 to send.

  Next, the control part 21 of the process server 20 performs the specific process of the execution plan relevant to a SQL sentence (step S2-4). Specifically, the learning processing means 216 of the control unit 21 searches the execution plan data storage unit 24 for execution plan data including the received address and hash value. Here, when the corresponding execution plan data cannot be extracted, the control unit 21 generates warning message data indicating that there is no execution plan, and transmits the warning message data to the worker terminal 10. In this case, the worker terminal 10 displays a warning message on the display.

  On the other hand, when the corresponding execution plan data can be extracted, the control unit 21 of the processing server 20 executes the influence pattern candidate output process (step S2-5). Specifically, the learning processing means 216 of the control unit 21 generates execution plan screen data as search pattern candidate screen data. The learning processing means 216 includes the environment name and module name, the SQL head sentence and execution time displayed on the tuning screen, and the extracted execution plan data in the execution plan screen data.

  Here, the learning processing means 216 determines whether or not a search pattern corresponding to the tuning target SQL sentence has already been registered. Specifically, the learning processing unit 216 compares the SQL head sentence of the tuning target SQL sentence with the search pattern stored in the learning data storage unit 25, and determines whether or not the SQL head sentence has a description that matches the search pattern. Determine whether. In this case, when the search pattern has already been registered, the learning processing unit 216 includes a confirmation button associated with the pattern identifier of the search pattern in the execution plan screen data. Then, the learning processing means 216 of the control unit 21 transmits the generated execution plan screen data to the worker terminal 10. The worker terminal 10 displays an execution plan screen as a search pattern candidate screen on the display.

  This execution plan screen includes an SQL head sentence, an execution time, and an execution plan, as well as an influence pattern input field, a registration button, and a return button. Further, when the registered search pattern is included in the description of the tuning target SQL statement, the execution plan screen includes a confirmation button.

Here, when the worker who has read the description and execution plan of the SQL head sentence determines that the tuning target SQL sentence includes a description pattern (search pattern) with poor performance, this search pattern is used. Enter in the influence pattern input field and select the registration button. In this case, the worker terminal 10 transmits an influence pattern registration instruction to the processing server 20. This influence pattern registration instruction includes the environment name, the sub name, and the search pattern input in the influence pattern input field.

  When an influence pattern registration instruction is received (in the case of “YES” in step S2-6), the control unit 21 of the processing server 20 executes an influence pattern registration process for each sub name (step S2—). 7). Specifically, the learning processing means 216 of the control unit 21 gives a pattern identifier to the acquired search pattern. The learning processing unit 216 stores the influence pattern data including the pattern identifier and the search pattern in the learning data storage unit 25. Then, the learning processing unit 216 specifies an unconfirmed SQL sentence obtained by removing the SQL sentence that includes the description of the search pattern registered in the learning data storage unit 25 from the SQL sentence temporarily stored in the memory in step S2-2. Further, the learning processing means 216 generates tuning screen data using the data relating to the identified SQL sentence and transmits it to the worker terminal 10 as in step S2-3. The worker terminal 10 displays the tuning target SQL screen again.

  On the other hand, when an execution plan screen including a confirmation button is displayed, an operator who determines that a description pattern with poor performance is not included selects this confirmation button. In this case, the worker terminal 10 transmits a confirmed instruction to the processing server 20. This confirmed instruction includes an environment name, a module name, and a pattern identifier associated with the confirmation button.

  When a confirmed instruction is received (in the case of “NO” in step S2-6), the control unit 21 of the processing server 20 executes a process for registering confirmed data (step S2-8). Specifically, the learning processing unit 216 of the control unit 21 stores the confirmed data including the environment name, the sub name, and the pattern identifier included in the confirmed instruction in the learned data storage unit 25. Then, the learning processing means 216 generates tuning screen data and transmits it to the worker terminal 10 to display the tuning screen on the display of the worker terminal 10. In this case, data relating to the SQL statement having the influence pattern registered in step S2-7 is not displayed on the tuning screen.

  When an operator who has viewed an execution plan on an execution plan screen that does not include a confirmation button determines that a description pattern with poor performance is not included, the operator selects the return button on the execution plan screen. In this case, the worker terminal 10 sends a tuning screen request to the processing server 20. Receiving this tuning screen request, the control unit 21 of the processing server 20 executes an SQL statement output process (step S2-3) and repeats the subsequent processes. In the execution of the SQL statement output process, the control unit 21 generates tuning screen data including data relating to an unconfirmed SQL statement temporarily stored in the memory. When the end button on the tuning target SQL screen is selected, the learning processing unit 216 ends the learning process.

According to this embodiment, the following effects can be obtained.
-In this embodiment, when it determines with it being a SQL sentence (in step S1-8, "YES"), the control part 21 of the processing server 20 performs a SQL sentence matching process (step S1-9). In this case, the control unit 21 compares the SQL sentence influence pattern data with the description of the check target line, and searches for a description that matches the SQL sentence influence pattern in the check target line. Further, the control unit 21 executes a matching process with the influence pattern recorded in the learning data storage unit 25. When matching is performed in this SQL sentence matching process (in the case of “YES” in step S1-10), the control unit 21 executes an error information registration process (step S1-11). Thereafter, in the processing result output process (step S1-15), the control unit 21 transmits check result screen data including error information including a description of the search pattern included in the check target row to the worker terminal 10. Therefore, by viewing the check result screen, the operator can easily grasp whether or not there is a description of the SQL sentence that affects the performance in the coding of the processing program. Therefore, the operator can efficiently tune the SQL sentence.

  -In this embodiment, the control part 21 of the processing server 20 which received the coding information acquisition request from the operator terminal 10 extracts the source code of a processing program, and includes the source code of a predetermined range among these. Check detail screen data is generated and transmitted to the worker terminal 10. The check detail screen displays a description detected by the check process and a source code in a predetermined range in a predetermined line before and after the description. For this reason, since the operator can browse the description before and after the detected description, the coding can be tuned efficiently.

  -In this embodiment, the control part 21 extracts the SQL sentence of the execution time which exceeded the threshold value as a tuning object, and performs the output process of an unconfirmed tuning object SQL sentence using this (step S2-3). ). In this case, the control unit 21 generates tuning screen data including the SQL head sentence and the execution time of the extracted SQL related data, and transmits the tuning screen data to the worker terminal 10. The worker terminal 10 displays a tuning screen including the SQL head sentence of the SQL sentence having a long execution time on the display. For this reason, the worker can efficiently grasp the SQL statement having a long actual execution time and poor performance.

  In the present embodiment, the control unit 21 generates tuning screen data including the address and hash value of the extracted SQL related data in association with each execution plan hyperlink, and transmits the tuning screen data to the worker terminal 10. When the execution plan hyperlink is selected on the tuning screen, the worker terminal 10 transmits an execution plan acquisition request including an address and a hash value associated therewith to the processing server 20. The control unit 21 of the processing server 20 executes an execution plan specifying process related to the SQL statement (step S2-4), and executes an influence pattern candidate output process including execution plan data. For this reason, the operator can tune efficiently by browsing the execution plan of the SQL statement having poor performance.

  -In this embodiment, when the SQL statement to be tuned includes a description pattern with poor performance in the execution plan screen, the registration button is selected after the search pattern is input to the influence pattern input field. . The worker terminal 10 transmits an influence pattern registration instruction to the processing server 20. In this case (in the case of “YES” in step S2-6), the control unit 21 of the processing server 20 executes an influence pattern registration process for each sub name (step S2-7). Specifically, the control unit 21 assigns a pattern identifier to the acquired search pattern, and stores the influence pattern data including the pattern identifier and the search pattern in the learning data storage unit 25. Moreover, in the SQL sentence matching process (step S1-9) of the check process, the control unit 21 executes a matching process with the influence pattern recorded in the learning data storage unit 25. For this reason, when a search pattern having a poor performance is newly registered, the control unit 21 performs a check process using the newly registered search pattern, so that the search pattern information can be quickly shared.

-In this embodiment, when the search pattern corresponding to the tuning target SQL sentence is already registered in the influence pattern candidate output process (step S2-5), the control unit 21 associates the search pattern with the pattern identifier of this search pattern. The check execution plan screen data including the confirmation button is transmitted to the worker terminal 10 to display the check execution plan screen. In this case, since a description pattern with poor performance is not included, when the confirmation button is selected, the worker terminal 10 transmits a confirmed instruction to the processing server 20. When the confirmed instruction is received (in the case of “NO” in step S2-6), the control unit 21 of the processing server 20 executes the registered process of the confirmed data (step S2-8). Thereafter, in the SQL sentence matching process (step S1-9) of the check process, the control unit 21 executes a matching process with the influence pattern recorded in the learning data storage unit 25. In this case, the matching process is not performed for the search data specified by the pattern identifier of the confirmed data. For this reason, the description allowed by the operator that the performance is not affected under the confirmed condition of the confirmed data is not detected in the subsequent check processing. Therefore, even for processing programs with the same module name, it is possible to easily grasp the description of the SQL sentence that affects the performance by using a more appropriate search pattern according to the system environment and the subsystem.

  -In this embodiment, the control part 21 performs a project standardization matching process (step S1-4), and performs a project standardization check process (step S1-5). When a standardization violation is detected (in the case of “YES” in step S1-6), the control unit 21 executes a violation information registration process (step S1-7). Further, if it is a loop statement, a performance influence pattern (code) matching process is executed (step S1-13), and if a match is found (in the case of “YES” in step S1-14), the control unit 21 Executes error information registration processing (step S1-11). For this reason, it is possible to check not only the SQL statement but also a description that violates the standardization of the project or a description with poor performance in the loop statement.

Moreover, you may change the said embodiment as follows.
In the above embodiment, when the control unit 21 obtains the check detail screen request from the worker terminal 10, the control unit 21 includes a predetermined line before and after the selected line number (for example, 10 lines before and after) of the source code of the processing program. The range was extracted, check detail screen data including the range was generated and transmitted to the worker terminal 10. Not limited to this, the number of predetermined lines before and after included in the check details screen may be changed according to the extracted search pattern. Specifically, the SQL statement effect pattern data including the description of the SQL statement and the number of rows extracted correspondingly (the number of extracted rows) is stored in the check processing means 215. Alternatively, the influence pattern data associated with the number of extracted rows may be stored in the learning data storage unit 25. Then, in the error information registration process (step S1-11), the control unit 21 acquires the number of extracted lines corresponding to the description of the matched search pattern and associates it with the error information. The control unit 21 also includes the number of extracted lines associated with the error information in the hyperlink information of the check result screen data. The worker terminal 10 makes a coding information acquisition request including the number of extracted lines. The control unit 21 of the processing server 20 extracts the source code in the range corresponding to the number of extracted lines, including the selected line number among the extracted source codes. And the control part 21 produces | generates the check detailed screen data containing the extracted line, and transmits to the operator terminal 10. FIG. As a result, the coding range of the check detail screen to be displayed is changed according to the search pattern, so that the description necessary for tuning can be grasped efficiently.

In the above embodiment, the combination of the system environment and the subsystem is used as the confirmed condition as the confirmed condition of the confirmed data. The confirmed condition is not limited to this. For example, the environment name and the module name may be used in combination. Moreover, you may use combining an environment name, a subname, and an operator. In this case, when there is an access from the worker terminal 10 in the check process or the learning process, the control unit 21 of the processing server 20 acquires a worker identifier that identifies the worker who performed the login process in the worker terminal 10. To do. Further, a worker identifier data area for recording the worker identifier is provided in the confirmed data. In the confirmation flag registration process (step S2-8), the control unit 21 of the processing server 20 learns the confirmed data including the environment name, the sub name, the worker identifier, and the pattern identifier included in the confirmed instruction. The data is stored in the data storage unit 25. Thereafter, in the SQL sentence matching process (step S1-9), the control unit 21 extracts confirmed data including the environment name, the sub name, and the worker identifier acquired from the worker terminal 10. The control unit 21 extracts influence pattern data other than the influence pattern data including the pattern identifier of the confirmed data, and searches whether or not there is a description matching the influence pattern in the check target line. Thereby, the influence pattern extracted according to an operator can be changed. For this reason, if the confirmed data suitable for the worker is registered according to the knowledge or habit of the worker who performs the coding check, the description can be changed to a description of good performance with little variation among the workers.

  In the above embodiment, when the search pattern is included in the description of the SQL head sentence, the control unit 21 includes a confirmation button associated with the pattern identifier of the search pattern in the execution plan screen data. In this case, when the registered search pattern is included in the description of the tuning target SQL sentence, this search pattern may be included in the execution plan screen data. Thereby, the registered search pattern is displayed on the execution plan screen together with the SQL head sentence. For this reason, it is possible to efficiently grasp whether or not another search pattern other than the registered search patterns is included in the SQL sentence having a long execution time and poor performance.

  In the above embodiment, the tuning target SQL information and the execution plan of each SQL are displayed separately. Not limited to this, the tuning target SQL information and the SQL execution plan may be included in the same screen. For example, when there are few tuning target SQL sentences (for example, one or two), the tuning target SQL information can be easily grasped. Specifically, when the unconfirmed SQL to be tuned is 1 or 2, the control unit 21 extracts execution plan data related thereto from the execution plan data storage unit 24 based on the address and the hash value. . Then, instead of generating the tuning target SQL screen data, the control unit 21 generates check execution plan screen data including the SQL head sentence and execution time displayed on the tuning target SQL screen, and the extracted execution plan data. To the worker terminal 10.

1 is a schematic diagram of a system in an embodiment. The flowchart for demonstrating the process sequence of a check process. The flowchart for demonstrating the process sequence of a learning process.

Explanation of symbols

  DESCRIPTION OF SYMBOLS 10 ... Worker terminal, 20 ... Processing server, 21 ... Control part, 22 ... Source data storage part, 23 ... SQL related data storage part, 24 ... Execution plan data storage part, 25 ... Learning data storage part, 30 ... Database, 211: Module execution means, 215: Check processing means, 216: Learning processing means

Claims (5)

Control means connected to the worker terminal used by the worker;
Source data storage means for storing a processing program for executing processing using a database;
A coding check support system comprising pattern data storage means in which a search pattern for detecting a description of an SQL sentence that affects performance is recorded,
The control means is
Check means for executing a matching process for comparing the coding of the processing program recorded in the source data storage means and the search pattern recorded in the pattern data storage means;
When a search pattern is detected in the coding, check result screen data including information for identifying a corresponding part including the search pattern and coding information acquisition means for acquiring a description including the corresponding part is generated. First information presenting means for transmitting to the worker terminal;
When a coding information acquisition request using the coding information acquisition unit is received from the worker terminal, the coding including the corresponding part is extracted from the source data storage unit, and coding information screen data including this is generated. A coding check support system comprising: second information presenting means for transmitting to the worker terminal. The control means includes
SQL-related data storage means for recording a description including at least a part of the SQL statement and an execution time of the SQL statement for information specifying the SQL statement of the processing program;
It is connected to the execution plan data storage means for recording the execution plan of the SQL statement for the information specifying the SQL statement,
The control means is
Means for acquiring a threshold of execution time of the SQL statement;
Means for extracting from the SQL related data storage means information identifying an SQL statement whose execution time has exceeded the threshold;
Means for extracting, from the execution plan data storage means, an execution plan recorded for information identifying the extracted SQL statement;
Candidate screen presenting means for generating search pattern candidate screen data including the extracted SQL sentence and an execution plan of the SQL sentence, and transmitting the search pattern candidate screen data to the worker terminal;
When the registration instruction instructed via the search pattern candidate screen is acquired from the worker terminal, the pattern data storage means identifies a search pattern from information for specifying a new search pattern included in the registration instruction. The coding check support system according to claim 1, further comprising new registration means for registering with the coding check. The pattern data storage means stores confirmed data including information for specifying the search pattern and a confirmed condition when the search pattern does not affect performance,
When the control means obtains a confirmed instruction including information for identifying a search pattern and a confirmed condition from the worker terminal, the confirmed data including the specified information and the confirmed condition is stored as the retrieved pattern. And a confirmed registration means for recording in the pattern data storage means,
3. The coding according to claim 2, wherein the checking means performs a comparison with a search pattern not associated with the confirmed condition in a matching process with the coding of the processing program of the confirmed condition. Check support system. Control means connected to the worker terminal used by the worker;
Source data storage means for storing a processing program for executing processing using a database;
A coding check support method using a system including a pattern data storage unit in which a search pattern for detecting a description of an SQL sentence affecting performance is recorded,
The control means is
A check stage for executing a matching process for comparing the coding of the processing program recorded in the source data storage means and the search pattern recorded in the pattern data storage means;
When a search pattern is detected in the coding, check result screen data including information for identifying a corresponding part including the search pattern and coding information acquisition means for acquiring a description including the corresponding part is generated. A first information presentation step to be transmitted to the worker terminal;
When a coding information acquisition request using the coding information acquisition unit is received from the worker terminal, the coding including the corresponding part is extracted from the source data storage unit, and coding information screen data including this is generated. And a second information presenting step of transmitting to the worker terminal. Control means connected to the worker terminal used by the worker;
Source data storage means for storing a processing program for executing processing using a database;
A coding check support program for use in a system comprising pattern data storage means in which a search pattern for detecting a description of an SQL sentence that affects performance is recorded,
The control means;
Check means for executing a matching process for comparing the coding of the processing program recorded in the source data storage means and the search pattern recorded in the pattern data storage means;
When a search pattern is detected in the coding, check result screen data including information for identifying a corresponding part including the search pattern and coding information acquisition means for acquiring a description including the corresponding part is generated. First information presenting means for transmitting to the worker terminal, and when receiving a coding information obtaining request using the coding information obtaining means from the worker terminal, coding including the corresponding portion is stored in the source data storage. A coding check support program that functions as a second information presentation unit that extracts from the means, generates coding information screen data including the data, and transmits the coding information screen data to the worker terminal.

Images