System and method for enhanced performance of a relational database management system through the use of application-specific memory-resident data6304867Abstract A system for enhancing the performance of a relational database management system is provided which consists of a computer (10) running a relational database management system (16) (RDBMS) with a data storage device (12) containing a database (14) under control of the RDBMS (16). The RDBMS performance enhancement system utilizes a computer memory (22) to store certain data from the database (14). A pre-processor (20), which is part of an application (18) program, executes the performance enhancements which include staging data into memory (22), creating memory-based alternate indexes, and deferring updates until the application (18) terminates. The performance enhancements reduce data access requests to the RDBMS (16) and eliminate unnecessary RDBMS (16) operations. Claims What is claimed is: Description TECHNICAL FIELD OF THE INVENTION
IF table-switch equals "on"
MOVE "select" to parmlist-VERB
CALL "pre-processor" using parmlist
ELSE
EXEC SQL SELECT . . .
END-EXEC
END-IF
The IF statement provides the software engineer with flexibility in the use of pre-processor 20 by allowing a table switch to be set in control file 41 and read during the initialization procedure for application 18 to determine whether pre-processor 20 will control the relational table for that execution of application 18. Thus, the software engineer can turn on and turn off pre-processor 20 without recompiling application 18. The IF statement also retains the original standard SQL statement for documentation purposes. Once the final source code statement in source code file 70 is reached, the process of FIG. 4 terminates, and the results of install/pre-compiler module 50 are placed in expanded source code file 72 for use in a standard compile process. Staging of Data One of the ways that pre-processor 20 enhances the performance of RDBMS 16 is through a process of staging data into application RAM 22. Staging data consists of reading relational table rows from database 14 and placing those rows in application RAM 22 for use during the execution of application 18. Thus, additional requests for the same table row will require only an access to application RAM 22 instead of a request to RDBMS 16. Pre-processor 20 keeps track of what relational tables have table rows staged into application RAM 22 so that further accesses to those table rows are directed to application RAM 22 instead of database 14. The application that requests data to be staged into memory has access to the data in application RAM 22. All other applications that may access the same data will access the data in the relational database. Pre-processor 20 may stage an entire relational table into RAM 22. The staging of an entire database may be used, for instance, to load small tables which are accessed frequently into RAM 22. For example, if a table is thirty rows long and is accessed an average three hundred times per execution of application 18, the performance of RDBMS 16 can be enhanced by expending one RDBMS request for the 30 rows at the start of the execution of application 18 in order to save three hundred RDBMS requests which would be distributed throughout the execution of application 18. In this example, each of the three hundred requests to the database table would be an access to RAM 22 which is significantly more efficient than a data access request to RDBMS 16. Staging an entire relational table into RAM 22 incurs the expense of one RDBMS request for each relational table row at the beginning of the execution of application 18. However, no further RDBMS requests to the relational table loaded into RAM 22 occur for the remainder of the execution of application 18. FIG. 5 is a flow diagram illustrating the basic process of staging an entire relational table into application RAM 2 and the subsequent data access requests from application 18. The process of staging an entire relational table into application RAM 22 commences at step 100 where pre-processor 20 receives an "initial" call causing it to read control file 41 which may contain an instruction requesting that a certain relational table be loaded into application RAM 22. Pre-processor 20 then loads the requested relational table into application RAM 22 and sets certain parameters so that all further requests for data from that relational table may be made to application RAM 22 instead of RDBMS 16. The process then proceeds to step 102 where application 18 requests data. The process then proceeds to step 104 where application 18 determines whether the relational table in the data request is controlled by pre-processor 20. If it is not controlled by pre-processor 20, the process proceeds to step 106 where a standard data request is issued to RDBMS 16. If step 104 determines that the relational table of the data request in step 102 is controlled by pre-processor 20, the process proceeds to step 108 where pre-processor 20 determines whether the requested relational table is loaded in application RAM 22. If it is not, the process proceeds to step 110 where pre-processor 20 determines if any other performance enhancements are applicable to application 18. Pre-processor 20 then retrieves the requested data from either RDBMS 16 or application RAM 22 depending upon the performance enhancements, if any, which are applicable to the requested relational table. If step 108 determines that the requested relational table is loaded in application RAM 22, the process proceeds to step 112 where the data requested in step 102 is retrieved from application RAM 22. Pre-processor 20 can also stage specific records into RAM 22. If the software engineer knows that certain table rows are accessed frequently during the execution of application 18, the software engineer can have pre-processor 20 load the specified table rows into application RAM 22 at the start of application 18 so that further accesses to the table rows during the execution of application 18 will not incur an RDBMS request. Pre-processor 20 keeps track of what table rows for what relational tables are loaded into application RAM 22. If a requested table row is in application RAM 22, pre-processor 20 retrieves it from application RAM 22, otherwise pre-processor 20 issues a standard RDBMS 16 request to retrieve the requested table row from database 14. FIG. 6 is a flow diagram illustrating the basic process of staging specific relational table rows into RAM 22, and the subsequent data access requests to those rows from application 18. The process commences at step 120 where pre-processor 20 receives an "initial" call causing it to read control file 41 which may contain statements for a particular relational table requesting that specific relational table rows be loaded into application RAM 22. Pre-processor 20 loads the requested table rows into application RAM 22 and keeps track of the table rows which were loaded so that further accesses to those table rows will be to application RAM 22 instead of RDBMS 16. The process then proceeds to step 122 where application 18 requests access to a certain table row. The process then proceeds to step 124 where a determination is made regarding whether the relational table to which access was requested in step 122 is under the control of pre-processor 20. If it is not, the process proceeds to step 126 where normal database processing takes place utilizing RDBMS 16. Note that the determination of which relational tables will be controlled by pre-processor 20 is done at compile time. However, the determination of which type of performance enhancement is applicable to the relational table is made at runtime by pre-processor 20 reading control file 41. If application 18 contains a call to pre-processor 20 for the data request in step 122, then the requested table is under the control of pre-processor 20. If application 18 contains only a standard SQL statement for the data access request of step 122, then the relational table is processed normally without any performance enhancements. The inclusion of step 124 is merely to explicitly state that at some point in time, a determination is made whether the data access request to the relational table is through pre-processor 20 or RDBMS 16. If step 124 determines that the relational table is under the control of pre-processor 20, the process proceeds to step 128 where pre-processor 20 determines if the requested table has any table rows loaded in application RAM 22. If it does not, the process proceeds to step 130 where pre-processor 20 checks for other types of performance enhancements for the requested relational table and retrieves the requested table row as necessary. If step 128 determines that the requested relational table has table rows loaded in application RAM 22, the process proceeds to step 132 where a determination is made regarding the existence of the requested table row in application RAM 22. If the requested table row is in application RAM 22, the process proceeds to step 134 where pre-processor 20 retrieves the table row from application RAM 22 and returns it for use in application 18. If step 132 determines that the requested table row is not in application RAM 22, the process proceeds to step 136 where pre-processor 20 performs a normal database access request through RDBMS 16 and returns the table row for use in application 18. Pre-processor 20 can also stage all records with similar keys or the same partial key into RAM 22. This is accomplished by storing the returned relational table rows of a "select on partial key" in RAM 22. As discussed above, pre-processor 20 keeps track of every relational table row loaded into RAM 22 and any further request for access to that relational table row is processed by an access to RAM 22 instead of a request to RDBMS 16. Staging the results of a select on partial key into RAM 22 can be used when the software engineer knows that only that specified group of relational table rows is used during the execution of application 18. FIG. 7 is a flow diagram illustrating the basic process of staging a relational table into application RAM 22 based on a partial key and the subsequent data access requests to that relational table in application 18. The process commences at step 140 where pre-processor 20 receives an "initial" call causing it to read control file 41 which may contain instructions requesting that table rows which match a specified partial key for a certain relational table be loaded into application RAM 22. Pre-processor 20 then selects the relational table rows as requested based on information contained in control file 41. The selected table rows are placed in application RAM 22, and pre-processor 20 keeps track of the table rows which it has placed in application RAM 22. The process then proceeds to step 142 where application 18 requests access to one or more table rows. The process then proceeds to step 144 where a determination is made regarding whether the requested relational table is controlled by pre-processor 20. If it is not, the process proceeds to step 146 where a normal database access takes place using RDBMS 16. If step 144 determines that the requested relational table is controlled by pre-processor 20, the process proceeds to step 148 where pre-processor 20 determines whether the requested relational table has any table rows loaded into application RAM 22. If it does not, the process proceeds to step 150 where pre-processor 20 checks for other types of performance enhancements and retrieves the requested table row from either application RAM 22 or RDBMS 16 depending upon what performance enhancements are in place for the requested relational table. If step 148 determines that the requested relational table has table rows in application RAM 22, the process proceeds to step 152 where a determination is made regarding the existence of the requested table row in application RAM 22. If the requested table row is not in application RAM 22, the process then proceeds to step 154 where pre-processor 20 performs a normal database access using RDBMS 16. If step 152 determines that the requested table row is in application RAM 22, the process proceeds to step 156 where pre-processor 20 retrieves the requested table row from application RAM 22 and returns it for use in application 18. Retention of Retrieved Relational Table Rows Pre-processor 20 can retain all table rows requested during an execution of application 18 in application RAM 22 so that further requests for those table rows do not incur another RDBMS 16 request. Pre-processor 20 accomplishes this by placing the results of every keyed select request into RAM 22. Pre-processor 20 keeps track of what table rows are loaded in application RAM 22 and determines whether an access to application RAM 22 is required or an access to database 14 is required dependent upon the select request received from application 18. If the requested relational table row exists in RAM 22, the retrieval time is much faster than a request to RDBMS 16. Staging relational table rows into RAM 22 as they are retrieved does not incur any RDBMS 16 data access requests up front. Instead, this method expends the RDBMS data access request during the first request for access to a specific relational table row. All additional requests for access to that relational table row will retrieve it directly from RAM 22. FIG. 8 is a flow diagram illustrating the basic processing which takes place when pre-processor 20 receives an "initial" call causing it to read control file 41 which may contain instructions requesting that all requested table rows for a certain relational table be retained in memory for further use. The process commences at step 160 where application 18 requests access to a table row. The process proceeds to step 162 where a determination is made regarding whether the requested relational table is under the control of pre-processor 20. If it is not, the process proceeds to step 164 where a normal database access request is performed by RDBMS 16. If step 162 determines that the requested relational table is under the control of pre-processor 20, the process proceeds to step 166 where a determination is made regarding the existence of the requested table row in application RAM 22. If it is in application RAM 22, the process proceeds to step 170 where pre-processor 20 retrieves the requested row from application RAM 22 and returns it for use in application 18. If step 166 determines that the requested table row is not in application RAM 22, the process proceeds to step 168 where pre-processor 20 retrieves the requested table row from RDBMS 16, loads the requested table row into application RAM 22 for future use, and returns the retrieved table row for use in application 18. Another form of staging data into memory is retaining the last X most frequently requested rows in memory, where X represents some number determined by the software engineer. For instance, X could be two hundred. In that case, pre-processor 20 would retain the last two hundred most frequently requested rows in application RAM 22. Pre-processor 20 keeps track of the number of requests for access to each of the table rows staged into application RAM 22. Therefore, when a request for access to a row which does not exist in application RAM 22 is received, pre-processor 20 retrieves the row from RDBMS 16 and overlays the least requested row in application RAM 22. If X rows have not yet been loaded into application RAM 22, pre-processor 20 loads the table row into the next available position. By doing this, pre-processor 20 can minimize the number of RDBMS requests since the most frequently requested records reside in RAM 22. FIG. 9 is a flow diagram illustrating the basic process of retaining a specific number of the most frequently requested table rows in memory and the subsequent data access requests to those table rows by application 18. Pre-processor 20 receives an "initial" call causing it to read control file 41 which may contain instructions requesting that a specific number of the most frequently requested table rows for a particular table be retained in memory for subsequent data access requests. The process commences at step 180 where application 18 requests access to a relational table row. The process then proceeds to step 182 where a determination is made regarding whether the requested relational table is under the control of pre-processor 20. If it is not, the process proceeds to step 184 where a normal database access request is performed by RDBMS 16. If step 182 determines that the requested relational table is under the control of pre-processor 20, the process proceeds to step 186 where a determination is made regarding the existence of the requested table row in application RAM 22. If the requested table row is in application RAM 22, the process proceeds to step 188 where pre-processor 20 retrieves the requested relational table row from application RAM 22 and returns it for use in application 18. The process then proceeds to step 190 where pre-processor 20 adds one to the retrieval count for the requested table row which was just retrieved from application RAM 22. If step 186 determines that the requested table row is not in application RAM 22, the process proceeds to step 192 where pre-processor 20 retrieves the requested table row from RDBMS 16. The process then proceeds to step 193 where a determination is made regarding whether X table rows have been loaded into application RAM 22. If step 193 determines that X rows have not yet been loaded into application RAM 22, the process proceeds to step 195 where the relational table row is loaded into the next available memory position and its retrieval counter is set to zero. If step 193 determines that X table rows have already been loaded into application RAM 22, the process proceeds to step 194 where pre-processor 20 locates the least requested table row in application RAM 22 for the requested relational table and replaces it with the table row just retrieved through RDBMS 16. The retrieval counter for each table row in application RAM 22 is used to determine which table row is the least requested. When a new table row is loaded into application RAM 22, its retrieval counter is reset to zero. Subsetting a Table Another form of staging data into memory in order to reduce data access requests to RDBMS 16 is to subset a relational table into memory-based on the known processing requirements of application 18. For example, if application 18 only processes relational table rows with a last name of "Doe", these records can be subsetted through a select statement and loaded into RAM 22 at the beginning of the execution of application 18. Pre-processor 20 then allows the subset in RAM 22 to be processed as a full relational table. An important advantage of this subsetting ability is that random accesses to a particular row in the subset are significantly more efficient due to the reduced number of rows which must be searched. FIG. 10 is a flow diagram illustrating the basic process of loading a subset of a relational table into RAM 22 and the subsequent data access requests to the loaded table rows. The process commences at step 200 where pre-processor 20 receives an "initial" call causing it to read control file 41 which may contain instructions requesting that a subset of a certain relational table be loaded into RAM 22 so that it may be processed as a separate, complete relational table. Pre-processor 20 then selects and loads relational table rows based on information received in the parameter list of control file 41. The process then proceeds to step 202 where application 18 requests access to a relational table row. The process then proceeds to step 204 where a determination is made regarding whether the requested relational table is under the control of pre-processor 20. If it is not, the process proceeds to step 206 where a normal database access request is performed by RDBMS 16. If step 204 determines that the requested relational table is under the control of pre-processor 20, the process proceeds to step 208 where a determination is made regarding whether the requested relational table is a subset in application RAM 22. If it is not, the process proceeds to step 210 where pre-processor 20 checks for other performance enhancements applicable to the requested relational table and retrieves the requested table row from application RAM 22 or RDBMS 16 dependent upon the performance enhancements in effect for the relational table. If step 208 determines that the requested relational table is a subset in application RAM 22, the process proceeds to step 212 where pre-processor 20 retrieves the requested table row from application RAM 22 and returns it for use in application 18. Memory-Based Alternate Indexes Pre-processor 20 can create an alternate index 24 which is loaded into application RAM 22. Memory-based alternate indexes may be created for relational tables which are wholly staged into application RAM 22. By doing this, both the relational table and the alternate index are resident in application RAM 22 thereby significantly decreasing the time to perform data access requests. The software engineer places statements in control file 41 which cause pre-processor 20 to stage the entire relational table into application RAM 22 and create a memory-based alternate index on a specified table column. Application 18 can access the specified relational table through alternate index 24 without incurring the expensive overhead processing associated with alternate indexes. This overhead includes RDBMS 16 creating and maintaining the alternate indexes whenever a table row is added to or deleted from database 14. In addition, alternate indexes can be changed which incurs additional overhead in updating the alternate index every time a table row has an alternate index column changed. Furthermore, accessing an alternate index in random access memory is significantly more efficient than accessing an alternate index which resides on a disk drive. The software engineer utilizes this alternate index by coding a standard SQL select statement based on the alternate key. Pre-processor 20 then processes the select statement and finds the requested relational table row or rows by accessing alternate index 24 in RAM 22. Memory-based alternate indexes allow the software engineer to determine what secondary keys are necessary for application 18 without requiring RDBMS 16 to maintain those alternate indexes. In addition, memory-based alternate indexes allow a secondary key to be defined for a single application program. FIG. 11 is a flow diagram illustrating the basic processing associated with creating a memory-based alternate index for a relational table and the subsequent use of that alternate index in application 18. The process commences at step 220 where pre-processor 20 receives an "initial" call causing it to read control file 41 which may contain instructions requesting that a memory-based alternate index be built for a certain relational table. Pre-processor 20 creates the memory-based alternate index based on information in the parameter list read from control file 41. The process then proceeds to step 222 where application 18 requests access to a relational table. The process then proceeds to step 224 where a determination is made regarding whether the requested relational table is under the control of pre-processor 20. If it is not, the process proceeds to step 226 where a normal database access is performed using RDBMS 16. If step 224 determines that the requested relational table is under the control of pre-processor 20, the process proceeds to step 228 where a determination is made regarding whether the database access request to the relational table is based on the memory-based alternate index created in step 220. If it is not, the process proceeds to step 230 where pre-processor 20 checks for other performance enhancements applicable to application 18 and retrieves the requested data from application RAM 22 or RDBMS 16. If step 228 determines that the data access request is based on a memory-based alternate index, the process proceeds to step 232 where pre-processor 20 locates the requested alternate index item in application RAM 22. The process then proceeds to step 234 where pre-processor 20 retrieves the requested table row from application RAM 22 based on a pointer to the memory location stored with the alternate index item located in step 232. Pre-processor 20 then returns the requested table row for use in application 18. Deferred Updates Another performance enhancement of pre-processor 20 is the ability to defer all updates to a relational table in database 14 until application 18 terminates. In order to defer updates to a relational table, application 18 should lock the relational table so that no other application can attempt an update to or read from the relational table until application 18 terminates. If application 18 requests pre-processor 20 to defer all updates to a certain relational table until termination of application 18, pre-processor 20 saves all update requests to that relational table in a saved updates 26 area. Saved updates 26 may be saved in memory and on a disk drive. Whenever application 18 attempts to update a row in a relational table with the deferred updates option activated, pre-processor 20 updates the row in application RAM 22, saves the update request to saved updates 26, and continues processing. When application 18 is ready to terminate, pre-processor 20 performs all updates in saved updates 26 to the specified relational table in database 14. By deferring updates until termination of application 18, the expense of updating a relational table row, in runtime and RDBMS requests, is deferred until application 18 has completed all of its assigned tasks. Therefore, the efficiency of application 18 is enhanced since its processing is not delayed due to time consuming updates during execution of application 18. FIG. 12 is a flow diagram illustrating the basic method involved in deferring updates to a certain relational table until termination of application 18. The method commences at step 240 where pre-processor 20 receives an "initial" call causing it to read control file 41 which may contain instructions requesting that updates to a certain relational table be deferred until the end of application 18. Pre-processor 20 sets the appropriate internal parameters so that all updates to the requested relational table are saved and performed at the end of application 18. The method then proceeds to step 241 where the requested relational table is locked so that no other applications can attempt updates until application 18 terminates. The method then proceeds to step 242 where application 18 requests update of a relational table row. The method then proceeds to step 244 where a determination is made regarding whether the requested relational table is under the control of pre-processor 20. If it is not, the method proceeds to step 246 where a normal database update is performed using RDBMS 16. If step 244 determines that the requested relational table is under the control of pre-processor 20, the method proceeds to step 248 where pre-processor 20 determines if deferred updates are applicable to the requested relational table. If the requested relational table does not have deferred updates, the method proceeds to step 250 where a normal relational database update is performed using RDBMS 16. If step 248 determines that the requested relational table has all of its updates deferred until termination of application 18, the method proceeds to step 252 where the requested update information is saved in both application RAM 22 and a saved updates 26 area. Application 18 continues processing until it reaches its termination point at which time the method proceeds to step 254 where all update information saved in step 252 is retrieved and applied to the requested relational table in database 14. The method then proceeds to step 256 where the relational table is unlocked. After step 256 is complete, application 18 terminates. Enhanced Search/Sort Pre-processor 20 also includes a more efficient search and sort engine. Most table search and sort engines are based on a standard binary search or sort technique. This is generally thought to be an efficient method of doing searches and sorts. However, there are more efficient methods of searching a relational table or sorting information. One of these more efficient methods is based on the Fibonacci number sequence. Utilizing the Fibonacci number sequence in designing a technique to search a database table results in fewer CPU instructions in order to locate the requested row. Likewise, a sort technique based on the Fibonacci number sequence utilizes fewer CPU instructions in sorting a given set of information in a particular order. FIG. 13 is a flow diagram illustrating the optimized sort technique of the present invention. The method commences at step 260 where application 18 requests that certain relational table rows be sorted in a particular order. This request may be made by using the "ORDER BY" sub-verb on a "SELECT" SQL statement. The method proceeds to step 262 where a determination is made regarding whether the relational table which supplied the relational table rows in step 260 is under the control of pre-processor 20. If it is not, the method proceeds to step 264 where the relational table rows are sorted in the standard manner of RDBMS 16. If step 262 determines that the relational table is under the control of pre-processor 20, the method proceeds to step 266 where the relational table rows retrieved in step 260 are sorted using an optimized sort procedure. FIG. 14 is a flow diagram illustrating an optimized search technique as defined in the present invention. The method commences at step 280 where application 18 requests access to a relational table row based on a "SELECT" SQL statement using a specific key. The process then proceeds to step 282 where a determination is made regarding whether the relational table which is the subject of the SQL "SELECT" statement of step 280 is under the control of pre-processor 20. If it is not, the method proceeds to step 284 where a normal database access is performed using RDBMS 16. If step 282 determines that the requested relational table is under the control of pre-processor 20, the method proceeds to step 286 where pre-processor 20 searches the requested relational table's index using an optimized search technique. The method then proceeds to step 288 where the relational table row associated with the index located in step 286 is retrieved and returned for use in application 18. Simulation Mode Another performance enhancement of pre-processor 20 is geared toward making software engineers more efficient and productive. Pre-processor 20 includes a simulation mode so that application programs can be tested on live production data without actually altering or updating any of that data. The software engineer sets an input parameter to application 18 in control file 41 which informs pre-processor 20 to run in simulation mode. Simulation mode allows all accesses to a relational database but prevents the actual updating or altering of any data. By utilizing simulation mode, application 18 can be tested using a live production database instead of a test version of that database. This speeds up testing since the software engineer does not have to have a database administrator load a test version of the database before tests can commence. Furthermore, the database does not have to be reloaded each time the software engineer needs to rerun the application. Therefore, multiple runs against live production data can be run without the necessity of loading and reloading test versions of the database. FIG. 15 is a flow diagram illustrating the simulation mode of the present invention. The method commences at step 300 where a software engineer sets a parameter in control file 41 which informs pre-processor 20 that application 18 is to be run in simulation mode. The method then proceeds to step 302 where application 18 is executed on computer 10. The method then proceeds to step 304 where, during execution of application 18, a determination is made regarding whether application 18 is being run in simulation mode. If it is not, the method proceeds to step 306 where application 18 runs normally and all updates are performed as necessary to database 14. If step 304 determines that application 18 is being run in simulation mode, the method proceeds to step 308 where application 18 executes normally in all ways except that no updates are actually performed to any relational table in database 14. According to the teachings of the present invention, a system for enhancing the performance of a relational database management system is provided that reduces utilization of the RDBMS and eliminates unnecessary RDBMS processing based on the requirements of an application. The advantages of the performance enhancement system for a relational database management system include reduced RDBMS utilization resulting in reduced runtime, the creation of memory-based alternate indexes as needed, and a simulation mode which allows testing against live production data. Thus, it is apparent that there has been provided in accordance with the present invention a system for enhancing performance of a relational database management system that satisfies the advantages set forth above. Although the present invention and its advantages have been described in detail, it should be understood that various changes, substitutions, and alterations readily apparent to those skilled in the art may be made herein without departing from the spirit and the scope of the present invention as defined by the following claims.
|
Same subclass Same class Consider this |
||||||||||