Optimization of database network traffic based upon data-use analysis

• US 7,617,181 B2
• Filed: 08/17/2006
• Issued: 11/10/2009
• 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
• Primary Examiner(s): Corrielus; Jean M
• Application Number:
  US11/465,246
• Publication Number:
  US 20060294080A1
• Time in Patent Office: 1,181 Days
• Field of Search: 707/2, 707/3, 707/4, 707/5, 707/10
• 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 processing a database query from a client in a client-server database management system, the method comprising:

receiving a database query from a client;

responding to the database query by returning to the client only a subset of the data requested by the client in the database query based upon a usage statistic associated with the database query such that less than all of the data requested by the client in the database query is returned to the client in response to the database query, wherein the usage statistic is based upon automatically tracked usage data that is indicative of how data requested by the client is used by the client; and

during a current or prior execution of the database query, generating the usage statistic by automatically tracking the usage of data returned in response to the database query during the current or prior execution of the database query to identify data from among the data returned in response to the database query that is unlikely to use, wherein the subset of data excludes data identified during automatic tracking as being unlikely to use.

2. The method of claim 1, wherein the usage statistic is indicative of how the data requested by the client is expected to use by the client, the method further comprising determining the subset of data to return the client in response to the database query based upon the usage statistic.

3. The method of claim 2, wherein the database query is generated by a client application resident on the client, and wherein determining the subset of data to return to the client is performed by a database driver.

4. The method of claim 3, wherein at least a portion of the database driver is resident on the client, and wherein determining the subset of data to return to the client is performed by the portion of the database driver resident on the client.

5. The method of claim 4, wherein at least a portion of the database driver is resident on a server, wherein the portion of the database driver resident on the server is responsive to the portion of the database driver resident on the client to retrieve the subset of data from a database and communicate the subset of data to the client, wherein the portion of the database driver resident on the client is configured to issue an intra-driver request specifying the subset of data, and wherein the portion of the database driver resident on the server is configured to return the subset of data responsive to the intra-driver request.

6. The method of claim 2, wherein the database query requests a plurality of columns from at least one row of data from a table stored in the database management system, wherein determining the subset of data to return to the client includes determining a subset of the plurality of columns to return for the at least one row of data, and wherein automatically tracking the usage of data returned in response to the database query during the current or prior execution of the database query includes identifying at least one column from among the plurality of columns that is unlikely to use, wherein the subset of columns excludes the at least one column that is unlikely to use.

7. The method of claim 6, wherein the usage statistic is associated with which of the plurality of columns are likely to use by the client, and wherein the subset of the plurality of columns includes only the columns likely to use by the client.

8. The method of claim 2, wherein the database query requests a plurality of rows from a table stored in the database management system, wherein determining the subset of data to return to the client includes determining a subset of the plurality of rows to return, and wherein automatically tracking the usage of data returned in response to the database query during the current or prior execution of the database query includes identifying at least one row from among the plurality of rows that is unlikely to use, wherein the subset of rows excludes the at least one row that is unlikely to use.

9. The method of claim 8, wherein the usage statistic is associated with a number of rows actually used during a prior execution of the database query.

10. The method of claim 1, further comprising compressing the subset of the data before returning the subset of data to the client.

11. The method of claim 1, further comprising generating the usage statistic during a first execution of the database query, and using the usage statistic during a second execution of the query to determine the subset of data to return to the client in response to the database query.

12. The method of claim 1, wherein the usage statistic is generated during execution of the database query by a different client.

13. The method of claim 1, wherein the usage statistic is generated during processing of data returned during execution of the database query.

14. The method of claim 1, wherein the usage statistic is generated during processing of prefetched data during execution of the database query.

15. A method of processing a database query from a client in a client-server database management system, the method comprising:

receiving a database query from a client, wherein the database query requests a plurality of rows and a plurality of columns from a table stored in the database management system;

generating a usage statistic associated with the database query and indicative of how the data requested by the client is used by the client during a current or prior execution of the database query, wherein the usage statistic is based on automatically tracked usage data and identifies data from among the data requested by the client that is unlikely to use;

determining a subset of data to return to the client in response to the database query based upon the usage statistic, including determining at least one of a subset of the plurality of rows and a subset of the plurality of columns anticipated to be used by the client, wherein the subset of data excludes data identified during generation of the usage statistic as being unlikely to use; and

responding to the database query by returning to the client only the subset of the data requested by the client in the database query such that less than all of the data requested by the client in the database query is returned to the client in response to the database query.

16. The method of claim 15, wherein determining the subset of data to return to the client in response to the database query based upon the usage statistic includes determining a subset of the plurality of rows such that less than all of the rows requested by the client in the database query are returned to the client in response to the database query.

17. The method of claim 15, wherein determining the subset of data to return to the client in response to the database query based upon the usage statistic includes determining a subset of the plurality of columns such that less than all of the columns requested by the client in the database query are returned to the client in response to the database query.

18. The method of claim 15, wherein determining the subset of data to return to the client in response to the database query based upon the usage statistic includes determining a subset of the plurality of rows and a subset of the plurality of columns such that less than all of the rows and columns requested by the client in the database query are returned to the client in response to the database query.

19. An apparatus, comprising:

at least one processor; and

program code configured to be executed by the at least one processor to respond to a database query from a client in a client-server database management system by returning to the client only a subset of the data requested by the client in the database query based upon a usage statistic associated with the database query such that less than all of the data requested by the client in the database query is returned to the client in response to the database query, wherein the usage statistic is based upon automatically tracked usage data that is indicative of how data requested by the client is used by the client, and wherein the program code is further configured to, during a current or prior execution of the database query, generate the usage statistic by automatically tracking the usage of data returned in response to the database query during the current or prior execution of the database query to identify data from among the data returned in response to the database query that is unlikely to be used, wherein the subset of data excludes data identified during automatic tracking as being unlikely to use.

20. The apparatus of claim 19, wherein the program code comprises a client-side portion of a database driver, the apparatus further comprising:

a client computer within which is resident the program code; and

a server computer coupled to the client computer, the server computer including a server-side portion of the database driver, the server-side portion of the database driver configured to transmit the subset of the data to the client-side portion of the database driver.

21. The apparatus of claim 19, wherein the database query requests a plurality of columns from at least one row of data from a table stored in the database management system, and wherein the program code is configured to determine the subset of data to return to the client by determining a subset of the plurality of columns to return for the at least one row of data.

22. The apparatus of claim 19, wherein the database query requests a plurality of rows from a table stored in the database management system, and wherein the program code is configured to determine the subset of data to return to the client by determining a subset of the plurality of rows to return.

23. The apparatus of claim 19, wherein the usage statistic is generated during a prior execution of the database query.

24. The apparatus of claim 19, wherein the usage statistic is generated during processing of prefetched data during execution of the database query.

25. A program product, comprising:

a recordable computer readable medium; and

program code stored on the recordable computer readable medium and configured upon execution to respond to a database query from a client in a client- server database management system by returning to the client only a subset of the data requested by the client in the database query based upon a usage statistic associated with the database query such that less than all of the data requested by the client in the database query is returned to the client in response to the database query, wherein the usage statistic is based upon automatically tracked usage data that is indicative of how data requested by the client is used by the client and wherein the program code is further configured to, during a current or prior execution of the database query, generate the usage statistic by automatically tracking the usage of data returned in response to the database query during the current or prior execution of the database query to identify data from among the data returned in response to the database query that is unlikely to use, wherein the subset of data excludes data identified during automatic tracking as being unlikely to use.