Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2013
    Posts
    12

    Unanswered: Need to Improve Performance for below query

    Hi Every Body,
    I want to improve the performance for the below query.

    SELECT DISTINCT
    A.RATING_EXHIBIT_CD
    ,A.RATING_EXHIBIT_NAME
    FROM
    IRP.IRP_RATING_EXHIBIT A
    ,IRP.IRP_TPROD_CVG_RATING_EXHIBIT B,
    IRP.IRP_PAR_STATES C
    WHERE
    A.RATING_EXHIBIT_CD = B.RATING_EXHIBIT_CD
    AND B.LINE_OF_BUS_CODE = 'A'
    AND B.STATE_CODE = C.STATE_CODE
    AND B.PRODUCT_GRP_CODE = 'AU'
    AND B.PRODUCT_TYPE_CODE = 'PAP'
    AND B.RTG_CO_CD IN
    (SELECT DISTINCT
    D.RTG_CO_CD
    FROM
    IRP.IRP_PRODUCT_TYPE D
    ,IRP.IRP_TPROD_CVG_RATING_EXHIBIT E,
    IRP.IRP_PAR_STATES F
    WHERE
    D.RTG_CO_CD= F.RTG_CO_CD
    AND D.IRP_PRODUCT_TYPE_DESC_BEA ='FA2'
    AND D.PRODUCT_TYPE_CODE = E.PRODUCT_TYPE_CODE
    AND E.LINE_OF_BUS_CODE= 'A'
    AND E.STATE_CODE= F.STATE_CODE
    AND E.PRODUCT_GRP_CODE= ?
    AND E.PRODUCT_GRP_CODE= D.PRODUCT_GRP_CODE
    AND F.ZONE IN ('CENTRAL','EAST','WEST')
    AND D.EFF_DT<=CURRENT DATE
    AND (D.EXP_DT IS NULL OR D.EXP_DT>CURRENT DATE) WITH UR)
    AND C.ZONE IN('CENTRAL','EAST','WEST')
    AND A.EFF_DT<=CURRENT DATE
    AND (A.EXP_DT IS NULL OR A.EXP_DT>CURRENT DATE) AND B.EFF_DT<=CURRENT DATE
    AND (B.EXP_DT IS NULL OR (B.EXP_DT>CURRENT DATE AND B.EXP_DT NOT IN '12/31/2999'))
    ORDER BY A.RATING_EXHIBIT_NAME WITH UR

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    db2level/platform ??
    explain output?
    describe table/indexes ??
    runstats ??
    many question to provide an answer to, before someone can start to have a look on it...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    1st impression:
    not valid SQL because I see "with ur" twice.
    bad SQL because I see the keyword "distinct" twice

    Try to re-write and loose the need for the distinct clause. That's my advice.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I don't think this will ever be blazing fast as it looks more like a report type query and the inputs being provided more than likely have very low cardinality. Also, since you don't provide the explain and what indexes already exist on these tables, not a lot we can tell ou.

    There are a couple of quick items.
    1. The IN subselect. Majority of time an EXISTS outperforms an IN, change that over to a correlated exists subselect.
    2. Why do you need the distinct? Can you filter your resultset down further to not need that sort?

    As an aside, no need to put a distinct in an IN subselect.
    Dave

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without knowing a lot more about your DB2 installation and your database schema we can only offer broad help instead of specifics. My first suggestion would be to try:
    Code:
    SELECT DISTINCT 
       A.RATING_EXHIBIT_CD
    ,  A.RATING_EXHIBIT_NAME 
       FROM IRP.IRP_RATING_EXHIBIT A
       JOIN IRP.IRP_TPROD_CVG_RATING_EXHIBIT B
          ON (A.RATING_EXHIBIT_CD = B.RATING_EXHIBIT_CD )
       JOIN IRP.IRP_PAR_STATES C 
          ON (B.STATE_CODE = C.STATE_CODE)
       WHERE  A.EFF_DT <= CURRENT DATE 
          AND ( A.EXP_DT IS NULL
             OR A.EXP_DT > CURRENT DATE)
          AND B.LINE_OF_BUS_CODE = 'A' 
          AND B.PRODUCT_GRP_CODE = 'AU' 
          AND B.PRODUCT_TYPE_CODE = 'PAP' 
          AND B.EFF_DT <= CURRENT DATE 
          AND (B.EXP_DT IS NULL
             OR (B.EXP_DT > CURRENT DATE AND B.EXP_DT < '12/31/2999')) 
          AND C.ZONE IN ('CENTRAL', 'EAST', 'WEST') 
          AND EXISTS (
              SELECT D.RTG_CO_CODE
                FROM IRP.IRP_PRODUCT_TYPE D
                JOIN IRP.IRP_TPROD_CVG_RATING_EXHIBIT E
                   ON (D.PRODUCT_TYPE_CODE = E.PRODUCT_TYPE_CODE
                   AND E.PRODUCT_GRP_CODE= D.PRODUCT_GRP_CODE )
                JOIN IRP.IRP_PAR_STATES F 
                   ON (D.RTG_CO_CD = F.RTG_CO_CD
                   AND E.STATE_CODE = F.STATE_CODE)
                WHERE  D.EFF_DT <= CURRENT DATE 
                   AND ( D.EXP_DT IS NULL
                      OR D.EXP_DT > CURRENT DATE)
                   AND D.IRP_PRODUCT_TYPE_DESC_BEA ='FA2'
                   AND E.LINE_OF_BUS_CODE= 'A' 
                   AND E.PRODUCT_GRP_CODE= ? 
                   AND F.ZONE IN ('CENTRAL', 'EAST', 'WEST')
    
                   AND D.RTG_CO_CODE = B.RTG_CO_CD  --  Correlate sub-query
             ) 
       ORDER BY A.RATING_EXHIBIT_NAME
       WITH UR
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •