Patent Application
20060010122
1. Technical Field
The present invention relates in general to a system and method for improving database table record insertion and reporting. More particularly, the present invention relates to a system and method for using timestamps that correspond to records in order to organize the records into multiple tables, which, in turn, are used to generate time-dependent reports.
2. Description of the Related Art
Large companies store an enormous amount of customer support inquiry data. Organizations make decisions, and service customers, based upon data they collect. A company may use the stored data to examine business trends to establish a strategy for the future. For example, a company may identify that a particular business area is receiving an unusually number of customer inquiries, and may increase staff to accommodate the situation. In addition, when a particular customer calls, the company is able to identify the customer's call history corresponding to a particular issue (e.g. customer complaint).
Companies may record customer inquiries using a few different approaches. For example, a customer may call a customer support representative whereby the customer support representative creates a record corresponding to the phone call. In another example, a customer may use a company's website to send an online inquiry, in which the online inquiry, along with its response, may be logged. A company may store hundreds of thousands of customer inquiries in a given week. This may equate to the company storing millions of customer inquires during a particular year, all of which are stored in a single table.
A challenge found with storing a large number of records in a single table is that it becomes extremely time intensive to generates reports, or find a particular record in a large table. Using a large table to search and retrieve records to include in a report results in slow database response-time and affects the whole database performance.
In addition, another challenge found is that a company may simultaneously generate multiple reports using a single large database. In this situation, the report generation process slows even more.
What is needed, therefore, is a system and method to improve report generation performance that corresponds to a large amount of records.
It has been discovered that the aforementioned challenges are resolved by using a record manager to store records in a plurality of tables based upon a record's corresponding timestamp. By using a plurality of tables to store records, the number of records in each table is decreased, resulting in faster report generation. When the record manager generates a report, the record manager identifies a particular table and uses the records in the table in order to generate the report.
A client generates a record and adds a timestamp to the record. The client sends the record and the timestamp to the record manager, whereby the record manager extracts the record's timestamp, and retrieves a “time interval” and a “total number of tables” from a storage area. The time interval is an amount of time that is associated with the records that are stored in each table. For example, a time interval may be one week whereby each table stores a particular week's worth of records. The total number of tables is the amount of tables that the record manager uses to store records, such as ten tables.
The record manager uses the time interval, the total number of tables, and the timestamp to determine which table to store the record. In one embodiment, processing may use the formula
(Timestamp/Interval) %(Total Number Tables)=Table Number
where “%” is the remainder after division. For example, if the interval is 86400000 (i.e. 24 hours in milliseconds), the current time is 1084571611014, and the total tables number is 10, then
Timestamp/interval=12552
12552/10=1255, with 2 as the remainder
Therefore, since “2” is the remainder, the second table is identified as the table to store the record that has the corresponding timestamp of 1084571611014.
The record manager may also receive report requests from a database administrator. A report request may include a particular timeframe that corresponds to the database administrator's interest. For example, the database administrator may wish to receive a report that categorizes customer complaints that its company received during a particular month. When the record manager receives a report request, the record manager uses the timeframe, the time interval, and the total tables number in order to identify the table that corresponds to the report request's timeframe. Once it identifies the corresponding table, the record manager uses the records that are located in the table to generate the report.
The foregoing is a summary and thus contains, by necessity, simplifications, generalizations, and omissions of detail; consequently, those skilled in the art will appreciate that the summary is illustrative only and is not intended to be in any way limiting. Other aspects, inventive features, and advantages of the present invention, as defined solely by the claims, will become apparent in the non-limiting detailed description set forth below.
The present invention may be better understood, and its numerous objects, features, and advantages made apparent to those skilled in the art by referencing the accompanying drawings. The use of the same reference symbols in different drawings indicates similar or identical items.
The following is intended to provide a detailed description of an example of the invention and should not be taken to be limiting of the invention itself. Rather, any number of variations may fall within the scope of the invention which is defined in the claims following the description.
Client A 120 timestamps record A 150, and sends record A 150 to record manager 100. Record manager 100 extracts record A 150's timestamp, and also retrieves time interval 105 and total tables number 108 from preferences store 110. Time interval 105 is an amount of time that is associated with the records that are stored in each table. For example, a time interval may be one week whereby each table stores a particular week's worth of records. Total tables number 108 is the total number of tables that record manager 100 uses to store records. The example in
Record manager 100 uses time interval 105 and total tables number 108 to determine which table to store record A 150 (see
Record manager 100 proceeds to receive record B 160 and record C 170 from client 130 and client 140, respectively. Record manager 100 uses record B 160's timestamp, time interval 105, and total tables number 108 in order to determine which table to store record B 160. The example in
Record manager 100 may also receive a report request from a database administrator. The report request may include a particular timeframe that corresponds to the database administrator's interest. For example, the database administrator may wish to receive a report that categorizes customer complaints that its company received during a particular month. When record manager 100 receives a report request, record manager 100 uses time interval 105 and total tables number 108 in order to identify the table that corresponds to the report request's timeframe. Once it identifies the corresponding table, record manager 100 uses the records that are located in the table to generate the report (see
Table 180 includes columns 200 through 230. Column 200 includes timestamps that correspond to the records that are included in table 180. The timestamp is based upon a particular point in time, such as Jan. 1, 1970. Columns 210 and 220 include a list of customer identifiers and customer names, respectively, that corresponds to table 180's records. Column 230 includes a list of notes that corresponds to table 180's records.
Table 180 includes records 240 and 250. Record 240 has a timestamp of “23424”, a customer identifier of “123” a customer name of “John Doe,” and notes that indicate that the customer switched from plan A to plan B. Record 250 has a timestamp of “23645”, a customer identifier of “456” a customer name of “Jane Doe,” and notes that indicate that the customer's call was to inquire about a bill.
Table 185 includes the same four columns as table 180, and includes records 260 and 270. Record 260 has a timestamp of “24248”, a customer identifier of “4256” a customer name of “Bill Doe,” and notes that indicate that the customer canceled service. Record 270 has a timestamp of “24540”, a customer identifier of “5352” a customer name of “Sam Doe,” and notes that indicate that the customer is a new customer. Note that the timestamps in table 180 start at “23424” whereas the timestamps in table 185 start at a different timestamp of “24248” which corresponds to a different timeframe.
Table 190 also includes the same four columns as table 180, and includes records 280 and 290. Record 280 has a timestamp of “25121”, a customer identifier of “5334” a customer name of “John Smith,” and notes that indicate that the customer switched from plan D to plan A. Record 290 has a timestamp of “25673”, a customer identifier of “0984” a customer name of “Sarah Jones,” and notes that indicate that the customer called to inquire about a bill. Note that the timestamps in table 190 start at “25121,” which corresponds to a different timeframe than tables 180 and tables 185.
If the record manager receives a record from client 305, decision 310 branches to “Yes” branch 312 whereupon the record manager uses a timestamp that corresponds with the received record in order to locate a table in table store 175, and store the record in the located table (pre-defined process block 320, see
A determination is made as to whether the record manager receives a report request from client 305 (decision 330). For example, client 305 may be a database administrator's computer terminal and the database administrator may wish to receive a report that corresponds to a timeframe, such as a particular month. If the record manager receives a report request from client 305, decision 330 branches to “Yes” branch 332 whereupon processing generates a report using records that are located in one of the tables that are stored in table store 175 (pre-defined process block 340, see
A determination is made as to whether the record manager receives a request to analyze table records (decision 350). For example, a database administrator may detect that reports are generated too slowly, which may be due to a table that has an unusually large number of record entries. In this example, the database administrator may wish to analyze the number of records in each table and increase the number of tables in order to decrease the number of records in each table. As one skilled in the art can appreciate, the time at which the number of tables changes should be stored in order to track table locations of past and future record entries.
If the record manager should analyze table records, decision 350 branches to “Yes” branch 352 whereupon the record manager analyzes the number of records that are included in the tables that are located in table store 175, and increases the number of tables if appropriate (pre-defined process block 360, see
A determination is made as to whether to continue receiving requests from client 305 (decision 370). If the record manager should continue to receive requests from client 305, decision 370 branches to “Yes” branch 372 which loops back to receive more client requests. This looping continues until the record manager should stop receiving client requests, at which point decision 370 branches to “No” branch 378 whereupon processing ends at 380.
Processing retrieves a time interval from preferences store 110 at step 420. The time interval is an amount of time that is associated with the records that are stored in each table. For example, a time interval may be one month whereby each table stores a particular month's worth of records. Preferences store 110 is the same as that shown in
Processing retrieves a total tables number from preferences store 110 at step 430. The total tables number is the total number of tables that a record manager uses to store records. Using the example described above, a record manager may use twelve tables whereby each table corresponds to a particular month in a given calendar year.
At step 440, processing uses the timestamp, the time interval, and the total tables number to identify a table number that corresponds to the record's timestamp. In one embodiment, processing may use the formula
(Timestamp/Interval) %(Total Tables Number)=Table Number
where “%” is the remainder after division. For example, if the interval is 86400000, the current time is 1084571611014, and the total tables number is 10, then
Timestamp/interval=12552
12552/10=1255, with 2 as the remainder
Therefore, since “2” is the remainder, the second table is identified as the table to store the record that has the corresponding timestamp of 1084571611014.
Processing locates the identified table in table store 175 at step 450, and stores the record in the located table at step 460. Table store 175 is the same as that shown in
Processing commences at 500, whereupon the report manager identifies a timeframe that is included in the received report request at step 510 (e.g. one month). At step 520, the report manager retrieves a time interval from preferences store 110. A time interval is an amount of time that is associated with the records that are stored in each table. For example, a time interval may be one month whereby each table stores a particular month's worth of records. Preferences store 110 is the same as that shown in
Processing retrieves a total tables number from preferences store 110 at step 530. The total tables number is the total number of tables that a record manager uses to store records. Using the example described above, a record manager may use twelve tables whereby each table corresponds to a particular month in a given calendar year. At step 540, the record manager uses the time interval and the total tables number to identify a table that corresponds to the report request's timeframe. In one embodiment, processing may use the formula
(Timeframe/Interval) %(Total Tables Number)=Table Number
where “%” is the remainder after division. For example, if the interval is 86400000, the timeframe is 1084571611014, and the total tables number is 10, then
Timestamp/interval=12552
12552/10=1255+2 as the remainder
Therefore, since “2” is the remainder, the second table is identified as the table to use for report generation. In one embodiment, the timeframe may have a start and a stop time. In this embodiment, processing may use the above formula for both times, and, if the remainder of the start time is different from the remainder of the stop time, processing uses a plurality of tables during the report generation.
Processing locates the identified table in table store 175 at step 550. At step560, processing generates a report using the records that are included in the identified table. Table store 175 is the same as that shown in
A determination is made as to whether there are more tables to identify their number of records (decision 640). If there are more tables to identify their number of records, decision 640 branches to “Yes” branch 642 whereupon processing selects (step 650) and processes the next table. This looping continues until there are no more tables to process, at which point decision 640 branches to “No” branch 648 whereupon processing analyzes the amount of records that are included in each table. For example, each of the existing tables may cover a timeframe of one month, and processing analyzes the number of records in each month and determines that there are such a large number of records in each month, that the number of tables should be doubled. In addition, the timeframe of each table is shortened to two-week intervals. In this example, and as one skilled in the art can appreciate, the time at which a change is made to the total number of tables should be stored in a storage area in order for the record manager to use during future record storage and report generations.
In one embodiment when the number of tables is increased, a database manager may choose to start a new database to store records. For example, the database manager may perform an analysis every two years and, after the analysis, the database manager may archive the existing database and start a new database. In this example, the database manager may increase the number of tables every two years without increasing the complexity of identifying which table to use during record storage and report generation.
A determination is made as to whether to increase the number of tables based upon the analysis (decision 670). If the report manager, or database administrator, wishes to increase the number of tables, decision 670 branches to “Yes” branch 672 whereupon processing increases the number of tables and stores the new total tables number in preferences store 110 (step 680). Preferences store 110 is the same as that shown in
PCI bus 714 provides an interface for a variety of devices that are shared by host processor(s) 700 and Service Processor 716 including, for example, flash memory 718. PCI-to-ISA bridge 735 provides bus control to handle transfers between PCI bus 714 and ISA bus 740, universal serial bus (USB) functionality 745, power management functionality 755, and can include other functional elements not shown, such as a real-time clock (RTC), DMA control, interrupt support, and system management bus support. Nonvolatile RAM 720 is attached to ISA Bus 740. Service Processor 716 includes JTAG and I2C busses 722 for communication with processor(s) 700 during initialization steps. JTAG/I2C busses 722 are also coupled to L2 cache 704, Host-to-PCI bridge 706, and main memory 708 providing a communications path between the processor, the Service Processor, the L2 cache, the Host-to-PCI bridge, and the main memory. Service Processor 716 also has access to system power resources for powering down information handling device 701.
Peripheral devices and input/output (I/O) devices can be attached to various interfaces (e.g., parallel interface 762, serial interface 764, keyboard interface 768, and mouse interface 770 coupled to ISA bus 740. Alternatively, many I/O devices can be accommodated by a super I/O controller (not shown) attached to ISA bus 740.
In order to attach computer system 701 to another computer system to copy files over a network, LAN card 730 is coupled to PCI bus 710. Similarly, to connect computer system 701 to an ISP to connect to the Internet using a telephone line connection, modem 775 is connected to serial port 764 and PCI-to-ISA Bridge 735.
While the computer system described in
One of the preferred implementations of the invention is an application, namely, a set of instructions (program code) in a code module which may, for example, be resident in the random access memory of the computer. Until required by the computer, the set of instructions may be stored in another computer memory, for example, on a hard disk drive, or in removable storage such as an optical disk (for eventual use in a CD ROM) or floppy disk (for eventual use in a floppy disk drive), or downloaded via the Internet or other computer network. Thus, the present invention may be implemented as a computer program product for use in a computer. In addition, although the various methods described are conveniently implemented in a general purpose computer selectively activated or reconfigured by software, one of ordinary skill in the art would also recognize that such methods may be carried out in hardware, in firmware, or in more specialized apparatus constructed to perform the required method steps.
While particular embodiments of the present invention have been shown and described, it will be obvious to those skilled in the art that, based upon the teachings herein, changes and modifications may be made without departing from this invention and its broader aspects and, therefore, the appended claims are to encompass within their scope all such changes and modifications as are within the true spirit and scope of this invention. Furthermore, it is to be understood that the invention is solely defined by the appended claims. It will be understood by those with skill in the art that if a specific number of an introduced claim element is intended, such intent will be explicitly recited in the claim, and in the absence of such recitation no such limitation is present. For a non-limiting example, as an aid to understanding, the following appended claims contain usage of the introductory phrases “at least one” and “one or more” to introduce claim elements. However, the use of such phrases should not be construed to imply that the introduction of a claim element by the indefinite articles “a” or “an” limits any particular claim containing such introduced claim element to inventions containing only one such element, even when the same claim includes the introductory phrases “one or more” or “at least one” and indefinite articles such as “a” or “an”; the same holds true for the use in the claims of definite articles.
