Select performance.

[rtupino]

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.

[alex_campbell]

- 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.

[deepak_dhamat]

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.

[Former Member]

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,

KS

<removed by moderator>

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

[Former Member]

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?

[Former Member]

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

[rtupino]

Thanks for all comments, i appreciate your help.

[Former Member]

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

[yuri_ziryukin]

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

[Former Member]

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

[Former Member]

> 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

[yuri_ziryukin]

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.

[Former Member]

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.

[yuri_ziryukin]

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

[HermannGahm]

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

[Former Member]

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

[Mathias_Uhlmann]

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

[Former Member]

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

[yuri_ziryukin]

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

[Former Member]

Hi Mathias, Yuri, and co.,

Thank you for taking the time to respond in detail. In testing, you have a simplified test using two fields. Of course, just by looking at the code I can tell that with just the company code and currency in the where

clause, it will bring back a "ton" of records of which many may be thrown away. However, your sample test is not exactly comparable to the original thread's author's. And I'm curious as to how many records are in your test system. in your test case it wouldn't matter if you had millions of records or not.

In general I whole-hearted agree with you, in general. But Reynaldo is saying, hey I got issues with my Select statement how can I make it better? Yes, I agree that you should only bring back data you are going to use.

I don't disagree with the generalization, but is it absolute? I'm sure you know that often times there are no issues with small tables but with volumes of millions of records, things start to behave differently.

With all due respect, Yuri's response to John was no help at all to Reynaldo. John just said "consider" this, and likewise I said to try it out (because it worked for me and perhaps John has done this before too).

So, can someone provide a specific answer to the original question. Can you improve this Select statement?

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.

( I have a question, if bukrs, belnr, gjahr is in index A, cpudt is in index B, is it helpful at all to have index B? I was under the impression the system only uses one index at a time.)

Respectfully,

Filler

[Former Member]

At runtime the optimizer will use the database statistics along with the values in the parameters/select-options to determine which index (if any) to use.

Rob

[HermannGahm]

Hi Filler,

> So, can someone provide a specific answer to the original question. Can you improve this Select statement?

>

> 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.

a specific answer can only be given wir mor details. (content of the bind variables, available indexes and the statistics....).

removing fields from the where condition is imho no good idea. Even as a guess i would consider it wrong (as Yuri) since

we don't have more details.

> ( I have a question, if bukrs, belnr, gjahr is in index A, cpudt is in index B, is it helpful at all to have index B? I was under the impression the system only uses one index at a time.)

Some databases can make use of more than one index of a table in an execution plan. This is e.g. called index-anding... .

Kind regards,

Hermann

[Former Member]

Hi Hermann,

Thanks for the info. I saw this index-anding (IXAND) in my testing so I thought I would ask the experts because I never knew this was possible. We're running DB6.

My (and John Cameron's) suggestion, and the keyword is suggestion, was, short of creating a new index, removal of non-indexed fields is an easy thing to try, because it can improve performance. It might be an exception but it's not absolute that this won't work. It is an exception rather than the norm, but I was just bothered by Yuri's "absolute" remark. I may be splitting hairs but to me, absolute means it will never work and should never be coded this way.

And if that was the case here, Reynaldo would have tested and found that out. As you mentioned there are so many variables we don't know about, no one can provide him with a definite answer, only suggestions he may not have tried.

Out of hundreds and thousands of Selects that I have coded, I could count the number of times I intentionally coded this way on one hand. So I want to be clear here that it is the exception not the norm.

That's all, no disrepect intended. SDN is an extremely valuable resource because of the SAP experts monitoring the forums and answering the difficult questions.

Thanks a lot!

Regards,

Filler

[yuri_ziryukin]

> My (and John Cameron's) suggestion, and the keyword is suggestion, was, short of creating a new index, removal of non-indexed fields is an easy thing to try, because it can improve performance. It might be an exception but it's not absolute that this won't work. It is an exception rather than the norm, but I was just bothered by Yuri's "absolute" remark. I may be splitting hairs but to me, absolute means it will never work and should never be coded this way. 

Hello Filler,

it looks like you still don't understand that your suggestion is a wrong way to go. Your exceptional cases when it (for whatever reason) might have helped, should have been resolved in an other way, e.g. by optimizing the access path of the select statement.

And remember, you are not alone when programming your coding. What if 1 week later another colleague motivate the DB administrator to indeed create an index with the fields that you have just dropped out of your where clause?

Spreading this wrong (as mentioned in the meantime by 4 other colleagues) information in SDN forums (indexed by Google!) may cost other colleagues valuable time and money.

Regards,

Yuri

[Former Member]

>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.

[Former Member]

Reynaldo,

Do the following:

- Avoid too many IN parameters (RANGES) in SQL clauses. Especially those that belong to non-obligatory selection-screen parameters (and even most if those fields are necessary to make use of an index). Users will try to fill as less as possible, leaving those necessary fields empty.

- Download the following software (free during 2011), and check the code for indexes (and SQL recommendations): http://www.abovesoft.com/dwnutilen.html

Regards,

Diego.