I changed the query. Both should return the same amount of tuples. Where there were JOIN, changed to KEY JOIN (based on the concept of using the KEY JOIN). Some fields that the type CHAR that receive only numeric values ​​have changed for the type INTEGER. (I turned off the primary and foreign keys and created again using the new fields of INTEGER). The indices were also deleted and re-created using the field of type INTEGER. The WHERE clause has not changed. Remains the same filter.

I changed the query code and grabbed the implementation plans and would like to make a comparison between them. How to correctly analyze the execution plan in ASA-9?alt text

before: after alt text

asked 04 Feb '13, 07:53

Walmir%20Taques's gravatar image

Walmir Taques
600293143
accept rate: 13%

edited 07 Feb '13, 06:53


Trivially, we can tell you where the differences are between the graphical representations:

  • TBVENPEDIDO is taking up 76.76% of your total execution time whereas it took 31.47% before. It is likely scanning more data. To find out why this is the case, you need to look at the node details to see where the differences are.
  • TBUNICLIFOR is taking up more of your execution time to match against TBVENPEDIDO, so it may be scanning more data out also. Again, the node details would tell you more.
  • There is a conversion of join strategies between these tables (JNL -> JH), possibly due to a change in runtime conditions or join cardinality estimations.

But without the actual plan details ("with statistics") on each node, it's difficult to comment on why this is the case, simply by looking at the graphical representation. You need to capture the SQL statements and plan with statistics. Can these full plan files (*.xml / *.saplan) be provided?

Do both of these queries really return the same set of results? Or are they looking at different data sets? Did you change the actual SQL statement, or just the predicates for your WHERE clause?

permanent link

answered 04 Feb '13, 09:49

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

edited 04 Feb '13, 09:49

Well, it might be noted that it's just "taking up more relative execution time" - without knowing the timing details, we can't even tell which query is faster (which you have already made very clear, there's no doubt about that!)...

(04 Feb '13, 10:56) Volker Barth
Replies hidden

I changed the query. Both should return the same amount of tuples. Where there were JOIN, changed to KEY JOIN (based on the concept of using the JOIN KEY). Some fields that the type CHAR that receive only numeric values ​​have changed for the type INTEGER. (I turned off the primary and foreign keys and created again using the new fields of INTEGER). The indices were also deleted and re-created using the field of type INTEGER. The WHERE clause has not changed. Remains the same filter.

(04 Feb '13, 11:49) Walmir Taques

Please post the plan files. To save a plan, use dbisql Plan Viewer, choose 'node and detailed statistics' ( the query will be executed), generate the plan, use Save as to save the plan in a file. The plan file contains many types of statistics, beyond your pictures above, which can be used to analyze the query execution as well as the query optimization process.

permanent link

answered 05 Feb '13, 09:50

Nica%20_SAP's gravatar image

Nica _SAP
866722
accept rate: 3%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×20
×12
×4

question asked: 04 Feb '13, 07:53

question was seen: 872 times

last updated: 07 Feb '13, 06:54