SQL stmt Performance

[dharma_esampalli]

Hi all,

I have a SQL statement with Inner join on 3 tables, (WB2_V_MKPF_MSEG2, MARA, MAKT ) . When I run my SQL statement on my PRODUCTION system, it has to fetch 80-100 Records, But its giving Time-out Error. But when i'm executing the same SQL statement on my QA for the same data , its taking 2-5 Secs and also fetching proper records. My Quality System has almost the same data (80-90% ) as of Production. My doubt is, there could be a time difference, but not such a huge difference of 20-30 mins. In PRODUCTION SYSTEM its taking 20 mins, and in QA its taking 5 Seconds.

Please help me out.

[Former Member]

Hi sekhar,

check if your given fields are the key-fields of your tables.

If you dont have the keyfields you maybe have to create a database index for faster access.

Can you post you complete statement here?

Regards Nico

[RaymondGiuseppi]

Check activation of secondary indexes, also execute a SQL trace ST05 on both system to identify such missing index.

Regards,

Raymond

[former_member187748]

Hi Sekhar,

either you can use ST05 as sujjested by Raymond, or you can use new SAT

to analyse the difference in performance at both servers.

It will give you the clear picture of data flow.

[Former Member]

HI,

Normally inner joins will give performance issue is no proper key fields in join condition and no of tables exceeds 3.

Regards,

Praveen Savanth N

[ThomasZloch]

I agree with the proper join conditions, but the limit of 3 is an urban legend that gets repeated continuously. The technical limit is 25, I have recently done one with 19 tables (or aliases, to be exact), and it works nicely. Of course, it doesn't get easier to construct properly the more tables are involved, but just saying don't join more than three is arbitrary and not backed by any practical evidence.

Thomas

[Former Member]

It is more of a limit of coder.

The average coder may loose track of table relationships after few level of joins.

For a coder who does not create table relationships diagram before coding, chances of bug increase with level of join.

And when the join does not work on first run, the coder will have hard time finding out at which level no match was found.

Before writing code, some mock the table join by creating database view or using sqvi, but some don't.

[ThomasZloch]

And when the join does not work on first run, the coder will have hard time finding out at which level no match was found.

That's right, but the ST05 SQL Explain feature can help in finding out the problem. At the moment I can only look at the DB2 version, there is a nice graph of the joined table tree including the number of rows that were found for each branch. Example snippet from my "big one":

Thomas

[Former Member]

The screenshot is really nice.

Many coders don't know the power of ST05.

Some know it just enough so that they can pinpoint the header and item tables being queried by a standard transaction, but not the meaning of all columns being shown.

Instead of teaching ST05 and patience, seniors find it easier (saves personal time) to put limit of 3 on joins.

Some even fend it off by saying that ST05 is for Basis and DB teams only.

Luckily the default developer authorization profile always has access to this transaction.

Short term benefit of limit of 3 is that new coders who start with writing reports end up finishing the task in given time. They are also able to support it.

On longer term, this practice becomes expensive.

We could compare this with a simple mathematical exercise.

(a+b)^2 = a^2 + 2ab + b^2

Some schools deduct marks if you write this directly instead of taking the 5-step route.

Some schools encourage doing it more in mind and less on paper.

[ThomasZloch]

Many thanks for this insider view. I can see some sense in limiting the number of tables for junior developers in order to control the complexity, but as we see it too easily transforms into a general rule to be followed otherwise performance will suffer...

Also, if your task involves combining data from more than three tables, then people turn to those awkward FAE chains which lead to (much) more code, internal table hassles, less CBO flexibility and again more complexity with additional "chances" to implement bugs elsewhere.

Thomas

[Former Member]

There was a guy who had spent 7 years in ABAP development. (grey-haired ABAPer)

When he came across a 6 table join (open SQL) during debugging, he openly cursed the unknown author, declaring him as a sadist.

[Former Member]

Hi Sekhar,

In this case, we don't have any short cut to identify the problem!

- better to run the SAT/ST05 ( activate the trace with your variant)

- Find the results, Observe the which statement is taking the much time and observe st05 parameters

- then check the indices, data , statement , version management kind of stuff to trace the issue

Rg, Kiran

[Former Member]

This message was moderated.

[Former Member]

Hi,

Do not join so many tables. At most you should use 3 tables in inner join. More than 3 permitted , if and only if table entries are less.  WB2_V_MKPF_MSEG2 itself is a view of MKPF& MSEG . So basically, you are joining 4 tables. Split the data fetching logic into parts and your performance will improve drastically.

Does the Quality & production server have similar number of records. Also, performance also depends on resource availability. If too many batch jobs are running at that time, system performance will be hit.

Regards,

DPM

[Former Member]

I would say check the data first of these 3-4 tables and see if the join condition satisfies it!! Most of the time we assume qa and prod data are same but they may be not.

[former_member821147]

Hi,

Please run trace using ST05 and then analyse the trace and check the index usage, if require create secondary index and give it in select statement.

or else separate the innerjoin with for all entries.

[Former Member]

Thanks Thomas for sharing the join diagram.  You always think out of the box when it comes to resolving issues related to performance.

I have never joined more than 7 tables but my personal experience is that when you are joining tables containing huge volume of data ,  limit the join to max 2 or 3 tables. Initially with join you will get good performance benefit but after some time number of records increases in all the transaction tables; the same join condition will take lotof time. So normally when joining MKPF, MSEG, VBAK, VBAP , VBKD & other tables FI, CO tables I restrict myself to max 2-3 tables.

In standard report also, I haven't seen many programs using more than 3 tables. When more than 3 tables are involved, SAP used to fetch data using logical database. Check tcodes MB5B, FBL3N etc.

I also liked your blog on "why into corresponding things is not a bad thing". I, do agree with you that if you select only the relevant fields and in the proper order, it gives good results. On the other hand, suppose you need to fetch only 4 fields from BSEG and the order of the fields in itab differs from  that of BSEG table and  you use

select * from BSEG into corresponding fields of table lt_bseg. Performance will be disastrous.

So, the bottomline is "Definitely there is some grain of truth, when lot of professionals are proclaiming something but accept everything with a pinch of salt". Do some testing and accept or reject things as per your own result.

[ThomasZloch]

Well, when the number of records increases then not only the join construct will take more time, but any other construct as well, and I bet relatively more than the join.

If proper index access is used throughout, then runtime should only increase slowly and linear, but not exponentially.

I'm not getting paid by joins to promote them, they have served me (and my clients) well for many years, so I'm trying to share the benefit, and also speak up against the simplistic performance rules that keep circulating.

select * from BSEG into corresponding fields of table lt_bseg. Performance will be disastrous.

As long as your WHERE-condition uses BSEG primary key and you declare lt_bseg containing only the four required fields, then performance will be OK, this is what I was trying to show. This is irrespective of the order of the fields in the database and your internal table, as per my observation.

Thomas

[devinder_abap]

Hi Sekhar,

Well, I would suggest create database view else use "FOR ALL ENTRIES IN" with your select statement.

or, as suggested by many others, you can check the performance in QA/PRD by using SAT/ST05.

Thnx

Devinder

[dharma_esampalli]

Hi All,

I have checked with the Secondary indexes.  Its ok.

To all who suggested me ST05 / SAT, when I run my SQL statement, it is giving me Time-Out Error. So I'm unable to check the SQL trace. I followed this process. Executed the T-Code, then to debugging mode, stopped at my SQL statement, then SQL Trace on.. thats it... My SQL statement is giving Time-Out Error . So any other suggestions please..

[ThomasZloch]

My next steps would be:

- ask system admin to rebuild statistics for the involved tables, and try again

- narrow the selection criteria so that there is no time out and try ST05 again

- run this in background while ST05 trace is active (filtered by user, program name or table names)

Thomas

[Former Member]

Hello All,

Run the report/transaction and get the PID using sm50/sm51 then goto st04 then check the active session and filter by PID then you will get execution plan.

Based on execution plan you may further steps like creating index or something.

and also consider the following things

1. Check the index storage quality(it should be more than 60% as SAP recommend)

2. Check table statistics

3. To activate table histogram this will give you column based search.

Cherrs.

Vadamalai