Abstract:
A system and method that enhances the data access performance of a multi-layer relational database manager by expanding the code generation component layer of the database manager to include a number of performance enhancing subroutines designed to execute functions performed by lower component layers substantially faster than if the functions were executed by such lower component layers. Each such subroutine includes logic for establishing the conditions under which the particular subroutine is invoked during the processing of a SQL request. During process of generating code for a specific SQL query, the code generation component layer inserts calls to the different performance enhancing subroutines in place of normally included calls to lower component layers. This results in the insertion of the different performance enhancing subroutines into the generated code. Such routines enable the dynamically generated code at query execution time to perform lower component layer functions based on the characteristics of the original query statement resulting in increased performance.

Description:
RELATED PATENT APPLICATIONS 
     1. A Method and System For Using Dynamically Generated Code to Perform Record Management Layer Functions in a Relational Database Manager invented by David S. Edwards, David A. Egolf and William L. Lawrance, filed on even date, bearing Ser. No. 09/408,985 and assigned to the same assignee as named herein. 
     2. A Method and System For Using Dynamically Generated Code to Perform Index Record Retrieval in Certain Circumstances in a Relational Database Manager invented by David S. Edwards and Todd Kneisel, filed on even date, bearing Ser. No. 09/408,986 and assigned to the same assignee as named herein. 
     BACKGROUND OF THE INVENTION 
     1. Field of Use 
     The present invention relates to data processing systems and more particularly to database management systems. 
     2. Prior Art 
     Typically, today&#39;s enterprise or legacy systems store large quantities of data in database systems accessed by database management system (DBMS) software. In such database systems, data is logically organized into relations or tables wherein each relation can be viewed as a table where each row is a tuple and each column is a component of the relation designating an attribute. It has become quite common to use relational database management systems (RDMS) for enabling users to enter queries derived from a database query language, such as SQL, into the database in order to obtain or extract requested data. 
     In compiling type database management systems, an application program containing database queries is processed for compilation prior to run time. This can be done and more frequently is done at run time by users of the INTEREL product discussed herein. Users of other database products such as DB2, do such processing prior to run time. 
     During compilation, database queries are passed to the database management system for compilation by a database management system compiler. The compiler translates the queries contained in the application program into machine language. Generally, a database compiler component referred to, as a query optimizer is included in the database management system to select the manner in which queries will be processed. The reason is because most users do not input queries in formats that suggest the most efficient way for the database management system to address the query. The query optimizer component analyzes how best to conduct the user&#39;s query of the database in terms of optimum speed in accessing the requested data. That is, the optimizer typically transforms a user query into an equivalent query that can be computed more efficiently. This operation is performed at compile time, in advance of execution. 
     A major component of the RDBMS is the database services component or module that supports the functions of SQL language, such as definition, access control, retrieval and update of user and system data. Such components may utilize a multilayer structure containing submodules or components for carrying out the required functions. For example, one such system includes a series of components or conceptually, a series of layers for carrying out the required functions for accessing data from the relational database. More specifically, a first layer functions as a SQL director component that handles requests at the interface to the requesting or calling application program. A second layer consists of two major components, an optimizer for optimizing the query and a RAM code generation component. The optimizer processes the query by determining the appropriate access plan strategy. The code generation component generates code according to such plan for accessing and processing the requested data. The access plan defines the type of access to each table, order of access, whether any sorts or joins are performed along with other related information. 
     The generated code calls a third layer that functions as a relational file manager (RFM) component. This component layer performs the relational file processing function of translating the code-generated requests into I/O file read/write requests. A fourth layer that functions as an  10  Controller performs the requested I/O operation designated by such I/O file requests that results in reading/writing the relational database files in page increments. The described architecture is characteristic of the INTEREL product developed and marketed by Bull HN Information Systems Inc. For information concerning this product, reference may be made to the publication entitled, “Database Products INTEREL Reference Manual INTEREL Performance Guidelines, Copyright, 1996 by Bull HN Information Systems Inc., Order No. LZ93 Rev01B. 
     It has been found that while the above architecture provides design advantages, it tends to slow down relational data access performance. More specifically, the relational retrieval process involves the execution of functions by a series of components or layers that can result in decreased performance. This is the case particularly when the RDMS is required to access non-partitioned (i.e., single page) data rows and perform index record retrieval operations. 
     Accordingly, it is a primary object of the present invention to provide a more efficient method and system for improving relational data access performance. 
     SUMMARY OF THE INVENTION 
     The above objects are achieved in a preferred embodiment of the present invention that can be utilized in a relational database management System (RDMS) that implements the Structured Query Language (SQL) standard. The present invention is a system and method that enhances the data access performance of a multi-layer relational database manager. According to the teachings of the invention, the code generation component layer of the database manager is expanded to include a number of performance enhancing subroutines designed to execute functions performed by lower component layers substantially faster than if the functions were executed by such lower component layers. Each such subroutine includes logic for establishing the conditions under which the particular subroutine is invoked during the processing of a SQL request. The different types of performance enhancing subroutines are described in the above-related patent applications. 
     According to the teachings of the present invention, during process of generating code for a specific SQL query, the code generation component inserts calls to the different performance enhancing subroutines in place of normally included calls to lower component layers. This results in the insertion of the different performance enhancing subroutines into the generated code. In the preferred embodiment, such routines enable the dynamically generated code to perform lower component layer record management functions based on the characteristics of the original query statement resulting in increased performance. For example, record management functions that are normally performed by the system&#39;s relational file manager component. Hence, when the generated SQL query code is being executed and accesses the predetermined type of data file record, the performance enhanced subroutine is executed in lieu of calling lower component layers to perform the required record file processing. By eliminating or bypassing such other layers, relational data access performance is substantially increased. 
     The present invention by incorporating the required record management functions into the code generated to execute the SQL query can be easily implemented without having to make changes to the other components/layers of the relational database manager. 
     The above objects and advantages of the present invention will be better understood from the following description when taken in conjunction with the accompanying drawings. 
    
    
     BRIEF DESCRIPTION OF THE DRAWINGS 
     FIG. 1 is an overall block diagram of a data processing system that utilizes the teachings of the present invention. 
     FIG. 2 a  is a block diagram illustrating the multi-layer organization of the relational database manager system (RDMS) of FIG.  1 . 
     FIG. 2 b  is a block diagram illustrating in greater detail, the major components of the second layer of the RDMS of FIG. 2 a.    
     FIG. 2 c  is a block diagram illustrating in greater detail, the code generation component of FIG. 2 b  designed to utilize the teachings of the present invention. 
     FIG. 2 d  illustrates the operational relationships between the second and third layers of the RDMS of FIG. 2 a.    
     FIGS. 3 a  and  3   b  illustrate the operational flow of two performance enhancing subroutines utilized according to the teachings of the present invention. 
    
    
     DESCRIPTION OF THE PREFERRED EMBODIMENT 
     FIG. 1 
     FIG. 1 is a block diagram of a conventional data processing system  10  that utilizes the system and method of the present invention. As shown, the system  10  includes a plurality of processing units  12 - 1  through  12 -n which connect to a system bus  14  through their individual physical caches in common with a number of input/output units (IOUs)  16 - 1  through  16 -n and a system control unit (SCU)  22 . As shown, each IOU couples to a particular I/O subsystem (i.e.,  19 - 1  through  19 -n) which in turn connect to any one of a number of different types of devices both local and remote such as workstation  21 - 1  via a network  20  or disk mass storage units  21 -n as indicated. 
     The SCU  22  connects to a number of memory units (MUs)  24 - 1  through  24 -n. For the purpose of the present invention, system  10  may be considered convention in design and may for example utilize a mainframe computer system such as the DPS9000 manufactured by Bull HN Information Systems Inc. which operates under the control of the GCOS8 operating system. 
     As shown, the system  10  further includes disk storage  21 -n that contains the database system that utilizes the teachings of the present invention. It will be appreciated that the software components that comprise the database system including the software components of the present invention may be loaded into the system  10  in a conventional manner (e.g. via CDROM, disk, communications link, etc.). The database system includes a multi-layer relational database management system (RDMS) and a relational database containing established data files. The relational database management system processes all user requests for accessing the files contained on disk storage  21 -n. Users initiate such requests via the network  20  by executing transaction processing routines or batch decision support programs via their workstation keyboard and/or via other input devices (e.g. mouse). The system  10  upon receiving an SQL query operates to initiate a search of the relational database files to obtain the data requested by the user. 
     In system  10 , the relational database management system (RDMS) takes the form of the above mentioned INTEREL software which runs under the GCOS8 operating system. As shown, the RDMS contains a SQL Director component layer, a Codegen Executor component layer, a Record File Manager (RFM) component layer and an IO Random Controller component layer. These component layers are shown in greater detail, in FIG. 2 a  along with other database related components. 
     FIG. 2 a —Multi-layer RDMS Organization 
     FIG. 2 a  depicts the major components of the RDMS that utilizes the teachings of the present invention. As shown, these components include the four component layers of RDMS  200  (INTEREL software) discussed above. During normal operation, the different software components of RDMS  200  including the present invention are loaded from disk storage  21 -n into memory (e.g. MU  24 - 1 ) in a conventional manner. 
     In greater detail, SQL Director Component layer  202  operatively couples to an SQL adapter  201  that serves as the application&#39;s interface to the RDMS  200 . The SQL Adapter  201  includes a runtime library that contains runtime routines bound into the application used by an application such as a COBOL-85 program for issuing calls. Each such call results in library sending a query statement to the SQL Director component layer  202 . 
     The SQL Director component layer  202  handles the interface processing between RDMS  200  and a calling program. Thus, it manages the database connection. Layer  202  contains routines which analyze each query statement for determining if the statement is of a type that accesses relational database files and thus is suitable for code generation and caching. Each process utilizes a “local cache” for such storage. The use of “local caches” is discussed in the above referenced INTEREL Reference manual. Additionally, reference may be made to the copending patent application of Donald P. Levine and David A. Egolf, entitled: A Method and Apparatus for Improving the Performance of a Database Management System Through a Central Cache Mechanism, bearing Ser. No. 08/999,248 filed on Dec. 29, 1997 which is assigned to the same assignee as named herein. 
     As indicated, the SQL Director component layer  202  operatively couples to the RAM Codegen Executor Component layer  204 . The SQL Director component layer  202  also contains routines that generate calls to a cache manager component to see if the code for that statement can be found in the process&#39;s local cache. When code for that statement is not found in the local cache, the SQL Director component layer  202  calls the RAM Codegen Executor Component layer  204  to process and “potentially” execute the query statement. 
     The RAM Codegen Executor layer  204  processes the SQL query. If the code has been generated for a particular query, layer  204  executes such code. When code has not been generated for a particular query, layer  204  optimizes the SQL query, generates code according to the optimized access plan and processes the requested data. The generated code is stored in “local cache” and executed. 
     As shown, the RAM Codegen Executor Component layer  204  operatively couples to the Record File Manager component layer  206 . During execution, the generated code calls various RFM functions to read or write relational data and/or index information from RFM files. Hence, this layer does not deal with the physical storage of data in a file. 
     The RFM component layer  206  performs the relational processing for RDMS  200 . It receives the read and write requests from layer  204  and then translates them into IO file read and write requests respectively. It processes the file pages (CIs) read by layer  208  to which it operatively couples. Thus, this layer hides the physical storage of data and all other file format details from layer  204 . 
     The IO Random Controller component layer  208  receives the requests from layer  206  and performs the relational file processing of translating the code-generated requests into I/O read/write requests. It processes the database files in page increments (CI). It is oblivious to physical storage of the data on the page. These details are handled by the other components illustrated in FIG. 2 a . That is, layer  208  operatively couples to Buffer pools  212  via an Integrity Control Buffer Manager component  210 . As known in the art, buffer pools contain buffers having a specific page size (control interval (CI). These buffers are used by the RDMS files. This arrangement is discussed in the above-mentioned INTEREL reference manual. 
     RAM Codegen Executor Layer  204 —FIG. 2 b    
     This figure illustrates in greater detail, the components that make up layer  204  according to the teachings of the present invention. As indicated, the layer  204  includes a common RAM Optimizer component  204 - 2 , a code generation component  204 - 4 , a code generation storage component corresponding to SQL cache memory component  204 - 6  and subroutine library component  204 - 8 . These components are operatively coupled as shown. 
     As discussed above, optimizer component  204 - 2  processes the SQL query by determining the appropriate access plan strategy. As a result of such processing, component  204 - 2  generates a set of EDOT structures that define the operation (SQL query) to execute (e.g. SELECT, UPDATE, INSERT or DELETE), the data to process (e.g. columns), the access method to use (e.g. scan or index or hash) and the restrictions that apply versus the access method to limit the amount of data (or rows) to process. Also, the structures define where the data obtained for the query is to be returned to be used by a user. The path to ODI is used only during EDOT generation for verifying the query for correctness (e.g. does a specified column belong to a specified table). As indicated, the EDOT structures are applied as inputs to Code generation component  204 - 4 . This component generates the required code that is stored in cache storage  204 - 6 . 
     The cache storage  204 - 6  operatively couples to a subroutine library  204 - 8 . Library  204 - 8  contains subroutines for communicating with RFM component layer  206 . In accordance with the teachings of the present invention, library  204 - 8  also includes a number of performance enhancing subroutines that allow the bypassing of the RFM layer  206  as discussed herein. In this case, library  204 - 8  operatively couples to component layer  208  as indicated in FIG. 2 b.    
     Component  204 - 4  Code Generation Routines—FIG. 2 c    
     FIG. 2 c  illustrates in greater detail, the structure of a portion of component  204 - 4  according to the present invention. More specifically, FIG. 2 c  depicts the routines that generate the code to perform index or data file retrievals. These routines include a number of standard routines that correspond to blocks  204 - 40  through  204 - 49  and  204 - 53 . Additionally, the routines gen_retr_method function  204 - 50  and the routine gen_index_leaf have been extended according to the present invention. The routine gen_retr_method  204 - 50  generates the code to call a performance enhancing subroutine for increasing and enhancing data row retrieval performance. The routine  204 - 50  incorporates into the code, any information that is necessary for carrying out the functions of the bypassed RFM manager component  206 . 
     The routine gen_index_leaf  204 - 54  determines whether to produce code that calls a different performance enhancing subroutine for executing indexing operations. By extending the code generation component  204 - 4  to include subroutines for adding specific code that calls specific performance enhancing subroutines, this results in a substantial improvement in overall performance in the RDMS. 
     Description of Operation 
     With reference to FIGS. 1 through 2 c , the operation of the preferred embodiment of the present invention will now be described with reference to FIGS. 2 d ,  3   a  and  3   b.    
     FIG. 2 d    
     FIG. 2 d  illustrates conceptually, the layered organization of the present invention and more particularly, the organization of the code generation component layer  204  when having processed a particular SQL query. For example, FIG. 2 d  illustrates the case where layer  204  includes two performance enhancing subroutines corresponding to subroutines A+ and B prestored in subroutine library  204 - 8  for implementing record management functions # 1  and # 2  respectively. Also, as indicated in FIG. 2 d , library  204 - 8  further includes the normal RFM interface subroutines for carrying out functions # 1  and # 2  in the conventional manner by use of the lower RFM component layer  206  routines/procedures stored in RFM library  206 - 8 . 
     During the processing of an SQL query, the code generation component layer  204  operates to generate code that includes specific call functions to either performance enhancing subroutines A+ and B or to the standard RFM interface subroutines. The pairs of dotted lines labeled “bypass” and “no bypass” between the output code block and subroutine library  204 - 8  indicates this in FIG. 2 d . When a specific operation is determined to be required that can utilize one of the performance enhancing subroutines stored in library  204 - 8 , code generation component layer  204  includes in the output code, a call that references that performance enhancing subroutine thereby bypassing RFM lower layer  206 . 
     For example, in the preferred embodiment of the present invention, management function # 1  is utilized in the handling of unpartitioned and partitioned data rows while management function # 2  is utilized in the handling of index searching. FIGS. 3 a  and  3   b  illustrate the use of the performance enhancing subroutines that implement these functions. It was determined that these record management functions could be more expeditiously handled by operating outside the RFM layer. However, it will be appreciated that other record handling functions could also have been selected but such functions were determined to have less effect on performance. 
     Functions # 1  and # 2  will now be considered in greater detail. As indicated in FIG. 3 a , function # 1  involves both partitioned and unpartitioned data rows. A partitioned row is a row that was originally inserted into a database page (i.e., CI). Initially, rows are always inserted into the database in an unpartitioned manner. That is, the RFM component layer  206  enforces a rule that initially the row must completely fit within the target page (i.e., CI). If the row does not fit into a given CI, a page is found that contains enough unused space to accommodate the row. Rows become partitioned when updates occur. For example, assume that a row was inserted into the database page that took up 200 bytes of storage and left 20 bytes of space remaining in the page. If an update occurs that changes a column that had used no bytes of space (i.e., it was NULL) to using 50 bytes of space, then the row no longer fits in the page or Cl (i.e., the page is 30 bytes too small). When this occurs, the RFM component layer  206  will transfer a subset of the row into another page. This results in the data row being split over two pages or CIs. This process can continue so that theoretically, different parts of the row exist in many pages. Since partitioned rows represent the exception and not the normal case, function # 1  is used to handle unpartitioned row retrievals resulting in enhanced performance. 
     FIG. 3 a  illustrates the manner in which component layer  204  utilizes function # 1  in executing unpartitioned row retrievals. As indicated in FIG. 2 d , the output code generated by layer  204  includes calls to subroutine A+ for two data items of the SQL query being processed. That is, it is assumed by way of example that the SQL query is generated for obtaining employee information for different employees requiring access to an employee table and a manager table. Accordingly, layer  204  generates code via routine gen_retr_method  204 - 50  of FIG. 2 c  includes calls to subroutine A+ in the output code as indicated in FIG. 2 d.    
     During execution, it is seen from FIG. 3 a  that subroutine A+ when called determines if the row being accessed is partitioned. This determination can be made by examining the record header information. If the row is not partitioned, then subroutine A+ performs the required record management operations by calling IO component layer  208  as indicated in FIG. 2 d.    
     In greater detail, during execution, the generated code passes to subroutine A+, a database key (DBK) for the row to retrieve. The DBK that was acquired in a prior index lookup, includes the CI number and a line array offset (i.e., the row identifier within the CI). Subroutine A+ will call IO component layer  208  and acquire the CI. Subroutine A+ will identify the row within the CI. Subroutine A+ will then examine the row&#39;s record header (i.e., includes a predetermined bit pattern coded to indicate if the record is partitioned) to determine if the row is partitioned. If it is not partitioned, subroutine A+ returns control to its calling routine. If the row is partitioned, then subroutine A+ calls the RFM component layer  206  to process the partitioned row. By bypassing the call to the RFM component layer  206  for unpartitioned rows (i.e., the normal case), a significant advantage in performance is obtained. 
     When subroutine A+ determines that the row from which data is being accessed is partitioned, then subroutine A+ calls the appropriate procedure within RFM library  206 - 8  for accessing the partitioned row data. The run time decision relative to the row being partitioned is made on the first call to IO component layer  208  (i.e. on the first call to the IO random controller of FIG. 3 a ). The RFM library procedure operates to retrieve all the pieces, concatenates them together and then returns to subroutine A+. Subroutine A+ passes a pointer to the concatenated row back to the generated output code for retrieval of the desired columns. For further details about this operation, reference may be made to the first cited related copending patent application. 
     As indicated in FIG. 2 d , the generated output code also contains reference calls to subroutine B for those parts of the SQL query that involves index searching. As known in the art, index searches are very common events in relational database processing. They can occur when processing SELECT, UPDATE or DELETE SQL statements. Because index searches occur so frequently, this was determined to be another area where a performance enhancing subroutine could be utilized. 
     FIG. 3 b  illustrates the manner in which index searches and more specifically, index scan operations are executed according to the teachings of the present invention. Generally, as indicated in FIG. 3 b , standard index processing performed by the code generation layer  204  in conjunction with RFM layer  206  is performed in two steps. As a first step, as indicated in FIG. 3 b , the layer  204  calls the RFM layer  206  to search for a specific index value. This is called the Find Index search. Once the RFM layer  206  finds the index entry, it establishes a currency to it. This currency is control information that indicates which fine level index entry corresponded with the search request. This currency information is stored in the RFM schema structure. The RFM layer  206  establishes a currency ID for the currency from the currency ID information that the code generation layer  204  sets in a RFM control structure RFM_XPT prior to the call. 
     The RFM_XPT is a very complex structure that is used by callers of RFM component layer  206  to pass in instructions to the operation to be performed by the RFM layer  206  and to return information pertaining to the result of the operation. Typically, the information passed would include the identification of the file to process, a directive on what type of search to do (i.e., &gt;=), pointers to where the starting index value is stored, a pointer to where the result should be returned, the currency ID, etc. An example of the type of information returned would be status. See the glossary for additional information regarding the RFM_XPT structure. 
     In a second step, the code generation layer  204  calls the RFM layer  206  to return the next index entry as indicated in FIG. 3 b . Because the index fine level entries are in sorted order, this means that the code generation layer  204  can pass in the currency ID from the prior search. Thus, the RFM layer  206  can go to the currency information stored in the RFM schema structure and use it to find the next index entry without having to repeat the index search. After the RFM layer  206  has identified the next index entry, it updates the currency information in the RFM schema structure. This second step is repeated until the query processing is completed. 
     As indicated in FIG. 3 b , the second step is altered to improve index access Search Next performance. The output code calls the subroutine B to determine if more than two next index accesses have been processed. On the second Search Next request, the subroutine B examines the fine level index CI from which the prior index entry was retrieved. If the currency has not changed and the index CI has not changed and if the currency points to a fine level index entry that does not have duplicates, then the subroutine B copies the fine level index entry from the fine level index CI located in the buffer pool to the requestor&#39;s key buffer along with the database key (DBKEY). The subroutine B updates the currency information to point to the next fine level index entry. Finally, the subroutine B returns to the caller. When any one of the conditions is not satisfied, subroutine B calls the RFM Search Next function procedure contained in RFM library  206 - 8  via the RFM interface function # 2  subroutine of FIG. 2 d  to process the request. Thus, each time the subroutine B is executed, this reduces the number of calls to the RFM layer  206  and IO layer  208  thereby substantially increasing performance. For further information regarding the operation of subroutine B, reference may be made to the second cited related patent application. The appendices illustrate examples of the output code organization used for implementing the preferred embodiment of the present invention. 
     While the present invention was described relative to processing SQL statements, it will be obvious to those skilled in the art that the present invention may be used in conjunction with other languages, code, etc. Further, the invention may be utilized in any system that generates executable code at execution time wherein the system generates support code and calls to task specific functions that permit the most commonly performed tasks to be executed as efficiently as possible. Thus, the code generated at executed at execution time in conjunction with its subroutines will circumvent a layer of the established architecture in order to improve performance. 
     It will also be appreciated that while the data manager software of the present invention was disclosed as being utilized with an enterprise or legacy system, it is not in any way limited to such use. It may be utilized with any RDMS installed on any type of computing system.