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

select performance help

rodrigo_paisante3 — Tue, 22 Jan 2008 16:20:08 GMT

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!

Re: select performance help

Former Member — Tue, 22 Jan 2008 16:33:40 GMT

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

Re: select performance help

Former Member — Tue, 22 Jan 2008 16:52:15 GMT

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

Re: select performance help

rodrigo_paisante3 — Tue, 22 Jan 2008 18:43:00 GMT

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!

Re: select performance help

Former Member — Tue, 22 Jan 2008 18:50:52 GMT

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

Re: select performance help

Former Member — Wed, 23 Jan 2008 12:00:20 GMT

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

Re: select performance help

rodrigo_paisante3 — Wed, 23 Jan 2008 12:57:14 GMT

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!

Re: select performance help

Former Member — Wed, 23 Jan 2008 22:24:38 GMT

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

Rob

Re: select performance help

Former Member — Thu, 24 Jan 2008 06:28:31 GMT

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

Re: select performance help

Former Member — Thu, 24 Jan 2008 07:16:32 GMT

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

Re: select performance help

rodrigo_paisante3 — Thu, 24 Jan 2008 10:19:07 GMT

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!

Re: select performance help

Former Member — Thu, 24 Jan 2008 11:53:09 GMT

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