topic Expensive SQL statement in Application Development and Automation Discussions

Expensive SQL statement

Former Member — Wed, 18 Jun 2008 11:24:24 GMT

The following Statement is expensive on DB.

Kindly suggest the changes.....

SELECT DISTINCT pernr INTO it_pernr-pernr

FROM pa0001

CLIENT SPECIFIED

WHERE mandt EQ sy-mandt

AND pernr IN s_pernr

AND subty EQ space

AND objps EQ space

AND sprps EQ space

AND endda GE sy-datum

AND begda LE sy-datum

AND seqnr EQ '000'

AND bukrs IN s_bukrs

AND werks IN s_werks

AND persg IN s_persg

AND persk IN s_persk.

READ TABLE it_adpernr INTO wa_adpernr

WITH TABLE KEY pernr = it_pernr-pernr.

IF sy-subrc EQ 0.

APPEND it_pernr.

wa_adpernr-entry = 'X'. "indicator selection

MODIFY it_adpernr FROM wa_adpernr

TRANSPORTING entry

WHERE pernr EQ it_pernr-pernr.

ENDIF.

ENDSELECT.

Re: Expensive SQL statement

Former Member — Wed, 18 Jun 2008 11:26:41 GMT

Hello,

I suggest you to do a select with all EQ and IN conditions and the use a if to delete the rows with the others conditions ( GE, and LE ).

And look in the table if all fields that you're using are in an index.

Regards.

Re: Expensive SQL statement

Former Member — Wed, 18 Jun 2008 11:28:21 GMT

Don't use select and aselect , instead do a seelct and do the read and modify opeartions later.

Re: Expensive SQL statement

Former Member — Wed, 18 Jun 2008 11:37:07 GMT

Hi,

just use the select query as given below.

SELECT DISTINCT pernr INTO table it_pernr

FROM pa0001

CLIENT SPECIFIED for all entries in it_adpernr

WHERE mandt EQ sy-mandt

AND pernr it_adpernr-pernr

AND subty EQ space

AND objps EQ space

AND sprps EQ space

AND endda GE sy-datum

AND begda LE sy-datum

AND seqnr EQ '000'

AND bukrs IN s_bukrs

AND werks IN s_werks

AND persg IN s_persg

AND persk IN s_persk.

If you use the select query like this it will bring the data at one shot.