Slow Select Statement from a Custom Field of Custom Table.

[Former Member]

Hi Gurus,

Kindly assist me on how to make my code run faster. I made a custom field with 50 characters and include it to a custom field. The program really took a long time in processing the program segment where I'm getting the rows that match the custom field.

here is my pseudocode:

loop at it_tab.

    select zfield into it_tab-zfield from ztable where standard_field eq it_tab-standard_field.
    endselect.


    if it_tab-zfield ne ''.

         select min( standard_date ) into it_tab-standard_date from ztable where zfield eq it_tab-zfield.
         endselect.

    else.
          it_tab-standard_date = 1.
    endif.

    modify it_tab.

endloop.


clear: it_tab   " I put a breakpoint to this line and the system took more than 5 mins to display the debugger window.

I already put index on our custom field on that custom table yet, the performace does not change.

I hope you can help me with this issue.

Thanks!

Regards,

Mon Magallanes

[Former Member]

Hi,

For improving the performance of the below program you can do as below.

You are fetching the data from the database in a loop, this is not recommended. To do so, you can fetch all the data into some internal table, now for this internal table using the FOR ALL ENTRIES, you can retrieve the dependency data from other tables.

Now looping on that internal table, you can, read the other internal tables for validation etc.. modify accordingly.

Regards,

Santhosh.

[Former Member]

Hello,

To increase the performance of any program,

1. Avoid Select statements inside loops. You can acheive the same functionality by selecting from ztable for all entries of it_tab-standard_field.

2. You can get the minimum value by sorting the table ascending. You can avoid select query here.

3. Sort the table it_tab before the loop. In the place of second select query, you can have a read with binary search.

Hope this helps.

Thanks,

Sowmya

[Former Member]

dont put select state ment in the loop .i t will create performance problem just select dat in internal table and then make loop on that table.

[Former Member]

hi gurus,

i tried to search on internal table instead of selecting on the database inside the loop yet, the query took 3 mins.

here is my revised code:

sort it_date by wdatu descending.


loop at it_lqua.

    clear: it_date.

    loop at it_date where lenum = it_lqua-lenum+10(10).
        it_lqua-zwm_serial = it_date-zwm_serial.
    endloop.

    if it_lqua-zwm_serial ne ''.

        clear: it_date.

        loop at it_date where zwm_serial eq it_lqua-zwm_serial.

          it_lqua-wdatu = it_date-wdatu.
          it_lqua-remday = sy-datum - it_date-wdatu.

        endloop.

        modify it_lqua.

    else.
        delete it_lqua.
    endif.


endloop.

I hope you can help me with this issue.

Thanks,

Mon Magallanes

[Former Member]

Hope this will be helpful.

data: lv_date like it_date-lenum.
 
loop at it_lqua.
   clear lv_date.
   move  it_lqua-lenum+10(10) to lv_date. 
 
   read table it_date with key lenum = lv_date.
   if sy-subrc = 0.
      it_lqua-zwm_serial = it_date-zwm_serial.
      if it_lqua-zwm_serial ne ''.
        it_lqua-wdatu = it_date-wdatu.
        it_lqua-remday = sy-datum - it_date-wdatu.
        modify it_lqua.
      else.
        delete it_lqua.
      endif.
   else.
      delete it_lqua.
   endif.    
endloop.

[Former Member]

Hi Richa Gupta,

thanks for the help but the solution you gave will not meet my requirement. First, I need to get the zwm_serial that matches the unique lenum. There may be possible multiple rows that have the same zwm_serial. I need to take the lowest wdatu of that rows that matches this unique lenum.

Thanks!

Regards,

Mon Magallanes

[Former Member]

hi,

hope the sample code given below solves your issue....

data : it_tab1 like it_tab.

select standard_field zfield min( standard_date ) into it_tab1
from ztable for all entries in it_tab 
where standard_field = it_tab-standard_field.

loop at it_tab.
	read table it_tab1 with key standard_field = it_tab-standard_field.
	if sy-subrc ne 0.
		it_tab-standard_date = 1.
		append it_tab to it_tab1.
	endif.
 
endloop.

Regards,

Siddarth

[Former Member]

Hi Siddharth Chordia,

I believe your sample code will return me to have a select command inside a loop. Here is my code from the selection until the end of data retrievel, along with my observation.

SELECT
    A~lgnum
    A~lgort
    A~verme
    A~werks
    A~lenum
    A~matnr
    B~matkl
    C~maktx
INTO TABLE it_lqua
FROM lqua as A INNER JOIN mara as B ON A~matnr = B~matnr
INNER JOIN makt as C ON A~matnr = C~matnr
WHERE
    A~lgnum  = s_lgnum AND
    A~werks in s_werks AND
    A~lgort in s_lgort AND
    A~matnr in s_matnr AND
    B~matkl in s_matkl AND
    A~lenum ne ''.


clear: it_lqua.    " performance is normal

SELECT
    wdatu
    lenum
    zwm_serial
INTO TABLE it_date FROM zwm_warranty
WHERE
    werks in s_werks AND
    matnr in s_matnr AND
    matkl in s_matkl.

clear: it_lqua.   " performance is normal

sort it_date by wdatu descending.

loop at it_lqua. 

    clear: it_date.

    loop at it_date where lenum = it_lqua-lenum+10(10).
        it_lqua-zwm_serial = it_date-zwm_serial.
    endloop.

    if it_lqua-zwm_serial ne ''.

        clear: it_date.

        loop at it_date where zwm_serial eq it_lqua-zwm_serial.

          it_lqua-wdatu = it_date-wdatu.
          it_lqua-remday = sy-datum - it_date-wdatu.

        endloop.

        modify it_lqua.

    else.
        delete it_lqua.
    endif.



endloop.

clear: it_lqua. " took 3 mins to finish the loop.

I hope you can help with this.

Thanks!

Regards,

Mon Magallanes

[Former Member]

Hi,

Please check the loop below... if it helps in improving the performance...

data : w_index type sy-index.
loop at it_date.
	if it_date-zwm_serial is not initial.
		clear it_lqua.
		read table it_lqua with key lenum+10(10) = it_date-lenum.
		if sy-subrc eq 0.
			w_index = sy-tabix.
			it_lqua-zwm_serial = it_date-zwm_serial.
			it_lqua-wdatu = it_date-wdatu.
			it_lqua-remday = sy-datum - it_date-wdatu.
			modify it_lqua index w_index.
		endif.	
	else.
		delete it_lqua where lenum+10(10) = it_date-lenum.
	endif.
endloop.

Regards,

Siddarth

[Former Member]

Hi Siddharth Chordia ,

I tried your sample code yet no changes on the perfomance of the program code. I have an idea but I still need an assistance. Is there a way where I can join two tables where their fields have different length?

I hope you can help me. I will be very grateful.

Thanks for your time and effort,

Regards,

Mon Magallanes

Edited by: Mon Magallanes on May 19, 2009 2:28 PM

[Former Member]

Hi,

comment out sort and check this loop whether it works or not...

" sort it_date by wdatu descending. comment this line...
 
loop at it_lqua.  
    clear: it_date. 
    loop at it_date where lenum = it_lqua-lenum+10(10).
        it_lqua-zwm_serial = it_date-zwm_serial.
        it_lqua-wdatu = it_date-wdatu.
        it_lqua-remday = sy-datum - it_date-wdatu.
	exit.
    endloop.
    if it_date-zwm_serial is not initial.
        modify it_lqua.
    else.
        delete it_lqua.
    endif. 
endloop.

Regards,

Siddarth

[Former Member]

Hi Gurus,

For now, due to urgency, I just made one of my selection fields to be required so that it can limit the queries. I hope you can help me with this issue. Might be the possible cause is my custom table. But I really don't know how to make this table efficient for searching.

Hi Siddharth Chordia,

Your code still took long time when processing. I really appreciate your effort to assist me. Thanks a lot.

Thanks to all!

Regards,

Mon Magallanes

[ThomasZloch]

FYI, you can mark helpful answers accordingly at the left side of each post.

Thomas

[Former Member]

Hi Mon Magallanes,

Why don't you try sorting the table i_date as ascending as you want the value for lowest value of WDATU. Then you can exit out of the loop after the 1st record is found,

-

---

sort it_date by wdatu ascending.

loop at it_lqua.

clear: it_date.

loop at it_date where lenum = it_lqua-lenum+10(10).

it_lqua-zwm_serial = it_date-zwm_serial.

exit.

endloop.

if it_lqua-zwm_serial ne ''.

clear: it_date.

loop at it_date where zwm_serial eq it_lqua-zwm_serial.

it_lqua-wdatu = it_date-wdatu.

it_lqua-remday = sy-datum - it_date-wdatu.

exit

endloop.

modify it_lqua.

else.

delete it_lqua.

endif.

endloop.

-

---

Also I am not clear about your requirement but will the below code fulfill your functionality,

loop at it_lqua.

loop at it_date where lenum = it_lqua-lenum+10(10).

it_lqua-zwm_serial = it_date-zwm_serial.

if it_lqua-zwm_serial ne '' ".

it_lqua-wdatu = it_date-wdatu.

it_lqua-remday = sy-datum - it_date-wdatu.

modify it_lqua

else.

delete it_lqua.

endif.

exit.

endloop.

endloop.

[Former Member]

Hi,

Please first try to avoid select endselect instead fetch the data into internal tables as required

and then avoid to write select inside a loop.

You can get the data into internal tables and then read that internal table inside a loop.

Thanks and Regards,

Rizwana

[Former Member]

Reading the internal table:

Make it at least a sorted table (if non-unique key) , if possible (eg unique key ) a hashed table.

Regards

[jayanthi_jayaraman]

Hi,

Just try selecting zfield min(standard_rate) in the same statement using group by zfield.

Try to make it outside the loop and then do the necessary manipulations inside the loop,if needed, for internal table.

[former_member194613]

I think there is only one comment possible, weird design => weird performance !

I don't understand your SELECT logic.

And I do not understand you internal table logic either:

> loop at it_date where lenum = it_lqua-lenum+10(10).

no sort will ever help, if you don't even explain, what is in the 10 digits offset.

I guess the only solution would be a proper design.

>>> here you select one line, but many seem to be possible:

select zfield into it_tab-zfield from ztable where standard_field eq it_tab-standard_field.

endselect.

Siegfried

[Former Member]

Hi Siegfried Boes,

Ok, I think it is critical for you guys to know my requirement so that you can help me properly.

I need to get the total number of days stored of all of our stock which is located in LQUA table (standard).

Our primary key there is the SU Number ( LQUA-LENUM ) that has 20 characters. Once the stock is sold, the item will be removed from the LQUA table then modify the same record at our Customized table (ZWM_WARRANTY) where Serial Number (ZWM_WARRANTY-zserial), Goods Receipt Date (ZWM_WARRANTY-WDATU) and SU Number (ZWM_WARRANTY-LENUM). In this customized table, the SU Number only accepts 10 characters (same with the last 10 characters of the LQUA ). The stock may return to us when the customer returned it. So the same item will return to the LQUA table with new serial number and will stored again to our costumized table with the same serial number but different SU number. So my algorithm is:

1. Get all the stock in the LQUA table.

2. Get the serial number of these stocks through the customized table by comparing the last 10 digits of SU number from lqua table to the SU Number of this customized table.

3. Get the lowest GR date for the items with multiple SU Number yet the same Serial Number. These are the items that returned by the customer.

4. Subtract the lowest GR Date to the current date. I will now have the total number of days where the item is stock.

Hope you will help me with this.

Thanks. Thanks a lot...

Regards,

Mon Magallanes