Browse Prior Art Database

Broadening/threading SQL statements to reduce the amount of ODP's

IP.com Disclosure Number: IPCOM000031583D
Original Publication Date: 2004-Sep-29
Included in the Prior Art Database: 2004-Sep-29
Document File: 2 page(s) / 48K

Publishing Venue

IBM

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

Page 1 of 2

Broadening/threading SQL statements to reduce the amount of ODP's

When running SQL in any environment, an ODP is needed for a given query to execute. ODP's take up temporary space, and on the AS/400*, the creation of ODP's at full open time is a considerable performance problem. Besides the creation of the ODP, the other bottleneck at full open time is the optimizer running deciding on an access path for the query. To solve these problems, the computer industry has chosen, or seems to have chosen, the path whereby we need to pay the penalty the first time when we execute but after that subsequent executions of the same query will go fast. The standard discussion we always have with our customers is to user prepared statements such that the ODP's need not get created every time and reusability occurs. The AS/400, as do other platforms, have system-wide statement cache and all of our newer programming paradigms have connection pooling classes, statement caching, etc... Unfortunately, what is overlooked is the "first" execution. There are many customers today that simply need relief from this first-time execution time. This invention is geared at making the first time less expensive. We accomplish this in two ways.

     First, we use historical information from a job stream to determine what queries will be run and in what order. Besides simply using historical information, we can use information that comes from PRTSQLINF (PRTSQLINF shows you all the static SQL statements embedded in a program). What we will then do is 1) in advance of needing an ODP, a separate thread will be off creating ODP's in anticipation of its future need. This type of work would, of course, be done assuming CPU and disk are not bottlenecks and that waiting for ODP's to create causes a delay in the job stream. One can in someways akin this to having multiple instruction pipes to execute. 2) perhaps more novel than the first idea is to analyze the SQL statements that will be run to see which ones can...