Skip to Content

SQL stmt Performance

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

11 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Feb 12, 2014 at 07:46 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 12, 2014 at 10:58 AM

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

    Regards,

    Raymond

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 12, 2014 at 11:04 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 12, 2014 at 11:13 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 12, 2014 at 11:16 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 12, 2014 at 11:27 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 12, 2014 at 12:13 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 12, 2014 at 12:18 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 12, 2014 at 04:30 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Feb 13, 2014 at 09:48 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.