Optimization of database network traffic based upon data-use analysis

• US 20050256884A1
• Filed: 07/26/2005
• Published: 11/17/2005
• Est. Priority Date: 04/18/2002
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Barsness, Eric Lawrence, Dettinger, Richard Dean, Santosuosso, John Matthew, Arnold, Jeremy Alan
• Granted Patent: US 8,639,710 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24552 Database cache management
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

An apparatus, program product and method utilize one or more usage statistics, which are reflective of what data is actually and/or likely to be used by a client when processing a database query, to reduce or eliminate the transfer of unnecessary data between a client and server in a client-server database management system. Information such as the columns that are or have been used and/or the number of rows that have been accessed, may be utilized to identify certain data that is likely to not be used during processing of the database query. Based upon the data that has been identified, a subset of the data that would otherwise be returned as a result of the database query may be identified and returned to a client, often substantially reducing the amount of data required to be retrieved by a client in connection with a database query.

Claims

1. A method of prefetching data from a server in a client-server database management system, the method comprising:

(a) tracking usage of data requested by a database query by a client application resident on a client to generate at least one usage statistic;

(b) receiving a record request from a client application resident on a client, wherein the record request requests at least one record that is not cached in a prefetch cache resident in the client;

(c) in response to the record request, using the usage statistic to formulate a data set to be retrieved from the server and into the prefetch cache; and

(d) retrieving the data set from the server and into the prefetch cache.

2. The method of claim 1, wherein the database query comprises an SQL statement, wherein the client-server database management system comprises a relational database, and wherein the record request comprises a position row request for a table in the relational database.

3. The method of claim 2, wherein tracking usage of data comprises determining which columns in the table are likely to be used by the client application.

4. The method of claim 3, further comprising prefetching a first block of rows into the prefetch cache in response to the database query, wherein the first block of rows includes all columns in such rows, wherein determining which columns in the table are likely to be used by the client application includes determining which columns in the table are actually used during processing of the first block of rows by the client application, and wherein retrieving the data set includes retrieving a second block of rows into the prefetch cache, the second block of rows including only those columns that are actually used by the client application during processing of the first block of rows.

5. The method of claim 3, wherein tracking usage of data comprises determining a number of rows used by a client application during a prior execution of the database query, and wherein retrieving the data set includes retrieving less than a full block of rows into the prefetch cache.

6. The method of claim 2, wherein tracking usage of data is performed in connection with a first execution of the database query, and wherein using the usage statistic to formulate a data set to be retrieved from the server and into the prefetch cache, and retrieving the data set from the server and into the prefetch cache, are performed in connection with a second execution of the database query.

7. The method of claim 6, wherein the first and second executions of the database query are initiated by the same client application.

8. The method of claim 6, wherein the first and second executions of the database query are initiated by different client applications.

9. The method of claim 6, wherein tracking usage of data includes associating the usage statistic with an SQL statement resident in a statement pool, and wherein using the usage statistic includes retrieving the usage statistic from a data structure associated with the SQL statement from the statement pool.

10. The method of claim 2, wherein tracking usage of data, receiving the record request from the client application, and using the usage statistic to formulate the data set are performed by a client portion of a JDBC driver resident on the client, and wherein retrieving the data set includes:

(a) with the client portion of the JDBC driver, transmitting a request for the data set to a server portion of the JDBC driver; and

(b) with the server portion of the JDBC driver, responding to the request for the data set by transmitting data from data set to the client portion of the JDBC driver.

11. An apparatus, comprising:

(a) a memory; and

(b) program code resident in the memory and configured to prefetch data from a server in a client-server database management system by tracking usage of data requested by a database query by a client application resident on a client to generate at least one usage statistic;

receiving a record request from a client application resident on a client, wherein the record request requests at least one record that is not cached in a prefetch cache resident in the client;

in response to the record request, using the usage statistic to formulate a data set to be retrieved from the server and into the prefetch cache; and

retrieving the data set from the server and into the prefetch cache.

12. The apparatus of claim 11, wherein the database query comprises an SQL statement, wherein the client-server database management system comprises a relational database, and wherein the record request comprises a position row request for a table in the relational database.

13. The apparatus of claim 12, wherein the program code is configured to track usage of data by determining which columns in the table are likely to be used by the client application.

14. The apparatus of claim 13, wherein the program code is further configured to prefetch a first block of rows into the prefetch cache in response to the database query, wherein the first block of rows includes all columns in such rows, wherein the program code is configured to determine which columns in the table are likely to be used by the client application by determining which columns in the table are actually used during processing of the first block of rows by the client application, and wherein the program code is configured to retrieve the data set by retrieving a second block of rows into the prefetch cache, the second block of rows including only those columns that are actually used by the client application during processing of the first block of rows.

15. The apparatus of claim 13, wherein the program code is configured to track usage of data by determining a number of rows used by a client application during a prior execution of the database query, and wherein the program code is configured to retrieve the data set by retrieving less than a full block of rows into the prefetch cache.

16. The apparatus of claim 12, wherein the program code is configured to track usage of data in connection with a first execution of the database query, and wherein the program code is configured to use the usage statistic to formulate a data set to be retrieved from the server and into the prefetch cache, and retrieve the data set from the server and into the prefetch cache, in connection with a second execution of the database query.

17. The apparatus of claim 16, wherein the first and second executions of the database query are initiated by different client applications.

18. The apparatus of claim 16, wherein the program code is configured to track usage of data by associating the usage statistic with an SQL statement resident in a statement pool, and wherein the program code is configured to use the usage statistic by retrieving the usage statistic from a data structure associated with the SQL statement from the statement pool.

19. The apparatus of claim 12, wherein the program code is configured to track usage of data, receive the record request from the client application, and use the usage statistic to formulate the data set using a client portion of a JDBC driver resident on the client, and wherein the program code is configured to retrieve the data set by:

(a) with the client portion of the JDBC driver, transmitting a request for the data set to a server portion of the JDBC driver; and

(b) with the server portion of the JDBC driver, responding to the request for the data set by transmitting data from data set to the client portion of the JDBC driver.

20. A program product, comprising:

(a) program code resident in the memory and configured to prefetch data from a server in a client-server database management system by tracking usage of data requested by a database query by a client application resident on a client to generate at least one usage statistic;

receiving a record request from a client application resident on a client, wherein the record request requests at least one record that is not cached in a prefetch cache resident in the client;

in response to the record request, using the usage statistic to formulate a data set to be retrieved from the server and into the prefetch cache; and

retrieving the data set from the server and into the prefetch cache; and

(b) a computer readable signal bearing medium bearing the program code.