IP Library › Granted Patent US 8,290,936
Granted Patent B2
US 8,290,936 · App. 12/857,873 · Granted Oct 16, 2012

Executing a query plan with display of intermediate results

Assignee: International Business Machines Corporation
View Patent ↗
Loading inventors, assignments & file history…
Monitor This Case
Get email alerts when status or documents change.
Order Certified Copies
Most orders are placed with the USPTO same day — all within 24 business hours.
Order via The Patent Place →
Pre-filled with this patent's details
Quick Facts
Patent No.
US 8,290,936
App. No.
12/857,873
Granted
Oct 16, 2012
Kind
B2
Abstract

In an embodiment, a FIRSTIO execution plan is selected that has a lowest estimated execution time for finding a number of records that satisfy the query and are simultaneously viewable. An ALLIO execution plan is selected that has a lowest estimated execution time for finding all records that satisfy the query. The FIRSTIO execution plan is executed for a first time period to create a FIRSTIO result set. The ALLIO execution plan is executed for a second time period to create an ALLIO result set. The FIRSTIO result set is displayed if the FIRSTIO result set comprises more records than the ALLIO result set. The ALLIO result set is displayed if the ALLIO result set comprises more records than the FIRSTIO result set. In an embodiment, the first and second time periods expire in response to the expiration of a maximum time specified by the query.

Claims (63)

1. A computer implemented method comprising:

selecting a FIRST INPUT/OUTPUT execution plan from a plurality of execution plans that has a lowest estimated execution time for finding a number of records that satisfy a query and are simultaneously viewable;

executing the FIRST INPUT/OUTPUT execution plan for a first time period to create a FIRST INPUT/OUTPUT result set, wherein the executing the FIRST INPUT/OUTPUT execution plan comprises executing for the first time period that is before the second time period if a maximum time that the query is to execute minus a FIRST INPUT/OUTPUT estimated time of execution of the FIRST INPUT/OUTPUT execution plan is greater than a first threshold and the FIRST INPUT/OUTPUT estimated time is less than an ALL INPUT/OUTPUT estimated time of execution of the ALL INPUT/OUTPUT execution plan;

wherein the executing the FIRST INPUT/OUTPUT execution plan further comprises if the maximum time expired and all records that satisfy the query are not found, continuing the executing the FIRST INPUT/OUTPUT execution plan if a number of records not found by the executing the FIRST INPUT/OUTPUT execution plan is less than a second threshold;

wherein the executing the FIRST INPUT/OUTPUT execution plan further comprises if the maximum time expired, all records that satisfy the query are not found, and the number of records not found by the executing the FIRST INPUT/OUTPUT execution plan is greater than the second threshold, then continue the executing FIRST INPUT/OUTPUT execution plan if a user consents;

selecting an ALL INPUT/OUTPUT execution plan from the plurality of execution plans that has a lowest estimated execution time for finding all records that satisfy the query;

executing the ALL INPUT/OUTPUT execution plan for a second time period to create an ALL INPUT/OUTPUT result set, wherein the executing the ALL INPUT/OUTPUT execution plan further comprises executing the ALL INPUT/OUTPUT execution plan if the maximum time minus an elapsed time since the query was received is less than a third threshold;

wherein the executing the ALL INPUT/OUTPUT execution plan further comprises if the maximum time expired and all records that satisfy the query are not found, then continue the executing the ALL INPUT/OUTPUT execution plan if a number of records not found by the executing the ALL INPUT/OUTPUT execution plan is less than a fourth threshold;

wherein the executing the ALL INPUT/OUTPUT execution plan further comprises if the maximum time expired, all records that satisfy the query are not found, and the number of records not found by the executing the ALL INPUT/OUTPUT execution plan is greater than the fourth threshold, then continuing the executing the ALL INPUT/OUTPUT execution plan if the user consents;

displaying the FIRST INPUT/OUTPUT result set if the FIRST INPUT/OUTPUT result set comprises more records than the ALL INPUT/OUTPUT result set; and

displaying the ALL INPUT/OUTPUT result set if the ALL INPUT/OUTPUT result set comprises more records than the FIRST INPUT/OUTPUT result set.

2. The computer implemented method of claim 1 , wherein the executing the query with the FIRST INPUT/OUTPUT execution plan comprises probing a first table that is first in a first join order specified by the FIRST INPUT/OUTPUT execution plan via an index, wherein the executing the query with the ALL INPUT/OUTPUT execution plan further comprises scanning a second table that is first in a second join order specified by the ALL INPUT/OUTPUT execution plan.

3. The computer implemented method of claim 1 , wherein the executing the FIRST INPUT/OUTPUT execution plan executes for the first time period that is after the second time period if a maximum time minus a FIRST INPUT/OUTPUT estimated time of execution of the FIRST INPUT/OUTPUT execution plan is not greater than a first threshold and the maximum time minus an ALL INPUT/OUTPUT estimated time of execution of the ALL INPUT/OUTPUT execution plan is greater than a fifth threshold.

4. The computer implemented method of claim 3 , wherein the executing the FIRST INPUT/OUTPUT execution plan further comprises executing the FIRST INPUT/OUTPUT execution plan if the maximum time minus an elapsed time since the query was received is less than a sixth threshold;

wherein the executing the ALL INPUT/OUTPUT execution plan further comprises if the maximum time expired and all records that satisfy the query are not found, then continue the executing the ALL INPUT/OUTPUT execution plan if a number of records not found by the executing the ALL INPUT/OUTPUT execution plan is less than a seventh threshold; and

wherein the executing the ALL INPUT/OUTPUT execution plan further comprises if the maximum time expired, all records that satisfy the query are not found, and the number of records not found by the executing the ALL INPUT/OUTPUT execution plan is greater than the eighth the seventh threshold then continue the executing the ALL INPUT/OUTPUT execution plan if a user consents.

5. The computer implemented method of claim 4 , wherein the executing the FIRST INPUT/OUTPUT execution plan further comprises if the maximum time expired and all records that satisfy the query are not found, then continue the executing the FIRST INPUT/OUTPUT execution plan if a number of records not found by the executing the FIRST INPUT/OUTPUT execution plan is less than an eighth threshold; and

wherein the executing the FIRST INPUT/OUTPUT execution plan further comprises if the maximum time expired, all records that satisfy the query are not found, and the number of records not found by the executing the FIRST INPUT/OUTPUT execution plan is less than the ninth threshold eighth threshold then continue the executing FIRST INPUT/OUTPUT execution plan if the user consents.

6. The computer implemented method of claim 1 , wherein the executing the FIRST INPUT/OUTPUT execution plan executes for the first time period that is after the second time period if the FIRST INPUT/OUTPUT estimated time is not less than an ALL INPUT/OUTPUT estimated time of execution of the ALL INPUT/OUTPUT execution plan and a maximum time minus an ALL INPUT/OUTPUT estimated time of execution of the ALL INPUT/OUTPUT execution plan is greater than a first threshold.

7. The computer implemented method of claim 1 , wherein the executing the FIRST INPUT/OUTPUT execution plan executes for the first time period that is after the second time period if the FIRST INPUT/OUTPUT estimated time is not less than an ALL INPUT/OUTPUT estimated time of execution of the ALL INPUT/OUTPUT execution plan and a maximum time minus an ALL INPUT/OUTPUT estimated time of execution of the ALL INPUT/OUTPUT execution plan is not greater than a fifth threshold, wherein the first time period expires in response to a number of records in the FIRST INPUT/OUTPUT result set exceeding a ninth threshold, wherein the second time period expires in response to the maximum time expiring.

8. The computer implemented method of claim 1 , wherein the first time period overlaps the second time period.

9. A non transitory computer-readable storage medium encoded with instructions, wherein the instructions when executed comprise:

selecting a FIRST INPUT/OUTPUT execution plan from a plurality of execution plans that has a lowest estimated execution time for finding a number of records that satisfy a query and are simultaneously viewable;

executing the FIRST INPUT/OUTPUT execution plan for a first time period to create a FIRST INPUT/OUTPUT result set;

selecting an ALL INPUT/OUTPUT execution plan from the plurality of execution plans that has a lowest estimated execution time for finding all records that satisfy the query;

executing the ALL INPUT/OUTPUT execution plan for a second time period to create an ALL INPUT/OUTPUT result set, wherein the executing the FIRST INPUT/OUTPUT execution plan executes for the first time period that is before the second time period if a maximum time that the query is to execute minus a FIRST INPUT/OUTPUT estimated time of execution of the FIRST INPUT/OUTPUT execution plan is greater than a first threshold and the FIRST INPUT/OUTPUT estimated time is less than an ALL INPUT/OUTPUT estimated time of execution of the ALL INPUT/OUTPUT execution plan;

wherein the executing the FIRST INPUT/OUTPUT execution plan executes for the first time period that is after the second time period if the FIRST INPUT/OUTPUT estimated time is not less than the ALL INPUT/OUTPUT estimated time and the maximum time minus then ALL INPUT/OUTPUT estimated time is not greater than a fifth threshold, wherein the first time period expires in response to a number of records in the FIRST INPUT/OUTPUT result set exceeding a ninth threshold, wherein the second time period expires in response to the maximum time expiring;

wherein the executing the ALL INPUT/OUTPUT execution plan further comprises executing the ALL INPUT/OUTPUT execution plan if the maximum time minus an elapsed time since the query was received is less than a third threshold;

wherein the executing the FIRST INPUT/OUTPUT execution plan further comprises if the maximum time expired and all records that satisfy the query are not found, then continue the executing the FIRST INPUT/OUTPUT execution plan if a number of records not found by the executing the FIRST INPUT/OUTPUT execution plan is less than a second threshold;

wherein the executing the FIRST INPUT/OUTPUT execution plan further comprises f the maximum time expired, all records that satisfy the query are not found, and the number of records not found by the executing the FIRST INPUT/OUTPUT execution plan is greater than the second threshold, then continue the executing FIRST INPUT/OUTPUT execution plan if a user consents;

displaying the FIRST INPUT/OUTPUT result set if the FIRST INPUT/OUTPUT result set comprises more records than the ALL INPUT/OUTPUT result set; and

displaying the ALL INPUT/OUTPUT result set if the ALL INPUT/OUTPUT result set comprises more records than the FIRST INPUT/OUTPUT result set.

10. The non transitory computer-readable storage medium of claim 9 , wherein the executing the FIRST INPUT/OUTPUT execution plan further comprises probing a first table that is first in a first join order specified by the FIRST INPUT/OUTPUT execution plan via an index; and.

wherein the executing the ALL INPUT/OUTPUT execution plan further comprises scanning a second table that is first in a second join order specified by the ALL INPUT/OUTPUT execution plan.

11. The non transitory computer-readable storage medium of claim 9 , wherein the executing the FIRST INPUT/OUTPUT execution plan executes for the first time period that is after the second time period if the maximum time minus the FIRST INPUT/OUTPUT estimated time is not greater than the first threshold and the maximum time minus the ALL INPUT/OUTPUT estimated time is greater than a fifth threshold; and

wherein the executing the FIRST INPUT/OUTPUT execution plan executes for the first time period that is after the second time period if the FIRST INPUT/OUTPUT estimated time is not less than the ALL INPUT/OUTPUT estimated time and the maximum time minus the ALL INPUT/OUTPUT estimated time is greater than the first threshold.

12. The non transitory computer-readable storage medium of claim 11 ,

wherein the executing the FIRST INPUT/OUTPUT execution plan further comprises executing the FIRST INPUT/OUTPUT execution plan if the maximum time minus an elapsed time since the query was received is less than a sixth threshold;

wherein the executing the ALL INPUT/OUTPUT execution plan further comprises if the maximum time expired and all records that satisfy the query are not found, then continue the executing the ALL INPUT/OUTPUT execution plan if a number of records not found by the executing the ALL INPUT/OUTPUT execution plan is less than a seventh threshold;

wherein the executing the ALL INPUT/OUTPUT execution plan further comprises if the maximum time expired, all records that satisfy the query are not found, and the number of records not found by the executing the ALL INPUT/OUTPUT execution plan is greater than the seventh threshold then continue the executing the ALL INPUT/OUTPUT execution plan if a user consents;

wherein the executing the FIRST INPUT/OUTPUT execution plan further comprises if the maximum time expired and all records that satisfy the query are not found, then continue the executing the FIRST INPUT/OUTPUT execution plan if a number of records not found by the executing the FIRST INPUT/OUTPUT execution plan is less than an eighth threshold; and

wherein the executing the FIRST INPUT/OUTPUT execution plan further comprises if the maximum time expired, all records that satisfy the query are not found, and the number of records not found by the executing the FIRST INPUT/OUTPUT execution plan is less than the eighth threshold then continue the executing FIRST INPUT/OUTPUT execution plan if the user consents.

13. The non transitory computer-readable storage medium of claim 9 , wherein the executing the ALL INPUT/OUTPUT execution plan further comprises if the maximum time expired and all records that satisfy the query are not found, then continue the executing the ALL INPUT/OUTPUT execution plan if a number of records not found by the executing the ALL INPUT/OUTPUT execution plan is less than a fourth threshold; and

wherein the executing the ALL INPUT/OUTPUT execution plan further comprises if the maximum time expired, all records that satisfy the query are not found, and the number of records not found by the executing the ALL INPUT/OUTPUT execution plan is greater than the sixth threshold fourth threshold then continue the executing the ALL INPUT/OUTPUT execution plan if the user consents.

14. A computer system, comprising:

a processor; and

memory communicatively coupled to the processor, wherein the memory is encoded with instructions that when executed on the processor comprise:

selecting a FIRST INPUT/OUTPUT execution plan from a plurality of execution plans that has a lowest estimated execution time for finding a number of records that satisfy a query and are simultaneously viewable,

executing the FIRST INPUT/OUTPUT execution plan for a first time period to create a FIRST INPUT/OUTPUT result set,

selecting an ALL INPUT/OUTPUT execution plan from the plurality of execution plans that has a lowest estimated execution time for finding all records that satisfy the query,

executing the ALL INPUT/OUTPUT execution plan for a second time period to create an ALL INPUT/OUTPUT result set, wherein the executing the FIRST INPUT/OUTPUT execution plan executes for the first time period that is before the second time period if a maximum time that the query is to execute minus a FIRST INPUT/OUTPUT estimated time of execution of the FIRST INPUT/OUTPUT execution plan is greater than a first threshold and the FIRST INPUT/OUTPUT estimated time is less than an ALL INPUT/OUTPUT estimated time of execution of the ALL INPUT/OUTPUT execution plan,

wherein the executing the FIRST INPUT/OUTPUT execution plan executes for the first time period that is after the second time period if the FIRST INPUT/OUTPUT estimated time is not less than the ALL INPUT/OUTPUT estimated time and the maximum time minus then ALL INPUT/OUTPUT estimated time is not greater than a fifth threshold, wherein the first time period expires in response to a number of records in the FIRST INPUT/OUTPUT result set exceeding a ninth threshold, wherein the second time period expires in response to the maximum time expiring,

wherein the executing the ALL INPUT/OUTPUT execution plan further comprises executing the ALL INPUT/OUTPUT execution plan if the maximum time minus an elapsed time since the query was received is less than a third threshold,

wherein the executing the FIRST INPUT/OUTPUT execution plan further comprises if the maximum time expired and all records that satisfy the query are not found, then continue the executing the FIRST INPUT/OUTPUT execution plan if a number of records not found by the executing the FIRST INPUT/OUTPUT execution plan is less than a second threshold,

wherein the executing the FIRST INPUT/OUTPUT execution plan further comprises if the maximum time expired, all records that satisfy the query are not found, and the number of records not found by the executing the FIRST INPUT/OUTPUT execution plan is greater than the second threshold, then continue the executing FIRST INPUT/OUTPUT execution plan if a user consents,

displaying the FIRST INPUT/OUTPUT result set if the FIRST INPUT/OUTPUT result set comprises more records than the ALL INPUT/OUTPUT result set, and

displaying the ALL INPUT/OUTPUT result set if the ALL INPUT/OUTPUT result set comprises more records than the FIRST INPUT/OUTPUT result set.

15. The computer system of claim 14 ,

wherein the executing the FIRST INPUT/OUTPUT execution plan further comprises probing a first table that is first in a first join order specified by the FIRST INPUT/OUTPUT execution plan via an index; and

wherein the executing the ALL INPUT/OUTPUT execution plan further comprises scanning a second table that is first in a second join order specified by the ALL INPUT/OUTPUT execution plan.

16. The computer system of claim 14 ,

wherein the executing the FIRST INPUT/OUTPUT execution plan executes for the first time period that is after the second time period if the maximum time minus the FIRST INPUT/OUTPUT estimated time is not greater than the first threshold and the maximum time minus the ALL INPUT/OUTPUT estimated time is greater than a fifth threshold; and

wherein the executing the FIRST INPUT/OUTPUT execution plan executes for the first time period that is after the second time period if the FIRST INPUT/OUTPUT estimated time is not less than the ALL INPUT/OUTPUT estimated time and the maximum time minus the ALL INPUT/OUTPUT estimated time is greater than the first threshold.

Assignments (1)
ASSIGNMENT OF ASSIGNOR'S INTEREST Recorded Aug 17, 2010
From: DAY, PAUL R.; EGAN, RANDY L.; MITTELSTADT, ROGER A.; MURAS, BRIAN R.
To: INTERNATIONAL BUSINESS MACHINES CORPORATION
Reel/Frame 024846/0623 →
Continuity (1)
Related Publication 20120047125A1 · Feb 23, 2012