2008 Jun 18 10:35 AM
in st05 it is showing that there are some identical sql stmt.
can any ony tell me how to eleminate that
2008 Jun 18 10:52 AM
There are several ways:
If the tables being read are small, load all the records into a hashed internal table at the start of the program and read data from that.
If the tables are big (e.g master data). Use the 'dynamic table build' technique.
Define a hashed internal table.
When you need to read data - read the table. If the entry is not found, use a SELECT SINGLE to read the database entry and add that to the internal table.
This way the next time you read you can get it from the internal table. If there is a chance that an entry does not exist in the database, then you should should define the internal table with a 'not found' flag field so that you know the next time you try to read that record you won't find it.
This example shows how to validate a customer number
Read internal table of customers previously read
READ TABLE it_customers INTO wa_customers
WITH TABLE KEY kunnr = p_kunnr.
We have already used this Customer
IF sy-subrc = 0.
p_error = wa_customers-is_error.
EXIT.
ENDIF.
New Customer - read from database
CLEAR wa_customers.
SELECT SINGLE kunnr
INTO wa_customers
FROM kna1
WHERE kunnr = p_kunnr.
No entry found
IF sy-subrc <> 0.
p_error = c_yes.
wa_customers-kunnr = p_kunnr.
wa_customers-is_error = c_yes.
ENDIF.
INSERT wa_customers INTO TABLE it_customers.
2008 Jun 18 10:52 AM
There are several ways:
If the tables being read are small, load all the records into a hashed internal table at the start of the program and read data from that.
If the tables are big (e.g master data). Use the 'dynamic table build' technique.
Define a hashed internal table.
When you need to read data - read the table. If the entry is not found, use a SELECT SINGLE to read the database entry and add that to the internal table.
This way the next time you read you can get it from the internal table. If there is a chance that an entry does not exist in the database, then you should should define the internal table with a 'not found' flag field so that you know the next time you try to read that record you won't find it.
This example shows how to validate a customer number
Read internal table of customers previously read
READ TABLE it_customers INTO wa_customers
WITH TABLE KEY kunnr = p_kunnr.
We have already used this Customer
IF sy-subrc = 0.
p_error = wa_customers-is_error.
EXIT.
ENDIF.
New Customer - read from database
CLEAR wa_customers.
SELECT SINGLE kunnr
INTO wa_customers
FROM kna1
WHERE kunnr = p_kunnr.
No entry found
IF sy-subrc <> 0.
p_error = c_yes.
wa_customers-kunnr = p_kunnr.
wa_customers-is_error = c_yes.
ENDIF.
INSERT wa_customers INTO TABLE it_customers.
2008 Jun 18 11:01 PM
Hi Ansuman,
An easy way: Delete the identical SQLs and sophisticate your logic.
Good luck,
Heinz
2008 Jun 19 4:59 AM
Hi,
See if the SQL can be combined to get the desired data. try to look into the WHERE clause of the Selects. If possible add/ delete fields in the WHERE clause, not compromising the Key fields.
Regards,
madhu
2008 Jun 19 12:15 PM
> An easy way: Delete the identical SQLs and sophisticate your logic.
that is a funny idea ....
Identical SELECTS are easy to analyse be not so simple to remove. You must check where they come from:
+ either from the same coding position because there is a wrong loop and it is actually not necessary that more than one execution is necessary. Should be simple to fix, and to avoid execution completely.
+ from the same coding, but the coding is called much later in the program, when the data are needed again. Information should be buffered, kept in internal table for example.
+ from different coding, where information is alos needed. Maybe not so simple to share information.
Buffering can be done by activating table buffering (data should be stable not changing master or customizing data, and table should not be too large)
Or with buffer modules, that are functions which controll how data are stored in a internal table.
function buffer_table_abc
using key
chnaging result
read table gt_abc
into result
with key ...
if (sy-subrc ne 0 ).
select *
append table gt_table_abc
from db_abc
Where key
endif.
endfunction.
Real buffers are even more sophisticated.
Siegfried