June 2009 Newsletter

This Month's Coding Tip from our Development Team

Efficient Joining of Tables with Multiple Keys

Do you have long-running reports that you would like to take less time? If so, this article, and others to follow, may help you. This first article describes how an UltraQuest customer was recently able to halve the amount of time needed to run a report by making a simple change.

The following (simplified) database describes a chain of hospitals. It contains two very large tables like the following, where columns that are keys are marked with an asterisk (*):

PATIENTS	ACCOUNTS
* PAT_HOSPITAL * PAT_CODE PAT_NAME PAT_RESIDENT_DR	* ACC_HOSPITAL * ACC_PAT_CODE * ACC_RECORD_NUM ACC_DATE ACC_AMOUNT

The PATIENTS table includes the name of a resident doctor treating a patient at a given hospital; different hospitals have different patient codes. The ACCOUNTS table contains accounts receivable records for a patient at a given hospital.

To find out how much billed amounts may be attributed to each resident doctor, the two tables are joined using an EXTRACT ALL operator, matching columns PAT_CODE and ACC_PAT_CODE. This generates a good report, but it runs more slowly than necessary. The request may be made more efficient by also matching columns PAT_HOSPITAL and ACC_HOSPITAL before matching columns PAT_CODE and ACC_PAT_CODE. In UltraQuest, these changes are made in the Sort and Join dialogs.

Why was this simple change able to make the request run so much faster? The answer lies in sorting.

UltraQuest and NOMAD typically need to sort data when a join is performed. However, sorting is skipped if the data is already in the desired order in the database. In the original request, there is a need to sort the PATIENTS table to order it by PAT_CODE. In the modified request, after PAT_HOSPITAL is added as the first sort (BY) column, the requested ordering is now the same as the keys of the PATIENTS table. The data in the database is already in the desired order, so no sorting is necessary.

Note the following about the use of this technique:

1. This technique works when the columns used for sorting match some or all of the first key columns in the table. For example, it works if either PAT_HOSPITAL or both of PAT_HOSPITAL and PAT_CODE (in that order) are specified as sort columns.
2. The same technique can avoid sorting of the join table, provided that the join type is a merge, extract, subset or reject join. Our example used an extract all join, so the technique could not be used on the join table.
3. If the values of the added columns are not desired in the report output, the NOPRINT option may be specified (in UltraQuest, in the Sort dialog).
4. Since adding sort or matching columns could change the data in some reports, always verify that the report still contains the desired data.
5. In UltraQuest, you may find the names and order of the key columns by double-clicking on a database in the tree view (to open it), right-clicking on the name of a table, and selecting the Properties choice. The names of the key columns are shown in order as the value of the keyname property.
6. In UltraQuest, if you want to test adding additional sort columns, you may use the Preview button. Do not select “Test with ___ input records” as this often forces a sorting of data that did not otherwise need this. For NOMAD users, this means that &INSTANCE_LIMIT may be used, but the TEST option should not be used.

For the record, following is the 4GL code used in the reports (which may be seen in UltraQuest by selecting View Code from the Jump button). First, here is the original code:

LIST

FROM PATIENTS

BY PAT_CODE

BY PAT_RESIDENT_DR

FROM ACCOUNTS

EXTRACT ALL

MATCHING ACC_PAT_CODE

SUM(ACC_AMOUNT) ;

And here is the modified code:

LIST

FROM PATIENTS

BY PAT_HOSPITAL NOPRINT

BY PAT_CODE

BY PAT_RESIDENT_DR

FROM ACCOUNTS

EXTRACT ALL

MATCHING ACC_HOSPITAL

MATCHING ACC_PAT_CODE

SUM(ACC_AMOUNT) ;

Next month’s newsletter will contain an article to help an UltraQuest administrator or NOMAD user create optimal schemas for accessing VSAM and QSAM data.

UltraQuest Webinars Featuring Version 5.0

Just a reminder to sign up for an UltraQuest Webinar and see the new features of version 5.0 including dashboards, SQL Server and Oracle access and more. See what others have experienced and learn how you can also receive a payback on your UltraQuest investment in less than one year.

The scheduled dates for the next UltraQuest Webinars are:

• July 22nd

• August 19th

• September 9th

Click here to register.

If you want to see UltraQuest in action and these dates aren't convenient for you, please This e-mail address is being protected from spambots. You need JavaScript enabled to view it with your availability.

Select Business Solutions

35 Nutmeg Drive

Trumbull, CT 06611

Phone: 1-888-472-7347

Email: This e-mail address is being protected from spambots. You need JavaScript enabled to view it

If you do not wish to receive future e-mails from

Select Business Solutions, please email: This e-mail address is being protected from spambots. You need JavaScript enabled to view it