It’s common to join tables within SQRs to retrieve data from normalized tables. As SQL statements consume significant computing resources, such joins may be a hindrance to performance of the SQR. Further, as the number of tables that are used in the join increases, the performance decreases.
This rational makes us look for ways to reduce the number of tables used in the join as a means to tune the SQR. This is when Load-Lookup in SQR comes into picture. Using Load-Lookup is a two step process – here’s how to make use of it in your SQR programs.
Load-Lookup
You start by loading the Load-Lookup. This can either be done within the setup section or within a procedure. While done within the setup section, it is only executed once. When within procedures, the execution happens each time the code is encountered.
The code snippet shows how this is used within the setup section. On execution of the below Load-Lookup, SQR creates an array containing a set of return values against keys.
Begin-Setup
Load-Lookup
Name = Product_Names
Table = PRODUCTS
Key = PRODUCT_CODE
Return_value = DESCRIPTION
End-Setup
Lookup
Once we have the first step in place, it’s time to utilize the lookup. The below code will essentially look up for a key (PRODUCT_CODE) in the array and return the return value (DESCRIPTION).
Begin-Select
ORDER_NUM (+1,1)
PRODUCT_CODE
Lookup Product_Names &PRODUCT_CODE $DESC
print $DESC (,15)
from ORDERLINES
End-Select
Multiple Keys / Return_values
Although Load-Lookup doesn’t support multiple keys or return_values, we can do this by concatenating the values using database specific concatenation operators. So if you are on Oracle DB, this would be how you can do it. The return values can later be separated using the unstring command.
Load-Lookup
Name = Product_Names
Table = PRODUCTS1
Key = 'PRODUCT_CODE||','||KEY2'
Return_value = 'DESCRIPTION||','||COLUMN2'
Using where clause in Load-Lookup
To limit the values that are populated in the Load-Lookup array, we can use a where clause as shown below.
Load-Lookup
Name = Product_Names
Table = PRODUCTS
Key = PRODUCT_CODE
Return_value = DESCRIPTION
Where = PRODUCT_CODE > 1000
This rational makes us look for ways to reduce the number of tables used in the join as a means to tune the SQR. This is when Load-Lookup in SQR comes into picture. Using Load-Lookup is a two step process – here’s how to make use of it in your SQR programs.
Load-Lookup
You start by loading the Load-Lookup. This can either be done within the setup section or within a procedure. While done within the setup section, it is only executed once. When within procedures, the execution happens each time the code is encountered.
The code snippet shows how this is used within the setup section. On execution of the below Load-Lookup, SQR creates an array containing a set of return values against keys.
Begin-Setup
Load-Lookup
Name = Product_Names
Table = PRODUCTS
Key = PRODUCT_CODE
Return_value = DESCRIPTION
End-Setup
Lookup
Once we have the first step in place, it’s time to utilize the lookup. The below code will essentially look up for a key (PRODUCT_CODE) in the array and return the return value (DESCRIPTION).
Begin-Select
ORDER_NUM (+1,1)
PRODUCT_CODE
Lookup Product_Names &PRODUCT_CODE $DESC
print $DESC (,15)
from ORDERLINES
End-Select
Multiple Keys / Return_values
Although Load-Lookup doesn’t support multiple keys or return_values, we can do this by concatenating the values using database specific concatenation operators. So if you are on Oracle DB, this would be how you can do it. The return values can later be separated using the unstring command.
Load-Lookup
Name = Product_Names
Table = PRODUCTS1
Key = 'PRODUCT_CODE||','||KEY2'
Return_value = 'DESCRIPTION||','||COLUMN2'
Using where clause in Load-Lookup
To limit the values that are populated in the Load-Lookup array, we can use a where clause as shown below.
Load-Lookup
Name = Product_Names
Table = PRODUCTS
Key = PRODUCT_CODE
Return_value = DESCRIPTION
Where = PRODUCT_CODE > 1000
No comments:
Post a Comment