select performance help

[rodrigo_paisante3]

Hi experts.

I have to change a zprogram, specific select.

I tried to change the basic things and i did not get the best performance yet. When the hs_iphas have data (2 to 6 lines only), get time out.

So, now i will try to change the filters order. See my code:

• Code before

• SELECT *

• FROM viaufks

• WHERE aufnr IN hs_aufnr

• AND objnr IN hs_objnr

• AND iphas IN hs_iphas

• AND iwerk IN s_iwerk

• AND priok IN s_priok

• AND ilart IN s_ilart

• AND ingpr IN s_ingpr

• AND ernam IN s_aernam

• AND erdat IN s_aerdat

• AND aenam IN s_aaenam

• AND aedat IN s_aaedat

• AND pspel IN hs_pspel

• AND swerk IN s_swerk

• AND bukrs IN s_bukrs

• AND beber IN s_beber

• AND vkorg IN s_vkorg

• AND vtweg IN s_vtweg

• AND spart IN s_spart

• AND tplnr IN s_tplnr

• AND gewrk IN hs_gewrk

• .

• routines.. appends and others

• ENDSELECT

*after change

SELECT

aedat aenam aufnr aufpl beber bukrs equnr

erdat ernam gewrk gltrp gluzp gstrp gsuzp

ilart ingpr iphas iwerk kostl ktext kunum

objnr priok sermat spart swerk tplnr vkorg

vtweg serialnr

INTO CORRESPONDING FIELDS OF TABLE ti_viaufks

FROM viaufks

WHERE aufnr IN hs_aufnr

AND objnr IN hs_objnr

AND iphas IN hs_iphas

AND iwerk IN s_iwerk

AND priok IN s_priok

AND ilart IN s_ilart

AND ingpr IN s_ingpr

AND ernam IN s_aernam

AND erdat IN s_aerdat

AND aenam IN s_aaenam

AND aedat IN s_aaedat

AND pspel IN hs_pspel

AND swerk IN s_swerk

AND bukrs IN s_bukrs

AND beber IN s_beber

AND vkorg IN s_vkorg

AND vtweg IN s_vtweg

AND spart IN s_spart

AND tplnr IN s_tplnr

AND gewrk IN hs_gewrk

.

LOOP AT ...

• routines appends and others

ENDLOOP.

Thanks in advance!

[Former Member]

code looks good except INTO CORRESPONDING FIELDS OF TABLE ,please change this into into table.

I guess you can decrease little bit time but you will not get it full performance.

Thanks

Seshu

[Former Member]

Well, the most important thing to do will be to check that there are actually entries in the select-options for primary or secondary index key fields.

Rob

[rodrigo_paisante3]

Thanks all replies!

Rob, do you write or have a blog about select performance, like the blog about nested loop? I used this logic here, it increase the performance.

Thanks!

[Former Member]

My Blogs (performance related):

[The Performance of Nested Loops|/people/rob.burbank/blog/2006/02/07/performance-of-nested-loops]

[Using an Index When You Don't Have all of the Fields|/people/rob.burbank/blog/2006/09/13/using-an-index-when-you-dont-have-all-of-the-fields]

[Performance - what will kill you and what will leave you with only a flesh wound|/people/rob.burbank/blog/2006/11/16/performance--what-will-kill-you-and-what-will-leave-you-with-only-a-flesh-wound]

[JOINS vs. FOR ALL ENTRIES - Which Performs Better?|/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better]

[Quickly Retrieving FI document Data from BSEG|/people/rob.burbank/blog/2007/11/12/quickly-retrieving-fi-document-data-from-bseg]

Rob

[Former Member]

You should be aware, that not all IN clauses are alwys filled. You must check the indexes whether they can support the important queries (i.e. a certain combination of filled in clauses).

Important queries should be index-supported, less important probably not as to many index will confuse the database.

Check performance with SQL Trace.

Siegfried

[rodrigo_paisante3]

Thanks for the replies.

These links help me a lot too:

/people/harry.dietz/blog/2005/11/03/performance-improvement-hints-4-loop-at-itab-where

/people/rich.heilman2/blog/2006/03/07/using-field-symbols-in-loop-statements--performance-boost

The time out dump occurs after select, in the loop at ti_viaufks. But i change the select with your tips.

More blog links, tips and others are welcome!

[Former Member]

If you paste the code within the LOOP (along with the FORMs it calls), we can look at that as well.

Rob

[Former Member]

Hi,

You did good changes. i have one idea but i am not sure.just try it. reward me if helpful.

give key field condition in SELECT statement.

give non-key field condition in LOOP statement.

because key fields are indexed. Fetching will take some more time While fetching records based on non-key field.

L.Velu

[Former Member]

Hallo Rodrigi,

the links you provide might help you with your actual problem but they are not related to your question, your question mentions only SELECT statements! So the answers will only refer to SELECT statements.

As always with performance problems, I would recommend to run traces, definitely ABAP trace (SE30) and also SQL trace (ST05)

List total runtime and top 10 of both traces

=> Then it becomes really obvious where the problem is hidden.

For the traces I have provided blogs

SQL trace

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

SE30

/people/siegfried.boes/blog/2007/11/13/the-abap-runtime-trace-se30--quick-and-easy

Siegfried

[rodrigo_paisante3]

Sorry about that Siegfried, but when i saw the info in links, i thought it can be a good ideal to put this precious links in the thread.

When i started the thread, i thought the time out problem was in select, but it is in loop.. endloop too.

The loop code with changes:

LOOP AT ti_viaufks ASSIGNING <fs_viaufks> WHERE objnr IN hs_objnr.

• CHECK ti_viaufks-objnr IN hs_objnr.

• Describe date selection in detail

• Why is the 'between' statement not used

IF NOT p_gstrp IS INITIAL.

CHECK <fs_viaufks>-gstrp GE p_gstrp OR

<fs_viaufks>-gltrp GE p_gstrp.

ENDIF.

IF NOT p_gltrp IS INITIAL.

CHECK <fs_viaufks>-gstrp LE p_gltrp OR

<fs_viaufks>-gltrp LE p_gltrp.

ENDIF.

• Data fits first selection

MOVE-CORRESPONDING <fs_viaufks> TO hviaufks.

APPEND hviaufks.

• Memorize TPLNR and EQUNR used in reference objects

IF NOT <fs_viaufks>-tplnr IS INITIAL.

hs_tplnr-low = <fs_viaufks>-tplnr.

APPEND hs_tplnr.

ENDIF.

IF NOT <fs_viaufks>-equnr IS INITIAL.

hs_equnr-low = <fs_viaufks>-equnr.

APPEND hs_equnr.

ENDIF.

ENDLOOP.

And i still working..

Thanks!

[Former Member]

I have checked the first blog which you mentioned, and I would not regard that one as especially helpful.

LOOP AT ... WHERE should only be used with sorted tables as the WHERE is then optimized.

An IF inside the LOOP can not provide a fast access to the line fulfilling the condition.

For standard tables one should not use LOOP AT WHERE but READ BINARY SEARCH and LOOP FROM INDEX with - very important - EXIT CONDITION. For details check here last section

Measurements on internal tables: Reads and Loops:

/people/siegfried.boes/blog/2007/09/12/runtimes-of-reads-and-loops-on-internal-tables

Also here an IF can not help. This should always be done if the loop is accessed several times and if the table is large.

Regarding your checks, IF p_... is ..., P is a constant, i.e. the check should not be done inside the loop,

it holds for all line of the loop and should be done outside. It will even improve your performance, if you have to program the loop several time depending on the cases whether the p_... are initial or not.

Siegfried