topic Re: optimize SELECT statement in Application Development and Automation Discussions

optimize SELECT statement

sudha_naik — Thu, 08 Oct 2009 05:31:38 GMT

Hello Colleagues,

Please assist me in optimizing the two SELECT statement which is taking too much time

1. SELECT SINGLE COUNT(*) FROM j_3abdsi

WHERE matnr IN r_matnr

AND j_4krcat EQ im_valcat.

-- I just need to check whether there are entries in the table with this criteria.

2. SELECT e~vbeln FROM ( vbep AS e

INNER JOIN vbap AS p ON p_{vbeln = e}vbeln

AND p_{posnr = e}posnr )

INTO lv_vbeln

WHERE p~matnr IN r_matnr

AND e~j_4krcat eq im_valcat.

EXIT.

ENDSELECT.

-- here also the case is same. Just need to check whether any value exist.

Kindly help.

Regards

Sudha

Edited by: Sudha Naik on Oct 8, 2009 7:32 AM

Re: optimize SELECT statement

former_member192432 — Thu, 08 Oct 2009 05:45:51 GMT

Hi,

You can check value in the internal table by debugging the prog .

Using select endselect and count * are all expensive statements .

regards

Re: optimize SELECT statement

Former Member — Thu, 08 Oct 2009 07:22:01 GMT

Hello Sudha,

Try:


SELECT SINGLE e~vbeln FROM ( vbep AS e
INNER JOIN vbap AS p ON p~vbeln = e~vbeln
AND p~posnr = e~posnr )
INTO lv_vbeln
WHERE p~matnr IN r_matnr
AND e~j_4krcat eq im_valcat.

or just load data into table and use DESCRIBE command.

Regards,

Lukas

Edited by: Lukasz Gruca on Oct 8, 2009 9:23 AM

Edited by: Lukasz Gruca on Oct 8, 2009 9:24 AM

Re: optimize SELECT statement

Former Member — Thu, 08 Oct 2009 07:27:01 GMT

Hi,

Try like this,



TABLES:  j_3abdsi.

 SELECT SINGLE *
 FROM j_3abdsi
WHERE matnr IN r_matnr
AND j_4krcat EQ im_valcat.

if sy-subrc NE 0.
write: 'No entries available'.
endif.

2. SELECT single  e~vbeln FROM ( vbep AS e
INNER JOIN vbap AS p ON p~vbeln = e~vbeln
AND p~posnr = e~posnr )
INTO lv_vbeln
WHERE p~matnr IN r_matnr
AND e~j_4krcat eq im_valcat.

Vikranth

Re: optimize SELECT statement

Former Member — Thu, 08 Oct 2009 07:29:54 GMT

Hi Sudha,

Try below are the best methods

1 ) 
data:lv_lines type i.

SELECT SINGLE * FROM j_3abdsi  "Take required fileds only
into it_itab
WHERE matnr IN r_matnr 
AND j_4krcat EQ im_valcat.
if sy-subrc = 0.
 DESCRBE table it_itab lines lv_lines.
endif.

" In lv_lines you will be getting the number of lines.

for the second query..try to avoid the SELECT..ENDSELECT....

and also check for the secondary indexs..if there you can avoid that..

hope this info useful to you.

Thanks!!

Re: optimize SELECT statement

Former Member — Thu, 08 Oct 2009 08:08:21 GMT

as always,

existenzchecks should be done like this


  SELECT ... one field of index ...
               INTO
               FROM
               UP TO 1 ROWS
               WHERE ...

+ You don't need to count, you need to know that there is at least 1 !

+ SINGLE and UP TO 1 ROWS are identical, but use SINGLE for full primary key (there is only solution)

and UP TO 1 ROWS when one out of many is sufficient.

+ Combination COUNT and UP TO 1 ROWS depends on database, some evaluate COUNT first, others UP TO 1 ROWS,

so be careful when you test yourself.

+ whether you use * or only field gives your some additionaly microseconds

Re: optimize SELECT statement

Former Member — Thu, 08 Oct 2009 08:51:16 GMT

Hello Sudha,

You only want to check this record is available or not then you can use statement 'select single count from' like this also you can add 'up to one row' this will give you subrc return.

Hope this will help you.

Regards,

Shrikant.

Re: optimize SELECT statement

Former Member — Thu, 08 Oct 2009 11:19:50 GMT

Never COUNT(*) for Existence Check !!!

Clear ?

Re: optimize SELECT statement

Former Member — Thu, 08 Oct 2009 11:31:51 GMT

Hi Sudha,

For Query 1:

No need to use count(*) as yiu are using select single. Supply all the promary keys (otherwise use up to 1 rows). As you wants to check whether record exist or not, select up to 1 rows or select single is enough. Checjk on sys-subrc after query will tell you if record exist or not.

Query 2:

Same is applicable for second query also.

Re: optimize SELECT statement

Former Member — Thu, 08 Oct 2009 18:15:08 GMT

Hi Sudha,

1) I do not understand why you would say SELECT SINGLE COUNT(). If you know the entire primary key and would like to retrieve a record you will use SELECT SINGLE to retrieve just that single record. SELECT COUNT() returns the number of records meeting your selection criteria in the system field SY-DBCNT. SELECT COUNT(*) is generally used to check for the existance of the record because this construct just check the number of qualifying records and does not retrieve any data fields from the table. You seem to have merged both these statements and have created something I have never seen before. If you are only interested in finding the existance of the record may I suggest that you use the following.

DATA: w_matnr TYPE mara-matnr.

SELECT matnr
  UP TO 1 ROWS
  FROM j_3abdsi
  WHERE matnr    IN r_matnr
  AND   j_4krcat EQ im_valcat.
ENDSELECT.

SELECT COUNT(*) is good if you are using an index. If not you will be trying to count the records without an index. This can be time consuming. If you are not interested in the record count and only want to know if a qualifying record exist the above option will be faster because SAP check for the first instance and then returns SY-SUBRC = 0.

2) Use the following code.

SELECT e~vbeln 
  UP TO 1 ROWS
  FROM       vbap AS p
  INNER JOIN vbep AS e
  ON  p~vbeln = e~vbeln
  AND p~posnr = e~posnr )
  INTO lv_vbeln
  WHERE p~matnr    IN r_matnr
  AND   e~j_4krcat EQ im_valcat.
ENDSELECT.

Re: optimize SELECT statement

Former Member — Thu, 08 Oct 2009 20:02:56 GMT

Lukasz Gruca,

Sudha does not care about the count. She is just concerned about the existance of a single entry. If you are downloading data into an internal table a READ statement would be less expensive than a DESCRIBE statement.

Secondly you should use SELECT SINGLE if you are entering single values for all the primary key fields.

Prasanth Maddela,

When you say SELECT SINGLE aren't you just expecting a single record? Why then would you use a DESCRIBE statement to find out what you already know?

Re: optimize SELECT statement

Former Member — Sat, 10 Oct 2009 16:58:05 GMT

Hi Sudha,

Do not use count in selection query.if you want to check the number of entries ,better you use describe table statement.

Hope this helps..

Regards

basavaraj