Query taking long time

  • ekknaveen

    SSCrazy

    Points: 2315

    Hi,

    The below query is taking around 1000 Seconds.
    SELECT ar.MOVE_QTY,ar.DM12,ar.AL_OR,ar.OR_CODE,ar.CAI_CODE,ar.CAI_DESCRIPTION,ar.CST_CODE,ar.AL_NO AL_NO,ar.OPEN_QTY,ar.WH_CODE,ar.ETA_DATE,
    ar.CATEGORY,ar.AL_DATE,ar.OD_CODE,
    ar.SHIPTO_CODE,ar.AL_LINE AL_LINE,ar.ORIG_QTY,ar.CNCL_QTY,b.SHIP_QTY SHIP_QTY,ar.LOCAL_CODE,0 AL_TXT_LINE,b.AVIEXP_NO,
    b.UC_NUMBER,
    b.AVIEXP_DATE,b.ETD_DATE,b.ETW_DATE,b.DISCHARGE_PORT,b.VESSEL_NAME,b.CONTAINER_NO,b.SHIPPING_CO,b.TRANSPORT_TYPE,b.WH_NAME,
    ar.AL_SET,ar.IS_AUTO,ar.MKT_CODE,ar.BRAND_CODE,ar.INVOIC_QTY
    FROM VW_RPT_EXTRACT_OPENAL ar left outer join VW_SHIPPED_AL_RPT b on ar.or_code=b.or_code and ar.cai_code=b.cai_code
    and ar.al_no=b.al_no
    WHERE ar.OR_CODE IN ('AU','JP','J1','TD','KE','KO','MY','ID','VN','SI')
    order by ar.or_code,ar.cai_code,ar.al_no

    Could you please let me know where is the problem and how we can improve it.

    Thanks
    Naveen

  • ekknaveen

    SSCrazy

    Points: 2315

    The explain plan is as below:

  • Thom A

    SSC Guru

    Points: 98022

    Just a (unformatted) query isn't going to tell us anything about why your query is performing badly, and pictures of your query plan aren't useful (we can't interact with them). Have a read of Jeff's article on How to Post Performance Problems and post back please. The .sqlplan file will be much more useful.

    Also, here's a more legible T-SQL Query, rather than just a large blob of text:
    SELECT ar.MOVE_QTY, ar.DM12, ar.AL_OR,
           ar.OR_CODE, ar.CAI_CODE,
           ar.CAI_DESCRIPTION, ar.CST_CODE,
           ar.AL_NO AL_NO, ar.OPEN_QTY,
           ar.WH_CODE, ar.ETA_DATE,
           ar.CATEGORY, ar.AL_DATE, ar.OD_CODE,
           ar.SHIPTO_CODE, ar.AL_LINE AL_LINE,
           ar.ORIG_QTY, ar.CNCL_QTY,
           b.SHIP_QTY SHIP_QTY, ar.LOCAL_CODE,
           0 AS AL_TXT_LINE, b.AVIEXP_NO,
           b.UC_NUMBER,
           b.AVIEXP_DATE, b.ETD_DATE,
           b.ETW_DATE, b.DISCHARGE_PORT,
           b.VESSEL_NAME, b.CONTAINER_NO,
           b.SHIPPING_CO, b.TRANSPORT_TYPE,
           b.WH_NAME,
           ar.AL_SET, ar.IS_AUTO,
           ar.MKT_CODE, ar.BRAND_CODE,
           ar.INVOIC_QTY
    FROM VW_RPT_EXTRACT_OPENAL ar
         LEFT OUTER JOIN VW_SHIPPED_AL_RPT b ON ar.or_code = b.or_code
                                            AND ar.cai_code = b.cai_code
                                            AND ar.al_no = b.al_no
    WHERE ar.OR_CODE IN ('AU','JP','J1','TD','KE','KO','MY','ID','VN','SI')
    ORDER BY ar.or_code, ar.cai_code, ar.al_no;

    Edit: Tone change.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • John Mitchell-245523

    SSC Guru

    Points: 148218

    Naveen

    Your query is joining (what looks like) two views, and from the picture you've posted, I'd say that each of those views in turn contains several joins.

    This is probably a bigger problem than can be covered in a single forum topic.  You need to look at the design and indexing of your database and the definitions of the individual views.  Get someone in to help you if you don't have the expertise in house.

    Just a couple of things that jump out at me from looking at the code, though: I assume this is for a report?  Do you really need all of those columns?  Can your presentation layer not handle the sorting and adding the constant "0" for AL_TXT_LINE?

    John

  • ekknaveen

    SSCrazy

    Points: 2315

    @Thom, the modified query is also taking long time around 900 seconds
    @Mitchell, Yes it is using views and have joins in that . When i tried to run the view seperately, each view is completing in 6 seconds. When i joined with inner join it is completing in 60 sec but as per the requirement i need to join with left join. 
    Also i will see if any indexes required or so. Meanwhile if you have any better option pls let me know

    Thanks
    Naveen

  • John Mitchell-245523

    SSC Guru

    Points: 148218

    ekknaveen - Wednesday, October 25, 2017 5:15 AM

    Yes it is using views and have joins in that . When i tried to run the view seperately, each view is completing in 6 seconds. When i joined with inner join it is completing in 60 sec but as per the requirement i need to join with left join. 

    There's not a linear relationship.  Just because each view runs in six seconds, doesn't mean it'll run in 12 if you combine them.

    Meanwhile if you have any better option pls let me know

    Unless you follow Thom's advice above, there's not much more we can say.  And, as I said before, this may be too complex for a forum thread even if you do that.

    @Thom, the modified query is also taking long time around 900 seconds


    Yes, it's the same query but laid out to make it readable in order to help people to help you.  It won't perform any better.

    John

  • Sue_H

    SSC Guru

    Points: 89707

    ekknaveen - Wednesday, October 25, 2017 1:21 AM

    The explain plan is as below:

    Looks like an Oracle Explain Plan - is this for Oracle or SQL Server? 

    Sue

  • ekknaveen

    SSCrazy

    Points: 2315

    It is oracle query. need to find where it is taking long time and how we can improve it

  • Thom A

    SSC Guru

    Points: 98022

    ekknaveen - Monday, November 20, 2017 5:43 AM

    It is oracle query. need to find where it is taking long time and how we can improve it

    You realise you've posted on a SQL Server (2005) forum, right? Although some principles are the same, SQL Server and Oracle are fundamentally different products. You'd be better asking somewhere devoted to Oracle for support on an Oracle database.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    This is a Microsoft SQL Server forum, you probably won't find Oracle query tuning people here.
    Maybe try an Oracle-focused forum?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Alan Burstein

    SSC Guru

    Points: 61006

    ekknaveen - Monday, November 20, 2017 5:43 AM

    It is oracle query. need to find where it is taking long time and how we can improve it

    SQL Server Furum notwithstanding, you don't need that order by clause. 
    Your oracle query is joining two views. See how they're performing individually before you join them. That's where I'd start.

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code.  -- Itzik Ben-Gan 2001

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply