KR20040009126A - Apparatus and method of low-load statistics processing in data base management system - Google Patents

Abstract

PURPOSE: A low-load statistics processing device for a database system and a method thereof are provided to reduce the system load and to shorten a search time by searching and statistically processing a data table automatically and periodically, storing a result in a statistics table for each sort, and offering data searched from only the statistics database. CONSTITUTION: A database(370) stores the data inputted/output from users(310). A DBMS(DataBase Management System)(350) manages the data input/output of the database. An automatic statistics processing part(390) statistically processes the data stored in the database automatically and periodically. An application program(330) transforms a data request order from the user into an SQL(Structured Query Language) recognizable to the DBMS and transfers it. The automatic statistics processing part comprises a timer(391) and an automatic collection module(393).

Description

Apparatus and method of low-load statistics processing in data base management system}

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a database system, and more particularly, to a statistical processing method and a low load statistical processing apparatus and method of a database system capable of improving performance speed in a database system processing a large amount of data. will be.

A database is a collection of data organized to make it easy to access, process, and update its contents. The most widespread database is a relational database, a tabular database that defines data that can be accessed and reorganized in a variety of ways.

The relational database was developed in 1970 by IBM's E.F.Codd. A relational database is a collection of data items consisting of a series of structured tables that can be accessed or combined in various ways without reorganizing the database table.

A database system is a system that allows a large number of computer users to record or access data in a database. The database system provides a convenient and efficient environment for searching or storing information in a database.

The configuration of a conventional database system is shown in FIG.

Referring to FIG. 1, a database is searched for data according to a plurality of users 110, application software 130 of a terminal used by the user 110, and a query transmitted from the application software 130. It is configured to include a DBMS 150 and a database 170 to provide and manage the database.

In particular, the database 170 includes a plurality of data tables 1 and 2 (171 and 173), a join table 175 for temporarily storing and extracting only data required by a user's data request, and a database 170. It is configured to include a statistics table 177 for storing the statistical data of the data stored in the).

In addition, in the above system, the user 110 and the application software 130 are briefly configured. However, this is only an example for explanation. In reality, a plurality of users and application software used by each user may use the DBMS 150. It is a system that inputs and outputs data connected with.

Similarly, although the data table of the database 170 is simplified to two tables in the configuration of FIG. 1, it is a database that can store a large amount of data with a plurality of data tables according to the type and size of data.

In addition, the DBMS 150 is a program that allows a large number of computer users to record or access data in a database, so that user requirements or other programs manage requirements so that users or other programs can actually store the data. It makes it possible for anyone in a multiuser environment to use data without having to understand where it is stored on the media.

In dealing with user requirements, the DBMS complements the integrity of the data and the security that only authorized users have access to the data. A typical type of DBMS is an RDBMS, a relational database management system.

The RDBMS uses a standardized user and program interface SQL (structured query language).

The technology of the DBMS structured as described above is based on the concept of object-oriented, development of a multimedia database including the processing of larger and larger data, development and commercialization of distributed databases, image and audio according to the morphological change of data, and the like. It is developing into the object-oriented database management system introduced.

2 is a flowchart illustrating an operation procedure of a statistical processing method of a conventional database system.

Referring to FIG. 2, when the user 110 requests data data from the application software 130 (S201), the application software 130 creates a query using a SQL language recognizable by the DBMS 150 and generates a DBMS. Request data to 130 (S203).

In response to the data request, the DBMS retrieves the data of the corresponding data table (S205) and temporarily stores the data of the corresponding data table as the join table 175 (S207). In the general database system, since two or more data tables are often associated with each other, a join table 175 for temporarily connecting the corresponding tables is temporarily created and stored.

When the user acquires the desired data through the join table 175 (S209), the DBMS 150 transmits the data to the application software 130 (S211), and the application software 130 transmits the received data. It checks whether there is newly updated data compared to the statistical data, requests the DBMS 150 to store the data in the statistics table 177 (S213), and provides the data to the user (S215).

In this case, if the data requested by the user is already stored in the statistics table 177, the DBMS 150 passes the data of the statistics table 177 directly to the application software 130 without retrieving the data table. Make it available to the user.

However, in the conventional statistical processing method, when the amount of data in a table that is accessed to collect statistical data increases or the number of statistics items increases, the time required for the DBMS to access the table and process the data increases proportionally. Increasing total system operation and CPU occupancy time increases the load, and when you configure join tables with two or more connections, the amount of data that the DBMS needs to process increases, which may affect system operation. Occurs.

In addition, in the method of storing the requested data in the statistics table in order to improve the system efficiency, the method of storing the data in the statistics table is often ineffective because it is not systematically searched. This is so similar that it does not work, but may cause problems that can result in double operation.

In particular, if a user's request for statistical work occurs frequently during system operation, there is a high probability of causing a failure in the system. Thus, a problem occurs because the system operation and statistical work can occur at the same time. There is a drawback to not doing it.

An object of the present invention is to automatically search the data table at regular intervals in a large database management system to perform statistical processing and to store the result in a statistical table for each type, to retrieve only the statistical table for the user's data request The purpose of the present invention is to provide a low-load statistical processing apparatus and method for a database system that can reduce the system load and provide a fast search time.

1 is a block diagram showing the configuration of a conventional database system.

2 is a block diagram showing an operation procedure of a statistical processing method of a conventional database system.

Figure 3 is a block diagram showing the configuration of a low load statistics processing apparatus of a database system according to an embodiment of the present invention.

4 is a flowchart illustrating an operation procedure of a low load statistical processing method of a database system according to an exemplary embodiment of the present invention.

Low load statistical processing apparatus of the database system according to the present invention,

A plurality of data tables for storing data input and output from a plurality of users, a temporary data table for temporarily storing only data generated for an integer multiple of a predetermined collection period among data stored in the data table; A database including a join table for data summing operation for statistical processing and a statistics table for statistically processing and storing data stored in the plurality of data tables for each type;

A database management system for managing input and output of data stored in the database;

And an automatic statistics processing unit for statistically processing the data stored in the database at the predetermined collection period and storing the data in a statistical table.

Preferably, the automatic statistics processing unit,

A timer for determining a schedule collection cycle,

And an automatic collection module for statistically processing data in a database at a predetermined collection cycle by the timer and storing the data in a statistics table.

Preferably, the statistical data stored in the statistics table,

And classified according to a statistical format according to a user's setting.

In addition, the low load statistical processing method of the database system according to the present invention,

Performing statistical processing of the database at a predetermined collection cycle by the automatic statistical processing unit and storing the statistical table;

Requesting, by an application software, statistical data stored in the statistical table;

And requesting data from the application program to the database management system to retrieve data from the statistics table and provide the data to the user.

Preferably, the statistical processing of the database every predetermined collection period in the automatic statistical processing unit,

Determining an automatic collection cycle by operating a timer;

Requesting automatic statistical processing from the automatic collection module to the database management system for each automatic collection cycle as a result of the determination;

The database management system extracts only the data stored from a certain time ago to the present from the data stored in a plurality of data tables for automatic statistical processing, copies the data into a temporary table, joins data by statistical type for statistical processing, and joins the join table. Storing and generating a result;

And classifying and storing the result in a statistical table according to a statistical format.

Preferably, a predetermined time for extracting data stored in the temporary table,

Characterized in that it is an integer multiple of the collection cycle or time according to the user's setting.

The low load statistics processing apparatus and method of the database system according to the present invention configured as described above will be described with reference to the accompanying drawings.

3 is a block diagram illustrating a configuration of a low load statistical processing apparatus of a database system according to an exemplary embodiment of the present invention.

3, a database 370 storing data input and output from a plurality of users 310, a DBMS 350 managing data input and output of the database 370, and a database 370. Automatic statistics processing unit 390 for automatically statistics the stored data at a predetermined collection period, and application software 330 for converting the data request command from the user 310 to SQL that the DBMS 350 knows and delivers it. Characterized in that it comprises a.

In particular, the automatic statistics processing unit 390 includes a timer 291 for checking whether or not the automatic statistics processing by checking the collection period, and the automatic collection module 393 to request statistical processing to the DBMS 350 to perform statistical processing; It is composed.

In addition, the database 370 is a plurality of data tables 1, 2 (371, 372), and the data stored in the data tables 1, 2 (371, 372) for the statistical processing of about 2 to 3 times the recent collection period And a temporary data table 373 for extracting and temporarily storing only the data stored for a time, a join table 374 for combining related data according to statistics items, and a statistics table 375.

In this case, the statistical table 375 is divided according to the statistical processing items, and the statistical data is stored. In the embodiment of the present invention, only three tables for the time 375a, the type 375b, and the final aggregation 375c are used. The statistics can be saved by dividing them into various statistical item tables according to the user's settings.

The statistical processing method in the system configured as described above is as follows.

4 is a flowchart illustrating an operation procedure of a low load statistical processing method of a database system according to an exemplary embodiment of the present invention.

Referring to Figure 4, it shows a procedure of automatic statistical processing performed by the automatic statistical processing unit, the automatic statistical processing unit 390 checks the schedule collection period according to the user's setting, when the collection cycle ( S401), the automatic collection module 393 informs that the collection cycle.

Accordingly, the automatic collection module 393 requests the automatic statistical processing to the DBMS 350 (S403), and the DBMS 350 has relevance for each statistical item among the data of the data tables 371 and 372 for statistical processing. The latest data from the data two to three times or more of the collection period is extracted from the data, stored in the temporary data table 373, and combined and stored in the join table 374 (S405).

A result value is generated using the value stored in the join table 374 (S409), and stored in a statistical table for each statistical item of the result value (S411).

As described above, the automatic statistical processing unit 390 automatically processes and stores the data of the database 370 at a predetermined collection period. Therefore, if the collection period is set to a time when the database is not used, statistics collection is easy, and other The problem of increased load by users is solved.

In addition, in the statistical processing, a temporary table for storing only the latest data stored two to three times before the collection period among the data stored in the data table is configured to extract only the latest data and perform statistical processing, thereby searching the database. In addition, the data to be joined is only handled two to three times the amount of data collected before a certain collection cycle, so that a certain speed can be achieved.

In this case, storing only the latest data in which the amount of data collected in the temporary data table is 2 to 3 times older than the collection cycle is to reduce the possibility of data loss that may occur in the case. Afterwards, selecting and joining only the latest stored data can maximize efficiency.

In addition, even when storing the data automatically processed in the statistical table, by storing each of the statistical items, it is possible to manage so that the data can be easily distinguished. That is, the data is classified and stored according to statistical items, such as the hourly table 375a, the table 375b, and the aggregation table 375c, and the statistical table distinguished by the user's setting is further increased. Can be.

In addition, if the same contents appear in the middle of storing the automatic statistically processed data in the statistics table, the recording can be prevented from being stored again by stopping the recording from that point in time.

At this time, it is effective to configure the statistics table only with numbers and 5-6 simple fields to increase the performance speed.

As described above, if the user automatically performs statistical processing other than the time to access the DBMS 350, if the user actually needs the statistical data, and transmits a data request command through the application 330, the application Since the program 330 constructs and delivers commands in an SQL language known to the DBMS 350, the DBMS 350 can provide data by searching only a statistical table instead of searching a large amount of data tables. The amount of time users can request and receive data is reduced, and the system load is reduced by real time statistical processing during times when users are not connected.

As described above, the low-load statistical processing apparatus and method of the database system according to the present invention is automatically applied by applying a certain collection period using a rare time of the user in the database management system that manages data. By processing the statistics and storing them by item, and afterwards, when the user requests data, the system simply searches the statistics table to provide the data, thereby reducing the load on the system due to the statistics processing and easily managing the statistics of each item of the data. Therefore, the data can be provided to the user at a faster search speed.

Claims (6)

A plurality of data tables for storing data input and output from a plurality of users, a temporary data table for temporarily storing only data generated for an integer multiple of a predetermined collection period among data stored in the data table; A database including a join table for data summing operation for statistical processing and a statistics table for statistically processing and storing data stored in the plurality of data tables for each type; A database management system for managing input and output of data stored in the database; And an automatic statistics processing unit for statistically processing the data stored in the database at the predetermined collection period and storing the data in a statistics table. The method of claim 1, wherein the automatic statistical processing unit A timer for determining a schedule collection cycle, Low load statistical processing apparatus of the database system, characterized in that it comprises an automatic collection module for statistically processing the data of the database at a predetermined collection cycle by the timer and stores in a statistical table. The method of claim 1, wherein the statistical data stored in the statistics table, Low load statistical processing apparatus of a database system, characterized in that the classification and storage according to the statistical format by the user's setting. Performing statistical processing of the database at a predetermined collection cycle by the automatic statistical processing unit and storing the statistical table; Requesting, by an application software, statistical data stored in the statistical table; And requesting data from the application program to the database management system to retrieve data of the statistics table and providing the data to the user. The method of claim 4, wherein the statistical processing of the database by the automatic statistical processing unit at a predetermined collection period is performed. Determining an automatic collection cycle by operating a timer; Requesting automatic statistical processing from the automatic collection module to the database management system for each automatic collection cycle as a result of the determination; The database management system extracts only the data stored from a certain time ago to the present from the data stored in a plurality of data tables for automatic statistical processing, copies the data into a temporary table, joins data by statistical type for statistical processing, and joins the join table. Storing and generating a result; And classifying and storing the result in a statistical table according to a statistical format. The method of claim 5, wherein the predetermined time for extracting data stored in the temporary table is Low load statistics processing method of a database system, characterized in that the time according to the integer multiple of the collection cycle or the user's setting.