JP5069525B2 - Data processing system - Google Patents

Description

  The present invention relates to a data processing system. In particular, a record classification system managed by a DB server is cached as an index in an AP server, and a primary key of a record is specified when an SQL statement is issued to the DB server. Thus, the present invention relates to a technique for realizing high-speed search processing and the like.

Along with the development of client-server systems, in order to meet the demand for larger-scale information processing, in addition to the client that displays data, it has an AP server that processes data and a DB server that stores data. A so-called three-layer client-server system has become widespread.
In order to improve the processing speed, a load is distributed by arranging a plurality of AP servers in parallel.
JP 2005-165610 A

  Since the AP server can be configured with an inexpensive PC server, it is relatively easy to increase the processing speed by increasing the number of installed servers, but there is a need to maintain data synchronization for the DB server Therefore, it is not possible to shift to distributed processing as easily as AP server. Of course, database system vendors have been using various technologies to speed up the DB server itself from both the software and hardware sides, and as a result, certain results have been achieved. It cannot be denied that prices will rise. As long as the scale of the database carried by the client-server system continues to increase, the disk I / O (data read / write) speed will eventually become a barrier, and it is expected that there will be a time when the performance of the DB server cannot be improved. The

  By the way, in a DB server, a unique primary key is generally assigned to each record, and a plurality of classification attributes for defining the position of each record are assigned. For example, in the voucher management table shown in FIG. 11, a unique primary key called “voucher number” is assigned to each record, and the target record is pinpointed by specifying this voucher number. However, for the convenience of search, classification attributes of year, corporation, and slip type are assigned. As a result, even if the user does not recognize a specific slip number, the user can extract a plurality of records that match the search condition from the DB server by specifying “year × corporate × slip type” as the search condition. Is possible.

  However, on the other hand, if you specify the year, corporation, and slip type classification attributes in the SQL statement and command the record extraction, the DB server will start all records that match the specified year, corporation, and slip type as the first record. Therefore, it is necessary to execute a process called a full scan in which the corresponding record is extracted by scanning from time to time.

  Moreover, since it is necessary to provide a classification attribute item for each record, the data capacity naturally increases accordingly, which leads to enlargement of the table. In the case of a major distribution group, the number of vouchers is 10 million units per year, and by having duplicate data such as “2006” and “H001” for each record, a huge amount of resources are consumed. Result.

  In addition, once the classification system has been assigned, it is necessary to follow this over a long period of time because the table definition must be changed (addition / change / deletion of columns) if the classification system is reviewed in the middle. There is also the problem that the flexibility of the system is impaired.

  Furthermore, in order to speed up the search process, an index (index information) is generally generated in the DB server according to the combination pattern of classification attributes, but there are many classification attribute items and various searches are performed. When a pattern is assumed, the index information itself becomes complicated and enlarged like a secondary index, a tertiary index, etc., and as a result, the disk capacity of the DB server is compressed. Of course, when adding records, these indexes need to be rebuilt, and the resulting CPU load cannot be ignored.

  The present invention has been devised to solve the above-mentioned problems that the conventional data processing system has, and does not depend on the performance improvement of the DB server itself. By reducing this, the search processing speed can be increased, the disk attribute of the DB server is not squeezed by the classification attribute item of each record, and the change of the record classification system can be flexibly handled. The purpose of the present invention is to provide a data processing system that does not require generation of a secondary or tertiary index.

In order to achieve the above object, a data processing system according to claim 1 is a data processing system including a DB server and an AP server, wherein the DB server stores a first record to which a primary key is assigned. A first table for storing, and a second table for storing a second record that defines a hierarchically structured classification attribute of the first record, wherein the AP server sends an SQL statement to the DB server. A data read means for issuing a command to read the second record stored in the second table, and generating a tree structure index based on the second record transmitted from the DB server, When a search condition that combines classification attributes is transmitted from the client terminal and the index generation means to be stored in the storage device, the index is referred to and the search condition is Means for acquiring the primary key of the first record to be matched, and means for generating an SQL statement specifying the primary key of the first record and issuing it to the DB server. It has a parent item in which a classification attribute is described and a child item in which a lower-level classification attribute directly connected to this is described, and the classification attribute described in the child item of one record is the classification of the parent item of another record while being described as an attribute includes a recursive structure where the primary key of the first record is written to the child items of the record that describes the lowest classification attributes to the parent item, further the AP server, the A means for adding unique information for imparting uniqueness to the classification attribute for the necessary classification attribute stored in the second record, and each classification attribute when generating the index It is characterized by comprising means for deleting the Martinique information.

A data processing system according to a second aspect is the system according to the first aspect, wherein the unique information is a serial number composed of numerical values that do not overlap each other .

The data processing system according to claim 3 is the system according to claim 1 , wherein the unique information is route information obtained by connecting classification attributes positioned higher than the classification attributes . .

  In the data processing system according to claim 1, the hierarchically structured classification attribute of the first record stored in the first table of the DB server is cached in the memory or disk of the AP server. Therefore, when a search is instructed to the DB server, it is possible to specify the primary key of the first record and issue a SQL statement in which the extraction target is pinpointed. As a result, a full scan does not occur in the DB server, and the search process can be speeded up accordingly. In this way, as long as the SQL statement narrowing down the extraction target is issued from the AP server, there is no need to prepare secondary and tertiary indexes on the DB server side.

  Further, in the second table, the first record classification is based on a so-called recursive data structure in which the child item of the upper record becomes the parent item of the record of the next hierarchy, and the lower classification attribute is described in the child item. Since the system is expressed, duplication of data can be greatly reduced as compared with the case where the classification attribute is defined in a tabular format for each first record. In addition, even if it is necessary to change the classification system during system operation, it is only necessary to change the record in the second table, and there is no need to change the table definition. There is an advantage of improving the performance.

In addition, since there is a mechanism for adding unique information such as serial numbers and route information to the classification attributes stored in the second record as necessary, there are multiple overlapping classification attributes. Even so, as a result of ensuring the uniqueness between them, the AP server can generate a correct tree-structured index without confusion.

FIG. 1 is an overall configuration diagram of a data processing system 10 according to the present invention, and this system 10 includes a plurality of AP servers 12, a DB server 14, and a load balancer (load balancer) 16.
The load balancer 16 and each AP server 12, and each AP server 12 and DB server 14 are connected by a network.
A large number of client terminals 20 are connected to each AP server 12 via a network such as an intranet 18 or the Internet and a load balancer 16.

Each AP server 12 includes a data processing unit 22, an index generation unit 24, a memory 26, and a hard disk (HDD) 27.
The hard disk 27 of each AP server 12 is set up with an OS and an application program dedicated to this system. When the CPU of the AP server 12 operates according to these programs, the data processing unit 22 and the index generation unit 24 described above. Is realized.

The DB server 14 includes a database management system (RDBMS) 28, a first table 30, and a second table 32.
The database management system 28 manages the first table 30 and the second table 32, and performs input / output and update of data stored in each table, predetermined calculation, and the like.

  The load balancer 16 serves to distribute the request transmitted from the client terminal 20 according to the load applied to each AP server 12.

  The client terminal 20 is composed of a computer such as a PC, and an application program such as a Web browser program is set up in addition to the OS.

  FIG. 2 shows an example of the configuration of the first table 30, in which a large number of first records 34 having data items such as a slip number, an issue date, an issue store, and a person in charge are registered. The first record 34 is uniquely specified by a unique slip number.

FIG. 3 shows a configuration example of the second table 32, in which a large number of second records 36 having two data items of a parent ID (parent item) and a child ID (child item) are registered. .
The second table 32 defines the hierarchically structured classification attributes of the first record 34 stored in the first table 30.

FIG. 4 represents the classification system of the first record 34 in a tree structure, and the years “2005”, “2006”, and “2007” are arranged directly under the ROOT. In each fiscal year, corporate codes “H001”, “H002”, and “H003” are arranged, and “C01”, “C02”, and “C03” slip type codes are arranged in each corporate code. Further, slip numbers such as “10001”, “10002”, “10003”, and the like are arranged in each slip type code (for the sake of illustration, description is omitted except for the line “2006-H001-C01”).
By referring to this classification system, for example, it can be understood that a slip with “slip number = 10002” belongs to the “C01” type issued by a corporation of H001 in FY2006.

  In the conventional data management system, as shown in FIG. 11, such a classification system was expressed by providing items of year code, corporate code, and slip type code in each record. In the case of this system 10, a slip classification system is defined by a second table 36 having two data items of a parent ID and a child ID.

  First, in the second table 36, three record groups of “parent ID = ROOT, child ID = 2005”, “parent ID = ROOT, child ID = 2006”, “parent ID = ROOT, child ID = 2007”. A defines that the top-level classification attributes connected to ROOT are three, “2005”, “2006”, and “2007”.

  In the second table 36, three record groups of “parent ID = 2006, child ID = 1001_H001”, “parent ID = 2006, child ID = 1002_H002”, “parent ID = 2006, child ID = 1003_H003”. B stipulates that there are three medium classification attributes connected to 2006: “H001”, “H002”, and “H003”.

“1001_” added before H001, “1002_” added before H002, and “1003_” added before H003 are for the purpose of adding uniqueness to each node (classification attribute). The serial number is automatically assigned to the necessary data by the AP server 12.
In other words, since the corporate code of H001 is not limited to 2006, it may exist under 2005 or 2007, so to distinguish it from H001 in other years, the data processing section of AP server 12 22 automatically assigns a unique serial number to the second record 36 stored in the second table 32 in advance.
Incidentally, 2005, 2006, and 2007 are the highest classification attributes connected to ROOT, and are inherently unique, so they are excluded from automatic serial number assignment.

Further, in the second table 32, three record groups of “parent ID = 1001_H001, child ID = 1004_C01”, “parent ID = 1001_H001, child ID = 1005_C02”, “parent ID = 1001_H001, child ID = 1006_C03” C stipulates that there are three lower classification attributes connected to H001: “C01”, “C02”, and “C03”.
Again, "1004_" added before C01, "1005_" added before C02, and "1006_" added before C03 are used to give each node (classification attribute) uniqueness. This means a serial number automatically assigned by the AP server 12.

  Further, in the second table 36, “parent ID = 1004_C01, child ID = 10001”, “parent ID = 1004_C01, child ID = 10002”, “parent ID = 1004_C01, child ID = 1003”, “parent ID = 1004_C01, child ID = 1004 ”,“ parent ID = C01, child ID = 10005 ”, the specific slip numbers linked to C01 which is the lowest classification attribute are“ 10001 ”,“ 10002 ”. , “10003”, “10004”, and “10005”.

  As described above, the second record 36 that defines the classification system of the first record 34 uniquely specified by the unique slip number is assigned a classification attribute such as year, corporation, and slip type for each first record 34. By expressing the classification system with a recursive structure in which the upper child item becomes the parent item of the next hierarchy and the lower classification attribute is specified (associated) as a child item instead of the normal tabular form to associate, Data duplication can be greatly reduced.

  Even if it is necessary to change the classification system during system operation, it is only necessary to change (add and delete) the records in the second table, and there is no need to change the table definition. There is an advantage that the system can be flexibly modified.

  When a new record 36 is added to the second table 32, the data processing unit 22 of the AP server 12 refers to the second table 32 to confirm the final serial number, and a serial number after this (not necessarily) It is not necessary to be the serial number immediately after, but a certain interval may be provided) is automatically given to the necessary data.

  Alternatively, a serial number management table is provided in the DB server 14, and when assigning a new serial number, the AP server 12 refers to this, and the last sequential number + 1 value is stored in the second record 36. You may comprise so that it may add. In this case, the AP server 12 updates the final value of the serial number management table with the latest serial number.

Hereinafter, a processing procedure related to generation of a tree structure index by the AP server 12 will be described with reference to the flowchart of FIG.
First, the data processing unit 22 of the AP server 12 periodically issues an SQL statement to the DB server 14 to obtain the second record 36 whose parent is the ROOT node stored in the second table 32. (S10).
Next, the index generation unit 24 extracts the child values of the second record 36, and generates a list of the highest nodes directly connected to the ROOT node (S12). Specifically, as shown in FIG. 6, “2005”, “2006”, and “2007” are recognized as the highest nodes.

  Next, the data processing unit 22 issues an SQL statement to the DB server 14 and acquires the second record 36 not having the ROOT node as a parent from the second table 32 in units of the highest node (S14).

  Next, the index generating unit 24 collects the second records 36 having a common parent and generates a group (S16). As shown in FIG. 7, this group 38 has a common parent as a key part and each child as a member part.

  Next, the index generation unit 24 refers to the key part of each group 38 and generates a list of parent nodes (S18). That is, a node that appears in any key part is determined as a parent node, and a node that never appears in the key part is excluded from the list as not being a parent node. This parent node list is later referred to when determining whether or not the child of each group 38 corresponds to the parent of another group 38.

  Next, after selecting one of the highest nodes (S20), the index generator 24 adds a lower node to the highest node to form a tree structure. Here, the description will proceed assuming that the tree structure under “2005” has already been completed and “2006” has been selected as the next processing target.

  First, the index generation unit 24 searches for a group having “2006” as a parent (S22). As a result, the group 38a in FIG. 7A having “H001”, “H002”, and “H003” in the member portion is extracted.

Next, the index generation unit 24 refers to the parent node list generated in S18, and determines whether the member part of the group 38a is a parent of another group 38 (S24).
In this case, “H001” or the like is a parent in the group 38b whose members are “C01”, “C02”, “C03”, and the like, and therefore a YES determination result is obtained.
As a result, the index generation unit 24 selects “H001”, which is one of the member units, and adds the group in FIG. 7B as a branch to the 2006 tree (S26). At this time, the serial number assigned to each data is deleted by the index generation unit 24 (the same applies hereinafter).

  Next, the index generation unit 24 selects “C01” which is one of the child nodes belonging to the group 38b (S30), and then searches for the group 38 whose parent is “C01” (S32). As a result, the group 38c in FIG. 7C is extracted.

Next, the index generating unit 24 refers to the parent node list generated in S18, and determines whether or not the member part of the group 38c is a parent of another group (S34).
In this case, since the member parts “10001” to “10005” of “C01” are not parents in other groups, the determination result of NO is obtained.
As a result, the index generating unit 24 adds the group 38c as a leaf to the Tree (S36).

In this way, as shown in FIG. 6, after the line “2006-H001-C01” is completed, the index generating unit 24 repeats the processes of S30 to S36, and “C02” and “C03” are used as key parts. By adding the groups to be added to the Tree as leaves, the lines of “2006-H001-C02” and “2006-H001-C03” are completed (not shown).
Thereafter, the index generation unit 24 repeats the processing of S26 to S36 for “H002” and below and “H003” and below to complete the tree under “2006”.
Then, after a tree having “2006” as the highest node is completed, the index generation unit 24 starts generating a tree having “2007” as the highest node.

In addition, when it determines with No in said S24 (when the child of the group 38 is not the parent of the other group 38), the index production | generation part 24 adds the said group 38 to a Tree as a leaf, and stops deepening of a hierarchy Then, the process moves to the process for adding the leaf on the right.
If the determination in S34 is Yes (when the child of the group 38 is the parent of another group 38), the index generation unit 24 adds the corresponding group 38 to the Tree as a branch, and at the next hierarchical level. Transition to construction.
In this way, the index generation unit 24 determines whether a child of a certain group 38 is a parent or not a parent of another group 38, and switches between adding as a branch or a leaf according to the result. Thus, even if the hierarchy of the classification system increases or decreases, the index generation unit 24 can correctly reproduce the hierarchical structure.

As described above, the index generation unit 24, which has completed all the trees under the highest classification attribute on the memory 26, stores the folder-file format index on the hard disk 27 based on the tree structure classification system. Generate.
Specifically, as shown in FIG. 8, year folders 46 of “2005”, “2006”, and “2007” are created under the “ROOT” folder 44 of the hard disk 27, and corporate folders 48 (“ H001 "etc.).
In addition, a text file 50 having a file name (“C01” or the like) corresponding to the slip type is generated by the index generation unit 24 and stored in the corresponding corporate folder 48.
In these text files 50, specific slip numbers 52 belonging to the slip type are described.

Next, search processing by the system 10 will be described with reference to the flowchart of FIG.
First, when the AP server 12 receives a search request from the client terminal 20 via the load balancer 16 (S50), the data processing unit 22 refers to the folder-file format index 42 formed on the hard disk 27, and searches. All slip numbers corresponding to the conditions are acquired (S52).
For example, when the search condition “2006 (year) × H001 (corporate) × C01 (slip type)” is transmitted from the client terminal 20, the data processing unit 22 sets “10001,” “10002,” “10003,” “ The values of “10004” and “10005” are acquired.

Next, the data processing unit 22 generates an SQL statement specifying the above slip number and issues it to the DB server 14 (S54).
In response to this, the DB server 14 extracts the corresponding first record 34 from the first table 30 and transmits it to the AP server 12.
Receiving this, the data processing unit 22 processes the data into a predetermined format and transmits it to the client terminal 20 (S56).

In this data processing system 10, the classification system of the first record 34 stored in the first table 30 of the DB server 14 is cached in the AP server 12 as a tree structure index. When instructing the server 14 to perform a search, it is possible to specify the primary key of the first record 34 and issue an SQL sentence in which the extraction target is narrowed down pinpointed.
As a result, a full scan does not occur in the DB server 14, and the search process can be speeded up accordingly.

  As described above, the tree structure index (classification system) once built on the memory 26 is converted into the folder-file format index 42 and stored in the hard disk 27. As a result, even if the index is relatively large, Since it is sufficient to restore the necessary parts individually to the memory, there is an advantage that it is possible to effectively avoid instantaneously pressing the memory of the AP server 12, but the number of classification attributes and the number of the first record 34 are If the number is relatively small, the tree structure index generated on the memory 26 may be referred to as it is.

  FIG. 10 shows another configuration example of the second table 32, and serial numbers such as “1001_” and “1002_” are automatically assigned to ensure the uniqueness of each node (classification attribute). Instead, it is characterized in that the route information of the upper node is given to the necessary data by the data processing unit 22 of the AP server 12.

For example, by adding “2006_” before “H001” like “2006_H001”, it becomes clear that it is H001 under 2006, and H001 under 2005 and H001 under 2007 can be identified.
Also, by adding “2006H001_” before “C01” like “2006H001_C01”, it becomes clear that C01 is under 2006 × H001, and can be identified from C01 under 2006 × H002.

This path information is deleted by the index generation unit 24 at the timing when a tree-structured index is generated on the memory 26, as in the case of the second table 32 of the serial number assignment method.
In addition, when the classification system needs to be changed during the operation of the system, and the second record 36 is added to or deleted from the second table 32, the data processing unit 22 of the AP server 12 changes the changed part. The rewriting of the route information of each data located under the subordinate is executed.

  Needless to say, when there is no possibility that the same value is duplicated between the classification attributes, it is not necessary to add the serial number or the route information to the classification attribute of the record 36 as described above.

1 is an overall configuration diagram of a data processing system according to the present invention. It is a figure which shows the structural example of a 1st table. It is a figure which shows the structural example of a 2nd table. It is the figure which expressed the classification system of the 1st record with the tree structure. It is a flowchart which shows the production | generation procedure of an index. It is a figure which shows the process which produces | generates the index of a tree structure. It is a figure which shows the production | generation process of a group. It is a figure which shows the index of a folder file format. It is a flowchart which shows a search procedure. It is a figure which shows the other structural example of a 2nd table. It is a figure which shows the structural example of the conventional table.

Explanation of symbols

10 Data processing system
12 AP server
14 DB server
16 Load balancer
18 Intranet
20 Client terminal
22 Data processing section
24 Index generator
26 memory
27 Hard disk
28 Database management system
30 First table
32 Second table
34 First record
36 Second record
38a-38c group
42 Folder-file format index
44 Root folder
46 year folder
48 Corporate folder
50 text files
52 Document number

Claims (3)

A data processing system comprising a DB server and an AP server,
The DB server stores a first table storing a first record to which a primary key is assigned, and a second table storing a second record that defines a hierarchically structured classification attribute of the first record. And
Data reading means for issuing a SQL statement to the DB server and instructing reading of the second record stored in the second table, the AP server;
Index generating means for generating a tree-structured index based on the second record transmitted from the DB server and storing it in the storage device of the AP server;
Means for acquiring a primary key of a first record that matches the search condition by referring to the index when a search condition that combines classification attributes is transmitted from the client terminal;
A means for generating a SQL statement specifying the primary key of the first record and issuing it to the DB server,
The second record has a parent item in which a higher level classification attribute is described and a child item in which a lower level classification attribute directly connected thereto is described, and the classification attribute described in the child item of a certain record Is described as a classification attribute of the parent item of another record, and has a recursive structure in which the primary key of the first record is described in the child item of the record in which the lowest classification attribute is described in the parent item. And
Further, the AP server adds, to the necessary classification attribute stored in the second record, unique information for imparting uniqueness to the classification attribute;
A data processing system comprising means for deleting the unique information from each classification attribute when generating the index . The data processing system according to claim 1 , wherein the unique information is a serial number composed of numerical values that do not overlap each other. The unique information, the data processing system according to claim 1, characterized in that the path information obtained by connecting a classification attribute located higher than the classification attributes.

Images