topic Better Way To Write Select Code in Application Development and Automation Discussions

Better Way To Write Select Code

Former Member — Mon, 23 Nov 2009 08:51:15 GMT

HI, Just wondering if someone can help. I'm trying to get all sales orders that have the kit header on them that is on a production order.

I have a list of all my production orders, then i'm using


SELECT aa~matnr aa~kwmeng bb~auart aa~netwr
         aa~abgru bb~erdat
    FROM vbap AS aa INNER JOIN vbak AS bb ON aa~vbeln  = bb~vbeln

    INTO wa_sales_orders
    FOR ALL ENTRIES IN it_prod_orders2
    WHERE aa~matnr = it_prod_orders2-plnbez AND
          bb~auart EQ 'ZOR'                 AND
          aa~abgru EQ ' '.

    APPEND wa_sales_orders TO it_sales_orders.

  ENDSELECT.

to get all the sales orders. But this is taking ages, could anyone suggest a better way to write this.

Many Thanks

Re: Better Way To Write Select Code

Former Member — Mon, 23 Nov 2009 08:54:05 GMT

Hello,

You could try this,



if not it_prod_orders2[] is initial.

SELECT aa~matnr aa~kwmeng bb~auart aa~netwr
         aa~abgru bb~erdat
    FROM vbap AS aa INNER JOIN vbak AS bb ON aa~vbeln  = bb~vbeln
 
    INTO table  it_sales_orders
    FOR ALL ENTRIES IN it_prod_orders2
    WHERE aa~matnr = it_prod_orders2-plnbez AND
          bb~auart EQ 'ZOR'                 AND
          aa~abgru EQ ' '.

endif.

Also check if you have used as many primary key fields possible in the where condition

Vikranth

Re: Better Way To Write Select Code

Former Member — Mon, 23 Nov 2009 08:55:30 GMT

HI,

it_data[] = it_prod_orders2[]. (move the data from it_prod_orders2 to it_data.)

SORT it_data BY plnbez.

DELETE ADJACENT DUPLICATES FROM it_data COMPARING plnbez.

IF NOT it_data[] IS INITIAL.

SELECT aa~erdat

aa~auart

bb~matnr

bb~abgru

bb~netwr

bb~kwmeng

FROM vbak AS aa INNER JOIN vbap AS bb ON aa_{vbeln = bb}vbeln

INTO TABLE it_sales_orders

FOR ALL ENTRIES IN it_prod_orders2

WHERE aa~auart EQ 'ZOR'

AND bb~matnr = it_data-plnbez

AND bb~abgru EQ ' '.

ENDIF.

instead of select endselect, you can use INTO TABLE IT_SALES_ORDERS for better performance.

Hope the above modified query will solve your problem.

Regards,

Phani.

Re: Better Way To Write Select Code

Former Member — Mon, 23 Nov 2009 08:58:26 GMT

Hello,

Avoid using select.....endselect loop. If the output from the where condition contins hundred thousand records than this select will run that many times. This would increase the overhead on the database and hence slow the query down. As mentioned in the previous post by Vikranth, insert all the records at a single shot into an internal table rather than placing it into a workarea and appending it into an internal table.

Hope this helps.

Regards,

Sachin

Re: Better Way To Write Select Code

Former Member — Mon, 23 Nov 2009 09:12:29 GMT

Hi ,

As sachin said avoid using SELECT...ENDSELECT

1. Use syntax into table

2. Make sure that the access is based on Primary Key / Secondary Indexes

Re: Better Way To Write Select Code

Former Member — Mon, 23 Nov 2009 10:03:41 GMT

Replace SELECT/ENDSELECT with SELECT in table.

Check for initial before using for all entries.

Also , try to use selection sceen values in where condition.

Hope it help you.

Re: Better Way To Write Select Code

Former Member — Mon, 23 Nov 2009 15:46:27 GMT

Thanks for all suggestion.

I've tried using the code in the first reply, but it is still running extremly slow.

any other suggestion would be welcome.

Thanks

Re: Better Way To Write Select Code

Former Member — Mon, 23 Nov 2009 16:19:26 GMT

You would probably have gotten better ideas if you had posted in the correct forum: . But before doing that, you would have to do some analysis on your part. Running transaction ST05 for starters, would show you what (if any) index was being used.

However, your select runs slowly because you are not using an index. You will be able to speed it up considerably if you JOIN table VAPMA into the SELECT. This is a secondary index table on material.

Rob

Re: Better Way To Write Select Code

Former Member — Mon, 23 Nov 2009 16:20:18 GMT

You need to performance tune the code.

1. Match the order of fields same as in database tables.

2. Do runtime analysis and trace analysis using SE30 and ST05.Check which index is used.

3. Try to increase fields in where condition, use selection screen fields.

Hope it help you.

Re: Better Way To Write Select Code

Former Member — Mon, 23 Nov 2009 16:25:41 GMT


> 1. Match the order of fields same as in database tables.

Harsh Bhalla - this will not help in performance - please search the forum. There are many discussions on this.

Rob

Re: Better Way To Write Select Code

Former Member — Tue, 24 Nov 2009 08:49:05 GMT

Sorry for my ignorance, I'm new to this.

If I join table VAPMA, do I select any fields from this or just put it in the join statement. Also do I join it on VBELN & POSNR?

Thanks

Re: Better Way To Write Select Code

Former Member — Tue, 24 Nov 2009 09:28:51 GMT

Hi MLockett,

Just have a look on below blog especially conmments for your purpose...

it's interesting one..

[Blog|http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=(J2EE3417500)ID1011873750DB00632167431349190219End?blog=/pub/wlg/8058]

VAPMA is having these many fields as primary keys... of course you can join using VBELN POSNR... Better you select data from this table first and then proceed further data selection... There are other fields as well which you can put in where clause if it suits your requirement...

MATNR

VKORG

TRVOG

AUDAT

VTWEG

SPART

AUART

KUNNR

VKBUR

VKGRP

BSTNK

ERNAM

VBELN

POSNR

Also have a look on VBAP_VAPMA View which joins VBAP and VAPMA and have a look on VAKPA Table as well

Hope it will solve your problem..

Thanks & Regards

ilesh 24x7

ilesh Nandaniya