One of the most important considerations when writing a select statement against a large table is the effective use of an index. However this is sometimes more easily said than done. Have you ever found that your WHERE clause is missing just one field of an index and that field is not at the end of the index?
There are some situations where you can make more effective use of the entire index even if you are missing a field. Here is a simple trick to help you do just that. If the field you are missing cannot contain too many entries, then if you create a range table with all possible entries and add that range table to your WHERE clause, you can dramatically speed up a SELECT statement. Even when you take into account the extra time needed to retrieve the key fields, the results are worth it. This may seem a bit counter-intuitive, but the example code shows what I’m doing (but be careful – if you run this code in a QA environment, it may take a while):
代码如下：
REPORT ztest_indexed_selects.
PARAMETERS: p_bukrs LIKE bkpf-bukrs MEMORY ID buk OBLIGATORY,
p_belnr LIKE bkpf-belnr MEMORY ID bln OBLIGATORY,
p_gjahr LIKE bkpf-gjahr MEMORY ID gjr OBLIGATORY.
TYPES: BEGIN OF bkpf_fields,
bukrs LIKE bkpf-bukrs,
belnr LIKE bkpf-belnr,
gjahr LIKE bkpf-gjahr,
blart LIKE bkpf-blart,
budat LIKE bkpf-budat,
END OF bkpf_fields.
DATA: bkpf TYPE bkpf,
dd07l TYPE dd07l.
DATA: bkpf_int TYPE TABLE OF bkpf_fields,
bkpf_wa TYPE bkpf_fields.
DATA: start TYPE i,
end TYPE i,
dif TYPE i.
START-OF-SELECTION.
PERFORM get_one_document.
PERFORM unindexed_select_bkpf.
PERFORM indexed_select_bkpf.
PERFORM unindexed_select_bkpf_2.
PERFORM indexed_select_bkpf_2.
*&———————————————————————*
*& Form get_one_document
*&———————————————————————*
* text
*———————————————————————-*
FORM get_one_document.
* First we get a single document using a select statement that is
* fully qualified on the primary key. Because buffering may be an issue,
* the first select will be disregarded in this test. However, in real
* life, this would be the important time.
* Initial select
SELECT bukrs belnr gjahr blart budat
FROM bkpf
INTO TABLE bkpf_int
WHERE bukrs = p_bukrs
AND belnr = p_belnr
AND gjahr = p_gjahr.
IF sy-subrc <> 0.
MESSAGE ID ’00’ TYPE ‘E’ NUMBER ‘001’ WITH
‘Document does not exist’.
ENDIF.
* Next we get the same document using the same fully qualified select
* statement. We will use the time for this in comparisons.
REFRESH bkpf_int.
GET RUN TIME FIELD start.
SELECT bukrs belnr gjahr blart budat
FROM bkpf
INTO TABLE bkpf_int
WHERE bukrs = p_bukrs
AND belnr = p_belnr
AND gjahr = p_gjahr.
GET RUN TIME FIELD end.
dif = end – start.
WRITE: /001 ‘Time for first (fully qualified) select’,
067 ‘:’, dif, ‘microseconds’.
SKIP 1.
* So we can use these fields later on
READ TABLE bkpf_int INTO bkpf_wa INDEX 1.
ENDFORM. ” get_one_document
*&———————————————————————*
*& Form unindexed_select_bkpf
*&———————————————————————*
* text
*———————————————————————-*
FORM unindexed_select_bkpf.
* Now we select a group of documents using a select statement that is
* missing the company code from the primary key. This may return a
* different set of documents from the first select, but we are just
* interested in how long it takes.
* Initial select
REFRESH bkpf_int.
SELECT bukrs belnr gjahr blart budat
FROM bkpf
INTO TABLE bkpf_int
WHERE belnr = p_belnr
AND gjahr = p_gjahr.
REFRESH bkpf_int.
GET RUN TIME FIELD start.
* Use this select in comparisons
SELECT bukrs belnr gjahr blart budat
FROM bkpf
INTO TABLE bkpf_int
WHERE belnr = p_belnr
AND gjahr = p_gjahr.
GET RUN TIME FIELD end.
dif = end – start.
WRITE: /001 ‘Time for second (unindexed) select’,
067 ‘:’, dif, ‘microseconds’.
ENDFORM. ” unindexed_select_bkpf
*&———————————————————————*
*& Form indexed_select_bkpf
*&———————————————————————*
* text
*———————————————————————-*
FORM indexed_select_bkpf.
* Now we’re going to use the first trick. Go to table T001 (company
* codes) and retrieve all the company codes and put them into a range
* table. We’ll put the range table into the select. So long as the
* number of company codes is not too great, this will speed up the
* select on BKPF.
RANGES: r_bukrs FOR bkpf-bukrs.
* Preliminary selects
r_bukrs-option = ‘EQ’.
r_bukrs-sign = ‘I’.
SELECT bukrs
FROM t001
INTO r_bukrs-low.
APPEND r_bukrs.
ENDSELECT.
REFRESH bkpf_int.
SELECT bukrs belnr gjahr blart budat
FROM bkpf
INTO TABLE bkpf_int
WHERE bukrs IN r_bukrs
AND belnr = p_belnr
AND gjahr = p_gjahr.
REFRESH: bkpf_int,
r_bukrs.
GET RUN TIME FIELD start.
* Use these selects in comparison
r_bukrs-option = ‘EQ’.
r_bukrs-sign = ‘I’.
SELECT bukrs
FROM t001
INTO r_bukrs-low.
APPEND r_bukrs.
ENDSELECT.
SELECT bukrs belnr gjahr blart budat
FROM bkpf
INTO TABLE bkpf_int
WHERE bukrs IN r_bukrs
AND belnr = p_belnr
AND gjahr = p_gjahr.
GET RUN TIME FIELD end.
dif = end – start.
WRITE: /001 ‘Time for third select’,
‘(indexed by selecting from the check table)’,
067 ‘:’, dif, ‘microseconds’.
SKIP 1.
ENDFORM. ” indexed_select_bkpf
*&———————————————————————*
*& Form unindexed_select_bkpf_2
*&———————————————————————*
* text
*———————————————————————-*
FORM unindexed_select_bkpf_2.
* Now we’ll get another group of records from BKPF. There is a
* secondary index on BKPF with fields BUKRS, BSTAT and BUDAT.
* We’re going to leave BSTAT out of the select and use
* BUKRS and BUDAT from the first document we selected.
* Preliminary select – to be ignored.
REFRESH bkpf_int.
SELECT bukrs belnr gjahr blart budat
FROM bkpf
INTO TABLE bkpf_int
WHERE bukrs = p_bukrs
AND budat = bkpf_wa-budat.
REFRESH bkpf_int.
GET RUN TIME FIELD start.
SELECT bukrs belnr gjahr blart budat
FROM bkpf
INTO TABLE bkpf_int
WHERE bukrs = p_bukrs
AND budat = bkpf_wa-budat.
GET RUN TIME FIELD end.
dif = end – start.
WRITE: /001 ‘Time for fourth (partially indexed) select’,
067 ‘:’, dif, ‘microseconds’.
ENDFORM. ” unindexed_select_bkpf_2
*&———————————————————————*
*& Form indexed_select_bkpf_2
*&———————————————————————*
* text
*———————————————————————-*
FORM indexed_select_bkpf_2.
* Finally, we will use the domain values of BSTAT in the select. If you
* are sure that you know all of the values, you can hardcode them. I am
* using all of the possible values of BSTAT so that all three selects
* return the same data. In practice, we would probably narrow it down.
* But since normal FI postings have BSTAT = SPACE, this won’t help
* unless we are looking for ‘not’ normal documents.
RANGES: r_bstat FOR bkpf-bstat.
DATA : d1 LIKE dd07l-domvalue_l,
d2 LIKE dd07l-domvalue_h.
* Hardcoded values
* Preliminary select.
REFRESH bkpf_int.
SELECT bukrs belnr gjahr blart budat
FROM bkpf
INTO TABLE bkpf_int
WHERE bukrs EQ p_bukrs
AND bstat IN (‘ ‘, ‘A’, ‘B’, ‘D’, ‘M’, ‘S’, ‘V’, ‘W’, ‘Z’)
AND budat = bkpf_wa-budat.
REFRESH bkpf_int.
GET RUN TIME FIELD start.
* Use this select in comparisons
SELECT bukrs belnr gjahr blart budat
FROM bkpf
INTO TABLE bkpf_int
WHERE bukrs EQ p_bukrs
AND bstat IN (‘ ‘, ‘A’, ‘B’, ‘D’, ‘M’, ‘S’, ‘V’, ‘W’, ‘Z’)
AND budat = bkpf_wa-budat.
GET RUN TIME FIELD end.
dif = end – start.
WRITE: /001 ‘Time for fifth select’,
‘(indexed by hardcoding the domain values)’,
067 ‘:’, dif, ‘microseconds’.
* After an upgrade, the values in a domain may change. It’s safer to
* retrieve all of the values from the data dictionary. There is a very
* slight increase in the time.
r_bstat-sign = ‘I’.
SELECT domvalue_l domvalue_h
FROM dd07l
INTO (d1, d2)
WHERE domname = ‘BSTAT’
AND as4local = ‘A’.
IF d2 IS INITIAL.
r_bstat-option = ‘EQ’.
r_bstat-low = d1.
CLEAR r_bstat-high.
ELSE.
r_bstat-option = ‘BT’.
r_bstat-low = d1.
r_bstat-high = d2.
ENDIF.
APPEND r_bstat.
ENDSELECT.
REFRESH bkpf_int.
SELECT bukrs belnr gjahr blart budat
FROM bkpf
INTO TABLE bkpf_int
WHERE bukrs EQ p_bukrs
AND bstat IN r_bstat
AND budat = bkpf_wa-budat.
REFRESH: bkpf_int,
r_bstat.
* Use this select in comparisons.
GET RUN TIME FIELD start.
r_bstat-sign = ‘I’.
SELECT domvalue_l domvalue_h
FROM dd07l
INTO (d1, d2)
WHERE domname = ‘BSTAT’
AND as4local = ‘A’.
IF d2 IS INITIAL.
r_bstat-option = ‘EQ’.
r_bstat-low = d1.
CLEAR r_bstat-high.
ELSE.
r_bstat-option = ‘BT’.
r_bstat-low = d1.
r_bstat-high = d2.
ENDIF.
APPEND r_bstat.
ENDSELECT.
REFRESH bkpf_int.
SELECT bukrs belnr gjahr blart budat
FROM bkpf
INTO TABLE bkpf_int
WHERE bukrs EQ p_bukrs
AND bstat IN r_bstat
AND budat = bkpf_wa-budat.
GET RUN TIME FIELD end.
dif = end – start.
WRITE: /001 ‘Time for sixth select’,
‘(indexed by selecting the domain values)’,
067 ‘:’, dif, ‘microseconds’.
ENDFORM. ” indexed_select_bkpf_2
I ran the above code in QA instances with a DB2 environment in both 4.6C and 4.7. There are more indexes on BKPF in 4.7, but I tried to use one that is in both versions. I also ran a similar program in Oracle with comparable results. But I really don’t know if it will work with other databases – please let me know!
I ran this many times in both active and quiet systems. Here are some typical results:
Time for first (fully qualified) select : 148 microseconds
Time for second (unindexed) select : 1,873,906 microseconds
Time for third select (indexed by selecting from the check table) : 455 microseconds
Time for fourth (partially indexed) select : 816,253 microseconds
Time for fifth select (indexed by hardcoding the domain values) : 43,259 microseconds
Time for sixth select (indexed by selecting the domain values) : 43,332 microseconds
Some things to note:
In the above times, the first select shows what happens in the ideal world. We are comparing select 2 against select 3 and select 4 against selects 5 and 6. But selects 2 and 3 should return the same results as should selects 4, 5 and 6.
But the point is that even though start out knowing nothing about (and presumably not caring about) the company code in selects 2 and 3 and the document status in selects 4, 5 and 6, if you put all possible values of these fields into the select statement, the results are dramatic.
If you try to combine both tricks, you will probably find that they don’t work very well together. Once seems to be enough.
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/18306.html