topic Re: Select performance. in Application Development and Automation Discussions

Select performance.

rtupino — Fri, 29 Apr 2011 18:41:34 GMT

Hi, i ahve a problem with "select", when i pass my report to 'production' . Please can i add performance this select :

1 :

SELECT zznumfisc belnr cpudt waers blart xblnr "CARGANDO NOTAS DE CREDITO EMITIDAS

INTO TABLE it_aux_bkpf

FROM bkpf

WHERE bukrs = p_bukrs AND

belnr in r_belnr and

gjahr in r_gjahr and

cpudt IN so_cpudt AND

waers = p_waers.

2 : SELECT bukrs kunnr belnr budat waers xblnr

blart wrbtr hkont zfbdt zterm vbeln

shkzg augbl

INTO TABLE it_tbsad

FROM bsad

WHERE bukrs = p_bukrs

AND kunnr IN so_kunnr

AND umsks IN r_umsks

AND umskz IN r_umskz

AND augdt IN r_augdt

AND augbl IN r_augbl

AND zuonr IN r_zuonr

AND gjahr IN r_gjahr

AND belnr IN r_belnr

AND buzei IN r_buzei

AND budat IN so_datum

AND zterm IN so_zterm.

3 : SELECT vbeln matkl matnr arktx fkimg erdat vrkme vkorg_auft vtweg_auft netwr FROM vbrp INTO TABLE it_vbrp

FOR ALL ENTRIES IN it_vbrk

WHERE vbeln = it_vbrk-vbeln and"AND matkl = 'ZPTER0032'.

posnr in r_posnr and

matkl in ('zpter0032','zpter0017','zpter0035','zpter0023','zpter0026','zpter0037') and

erdat in so_erdat.

Re: Select performance.

alex_campbell — Fri, 29 Apr 2011 19:06:53 GMT

- Make sure that your ranges are as specific as possible.

- Make sure that at least one parameter/select option/range for an indexed field is populated in each select.

- Check that it_vbrk is not initilal before select #3.

Re: Select performance.

deepak_dhamat — Sat, 30 Apr 2011 09:01:03 GMT

hi ,

Where is your it_vbrk table ?

Note :Before passing data to 3# query

Please check

if it_vbrk[] is not initial .

3 : SELECT vbeln matkl matnr arktx fkimg erdat vrkme vkorg_auft vtweg_auft netwr FROM vbrp INTO TABLE it_vbrp

FOR ALL ENTRIES IN it_vbrk

WHERE vbeln = it_vbrk-vbeln

and posnr in r_posnr

and matkl in ('zpter0032','zpter0017','zpter0035','zpter0023','zpter0026','zpter0037') and

erdat in so_erdat.

Note: table it_vbrk is blank before passing to query 3# then it iwll go in infitnite loop

it will hamper performance of query and also check whether you are selecting all primary fields in select query for respective table .

regards

Deepak.

Re: Select performance.

Former Member — Mon, 02 May 2011 05:46:44 GMT

Hi,

Please make sure control goes to 3rd select statement only when it_vbrk is not empty. If its empty, select query will select all the data (which can be huge) and hence performance problem.

BR,

Edited by: Thomas Zloch on May 2, 2011 12:35 PM - please do not ask for ...

Re: Select performance.

Former Member — Mon, 02 May 2011 08:36:30 GMT

Please understand what the RANGES mean, it is dynamic coding!

I would expect that there are no problems if all ranges are filled, and there are lots if no range is filled.

So you can have any problem with these SELECTs.

Which range do you want to support?

Re: Select performance.

Former Member — Mon, 02 May 2011 17:58:09 GMT

1. When you query from tables like BSAD (Which are always huge) try to reduce restricting the number of entries within the query like putting more conditions in the where clause (there are chance you might go into a timeout). Instead try with few conditions in where clause(according to the secondary index created for the table BSAD).

2. No query is perfect at start try to run a SQL trace and make sure your query is using the Index you wanted to

3. When you use For all entries make sure that table has entries

Re: Select performance.

rtupino — Tue, 03 May 2011 14:11:29 GMT

Thanks for all comments, i appreciate your help.

Re: Select performance.

Former Member — Thu, 05 May 2011 14:48:16 GMT

With large tables it is best to avoid WHERE statements for non-indexed fields. Consider dropping any non-indexed fields from the select query and instead loop through the returned results to filter out unwanted records in a separate step. The benefit of this all depends on the number of records retrieved with the non-indexed fields in the query and the number of records without those fields. The only way to be certain of the result is to test it with realistic data volumes.

Regards,

John

Re: Select performance.

yuri_ziryukin — Thu, 05 May 2011 15:00:22 GMT

Hello John,

how did you come to the idea to drop the where clause for the select statement and filter in ABAP?

This is absolutely incorrect advice.

Yuri

Re: Select performance.

Former Member — Wed, 11 May 2011 19:49:40 GMT

Yuri,

John is correct on this. Test this out for yourself. The first Select above is a perfect example. Perform select bkpf without currency and cpudt run it. And then run with the two non-key fields as above.

You have to do some analysis on the table and queries in question. It is not always cut and dried. And when it's feasible and beneficial, a secondary index including all fields would be the correct way to code.

Filler

Re: Select performance.

Former Member — Wed, 11 May 2011 20:03:11 GMT


> John is correct on this. Test this out for yourself. The first Select above is a perfect example. Perform select bkpf without currency and cpudt run it. And then run with the two non-key fields as above. 
> You have to do some analysis on the table and queries in question. It is not always cut and dried. And when it's feasible and beneficial, a secondary index including all fields would be the correct way to code.

Buffering is the issue here. If instead of running the SELECTs one after another, you run them after an interval, I think you'll see that eliminating the WHERE does not help.

And adding indexes should be avoided.

And CPUDT is an index field.

Rob

Edited by: Rob Burbank on May 11, 2011 4:55 PM

Re: Select performance.

yuri_ziryukin — Thu, 12 May 2011 07:33:35 GMT


Yuri,
> John is correct on this. Test this out for yourself. The first Select above is a perfect example. Perform select bkpf without currency and cpudt run it. And then run with the two non-key fields as above. 
> You have to do some analysis on the table and queries in question. It is not always cut and dried. And when it's feasible and beneficial, a secondary index including all fields would be the correct way to code. 
> 
> Filler

Filler, please read the answer of Rob above.

again, eliminating WHERE clause does not help. Moreover, it makes things slower.

If you understand, how ABAP, SAP DB interface and the Database itself work and communicate with each other the answer becomes obvious.

The only case when it can help is when DB is selecting the wrong access path due to the existence of similar indexes or outdated statistics or a bug in DB.

But I would never base my recommendations on that.

Re: Select performance.

Former Member — Thu, 12 May 2011 08:40:54 GMT

>Consider dropping any non-indexed fields from the select query and instead

>loop through the returned results to filter out unwanted records in a separate step.

incorrect and not recommended. The correct usage of indexes is a never ending story, because it can depend on so many factors. However, simple short-cut recommendations are generally wrong, even if a positive effect was observed in a certain special setting.

Re: Select performance.

Former Member — Thu, 12 May 2011 21:53:53 GMT

Rob,

I agree the buffering will taint the results if you are not aware, so test them one day at a time per scenario just to be sure.

You can't say adding indexes is to be avoided. You do have to do the analysis and take into consideration which table you are having trouble with.

CPUDT is an index field on index 5, correct? So how does that help when the other fields in the WHERE clause is in another index? Not only do they need to be in the same index but they need to be in the right sequence otherwise it will not work efficiently. Dig out your BC490 courses people!!!

Yuri and Rob, please read... I didn't say eliminating the WHERE clause. I said removing the non-indexed fields from the WHERE clause.

I use ST05, STAD, SM50/SM66 regularly for identifying and resolving performance issues.

I have used the technique of overselecting and removing non-indexed fields from the WHERE clause with success. The difference of being able to run a report online and not.

And I have implemented secondary indexes many a time at several project sites big and small.

You cannot blanketly say "don't create secondary indexes" or "don't overselect" or "don't do loops within loops" or "don't use FOR ALL ENTRIES", etc. ANALYZE. Test it out.

Creating indexes tend to be a last resort, I agree, but in a case where it's needed, there's no hesitation. Siegfried, mentioned index usage depends on many

factors, that is why sometimes it is good to create one and other times it is not.

And as Rob said, watch out for buffering when testing but also, make sure you have data to test with. A development environment usually lacks data so

you need a Test or Quality environment that may be a copy of production so you have comparable volumes of data to test with.

Re: Select performance.

yuri_ziryukin — Fri, 13 May 2011 09:13:21 GMT


Rob,
> I agree the buffering will taint the results if you are not aware, so test them one day at a time per scenario just to be sure.
> 
> You can't say adding indexes is to be avoided. You do have to do the analysis and take into consideration which table you are having trouble with. 
> 
> CPUDT is an index field on index 5, correct? So how does that help when the other fields in the WHERE clause is in another index? Not only do they need to be in the same index but they need to be in the right sequence otherwise it will not work efficiently. Dig out your BC490 courses people!!!
> 
> 
> Yuri and Rob, please read... I didn't say eliminating the WHERE clause. I said removing the non-indexed fields from the WHERE clause.
> 
> I use ST05, STAD, SM50/SM66 regularly for identifying and resolving performance issues.
> 
> I have used the technique of overselecting and removing non-indexed fields from the WHERE clause with success. The difference of being able to run a report online and not. 
> 
> And I have implemented secondary indexes many a time at several project sites big and small. 
> 
> 
> You cannot blanketly say "don't create secondary indexes" or "don't overselect" or "don't do loops within loops" or "don't use FOR ALL ENTRIES", etc. ANALYZE. Test it out.
> Creating indexes tend to be a last resort, I agree, but in a case where it's needed, there's no hesitation. Siegfried, mentioned index usage depends on many
> factors, that is why sometimes it is good to create one and other times it is not. 
> 
> And as Rob said, watch out for buffering when testing but also, make sure you have data to test with. A development environment usually lacks data so 
> you need a Test or Quality environment that may be a copy of production so you have comparable volumes of data to test with.

Hello Filler,

do you understand the way how the database works? Please explain me here why do you think that filtering for non-indexed fields in ABAP is better than doing the same in the database.

I am looking forward for your explanation.

Don't just tell me that you have used it somewhere somewhen and you have seen positive result.

I am very concerned because many developers read this forum and it we start writing here things like that we (SAP) and customers are going to have problems in future.

I think Siegfried's name should tell you something if you are interested in performance optimization. And he explicitly told you above: "incorrect and not recommended". I work 10 years at SAP doing 7 years performance optimization in many different areas. I also know exactly what I am talking about. Every single recommendation needs a clear explanation. I can clearly explain you why your recommendation of dropping non-indexed fields and filter in ABAP is wrong. Can you prove the opposite?

Kind regards,

Yuri

Re: Select performance.

HermannGahm — Fri, 13 May 2011 10:10:58 GMT

Hi,

i totally agree with Siegrfried and Yuri.

it is not recommended to remove non indexed fields from the WHERE condition.

It is wrong and not recommended. A Anti-Pattern... .

I have seen this in recommendations being used as WORKAROUNDS to work

around an optimizer problem that could not be fixed otherwise ... and when the

impact of the additional data selected was less... that's the only case when I tolerate it... .

Kind regards,

Hermann

@FILLER: you may want ot check:

http://carymillsap.blogspot.com/2010/05/filter-early.html

Edited by: Hermann Gahm on May 13, 2011 1:18 PM

Re: Select performance.

Former Member — Fri, 13 May 2011 13:12:24 GMT


Not only do they need to be in the same index but they need to be in the right sequence otherwise it will not work efficiently. Dig out your BC490 courses people!!!

I'd like to see where BC490 says that.

Rob

Re: Select performance.

Mathias_Uhlmann — Fri, 13 May 2011 13:40:26 GMT

Hi Filler,

I wonder who you are looking for inside SAP if you got an answer by Siegfried Bu00F6s already. In the meantime also Hermann Gahm has answered. For both of them you will find an answer to who they are within SDN as well as in good bookstores.

As to Yuri: Yuri is one of our Architects in SAP Active Global Support working on performance optimization. Apparently he was not promoted to the Architect level for delivering bad optimizations.

In principle Hermanns link says it all but I will nevertheless show some measurements from one of our internal test systems. The DB is DB6 but the results can in this case be easily transferred to other Databases.

I execute the following code:



PERFORM prepare_measurement.
PERFORM do_measurement.

FORM DO_MEASUREMENT.
  DO 5 TIMES.
    perform do_full_select.
    perform do_index_select.
  ENDDO.
ENDFORM.

FORM prepare_measurement.
  DO 3 TIMES.
    perform do_full_select.
    perform do_index_select.
  ENDDO.
ENDFORM.

FORM DO_FULL_SELECT.
  DATA:
    lt_bkpf TYPE TABLE OF bkpf.

  SELECT * FROM BKPF INTO TABLE lt_bkpf
    WHERE bukrs = '1000' AND waers = 'EUR'.
ENDFORM.

FORM DO_INDEX_SELECT.
  DATA:
    lt_bkpf TYPE TABLE OF bkpf.

  SELECT * FROM BKPF INTO TABLE lt_bkpf
    WHERE bukrs = '1000'.

  DELETE lt_bkpf WHERE waers NE 'EUR'.
ENDFORM.

The ABAP Trace taken with [ST12|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/15702] [original link is broken] [original link is broken] [original link is broken]; shows the following when displaying the results per modularization unit.


Form DO_MEASUREMENT
Perform DO_FULL_SELECT                 5 Executions              42,947 ms 
Perform DO_INDEX_MEASUREMENT           5 Executions              78,289 ms

Note, these numbers are now comparable since each Select was executed three times in Form PREPARE_MEASUREMENT so that the buffers in the DB are filled.

The SQL trace shows that the statement using only the company code in the Where clause (DO_INDEX_MEASUREMENT) returns 114620 records and uses index BKPF~BUT (MANDT, BUKRS). The other statement in which also the currency is handed to the DB (DO_FULL_MEASUREMENT) returns 61545 records and uses the same index.

Obviously, handing the currency to the DB accelerates the processing significantly even though the index does not contain field WAERS.

Why? In case of DO_FULL_MEASUREMENT the not required records are filtered out at an early stage in the database processing, i.e. when the full rows are accessed through the index. Then only the required records are sent to the ABAP.

In case of DO_INDEX_MEASURMENT nearly 50 % of the records are sent to ABAP in vain since they are deleted directly after the Select. But they still have to be processed further within the DB, have to be sent (possibly through the network) to the application server, they have to be processed in the database interface, they have to be stored in the internal table lt_bkpf and eventually they have to be deleted in ABAP (which took 1,045 ms for 8 executions in the trace) . And all of this takes time.

This is the reason why it is in general recommended to put all selective fields into the Where clause. The special cases where a modified strategy needs to be pursued need expert analysis taking the technical processing (in particular in the DB) into consideration.

Ciao.

Mathias.

Edited by: Mathias Uhlmann on May 13, 2011 3:41 PM

Edited by: Mathias Uhlmann on May 13, 2011 3:42 PM

Edited by: Mathias Uhlmann on May 13, 2011 3:43 PM

Re: Select performance.

Former Member — Fri, 13 May 2011 15:09:03 GMT

Hello Yuri,

Perhaps it's your choice of words, when you say "absolutely incorrect", those are strong words, and like you say many people are reading these threads looking for answers, myself included. Thiis is why I responded, because John is not absolutly incorrect.

Please tell me why dropping non-indexed fields and filtering in ABAP is wrong. You said everything needs a clear explanation, but your only response to John is he is absolutely incorrect.

Where is the explanation part? I missed that. So then what is your recommendation to Renaldo's question?

"Don't just tell me that you have used it somewhere somewhen and you have seen positive result." My "proof" is a positive result, yes. How you do prove something, when you get a negative result?

People reading these threads are looking for what works, from other peoples experiences, they are not looking for what doesn't work, they already have that. They are looking for positive results.

Please provide a solution then to Reynaldo's question. I have proved my solution to myself and my clients, to prove to you, you have to test this out yourself as I suggested, on a real system with realistic volumes (millions of records).

If I'm absolutely wrong on this, I guess I will have to tell my users their fixed report is imaginary and their more efficient program is not real and that they are only imagining it is not timing out and running faster.

Please provide a solution.

Kindest regards,

Filler

Re: Select performance.

yuri_ziryukin — Fri, 13 May 2011 15:57:54 GMT

Hello Filler,

please read the reply from Mathias Uhlmann just above yours.

He has tested that on the real example.

Although this forum is probably not a best place for that, let me provide you a rough explanation why your approach is not working (in general).

You have written something like:

select * from bkpf where bukrs = '1000' and waers = 'EUR'.

Assuming BUKRS is in the index, WAERS is not.

The following is happening:

1. Your request goes to DB.

2. Optimizer finds an index containing BUKRS field.

3. Index is accessed with the predicate BUKRS and relevant blocks containing a reference to the table records are found.

4. Table blocks are read to get the contents of the table entry

5. Entries that do not have WAERS = EUR are thrown away.

6. The rest of the entries are passed back to the SAP DB interface and written to internal table

So what operations did we do?

1. Disk/buffer reads of index blocks

2. Disk/buffer reads of table blocks

3. Passing back N records (let's say 50.000 with both combinations of the BUKRS and WAERS)

Your "optimization":

select * from bkpf where bukrs = '1000'.

delete from it_result where waers <> 'EUR'.

The following is happening:

1. Your request goes to DB.

2. Optimizer finds an index containing BUKRS field.

3. Index is accessed with the predicate BUKRS and relevant blocks containing a reference to the table records are found.

4. Table blocks are read to get the contents of the table entry

5. All found entries are passed back to the SAP DB interface and written to internal table

6. You delete entries where WAERS not equal to EUR from the table

So what operations did we do?

1. Disk/buffer reads of index blocks

2. Disk/buffer reads of table blocks

3. Passing back NN records (let's say 150.000 with BUKRS = 1000)

4. Deleting unnecessary entries in ABAP.

Compare these two actions. In the second case you are having additional overhead by transferring many unnecessary records from DB to the SAP work process, many memory operations. And expensive deletion of the unnecessary entries in ABAP.

This simply CANNOT be faster assuming that in both cases the same index on DB is used.

And if not the same index is used, then it should be solved by the optimization on the DB level (updating statistics, patches, changing indexes, etc.).

Is this explanation enough or you need more?

Mathias and Hermann already replied you with (from my PoV) detailed enough explanations why you are wrong.

Yuri

Edited by: Yuri Ziryukin on May 13, 2011 5:59 PM