JPH10283371A - Data base device - Google Patents

Abstract

PROBLEM TO BE SOLVED: To provide a data base device by which even a user unfamiliar with the optimal preparation of the processing instruction of an SQL(structured questioning language) sentence or the like can perform access to a data base without deteriorating large speed deterioration. SOLUTION: The processing content of a processing instruction inputted from an inputting device 1 is analyzed by a decoding device 5, and when the recombination of the processing instruction is impossible, it is outputted to a selecting device 8. When the recombination is impossible, it is inputted to a processing speed estimating device 7 as it is, and the processing speed is estimated, and compared with the estimation of the processing speed of the processing instruction recombined by a recombining device 6. Then, the processing whose processing speed is faster is selected by a selecting device 8, and the processing instruction outputted from the selecting device 8 is executed by a data managing device 4.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a database device, and more particularly to a database access method for an information processing system having a database.

[0002]

2. Description of the Related Art In a conventional database device, when a user performs an atypical process, a structured language (SQL), which is a database language created by the user, is used.
nguage; structured query language, for example, JIS X3005
(See) Statement is executed as it is. FIG. 4 shows an example of the configuration of this conventional database device. Referring to FIG. 4, the database device includes an input device 1 for inputting an SQL sentence and the like, and a data management device 4 for executing the input SQL sentence and performing a specified data operation. .

The above-mentioned conventional method is an effective method when a user sufficiently grasps the structure of a database and the storage state of data and makes an inquiry, but a general user does not have such knowledge. In general, most users do not have any understanding of the structure of the database, the state of data storage, and the like.

In such a case, an SQL statement created by a user for querying a database is not an SQL statement optimized for efficient data retrieval and the like, and is usually exclusively used. It is created as a SQL sentence that is easy for the user to understand.

For this reason, for example, in a case where data meeting certain conditions is extracted from a certain database, and the data is used for other data processing, the data amount of the data to be accessed first is large. Therefore, even if the processing time is greatly shortened by creating an intermediate database, the same access to the first database is performed every time, just as with a small database.
As a result of the access, a process of performing the next process based on the corresponding data may be repeated many times. Such data manipulation causes an increase in the processing time as the initial data amount increases, and has a great influence on other processing times.

That is, processing that ignores the amount of data and the storage state held in the database greatly reduces the processing capacity of the entire system.

[0007] For example, Japanese Patent Laid-Open Publication No. Hei 6-231181 proposes a distributed database control method that enables a single SQL statement to execute a subquery across other sites in a distributed database system. However, for example, means for optimizing processing depending on the size of a table or the like is not disclosed.

[0008] For example, Japanese Patent Application Laid-Open No. 8-147200 discloses that when a data object is searched for in a database system, the size of a search result set (effective table) can be obtained in advance. And a method for reducing unnecessary processing in a database system and realizing efficient database control have been proposed. That is, the SQL statement is analyzed and executed, and the efficiency is attained by storing the size of the table of the execution result. However, storing the size of the table of the execution result for all processes is practically impossible. It is difficult, and effective use is limited to specific processing.

[0009]

As described above, it is difficult to optimize access to a database by a conventional method in which a SQL statement created by a user is directly executed in a database device. Have a point.

[0010] The reason is that it is difficult for an ordinary user to create an optimized SQL sentence.

That is, a general user can accurately grasp the status of the database and reflect the status of the database.
It is usually difficult to optimize and create a QL sentence. In addition, although it is possible for a general user to create a desired SQL sentence, it is difficult to always recognize information on data arrangement and search order.
That is because it is the actual situation.

Accordingly, the present invention has been made in view of the above-described problems, and has as its object to provide a user who is not accustomed to optimally creating a processing instruction such as a SQL statement as a data manipulation language. On the other hand, an object of the present invention is to provide a database device that can efficiently access a database without causing a significant reduction in speed.

[0013]

In order to achieve the above object, a database apparatus of the present invention comprises a data processing command inputting means for receiving a data processing command created by a user, and analyzing the input data processing command. Analyzing means for determining whether or not the processing method can be divided, and generating a data processing instruction obtained by dividing the processing if the processing method can be divided;
Storage means for storing information on the data amount and processing speed of each data to be processed, and a processing method in the data management device, wherein the analysis means executes the input data processing instruction as it is And a case where the processing is executed with the corrected data processing instruction, the respective processing time estimates are compared, and a data processing instruction that can be processed at higher speed is selected, and the selected data processing instruction is transferred to the data management device. , And is executed.

In the present invention, SQL language is preferably used as the data processing instruction.

Further, in the present invention, the analysis means judges whether or not the input SQL sentence can be decomposed into an SQL sentence for performing a plurality of processes. A processing time and a processing time by a series of decomposed SQL statements are calculated based on the information in the storage unit,
Only SQL statements that can be processed at higher speed are selected.

[0016]

Embodiments of the present invention will be described below. According to a preferred embodiment of the present invention, the processing time of a data processing command (also referred to simply as a “processing command”) such as an SQL statement input by a user is estimated, and the SQL is processed within a range where the processing result does not change. It is automatically determined whether or not to modify the processing instruction such as a sentence, and by adopting a processing method capable of higher speed, the processing speed is improved, and
Even if the user is unfamiliar with creating processing instructions such as QL sentences optimally, the database can be accessed without causing a significant speed reduction.

More specifically, in a preferred embodiment of the database device of the present invention, the data processing command input means (1 in FIG. 1) for receiving input of a data processing command created by a user is inputted. The data processing instruction is analyzed, it is determined whether or not the processing method can be divided. If it is determined that the processing method can be divided, an analyzing means (FIG. 1) for creating a data processing instruction obtained by dividing the input data processing instruction. 2)
A data management device that processes a data processing instruction and manages a database; storage means for storing information on individual data amounts and processing speeds to be processed; and a processing method in the data management device (FIG. 1). 3), and the analysis means (2 in FIG. 1) estimates the respective processing times when executing the input data processing instruction as it is and when executing the corrected data processing instruction. By comparing and examining, a data processing instruction that can be processed at a higher speed is selected, and the selected data processing instruction is transferred to a data management device (FIG. 1).
Execute in 4).

In the embodiment of the present invention, a processing instruction input by a user is optimized based on information in an analyzing means and a storing means, and a processing instruction considered to be able to be processed at a higher speed is passed to a data management device. Therefore, if the user's knowledge level or proficiency is low, a corrected processing instruction is passed; otherwise, the original processing instruction is passed and processed by the data management device. If the input and output of the analysis means are the same processing instruction, the data management device does not need to distinguish between the processing instruction as given by the user and the processing instruction corrected by the analysis means. Absent.

In the embodiment of the present invention, the storage means preferably stores basic management information such as the size and physical arrangement of data managed by the data management apparatus, and physical Processing time corresponding to various arrangements, for example, information such as data access time per unit and data transfer time per unit are stored, and in addition to these, the processing time when accessing various data and , Information on the processing method in the data management device is also stored.

Based on these pieces of information held in the storage means, when the analysis means determines that the processing instruction clearly has a useless function, the analysis means automatically performs a possible rearrangement of the processing instruction and the like. Evaluate each and select the processing instruction that is considered optimal.

Further, in the embodiment of the present invention, the same processing is performed by decomposing and / or rearranging the input SQL sentence with respect to the database device using the SQL language as the processing instruction. Construct an SQL statement,
Estimate the processing time. That is, the input SQ
Judge whether the L sentence can be decomposed into an SQL sentence that performs a plurality of processes, and if it can be decomposed, calculate the processing time for the original SQL sentence and the processing time for a series of decomposed SQL sentences Then, only SQL statements that can be processed at higher speed are selected.

In the embodiment of the present invention,
(A) a processing procedure for receiving a data processing command created by a user from an input device, analyzing the input data processing command, and determining whether the data processing command can be disassembled or rearranged; A processing procedure for creating a data processing instruction modified by disassembly or rearrangement if possible, (c) information on the amount of data and processing speed to be processed, and a data management apparatus for executing the data processing instruction By referring to information in the storage unit storing the processing method, an estimate of each processing time is calculated for the case where the input data processing instruction is executed as it is and for the case where the input data processing instruction is executed using the corrected data processing instruction. Processing procedure, and (d) selecting a data processing instruction that can be processed at a higher speed among these estimates, and selecting the selected data processing instruction. Each of the processing procedures (a) to (d) of the processing procedure for supplying an instruction to the data management apparatus is realized by a program module or the like. An optimum data processing instruction for the instruction is supplied to the data management device, so that the efficiency of data access can be improved.

[0023]

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS In order to explain the embodiments of the present invention in more detail, embodiments of the present invention will be described below with reference to the drawings. FIG. 1 is a diagram showing the configuration of one embodiment of the present invention. Referring to FIG. 1, reference numeral 1 denotes an input device, 2 denotes an analysis device, 3 denotes a storage device, and 4 denotes a data management device.
The analysis device 2 is connected to the input device 1, the storage device 3, and the data management device 4, and the storage device 3 is connected to the analysis device 2 and the data management device 4.

The storage device 3 receives data and the like indicating the status of the database from the data management device 4, stores and manages the data, and outputs related data in response to a request from the analysis device 2.

The analysis device 2 receives a processing instruction input to the input device 1, finds data to be processed, for example, a table, and receives related information from the storage device 3. At the same time, the analysis device 2 analyzes the input processing instruction, and when the processing can be decomposed into a plurality of procedures, combines them and performs a rearrangement processing or the like within a range where the processing result does not change.

Then, the analyzing apparatus 2 estimates the processing time for each of the original processing and the variously modified processing in accordance with the processing procedure.

Here, the processing command input to the analysis device 2 does not need to be the same as that of the input device 1, but may be an intermediate language, and does not necessarily have to be the same. Similarly, in the analysis device 2, the input processing command and the output processing command are also used, for example, when the input of the analysis device 2 is in the SQL language and the output from the analysis device 2 is used in the data management device 4. However, the description and language level do not necessarily have to match.

The storage device 3 stores data size, distribution status, and the like, and is updated at certain intervals. The update interval is, for example,
It is also possible to adopt a method of performing a process of confirming at a fixed time interval or updating each time the database is accessed. Alternatively, at a certain time interval, actually access the data and store the result of measuring the processing completion time,
It is also possible.

FIG. 2 shows an example of the configuration of the analyzer 2 in this embodiment. Referring to FIG. 2, 5 is a decoding device, 6 is a rearrangement device, 7 is a processing speed estimation device, and 8 is a selection device.

Referring to FIG. 2, the decoding device 5 analyzes the processing contents of the processing command input from the input device 1 and, when it is determined that the rearrangement of the processing command is not possible, selects the processing command as it is. Output to the device 8. on the other hand,
If the rearrangement is possible, the input original processing instruction is input as it is to the processing speed estimating device 7 to estimate the processing speed. The modified processing instruction output by rearranging the original processing instruction in the rearrangement device 6 is input to the processing speed estimating device 7 to estimate the processing speed of the corrected processing instruction, and the selecting device is selected from among these estimates. 8 selects the faster process.

The input and output of each device can take any form, and the form of each device is not particularly limited. For example, all devices other than the storage device 3 and the data management device 4 can be performed by the client-side front-end device. In this case, since the input and output to the client can be the same processing instruction in the SQL language or the like, the input device of the conventional database device shown in FIG. 4 is functionally expanded. Of course, the storage device 3
It is also possible to take in. But in that case,
It is necessary to communicate with the data management device 4 and periodically update the stored data.

Conversely, a function other than the input device 1 may be a function on a database management device such as a server. This is different from the case where a conventional embedded SQL language is converted into another language such as C language and optimized.

FIG. 3 shows an example of the rearrangement of the SQL sentence. FIG. 3A shows an example of the input SQL sentence.
FIG. 3B is a diagram showing an example of the SQL sentence after rearranging the SQL sentence of FIG.

In the query (SELECT sentence) based on the SQL sentence shown in FIG. 3A, the table α and the table β exist, and the term A is extracted. At this time, the process of extracting the term B of the table β is necessary. Become. In the conventional processing, the table β may be accessed each time this processing is required. Therefore, the problem is small when the table β is small, but the processing time rapidly increases as the table becomes larger. In order to avoid this, it is necessary to be able to estimate the size of the table β and the processing time in advance.

In this embodiment, the SQL shown in FIG.
As shown in the SQL statement of FIG. 3B by the reordering device 6, the statement is rearranged so as to maintain the intermediate state, thereby making it possible to reduce the access to the table β and to increase the speed. Can be achieved. Note that FIG.
In (B), the view definition instruction for defining a view table has a syntax of “CREATE VIEW <table name> (view column list) AS <query expression> ...”.
The SELECT statement is composed of “SELECT <list of column names> FROM <list of table names> WHERE <
Search condition> ... ".

In the above example, the data held in the storage device 3 is the data amount and the data access time of the tables α and β.

That is, as a result of the user creating the SQL statement, the table β is large despite the fact that the table β is large.
When an access like an SQL statement shown in (A) is to be performed, in the present embodiment, the processing speed estimation device 7
The estimation of the processing speed of the SQL sentence shown in FIG. 3A and the SQL sentence after the rearrangement shown in FIG. 3B are compared. For example, as shown in the SQL statement of FIG.
When there is a nest of CT statements, the reordering device 6 examines the reordering of the SQL statements so that an intermediate view is created for each SELECT statement.

The problem here is that the SQ at a level where the data amount is large and the data access time cannot be ignored.
Since the L sentence is rearranged, the processing time can be estimated relatively easily from the disk access speed and the data amount.

It is also possible to adopt a method in which the processing is actually performed in a time zone where there is room for the processing by this apparatus, and the measured value of the processing time is held.

As described above, in this embodiment, the storage device 3 that separately manages information such as the data amount, not the data itself managed by the database, is provided.
Processing that can be speeded up can be changed to equivalent SQL statements for processing.

At present, the process of counting the number of records in the table also requires data access every time to reflect the latest data. However, in this embodiment, when estimating the processing time from the data processing amount, However, the accuracy is not so required, and even if an error of several percent is included, the influence is small.

As described above, according to the above-described embodiment, optimization is performed by detecting room for speeding up from the input processing instruction.
The processing time can be shortened.

[0043]

As described above in detail, according to the present invention, the processing is optimized even for the use of a user who is not familiar with the SQL language and the situation of the database, and the processing efficiency is improved. It is possible to achieve the effect.

The reason is that, in the present invention, the processing time of a processing instruction such as an SQL statement input by a user is estimated,
Within the range where the processing result does not change, it is automatically determined whether to modify the processing instruction such as an SQL statement or the like, and by adopting a processing method capable of increasing the processing speed, the processing speed is improved, As a result, even a user who is unfamiliar with creating processing instructions such as SQL statements optimally can access the database without causing a significant speed reduction.

[Brief description of the drawings]

FIG. 1 is a block diagram showing the configuration of an embodiment of the present invention.

FIG. 2 is a block diagram illustrating a configuration of an analyzer according to one embodiment of the present invention.

FIG. 3 is a diagram for explaining one embodiment of the present invention;
FIG. 14 is a diagram illustrating an example of rearrangement of nested SQL statements.

FIG. 4 is a block diagram illustrating an example of a configuration of a conventional database device.

[Explanation of Signs] 1 Input device 2 Analysis device 3 Storage device 4 Data management device 5 Decoding device 6 Rearrangement device 7 Processing speed estimation device 8 Selection device

Claims (6)

[Claims] 1. A data processing command input means for accepting input of a data processing command created by a user, analyzing the input data processing command, determining whether or not the processing method can be divided, and processing if the processing method can be divided. Analysis means for creating a data processing instruction obtained by dividing a data processing instruction, a data management device for processing the data processing instruction and managing a database, information on the data amount and processing speed of each processing target, and processing in the data management device Storage means for storing a method, wherein the analysis means estimates the respective processing times for executing the input data processing instruction as it is and executing the corrected data processing instruction. Comparing, selecting a data processing instruction that can be processed at a higher speed, and executing the selected data processing instruction in the data management device. That the database apparatus. 2. The database apparatus according to claim 1, wherein an SQL language is used as said data processing instruction. 3. The method according to claim 1, wherein the analyzing unit determines whether or not the input SQL sentence can be decomposed into an SQL sentence for performing a plurality of processes.
If it can be decomposed, the processing time by the original SQL statement and
A processing time based on a series of decomposed SQL statements is calculated based on the information in the storage means, and a SQL that can be processed at a higher speed.
3. The database device according to claim 2, wherein only a sentence is selected. 4. An input data processing instruction is analyzed by a decoding means to determine whether or not the data processing instruction can be rearranged. Output and execute by the data management device.On the other hand, if the rearrangement is possible, for the input data processing instruction, the processing speed estimation means calculates the processing speed estimate in the original form, For the data processing instruction obtained by rearranging the input data processing instruction by the rearranging unit, the processing speed estimation unit calculates an estimation of the processing speed, and among these processing speed estimations, the data processing instruction having the fastest processing speed is calculated. A database device, which is selected and executed by the data management device. 5. The processing speed estimating means accesses management information such as a size of data handled by the data management device, a physical arrangement, a processing time corresponding to the physical arrangement, and various types of data. Processing time in the case, with reference to the storage means storing the processing method in the data management device,
5. The database apparatus according to claim 4, wherein the processing time of the data processing instruction is estimated. 6. A process for receiving a data processing command created by a user from an input device, analyzing the input data processing command, and determining whether the data processing command can be disassembled or rearranged. (B) processing for creating a corrected data processing instruction by disassembling or rearranging the input data processing instruction if it can be disassembled or rearranged; (c) information on the amount of individual data to be processed and the processing speed And a case where the input data processing instruction is executed as it is with reference to the storage unit storing the processing method in the data management device that executes the data processing instruction, and when the corrected data processing instruction is executed And (d) the data processing of the faster one of these estimates. Select instructions, processes and supplies the data processing instruction that is selected in the data management apparatus, the (a) ~ a recording medium recording a program for executing each processing in the information processing apparatus of (d).