optimization - Removing a sub-select in SQL query - Stack Overflow

admin管理员组

文章数量:1278984

I have this poorly performing DB2 query that is being executed +800000 daily. I know I can try an index to speed that up but Im wondering how I could possible get rid of this sub-select and use a join instead.

SELECT 1
 INTO V_SELECT_ONE
 FROM FB_POLICY
WHERE FB_POL_COUNTY || SUBSTR(CHAR(FB_POL_SERIAL),3,5) = IN_MEMBER_NUMBER
  AND FB_POL_STATUS_CD = 'A'
  AND (FB_POL_POLICY_NO, FB_POL_GROUP_NO, FB_POL_ACTIVITY_TS) IN
      (SELECT FB_POL_POLICY_NO, FB_POL_GROUP_NO, MAX(FB_POL_ACTIVITY_TS) 
                           FROM FB_POLICY
                          WHERE FB_POL_COUNTY || SUBSTR(CHAR(FB_POL_SERIAL),3,5) = 
                                                       IN_MEMBER_NUMBER
          AND NOT (SUBSTR(FB_POL_LOB,1,4) IN ('FBHP','LIFE')
          AND FB_POL_EFFECTIVE_DT <= CURRENT DATE)
       GROUP BY FB_POL_POLICY_NO, FB_POL_GROUP_NO
      )
     FETCH FIRST 1 ROWS ONLY;

Primary keys are PK is FB_POL_COUNTY, FB_POL_SERIAL, FB_POL_ACTIVITY_TS

I have this poorly performing DB2 query that is being executed +800000 daily. I know I can try an index to speed that up but Im wondering how I could possible get rid of this sub-select and use a join instead.

SELECT 1
 INTO V_SELECT_ONE
 FROM FB_POLICY
WHERE FB_POL_COUNTY || SUBSTR(CHAR(FB_POL_SERIAL),3,5) = IN_MEMBER_NUMBER
  AND FB_POL_STATUS_CD = 'A'
  AND (FB_POL_POLICY_NO, FB_POL_GROUP_NO, FB_POL_ACTIVITY_TS) IN
      (SELECT FB_POL_POLICY_NO, FB_POL_GROUP_NO, MAX(FB_POL_ACTIVITY_TS) 
                           FROM FB_POLICY
                          WHERE FB_POL_COUNTY || SUBSTR(CHAR(FB_POL_SERIAL),3,5) = 
                                                       IN_MEMBER_NUMBER
          AND NOT (SUBSTR(FB_POL_LOB,1,4) IN ('FBHP','LIFE')
          AND FB_POL_EFFECTIVE_DT <= CURRENT DATE)
       GROUP BY FB_POL_POLICY_NO, FB_POL_GROUP_NO
      )
     FETCH FIRST 1 ROWS ONLY;

Primary keys are PK is FB_POL_COUNTY, FB_POL_SERIAL, FB_POL_ACTIVITY_TS

• sql
• optimization
• db2
• query-optimization

Share Improve this question Follow edited Feb 24 at 21:04 JDBA asked Feb 24 at 20:18 JDBAJDBA 831111 bronze badges 11

• 1 I suspect it's Oracle, not SQL Server – Sergey Commented Feb 24 at 20:20
• Sorry, its DB2. I edited the question, its been a long day – JDBA Commented Feb 24 at 20:24
• Show table definition and data example. Is there IN_MEMBER_NUMBER - external parameter or table column? – ValNik Commented Feb 24 at 20:34
• The lack of table DDL, sample data and expected results isn't helping, nor is the lack of table references on each of the columns (to see which column came from which table in the subquery). Does FB_POL_POLICY_NO, FB_POL_GROUP_NO, FB_POL_ACTIVITY_TS uniquely identify a row? If not then what does? – Charlieface Commented Feb 24 at 20:56
• The problem is the data model. You should not have to use substrings and string concatenation on your columns to get the data you want. Computed columns may be a good solution here. – Thorsten Kettner Commented Feb 24 at 21:00

 |  Show 6 more comments

3 Answers 3

Sorted by: Reset to default 0

See this query example.
I hope this query is equivalent to your query.
It's easier to switch from it to JOIN.

SELECT 1
 INTO V_SELECT_ONE
FROM FB_POLICY t1
WHERE FB_POL_COUNTY || SUBSTR(CHAR(FB_POL_SERIAL),3,5) = IN_MEMBER_NUMBER
  AND FB_POL_STATUS_CD = 'A'
  AND t1.FB_POL_ACTIVITY_TS=
      (SELECT  MAX(FB_POL_ACTIVITY_TS) 
       FROM FB_POLICY t2 
       WHERE t2.FB_POL_POLICY_NO=t1.FB_POL_POLICY_NO
         and t2.FB_POL_GROUP_NO=t1.FB_POL_GROUP_NO
         and t2.FB_POL_COUNTY=t1.FB_POL_COUNTY 
         and SUBSTR(CHAR(t2.FB_POL_SERIAL),3,5) = substr(char(IN_MEMBER_NUMBER),3,5)
          AND NOT (SUBSTR(FB_POL_LOB,1,4) IN ('FBHP','LIFE')
                  AND FB_POL_EFFECTIVE_DT <= CURRENT DATE)
      )
FETCH FIRST 1 ROWS ONLY;

I'm not specifically a DB2 expert, but from what I can tell, this is equivalent. I doubt it's faster, but it seems easier to read.

SELECT 1

INTO V_SELECT_ONE

FROM (
    SELECT
      FB_POL_POLICY_NO
    , FB_POL_GROUP_NO
    , FB_POL_ACTIVITY_TS
    
    FROM FB_POLICY
    
    WHERE FB_POL_COUNTY || SUBSTR(CHAR(FB_POL_SERIAL), 3, 5) = IN_MEMBER_NUMBER
      AND FB_POL_STATUS_CD = 'A'
  ) A
  INNER JOIN (
    SELECT
      FB_POL_POLICY_NO
    , FB_POL_GROUP_NO
    , MAX(FB_POL_ACTIVITY_TS) AS FB_POL_ACTIVITY_TS
    FROM FB_POLICY
    WHERE FB_POL_COUNTY || SUBSTR(CHAR(FB_POL_SERIAL), 3, 5) = IN_MEMBER_NUMBER
      AND NOT (
            SUBSTR(FB_POL_LOB, 1, 4) IN ('FBHP', 'LIFE')
        AND FB_POL_EFFECTIVE_DT <= CURRENT DATE
      )
   GROUP BY
     FB_POL_POLICY_NO
   , FB_POL_GROUP_NO
  ) B ON B.FB_POL_POLICY_NO   = A.FB_POL_POLICY_NO
     AND B.FB_POL_GROUP_NO    = A.FB_POL_GROUP_NO
     AND B.FB_POL_ACTIVITY_TS = A.FB_POL_ACTIVITY_TS

FETCH FIRST 1 ROWS ONLY;

try not to use negative condition, like your: AND NOT (SUBSTR(FB_POL_LOB,1,4) IN ('FBHP','LIFE'). Try to use your query without this negative condition and add EXCEPT with same conditions plus AND (SUBSTR(FB_POL_LOB,1,4) IN ('FBHP','LIFE') -- without NOT.

本文标签： optimizationRemoving a subselect in SQL queryStack Overflow