Optimization supporting device and method for supporting optimization

Abstract

To provide a user with information for determining whether to apply an SQL statement obtained as a result of optimization.SOLUTION: An acquisition unit 112 acquires a second SQL (Structured Query Language) statement obtained by optimizing a first SQL statement input by a user U. A presentation unit 114 presents to the user U an index indicating an improvement effect of the second SQL statement relative to the first SQL statement. A specification reception unit 115 receives from the user U a specification indicating whether the first SQL statement is to be replaced with the second SQL statement.SELECTED DRAWING: Figure 2

Description

[Technical Field]

[0001] The present invention relates to an optimization support device and an optimization support method. [Background technology]

[0002] Conventionally, there is known a technique for proposing an optimal SQL (Structured Query Language) statement according to the type or version of a database to be searched. For example, the optimal SQL statement automatic generation device disclosed in Patent Document 1 below comprises: type identification means for identifying the type of relational database management system in which an input SQL statement is to be executed; condition analysis means for analyzing conditions included in the input SQL statement; and optimization means for retrieving a corresponding optimization rule from optimization rule storage means based on the sentence type of the input SQL statement, the type identified by the type identification means, and the conditions analyzed by the condition analysis means, and for converting the input SQL statement into an optimal SQL statement for the relational database management system to be executed using the retrieved optimization rule. [Prior art documents] [Patent documents]

[0003] [Patent Document 1] Japanese Patent Application Publication No. 11-25116 Summary of the Invention [Problem to be solved by the invention]

[0004] However, the SQL statements obtained as a result of optimization may not necessarily be more efficient than the SQL statements before optimization. In particular, if the user does not have a high level of knowledge about SQL statements, it is difficult to judge how appropriate the optimization results are.

[0005] The present invention aims to provide a user with information to help them decide whether or not to apply an SQL statement obtained as a result of optimization. [Means for solving the problem]

[0006] An optimization support device according to one embodiment of the present invention includes an acquisition unit that acquires a second SQL statement that has been optimized for a first SQL (Structured Query Language) statement input by a user, a presentation unit that presents to the user an indicator of the improvement effect of the second SQL statement relative to the first SQL statement, and a specification receiving unit that receives from the user a specification as to whether or not to replace the first SQL statement with the second SQL statement.

[0007] An optimization support method according to one embodiment of the present invention obtains a second SQL statement that has been optimized from a first SQL statement generated by a user, presents an indicator to the user regarding the improvement effect of the second SQL statement relative to the first SQL statement, and receives from the user an indication of whether or not to execute the second SQL statement instead of the first SQL statement. [Effects of the Invention]

[0008] According to one aspect of the present invention, it is possible to provide a user with information to help them decide whether or not to apply an SQL statement obtained as a result of optimization. [Brief explanation of the drawings]

[0009] [Figure 1] 1 is a block diagram showing the configuration of an SQL execution system 1 according to an embodiment. [Figure 2] FIG. 2 is a block diagram showing the configuration of a user terminal 10. [Figure 3] FIG. 10 is a schematic diagram showing an example of a program code creation screen SG. [Figure 4] FIG. 10 is a schematic diagram showing an example of a program code creation screen SG. [Figure 5] FIG. 10 is a schematic diagram showing an example of a program code creation screen SG. [Figure 6] FIG. 10 is a schematic diagram showing an example of a program code creation screen SG. [Figure 7] FIG. 10 is a diagram illustrating an example of optimizing an SQL statement using method 1. [Figure 8] FIG. 10 is a diagram illustrating an example of optimizing an SQL statement using method 2. [Figure 9] FIG. 10 is a diagram illustrating an example of optimizing an SQL statement using method 4. [Figure 10] FIG. 10 is a diagram illustrating an example of optimizing an SQL statement using method 5. [Figure 11] 10 is a diagram illustrating an example of SQL statement optimization using Method 6. [Figure 12] 10 is a flowchart showing the operation of the processing device 105 of the user terminal 10. DETAILED DESCRIPTION OF THE INVENTION

[0010] A. Embodiment A-1. System Configuration FIG. 1 is a block diagram showing the configuration of an SQL execution system 1 according to an embodiment. In the SQL execution system 1, a user terminal 10 and an SQL execution environment 20 are connected via a network N. The user terminal 10 is an example of an optimization support device. The user terminal 10 is an information processing terminal used by a user U, such as a personal computer. The user U uses the user terminal 10 to generate program code including an SQL statement. The program code is sent to the SQL execution environment 20 via the network N.

[0011] The SQL execution environment 20 is, for example, a data warehouse service that stores data and performs data analysis based on program code including SQL statements. When the SQL execution environment 20 receives program code from the user terminal 10, it returns the results of data analysis based on the program code to the user terminal 10. The SQL execution environment 20 may be provided as a cloud service.

[0012] A-2. Configuration of User Terminal 10 2 is a block diagram showing the configuration of the user terminal 10. The user terminal 10 includes a display device 101, an input device 102, a communication device 103, a storage device 104, a processing device 105, and a bus 120 that interconnects these devices.

[0013] The display device 101 is a display device (for example, various display panels such as a liquid crystal display panel or an organic EL display panel) that displays information to the outside. The input device 102 is an input device (for example, a keyboard, a mouse, a microphone, a switch, a button, or a sensor) that accepts input from the outside. The display device 101 and the input device 102 may be integrated into one device (for example, a touch panel). The communication device 103 has an interface connectable to the network N and communicates with other devices connected to the network N using wireless or wired communication.

[0014] The storage device 104 is a recording medium readable by the processing device 105. The storage device 104 includes, for example, a nonvolatile memory and a volatile memory. The nonvolatile memory is, for example, a read-only memory (ROM), an erasable programmable read-only memory (EPROM), and an electrically erasable programmable read-only memory (EEPROM). The volatile memory is, for example, a random access memory (RAM).

[0015] The storage device 104 stores a program PG1 and optimization rule data DO. The program PG1 is a program for operating the user terminal 10. The optimization rule data DO stores data for optimization by the optimization processing unit 113, which will be described later.

[0016] The processing device 105 includes one or more central processing units (CPUs). The one or more CPUs are examples of one or more processors. Each of the processor and the CPU is an example of a computer.

[0017] The processing device 105 reads the program PG1 from the storage device 104. By executing the program PG1, the processing device 105 functions as an input receiving unit 111, an acquisition unit 112, an optimization processing unit 113, a presentation unit 114, a specification receiving unit 115, and a replacement unit 116. In this embodiment, these functions are realized as the operation of an SQL editor with optimization function (hereinafter referred to as "program editor") installed on the user terminal 10, or as the operation of an add-on that provides an optimization function for SQL statements.

[0018] The input receiving unit 111, the acquisition unit 112, the optimization processing unit 113, the presentation unit 114, the specification receiving unit 115 and the replacement unit 116 may be configured using circuits such as a DSP (Digital Signal Processor), an ASIC (Application Specific Integrated Circuit), a PLD (Programmable Logic Device) and an FPGA (Field Programmable Gate Array).

[0019] A-3. Details of the processing device 105 Next, the input receiving unit 111, the acquisition unit 112, the optimization processing unit 113, the presentation unit 114, the designation receiving unit 115, and the replacement unit 116 realized by the processing device 105 will be described in detail.

[0020] The input receiving unit 111 receives input of program code including an SQL statement from a user U. In this embodiment, two or more SQL statements (first SQL statements described later) are input by the user U as part of the program code in the process of generating the program code. The program code may be input by selecting a pre-prepared template, or by the user U inputting a character string.

[0021] The acquisition unit 112 acquires a second SQL statement obtained by optimizing a first SQL statement entered by a user U. Optimization refers to reducing the execution time of a process using code written in SQL or reducing the amount of database scanning by rewriting code written in SQL so that the output result for the code written in SQL is equivalent to the code written in SQL. The first SQL statement is the SQL statement entered by the user U, and the second SQL statement is an SQL statement obtained by optimizing the first SQL statement. In cases where the user U enters an appropriate SQL statement and there is no room for optimization, the first SQL statement and the second SQL statement may be the same. In this embodiment, when the acquisition unit 112 detects that a first SQL statement has been entered into program code, the acquisition unit 112 inputs the first SQL statement to the optimization processing unit 113 and acquires the second SQL statement output from the optimization processing unit 113. For example, each time a first SQL statement is entered into program code, the acquisition unit 112 acquires a second SQL statement corresponding to the first SQL statement.

[0022] The optimization processing unit 113 performs optimization on the input first SQL statement and outputs it as a second SQL statement. The optimization processing unit 113 performs optimization on the first SQL statement, for example, using optimization rule data DO. The optimization rule data DO includes a table in which, for example, an SQL statement example, a target system name, a rule application condition, and an optimization rule form one unit (one set). The target system name may be, for example, an identifier that identifies the SQL execution environment 20. Optimization rules are added sequentially to the optimization rule data DO.

[0023] The optimization processor 113 searches for an information set that has an example SQL statement of the same syntax as the input first SQL statement and that includes, as the target system name, an identifier that specifies the SQL execution environment 20 that will execute the first SQL statement. This information set includes a rule application condition and an optimization rule. The rule application condition is a rule for determining whether or not to apply an optimization rule in the information set based on the correspondence between attribute information obtained from the input first SQL statement. If the optimization processor 113 determines, based on the rule application condition, that an optimization rule should be applied, it rewrites the first SQL statement based on the optimization rule and outputs it as a second SQL statement.

[0024] The presentation unit 114 presents to the user U an index relating to the improvement effect of the second SQL statement relative to the first SQL statement. The index relating to the improvement effect is the element improved and the amount of improvement when processing is performed using the second SQL statement instead of the first SQL statement. The index relating to the improvement effect may include, for example, at least one of the amount of database scanning and the processing execution time when processing is performed on a database using the second SQL statement.

[0025] When the program code includes multiple SQL statements, the presentation unit 114 presents an index related to the improvement effect for the entire program code. Furthermore, when the program code includes multiple SQL statements, the presentation unit 114 may present an index related to the improvement effect each time a first SQL statement is input and a second SQL statement is acquired. Furthermore, the presentation unit 114 may present the first SQL statement and the second SQL statement to the user U so that they can be compared, along with the index related to the improvement effect.

[0026] The specification receiving unit 115 receives a specification from the user U as to whether or not to replace the first SQL statement with the second SQL statement. When the program code includes multiple SQL statements, the specification receiving unit 115 may receive the specification each time the presentation unit 114 presents an index relating to the improvement effect.

[0027] When the specification receiving unit 115 specifies that replacement with the second SQL statement is permitted, the replacing unit 116 replaces the first SQL statement with the second SQL statement. When the specification receiving unit 115 specifies that replacement with the second SQL statement is not permitted, the replacing unit 116 leaves the first SQL statement in the program code as is and does not replace it with the second SQL statement.

[0028] The above-mentioned functional units will be described in detail below with reference to the drawings. Figures 3 to 6 are schematic diagrams showing an example of a program code creation screen SG. The program code creation screen SG shown in Figures 3 to 6 is displayed on the display device 101 of the user terminal 10, for example. The program code creation screen SG is displayed by starting a program editor installed on the user terminal 10, for example.

[0029] 3, the program code creation screen SG includes a program code input section 301, a save button 302, an execute button 303, and a total improvement effect display section 304. A user U inputs program code into the program code input section 301 via the input device 102 of the user terminal 10. In other words, the input accepting section 111 accepts the input of program code into the program code input section 301. In the example of FIG. 3, a first non-SQL statement (denoted as "non-SQL statement-1" in the figure) and a second non-SQL statement (denoted as "non-SQL statement-2" in the figure) have been input into the program code input section 301. These two statements are non-SQL statements, and therefore are not optimized by the optimization processing section 113.

[0030] When the save button 302 is pressed, the program code entered in the program code input section 301 is stored in the storage device 104. The user U can read and re-edit the program code stored in the storage device 104 by operating a read button (not shown). Furthermore, when the execute button 303 is pressed, the program code entered in the program code input section 301 is sent to the SQL execution environment 20 via the communication device 103, and the execution results of the program code are returned to the user terminal 10. When the execution results of the program code are returned, for example, a screen different from the program code creation screen SG is displayed, and the execution results of the program code are displayed on that screen.

[0031] The total improvement effect due to the optimization of SQL statements implemented in the process of creating the program code is displayed in the total improvement effect display section 304. In this embodiment, the amount of database scanning (hereinafter simply referred to as "scan amount") and the execution time of processing (hereinafter simply referred to as "execution time") are displayed as indicators of the improvement effect.

[0032] 4, a first SQL statement (denoted as "first SQL statement-1" in the figure) is input to program code input unit 301. When an SQL statement is input to program code input unit 301, acquisition unit 112 passes the input SQL statement as a first SQL statement to optimization processing unit 113. Optimization processing unit 113 optimizes the first SQL statement to generate a second SQL statement and passes it to acquisition unit 112.

[0033] In addition to the display shown in FIG. 3 , FIG. 4 displays an optimization result display section 305 by the presentation unit 114. The optimization result display section 305 includes a first SQL statement display section 306, a second SQL statement display section 307, an improvement effect display section 308, and a selection button 309. The first SQL statement display section 306 displays the SQL statement input to the program code input section 301 (first SQL statement-1 in FIG. 4 ). The second SQL statement display section 307 displays the SQL statement optimized by the optimization processing unit 113 (second SQL statement corresponding to first SQL statement-1). That is, the first SQL statement display section 306 and the second SQL statement display section 307 are used to present the first SQL statement and the second SQL statement to the user U so that they can be compared. At least one of the first SQL statement display section 306 and the second SQL statement display section 307 may highlight the differences between the first SQL statement and the second SQL statement.

[0034] The improvement effect display section 308 displays an index relating to the improvement effect when the first SQL statement displayed in the first SQL statement display section 306 is replaced with the second SQL statement displayed in the second SQL statement display section 307. In the example of Fig. 4, it is displayed that the execution time is reduced by 123 ms (-123 ms). A reduction in execution time may be rephrased as an increase in processing speed.

[0035] The selection buttons 309 include a replace button, a cancel button, and a hold button. The replace button is a button that allows the first SQL statement displayed in the first SQL statement display section 306 to be replaced with the second SQL statement displayed in the second SQL statement display section 307. The cancel button is a button that does not allow the first SQL statement displayed in the first SQL statement display section 306 to be replaced with the second SQL statement displayed in the second SQL statement display section 307. The hold button is a button for holding off on determining whether or not to replace the first SQL statement displayed in the first SQL statement display section 306 with the second SQL statement displayed in the second SQL statement display section 307. The user U presses one of the selection buttons 309. Note that the hold button may not be provided, and only the replace button and the cancel button may be provided. The specification receiving unit 115 receives an input to the selection buttons 309 as a specification from the user U as to whether or not to replace the first SQL statement with the second SQL statement.

[0036] As shown in FIG. 4, the user U can determine whether or not to replace the first SQL statement with the second SQL statement after checking the indicators related to the improvement effect presented in the improvement effect display unit 308. In other words, the user U can be provided with information to determine whether or not to replace the first SQL statement with the second SQL statement. Furthermore, the first SQL statement and the second SQL statement are displayed in the first SQL statement display unit 306 and the second SQL statement display unit 307 so that they can be compared, allowing the user U to understand the differences between the SQL statements before and after optimization. As a result, it is possible to provide the user U with an opportunity to learn how to create more optimal SQL statements.

[0037] FIG. 5 shows a case where the Replace button of the selection buttons 309 is pressed in the state shown in FIG. 4. In place of the first SQL statement-1, the second SQL statement-1, which is a second SQL statement corresponding to the first SQL statement-1, is displayed in the program code input section 301. In the program code input section 301, the SQL statement may be displayed in a different display mode from the non-SQL statement. A different display mode may, for example, be displayed in a different color or marked. This makes it easier for the user U to identify the position of the SQL statement in the program code input section 301. In the example of FIG. 5, the second SQL statement-1 is shaded.

[0038] Furthermore, the display mode may be changed even within an SQL statement based on, for example, whether or not a first SQL statement can be replaced with a second SQL statement (the input result on the selection button 309) (see FIG. 6). Specifically, the display mode in the program code input section 301 may be different for an SQL statement permitted to be replaced with a second SQL statement, an SQL statement permitted to be replaced with a second SQL statement, and an SQL statement for which the question of whether or not to replace with a second SQL statement is pending. This makes it easier for the user U to understand the designated status of whether or not an SQL statement can be replaced.

[0039] 5, an index relating to the improvement effect resulting from replacing the first SQL statement-1 with the second SQL statement-1 is displayed in the total improvement effect display section 304. As displayed in the improvement effect display section 308 in Fig. 4, replacing the first SQL statement-1 with the second SQL statement-1 reduces the execution time by 123 ms, and therefore the execution time column in the total improvement effect display section 304 displays "-123 ms."

[0040] FIG. 6 shows a state in which multiple more statements have been input into the program code input section 301. Assume that after the second SQL statement-1 shown in FIG. 5, a third non-SQL statement (denoted as "non-SQL statement-3" in the figure), a second SQL statement (denoted as "first SQL statement-2" in the figure), a third SQL statement (denoted as "second SQL statement-3" in the figure), and a fourth non-SQL statement (denoted as "non-SQL statement-4" in the figure) have been input. The second SQL statement is not permitted to be replaced with the second SQL statement and remains the first SQL statement, so it is represented as "first SQL statement-2." The third SQL statement is permitted to be replaced with the second SQL statement and is replaced with the second SQL statement, so it is represented as "second SQL statement-3." In FIG. 6, "first SQL statement-2," which was not permitted to be replaced with the second SQL statement, is displayed differently from "second SQL statement-1" and "second SQL statement-3," which were permitted to be replaced with the second SQL statement.

[0041] 6, the total value (total value) of the indexes relating to the improvement effect resulting from replacing the first SQL statement with the second SQL statement for the first and third SQL statements is displayed in the total improvement effect display section 304. Specifically, the scan amount column of the total improvement effect display section 304 in FIG. 6 displays "-27 GB" and the execution time column displays "-123 ms."

[0042] In this way, by displaying an index relating to the improvement effect for the entire program code, the user U can specifically grasp the merits of optimizing the SQL statements. Note that in FIG. 6 and other figures, the total improvement effect display section 304 displays the improvement effect obtained as a result of the user U selecting whether or not to replace the first SQL statements with the second SQL statements. However, without being limited to this, for example, the improvement effect obtained when all the first SQL statements are replaced with the second SQL statements may also be displayed (also displayed) in the total improvement effect display section 304.

[0043] Next, details of the optimization of the SQL statement by the optimization processor 113 will be described. In this embodiment, the optimization processor 113 optimizes the first SQL statement and generates the second SQL statement using an optimization method including the following methods 1 to 6. Note that the method of optimizing the SQL statement by the optimization processor 113 is not limited to the one described below, and various methods can be applied.

[0044] [Method 1] Use the WHERE clause instead of the LIMIT clause Figure 7 shows an example of optimizing an SQL statement using Method 1. Method 1 uses a WHERE clause to filter data in order to avoid scanning all data in the table. As a prerequisite, it is necessary to specify the optimal columns and ranges for narrowing down the WHERE clause for each table.

[0045] It is possible to reduce the amount of scanning by using Method 1. For example, the scan amount for the SQL statement before optimization shown in Figure 7 is 287.85 GB, while the scan amount for the SQL statement after optimization is 797.33 MB, meaning that the scan amount can be reduced by approximately 287.05 GB before and after optimization.

[0046] [Method 2] Convert DISTINCT to GROUP BY Figure 8 is a diagram showing an example of optimizing an SQL statement using Method 2. Method 2 is a method of converting DISTINCT to GROUP BY. This is because deleting duplicates using GROUP BY is faster than deleting duplicates using DISTINCT. No special pre-settings are required.

[0047] By using Method 2, it is possible to reduce the execution time. For example, in the SQL statement before optimization shown in Figure 8, the elapsed time (execution time) until the end of processing is 495 ms and the slot consumption time is 5 seconds, whereas in the SQL statement after optimization, the elapsed time (execution time) is 372 ms and the slot consumption time is 2 seconds. Therefore, the elapsed time (execution time) can be reduced by 123 ms and the slot consumption time by 3 seconds.

[0048] [Method 3] Use ORDER BY on the outermost side Method 3 avoids sorting records of large tables within subqueries, so when queries are nested, the ORDER BY clause is specified only in the top-level SELECT, without sorting (ORDER BY clause) in the subquery. This prevents performance bottlenecks caused by memory overflow. Using method 3 can reduce the amount of scanning.

[0049] [Method 4] Avoid self-joins Figure 9 is a diagram showing an example of optimizing an SQL statement using Method 4. Method 4 is a method that reduces join processing costs and execution time by avoiding self-joins. By using Method 4, it is possible to reduce execution time.

[0050] [Method 5] Optimizing the LIMIT position when using window functions Figure 10 shows an example of SQL statement optimization using Method 5. Method 5 is a method for narrowing down large amounts of data using Limit before using a window function when you want to assign higher ranks or row numbers to the data. By using Method 5, you can reduce the amount of processing required to assign ranks and row numbers, thereby reducing execution time.

[0051] [Method 6] Optimizing aggregate functions to retrieve the latest records Figure 11 is a diagram showing an example of optimizing an SQL statement using Method 6. Method 6 is a method for extracting the latest records using aggregate functions (min_by, max_by, etc.) instead of window functions (RANK or ROW_NUMBER, etc.). Using Method 6 can reduce execution time.

[0052] A-4. Flowchart 12 is a flowchart showing the operation of the processing device 105 of the user terminal 10. The processing device 105 waits until an instruction to start creating program code is received (step S100: NO). When an instruction to start creating program code is received (step S100: YES), the processing device 105 functions as the input receiving unit 111 and receives input from the user U to the program editor (for example, the program code input unit 301 in FIG. 3) (step S102).

[0053] If the statement input by the user U is not an SQL statement (step S104: NO), the processing device 105 proceeds to step S116. If the statement input by the user U is an SQL statement (step S104: YES), the processing device 105 functions as the acquisition unit 112 and the optimization processing unit 113, and performs optimization on the input SQL statement (step S106). The input SQL statement is the first SQL statement, and the optimized SQL statement is the second SQL statement.

[0054] The processing device 105 functions as the presentation unit 114 and presents to the user U an index relating to the improvement effect of the second SQL statement relative to the first SQL statement (step S108). At this time, the processing device 105 may present the first SQL statement and the second SQL statement to the user U so that they can be compared. The processing device 105 functions as the specification reception unit 115 and receives from the user U a specification as to whether or not to replace the first SQL statement with the second SQL statement (step S110).

[0055] If the user U permits the replacement (step S110: YES), the processing device 105 functions as the replacement unit 116 and replaces the first SQL statement in the program code with the second SQL statement (step S112). If the user U does not permit the replacement (step S110: NO), the processing device 105 leaves the first SQL statement in the program code as is (step S114). Note that if the Hold button of the selection buttons 309 is pressed, the user U may be allowed to make a final decision, for example, at the timing when the program code is sent to the SQL execution environment 20 in step S118.

[0056] Until the creation of the program code is completed (step S116: NO), the processing device 105 returns the process to step S102. In this flowchart, the completion of the creation of the program code corresponds to pressing the execute button 303 shown in FIG. 3 etc. When the creation of the program code is completed (step S116: YES), the processing device 105 sends the program code to the SQL execution environment 20 (step S118). The processing device 105 receives the execution result of the program code from the SQL execution environment 20, outputs the execution result (step S120), and ends the processing of this flowchart.

[0057] A-5. Summary of implementation As described above, when the user terminal 10 according to the embodiment performs optimization on a first SQL statement input by the user U, it presents the user U with an index relating to the improvement effect of the second SQL statement relative to the first SQL statement. This provides the user U with information to determine whether or not to replace the first SQL statement with the second SQL statement. After checking the specific effect of the optimization, the user U can determine whether or not to replace the first SQL statement with the second SQL statement, improving convenience for the user U.

[0058] Furthermore, the user terminal 10 presents not only the improvement effect due to the optimization of individual SQL statements but also an index relating to the improvement effect of the entire program code, thereby enabling the user U to grasp the degree of improvement in the execution efficiency of the entire program code due to the optimization, thereby improving convenience.

[0059] Furthermore, the user terminal 10 acquires an optimized second SQL statement each time a first SQL statement is input, and presents an index of the improvement effect each time a second SQL statement is acquired, prompting the user to determine whether or not replacement is possible. Thus, the user U receives feedback each time an SQL statement is input, thereby improving the efficiency of learning SQL statements. Furthermore, after some time has passed since inputting an SQL statement, the user may forget why he or she selected that SQL statement. By receiving feedback each time an SQL statement is input, the user U can determine whether or not replacement is possible while the intention behind selecting that SQL statement is still clear.

[0060] Furthermore, since the user terminal 10 replaces the first SQL statement in the program code with the second SQL statement after receiving permission from the user U, it is possible to prevent the user U from replacing the SQL statement with another SQL statement that he or she does not intend.

[0061] Furthermore, the user terminal 10 displays the first SQL statement and the second SQL statement in a comparative manner in the first SQL statement display section 306 and the second SQL statement display section 307. The user U can understand the difference between the SQL statements before and after optimization. As a result, it is possible to provide the user U with an opportunity to learn how to create more optimal SQL statements.

[0062] B: Modified example The following are variations of the above-described embodiment. Two or more variations arbitrarily selected from the following variations may be combined as appropriate within the scope of not mutually contradicting each other.

[0063] B1: First modified example In the above-described embodiment, the optimization processing unit 113 is provided in the user terminal 10. However, the present invention is not limited to this, and a component corresponding to the optimization processing unit 113 may be provided in a device separate from the user terminal 10. In this case, the separate device holds the optimization rule data DO. The acquisition unit 112 transmits the first SQL statement to the separate device and receives the second SQL statement from the separate device. The separate device may be a device constituting the SQL execution environment 20.

[0064] According to the first modified example, it is possible to reduce the processing load on the user terminal 10. Furthermore, according to the first modified example, it becomes easier to keep the optimization rule data DO up to date, and optimization can be performed efficiently.

[0065] B2: Second variant In the above-described embodiment, the SQL statement is optimized each time the SQL statement is input into the program editor. However, the present invention is not limited to this. For example, the SQL statement may be optimized at the timing specified by the user U. In this case, if multiple SQL statements have been input before the user U issues an instruction to perform optimization, the multiple SQL statements are optimized together. Alternatively, for example, the SQL statements included in the program code may be optimized together when the user U issues an instruction to end the creation of the program code.

[0066] According to the second modification, the processing load on the processing device 105 is reduced compared to when optimization is performed every time an SQL statement is input. Also, according to the second modification, optimization related to, for example, multiple SQL statements can be performed efficiently.

[0067] C:Other (1) In the above-described embodiment, ROM and RAM are exemplified as storage device 104, but storage device 104 may also be a flexible disk, a magneto-optical disk (e.g., a compact disk, a digital versatile disk, a Blu-ray (registered trademark) disc), a smart card, a flash memory device (e.g., a card, a stick, a key drive), a CD-ROM (Compact Disc-ROM), a register, a removable disk, a hard disk, a floppy (registered trademark) disk, a magnetic strip, a database, a server, or other suitable storage medium.

[0068] (2) In the above-described embodiments, the described information, signals, etc. may be represented using any of a variety of different technologies. For example, data, instructions, commands, information, signals, bits, symbols, chips, etc. that may be referred to throughout the above description may be represented by voltages, currents, electromagnetic waves, magnetic fields or magnetic particles, optical fields or photons, or any combination thereof.

[0069] (3) In the above-described embodiment, input and output information may be stored in a specific location (for example, a memory) or may be managed using a management table. Input and output information may be overwritten, updated, or added to. Output information may be deleted. Input information may be transmitted to another device.

[0070] (4) In the above-described embodiment, the determination may be made based on a value represented by one bit (0 or 1), a Boolean value (true or false), or a comparison of numerical values ​​(e.g., comparison with a predetermined value).

[0071] (5) The order of the process procedures, sequences, flowcharts, etc. illustrated in the above-described embodiments may be rearranged unless inconsistent. For example, the methods described in this disclosure present elements of various steps using an example order, and are not limited to the particular order presented.

[0072] (6) Each function illustrated in FIG. 2 is realized by any combination of at least one of hardware and software. Furthermore, the method for realizing each functional block is not particularly limited. That is, each functional block may be realized using a single device that is physically or logically coupled, or may be realized using two or more physically or logically separated devices that are directly or indirectly connected (for example, by wire, wirelessly, etc.) and these multiple devices. A functional block may also be realized by combining software with the single device or the multiple devices.

[0073] (7) The programs exemplified in the above-described embodiments should be broadly construed to mean instructions, instruction sets, code, code segments, program code, programs, subprograms, software modules, applications, software applications, software packages, routines, subroutines, objects, executable files, threads of execution, procedures, functions, etc., regardless of whether they are called software, firmware, middleware, microcode, hardware description language, or by other names.

[0074] Software, instructions, information, etc. may also be transmitted or received over a transmission medium. For example, if software is transmitted from a website, server, or other remote source using wired technologies (such as coaxial cable, fiber optic cable, twisted pair, Digital Subscriber Line (DSL)), and / or wireless technologies (such as infrared, microwave), then these wired and / or wireless technologies are included within the definition of transmission media.

[0075] (8) In each of the foregoing embodiments, the terms "system" and "network" are used interchangeably.

[0076] (9) The information, parameters, etc. described in this disclosure may be expressed using absolute values, relative values ​​from a predetermined value, or corresponding other information.

[0077] (10) In the above-described embodiments, the portable device may be a mobile station (MS). Those skilled in the art may also refer to a mobile station as a subscriber station, mobile unit, subscriber unit, wireless unit, remote unit, mobile device, wireless device, wireless communication device, remote device, mobile subscriber station, access terminal, mobile terminal, wireless terminal, remote terminal, handset, user U agent, mobile client, client, or some other suitable terminology. In this disclosure, terms such as "mobile station," "user U terminal," "user U equipment (UE)," and "terminal" may be used interchangeably.

[0078] (11) In the above-described embodiments, the terms "connected," "coupled," or any variation thereof refers to any direct or indirect connection or coupling between two or more elements, and may include the presence of one or more intermediate elements between two elements that are "connected" or "coupled" to each other. The coupling or connection between elements may be physical, logical, or a combination thereof. For example, "connected" may be read as "access." As used in this disclosure, two elements may be considered to be "connected" or "coupled" to each other using at least one of one or more wires, cables, and printed electrical connections, as well as electromagnetic energy having wavelengths in the radio frequency range, microwave range, and optical (both visible and invisible) range, as some non-limiting and non-exhaustive examples.

[0079] (12) In the above embodiments, the phrase "based on" does not mean "based only on," unless otherwise specified. In other words, the phrase "based on" means both "based only on" and "based at least on."

[0080] (13) As used in this disclosure, the terms "determining" and "determining" may encompass a wide variety of actions. "Determining" and "determining" may include, for example, judging, calculating, computing, processing, deriving, investigating, looking up, searching, inquiring (e.g., searching in a table, database, or other data structure), ascertaining, and the like. "Determining" and "determining" may also include receiving (e.g., receiving information), transmitting (e.g., sending information), input, output, accessing (e.g., accessing data in memory), and the like. Furthermore, "judgment" and "decision" can include regarding reSQLving, selecting, choosing, establishing, comparing, etc. as having been "judged" or "decided." In other words, "judgment" and "decision" can include regarding some action as having been "judged" or "decided." Furthermore, "judgment (decision)" can be interpreted as "assuming," "expecting," "considering," etc.

[0081] (14) In the above embodiments, when "include," "including," and variations thereof are used, these terms are intended to be inclusive, similar to the term "comprising." Furthermore, the term "or" as used in this disclosure is not intended to be an exclusive or.

[0082] (15) In this disclosure, where articles are added by translation, such as a, an, and the in English, this disclosure may include the nouns following these articles being plural.

[0083] (16) In this disclosure, the term "A and B are different" may mean "A and B are different from each other." The term may also mean "A and B are each different from C." Terms such as "separate" and "combined" may also be interpreted in the same way as "different."

[0084] (17) Each aspect / embodiment described in this disclosure may be used alone, in combination, or switched depending on the implementation. Notification of predetermined information (e.g., notification that "X is true") is not limited to explicit notification, but may be implicit (e.g., not notifying the predetermined information). [Explanation of symbols]

[0085] 1...SQL execution system, 10...user terminal, 20...SQL execution environment, 101...display device, 102...input device, 103...communication device, 104...storage device, 105...processing device, 111...input reception unit, 112...acquisition unit, 113...optimization processing unit, 114...presentation unit, 115...specification reception unit, 116...replacement unit, N...network, U...user.

Claims

1. an acquisition unit that acquires a second SQL statement that is an optimized version of a first SQL statement input by a user; a presentation unit that presents to the user an index relating to an improvement effect of the second SQL statement relative to the first SQL statement; a designation receiving unit that receives, from the user, a designation as to whether or not the first SQL statement should be replaced with the second SQL statement; An optimization support device comprising:

2. two or more of the first SQL statements are input by the user as part of the program code in a process of generating the program code; the presenting unit presents an index regarding an improvement effect in the entire program code. The optimization support device according to claim 1.

3. the index relating to the improvement effect includes at least one of the amount of scanning of the database and the execution time of the processing when the processing is performed on the database using the second SQL statement; The optimization support device according to claim 1.

4. a replacement unit that replaces the first SQL statement with the second SQL statement when the specification receiving unit is instructed to replace the first SQL statement with the second SQL statement; The optimization support device according to claim 1.

5. the presenting unit presents the first SQL statement and the second SQL statement to the user in a comparable manner together with an index related to the improvement effect. The optimization support device according to claim 1.

6. Obtaining a second SQL statement that is an optimization of the first SQL statement generated by the user; presenting to the user an index relating to an improvement effect of the second SQL statement relative to the first SQL statement; accepting, from the user, whether or not the second SQL statement is to be executed in place of the first SQL statement; Optimization support methods.

Images