topic Re: select query performance in Application Development and Automation Discussions

select query performance

Former Member — Wed, 27 Feb 2008 11:01:03 GMT

Hi friends,

Assume that it_pos is an internla table and its having more than 15000 records.

Which of the following query will optimize performance of programme. <REMOVED BY MODERATOR>

SORT it_pos BY belnr.

LOOP AT it_pos INTO wa_pos .

SELECT hkont zuonr budat blart wrbtr bschl xref3

INTO CORRESPONDING FIELDS OF wa_bsis

FROM bsis

WHERE belnr = wa_pos-belnr

AND hkont = wa_pos-konto

AND zuonr = wa_pos-zuonr

AND blart = 'WE' "IN ('WE' , 'RE' , 'RO')

AND bschl = '96'

AND shkzg = 'H'

AND budat = sta_date .

APPEND wa_bsis to it_bsis.

ENDSELECT.

ENDLOOP.

SELECT hkont zuonr budat blart wrbtr bschl xref3

INTO CORRESPONDING FIELDS OF TABLE it_bsis1

FROM bsis

FOR ALL ENTRIES IN it_pos

WHERE belnr = it_pos-belnr

AND hkont = it_pos-konto

AND zuonr = it_pos-zuonr

AND blart = 'WE' "IN ('WE' , 'RE' , 'RO')

AND bschl = '96'

AND shkzg = 'H'

AND budat = sta_date .

Regards,

s.senthil kumar

Edited by: Alvaro Tejada Galindo on Feb 27, 2008 3:52 PM

Re: select query performance

Former Member — Wed, 27 Feb 2008 11:04:05 GMT

hi,,

SAP Provides a transaction called SE30 Which will be use for ur query performaance..there is Tips and tricks also to improve your performance tuning...

U can use it and post me a mail.. '

<REMOVED BY MODERATOR>

Regards

Karthik.T

Edited by: Alvaro Tejada Galindo on Feb 27, 2008 3:51 PM

Re: select query performance

Former Member — Wed, 27 Feb 2008 11:04:58 GMT

The second way of select option is right one.

Because in your first code .

you have loop statement then you have the select statement.

Loop statement executes in the Application layer and Select statement executes in the Database Layer.

So moving from loop to select - > time consuming and also it moves from one layer to another layer in three tier architecture.

Whereas the second one has only Select statement operating upon the Database layer . and then comes out completely.

So it is more optimal and performance is good comparatively.

Thanks and regards,

Chithra

Re: select query performance

Former Member — Wed, 27 Feb 2008 11:05:52 GMT

Hi,

Second select query would give you good result, because instead of putting a select query inside a loop, better avoid such sort of coding.

SUGGESTION1: TRY TO AVOID 'INTO CORRESPONDING FIELDS' STATEMENT IN THE SECOND SELECT QUERY.

SUGGESTION 2: DONT FORGET TO CHECK THE INITIALITY OF THE INTERNAL TABLE IT_POS BEFORE USING 'FOR ALL ENTRIES'.

Re: select query performance

Former Member — Wed, 27 Feb 2008 11:07:30 GMT

your second part is better obviously

but let me remind you that you need to remove move corresponding

because that is time consuming and other then this plz use

into workarea that will b helpful for sure

<REMOVED BY MODERATOR>

vivek

Edited by: Alvaro Tejada Galindo on Feb 27, 2008 3:52 PM

Re: select query performance

Former Member — Wed, 27 Feb 2008 11:49:59 GMT

Hi,

the first select stmt is hitting the database table for 15000 times...instead of it you can try fetching the data into an internal table outside the loop and hit the internal table inside the loop.

this may help you out.

thanks,

teja.

Re: select query performance

Former Member — Wed, 27 Feb 2008 12:06:49 GMT

The second way is right one. And you must to change "into corresponding fields of table" by "into table" and make shure that the internal table has te same fields in the same order as the select.

Re: select query performance

Former Member — Thu, 28 Feb 2008 01:27:32 GMT

Hi,

In addition to the suggestions given above, you can add the following things:

1) Add company code in the query if possible

2) Also add field GJAHR

3) Arrange the fields in the where clause as per sequence of the primary key combination

Once you do this, do the trace analysis and check if the proper index is being used or not.

Hope this helps.

Regards,

Shahu

Re: select query performance

Former Member — Thu, 28 Feb 2008 02:49:11 GMT

BUKRS is the first field of the index. Either way will be extremely slow until you add this to the WHERE clause.

Rob

Re: select query performance

Former Member — Thu, 28 Feb 2008 04:17:12 GMT

Hi,

Avoid using select statement within loop. This will increase your execution time. Instead use 'for all entries in table1'. Make sure that u check if the table 1 is not initial before using 'for all entries'.

Using select-endselect is also not advisable. Dont use 'corresponding fields of' .Instead specify the correct field names in the same order as that of internal table.

Hope this helps u.

Regards,

Ramya

Re: select query performance

Former Member — Thu, 28 Feb 2008 05:34:15 GMT

Hi,

Thank you friends for ur reply. I have given points on basis of first in first out .

Regards,

s.senthil kumar