Browse Prior Art Database

SQL Performance enhancement by local client cache

IP.com Disclosure Number: IPCOM000245311D
Publication Date: 2016-Feb-29

Publishing Venue

The IP.com Prior Art Database

Abstract

This invention focuses on the performance enhancement for SQLs which are complex, time consuming and would be executed multiples time. In this invention, a new method called: ‘SQL local cache’ is introduced to improve performance of SQL. For an SQL that is time consuming and executed frequently or multiples times, its statement text and corresponding result set will be saved within local cache of client. Before next SQL statement (come from application) sent to database server, it will be tried to match with existed SQL text saved in cache. If an exactly matched SQL could be found in cache, corresponding result set will be returned instead of fetching data from database system server.

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

Page 01 of 10

SQL Performance enhancement by local client cache

This invention focuses on the performance enhancement for SQLs which are complex, time consuming and would be executed multiples time.

In this invention, a new method called: 'SQL local cache' is introduced to improve performance of SQL. For an SQL that is time consuming and executed frequently or multiples times, its statement text and corresponding result set will be saved within local cache of client. Before next SQL statement (come from application) sent to database server, it will be tried to match with existed SQL text saved in cache. If an exactly matched SQL could be found in cache, corresponding result set will be returned instead of fetching data from database system server.

Advantage of 'SQL local cache' comparing with conventional 'database system cache'

1. Performance of SQLs could be benefit with more efficiency, since there is no need to access database server to fetch data. Namely, the cost on communication between local client and server, and the cost on accessing tables in database for fetching data could be saved.


2. Leverage the burden of database system server so that performance of the server could be promoted as well.

3. More information could be save in local cache, without worrying about useful information is kicked off from the cache due to its capacity. The following is the detail process.

1



Page 02 of 10

2



Page 03 of 10

Illustration:
1 Application in local client will send SQLs to database server for fetch data or modify data within database.

2 First of all, SQLs sent out by application will be processed by 'SQL local cache' manager (located in local client), prior to sending to database server.

2.1 In order to locate the matched SQL in cache with high efficiency, order the 'search sequence' by sort SQL_Rank in 'SQL local cache' manager is necessary.

SQL_Rank = (SQL_Frequency * SQL_Elapse_Time) / SQL_Result_Size

SQL_Frequency = SQL_Execution_Times / SQL_Interval

'Search sequence' will be built by sort SQL_Rank from high value to low value as following:

3



Page 04 of 10

This sort operation is optional or could be performed periodically.

3 'SQL local cache' manager will try to use current SQL text to match with existed SQL text that stored within cache.

3.1 If a SQL (let's say SQL #n) saved in local cache could be exactly matched with current SQL, there is no need to send current SQL to database server for processing. Instead, result set corresponding to the SQL #n will be returned to application directly.

4



Page 05 of 10


4 If current SQL cannot be matched with any SQL saved in local cache, it will be sent to database server for processing business as usual.

5 If the SQL is a SELECT statement for fetch data only,
5.1 database will return the result set and a table list which are referenced by current SQL to client application;

Example:


Suppose the SQL statement passed to database looks like:

SELECT A.C3, B.C2 || 'A.C2',

FROM T2 AS A, T3AS B

WHERE A.C1 = B...