Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

how can i eleminate the identical sql statements

Former Member
0 Kudos
223

in st05 it is showing that there are some identical sql stmt.

can any ony tell me how to eleminate that

1 ACCEPTED SOLUTION

Former Member
0 Kudos
120

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.

4 REPLIES 4

Former Member
0 Kudos
121

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.

Former Member
0 Kudos
120

Hi Ansuman,

An easy way: Delete the identical SQLs and sophisticate your logic.

Good luck,

Heinz

Former Member
0 Kudos
120

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

former_member194613
Active Contributor
0 Kudos
120

> 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