Optimization of database network traffic based upon data-use analysis

• US 8,639,710 B2
• Filed: 07/26/2005
• Issued: 01/28/2014
• Est. Priority Date: 04/18/2002
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Arnold, Jeremy Alan, Barsness, Eric Lawrence, Dettinger, Richard Dean, Santosuosso, John Matthew
• Primary Examiner(s): LIN, SHEW FEN
• Application Number:
  US11/189,395
• Publication Number:
  US 20050256884A1
• Time in Patent Office: 3,108 Days
• Field of Search: 707/759, 707/769, 707/713
• US Class Current: 707/759
• 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) using at least one hardware processor, 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;

wherein the database query comprises an SQL statement, wherein the client-server database management system comprises a relational database, wherein the record request comprises a position row request for a table in the relational database, 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.

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

3. The method of claim 2, 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.

4. The method of claim 1, 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 the data set to the client portion of the JDBC driver.