Browse Prior Art Database

Technique for Improving the Performance of Parameterized Database Queries on Infrequently Updated Tables Disclosure Number: IPCOM000127406D
Original Publication Date: 2005-Aug-29
Included in the Prior Art Database: 2005-Aug-29
Document File: 3 page(s) / 26K

Publishing Venue



Technique for Improving the Performance of Parameterized Database Queries that Operate on Infrequently Updated Tables

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 48% of the total text.

Page 1 of 3

Technique for Improving the Performance of Parameterized Database Queries on Infrequently Updated Tables

Disclosed is a technique for improving the performance of parameterized database queries that operate on infrequently updated tables.

Certain database queries take a long time to execute. This is caused by the complex nature of the queries; such queries frequently express complex relationships between multiple tables, some of which have large numbers of rows. Because of the complexity of the queries, they defy optimization, either via automatic optimization by a database query optimizer, or manual optimization by a database application programmer.

In summary, the invention solves the problem by saving the result rows of a long-running query in a new table, which we refer to as a cache table. As long as the tables underlying the query remain unchanged, successive executions of the query with the same parameters can retrieve the result rows from the cache table, instead of having to reexecute the long-running query.

The advantages of using the invention are as follows: 1) Queries processed using the invention execute much faster than queries processed without the invention; and, 2) Queries processed using the invention consume much less system resources than queries processed without the invention.

Original query: This is the query whose performance is to be improved by the cache table.

Underlying table: This is a table referred to in the original query.

Parameter: This is a placeholder in the original query. A value is substituted for this placeholder before the query is executed. Furthermore, the original query could be executed multiple times, each time substituting a different parameter value for a parameter.

Parameter value: A value that is substituted for a parameter when the query is executed.

Parameterized query: A query that contains parameters.

Taking an original, parameterized query as an input, the invention creates the following data objects:

1) A cache table. The cache table has the following columns:
a) A column for each of the result columns in the original query,
b) A column for each of the parameters in the original query,
c) A column entitled "result_is_null", and,
d) A timestamp column.
2) An index on the cache table, which indexes the columns corresponding to the query parameters (from b above).

    3) For each table referenced by the original query, a timestamp indicating when the table was last updated.

Page 2 of 3

Whenever the original query is to be executed, a query on the cache table is executed instead. This query looks up rows in the cache table whose parameter column values match the parameter values that were to be used in the original query. The performance of this query is enhanced by the index on the cache table. There are three possible results to this query:

1) No rows are returned.
2) One row is returned whose "result_is_null" column is set to "YES".
3) One or more rows are returned whose "result_is_null...