Browse Prior Art Database

System and method of common intermediate result set reusing in large scale transaction processing Disclosure Number: IPCOM000240118D
Publication Date: 2015-Jan-05
Document File: 8 page(s) / 120K

Publishing Venue

The Prior Art Database


In this invention, we provide a method identifies common plan portions among existing plans in package cache and builds hyperlinks between them. In a short time window specified by the user, database collects queries which are inter-connected by hyperlinks and makes them a group. At the end of the time window, fire all plans in this group. In runtime stage, common plan portions identified by hyperlinks will be computed only once and the output buffer is linked to all consumers(upper plans) belonging to separate plans, thus result sets from common plan portions are reused to the maximal extent. After current group plans' execution the loop continues to next time window.

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

Page 01 of 8

System and method of common intermediate result set reusing in large scale transaction processing


Database performance has always been a key factor of the overall system performance in large scale transaction processing . Take e-commence website for example, it often suffers from exhausting of database computing resource in peak time in processing. Look at one of the typical scenario:

One day, there is BIG SALE on a e-commerce website. You search on the website passionately and plan to buy a lot of staff. Unfortunately, the web page responds like a snail. After 1 minute, the page has not give the search result yet. You are burning with impatience but you say to yourself


you close the page.

System performance problem is a old problem for such super simultaneously multi-user system.

These scenarios have something in common when problem occurred: A large number of client users always have multiple queries against the system simultaneously. The bottleneck of the system performance is the database performance.

Loosely, data query from database is a I/O operation. Therefore the overall database performance will be effectively improved if we effectively improve the database disk I/O or deduce the database disk I/O.

For DBA, there are already a lot of method to improve database performance.

a) Utilize a proper indexing strategy

b) Add memory. Make sure you have enough memory on your server in order to store as many pages as possible

c) Using distinct disks for data files, log files, and database backup

d) Get faster disks.

e) Optimize tempdb. SQL statement with order by and group by will consume lots of tempdb and it will deduce the performance.
f) Using big data tools such as Hbase.

These methods can improve database performance to some extent, but they require either more hardware input , or more effort of statements re-design. They will cost more money and effort from database customer.

In this disclosure, we will provide a new method.

There is one assumption. We propose a notion of "Short Time Window", it means a short time of period in which the queries are presumed to be happening at the same time. This is an assumption and not precisely exact, but is acceptable. Because the time window is relatively very short compared to the response time of the queries. So we assume all the queries located in the time window are happening at the same time.

information but tell you

terrible website!

" Oh, there are so many people on the website

" your response is timeout

" . During 1 hour, you struggle with the snail-pace website and get nothing.

" and choose to wait longer. Finally, the page does not provide any valuable

Such a



Page 02 of 8

In database processing, there would always be number of similar queries which share same portions of the execution plan, then to share the same intermediate result sets. Currently, for similar queries, database would repeatedly compute the same intermediate result for each query. This is a dupl...