topic Re: Select Statement Performance issue ? in Application Development and Automation Discussions

Select Statement Performance issue ?

Former Member — Mon, 22 Oct 2007 10:53:34 GMT

Hi experts,

I getting the performance issue using select statements in such order for validations in selection screen. the code is given below.I wanna through the error message if any wrong entries been entered in the selection screen.The code is given below,

************************************************************

SELECT pernr FROM pa0001 INTO TABLE int_pernr

WHERE pernr IN s_pernr.

IF sy-subrc <> 0.

MESSAGE e000(zsmp) WITH 'No entries found for given personnel number'.

ENDIF.

----

Internal tables for selection screen validations *

----

SELECT abkrs FROM pa0001 INTO TABLE int_abkrs

WHERE abkrs = p_abkrs.

IF sy-subrc <> 0.

MESSAGE e000(zsmp) WITH 'Invalid payroll area '.

ENDIF.

SELECT kostl FROM pa0001 INTO TABLE int_kostl

WHERE kostl IN s_kostl AND

pernr IN s_pernr.

IF sy-subrc <> 0.

MESSAGE e000(zsmp) WITH 'Invalid Cost Center'.

ENDIF.

SELECT orgeh FROM pa0001 INTO TABLE int_orgeh

WHERE orgeh IN s_orgeh AND

pernr IN s_pernr.

IF sy-subrc <> 0.

MESSAGE e000(zsmp) WITH 'Invalid Organization Unit'.

ENDIF.

SELECT persg FROM pa0001 INTO TABLE int_persg

WHERE persg IN s_persg AND

pernr IN s_pernr.

IF sy-subrc <> 0.

MESSAGE e000(zsmp) WITH 'Invalid Employee Group'.

ENDIF.

SELECT persk FROM pa0001 INTO TABLE int_persk

WHERE persk IN s_persk AND

persg = 3 AND

pernr IN s_pernr.

IF sy-subrc <> 0.

MESSAGE e000(zsmp) WITH 'Invalid Employee Sub-Group'.

ENDIF.

SELECT abkrs FROM pa0001 INTO TABLE int_abkrs

WHERE abkrs IN s_abkrs AND

pernr IN s_pernr.

IF sy-subrc <> 0.

MESSAGE e000(zsmp) WITH 'Invalid payroll area'.

ENDIF.

*****************************************************************************

I need to validate those fields using a single select statement else some other methods awaiting for ur suggestions.

Re: Select Statement Performance issue ?

Former Member — Mon, 22 Oct 2007 10:59:03 GMT

Hi,

Use select count(*) from Pa000 instead of select pernr/abkrs into table.

Regards,

Nandha

Re: Select Statement Performance issue ?

Former Member — Mon, 22 Oct 2007 10:59:07 GMT


************************************************************
data : l_abkrs type pa0001-abkrs,
         l_pernr  type pa0001-pernr,
         l_kostl  type pa0001-kostl,
         l_orgeh type pa0001-orgeh.

SELECT single pernr FROM pa0001 INTO l_pernr
WHERE pernr IN s_pernr.
IF sy-subrc <> 0.
MESSAGE e000(zsmp) WITH 'No entries found for given personnel number'.
ENDIF.
ENDIF.
*-----------------------------------------------------------------------*
* Internal tables for selection screen validations *
*-----------------------------------------------------------------------*
SELECT single abkrs FROM pa0001 INTO l_abkrs
WHERE abkrs = p_abkrs.
IF sy-subrc <> 0.
MESSAGE e000(zsmp) WITH 'Invalid payroll area '.
ENDIF.

SELECT single kostl FROM pa0001 INTO l_kostl
WHERE kostl IN s_kostl AND
pernr IN s_pernr.
IF sy-subrc <> 0.
MESSAGE e000(zsmp) WITH 'Invalid Cost Center'.
ENDIF.

SELECT single orgeh FROM pa0001 INTO l_orgeh
WHERE orgeh IN s_orgeh AND
pernr IN s_pernr.
IF sy-subrc <> 0.
MESSAGE e000(zsmp) WITH 'Invalid Organization Unit'.
ENDIF.

SELECT single persg FROM pa0001 INTO l_persg
WHERE persg IN s_persg AND
pernr IN s_pernr.
IF sy-subrc <> 0.
MESSAGE e000(zsmp) WITH 'Invalid Employee Group'.
ENDIF.

SELECT single persk FROM pa0001 INTO l_persk
WHERE persk IN s_persk AND
persg = 3 AND
pernr IN s_pernr.
IF sy-subrc <> 0.
MESSAGE e000(zsmp) WITH 'Invalid Employee Sub-Group'.
ENDIF.

SELECT single abkrs FROM pa0001 INTO l_abkrs
WHERE abkrs IN s_abkrs AND
pernr IN s_pernr.
IF sy-subrc <> 0.
MESSAGE e000(zsmp) WITH 'Invalid payroll area'.
ENDIF.

Re: Select Statement Performance issue ?

Former Member — Mon, 22 Oct 2007 11:02:15 GMT

Hi Sakthi,

hope this helps u..

Tables: pa0001.table2....

SELECT single pernr FROM pa0001WHERE pernr IN s_pernr.

IF sy-subrc <> 0.

MESSAGE e000(zsmp) WITH 'No entries found for given personnel number'.

ENDIF.

similarly for others too...

Re: Select Statement Performance issue ?

Former Member — Mon, 22 Oct 2007 11:06:24 GMT

Hi,

Check whether the parameter is not empty. Then hit a Query on database.

This will reduce no of unwanted hits.

Reward points if usefull.

Regards,

Niyaz

Re: Select Statement Performance issue ?

Former Member — Mon, 22 Oct 2007 11:07:46 GMT

Hi sakthi,

use select single to validate the parameter values.

where as to validate the select-options values you can't use select single.

<b>Reward for helpful answers</b>

Satish