2

this is my first question here. Although I've been helped out from this forum over a hundred times. I was having difficulties in optimizing sql query. It takes hours to execute. Record set is also large enough. The query was not written by me. So just to find the bottle neck I tried removing conditional clauses but that doesn't make any difference. Indexing the ID's done.

Can any sql guru here could throw some light on it. Is there any fine tuning room left in the query below? The server hosting the database in DB2. I'm not too pro in sql. Thanks as always. Regards, Nuh

This is the query:

SELECT
HEALTH_INSURANCE.RISK_DETAIL_ID ,
POLICY_RISK_COVER.RISK_COVER_ID ,
HEALTH_INSURANCE.RD_POLICY_SYSTEM_NO ,
RD_POLICY_END_NO_IDX ,
HEALTH_INSURANCE.RD_POLICY_ID ,
HEALTH_INSURANCE.RD_LEVEL1_ID ,
HEALTH_INSURANCE.RD_SUM_INSURED_AMT_LC ,
HEALTH_INSURANCE.RD_PREMIUM_AMT_LC ,
POLICY_RISK_COVER.PREMIUM_AMOUNT_FC ,
POLICY_RISK_COVER.SUM_INSURED_AMT_FC ,
HEALTH_INSURANCE.RD_REC_TYPE ,
HEALTH_INSURANCE.RD_EFFECT_FROM_DT ,
HEALTH_INSURANCE.RD_EFFECT_TO_DT ,
HEALTH_INSURANCE.RD_END_EFFECT_FROM_DT ,
HEALTH_INSURANCE.SEX_MAS_CD ,
HEALTH_INSURANCE.MARITAL_STATUS_CD ,
HEALTH_INSURANCE.EMP_CATG ,
HEALTH_INSURANCE.NO_OF_DEPENDENTS ,
CAST((
CASE 
    WHEN HEALTH_INSURANCE.AUTHORITY_LETTER_NO IS NULL 
    THEN HEALTH_INSURANCE.EMP_AL_NO 
    ELSE HEALTH_INSURANCE.AUTHORITY_LETTER_NO 
END) AS INT) AS EMP_AL_NO ,
HEALTH_INSURANCE.DOB ,
HEALTH_INSURANCE.EFF_DATE ,
HEALTH_INSURANCE.EFF_DATE2 ,
HEALTH_INSURANCE.NAME ,
CAST((SUBSTR(HEALTH_INSURANCE.RELATIONSHIP_CD, 5,2)) AS INT) AS 
RELATIONSHIP_CD_S ,
HEALTH_INSURANCE.RELATIONSHIP_CD ,
HEALTH_INSURANCE.DESIGNATION ,
HEALTH_INSURANCE.BRANCH ,
HEALTH_INSURANCE.BANK_ACCOUNT ,
HEALTH_INSURANCE.BANK_BRANCH_NAME ,
HEALTH_INSURANCE.PRE_EXISTING_AILMENT ,
HEALTH_INSURANCE.AUTHORITY_LETTER_NO ,
HEALTH_INSURANCE.AGE ,
HEALTH_INSURANCE.REGION ,
HEALTH_INSURANCE.CNIC ,
HEALTH_INSURANCE.CO_CODE ,
HEALTH_INSURANCE.EMP_LOCATION ,
HEALTH_INSURANCE.SUB_LOCATION ,
CATEGORY_LIMIT_HEADER.CLH_SYSTEM_NO ,
CATEGORY_LIMIT_HEADER.CTH_SYS_ID ,
CATEGORY_LIMIT_HEADER.CTH_POL_SYS_ID ,
CATEGORY_LIMIT_HEADER.CTH_END_NO_IDX ,
CATEGORY_LIMIT_HEADER.CTH_END_SR_NO ,
CATEGORY_LIMIT_HEADER.CTH_CATEGORY ,
CATEGORY_LIMIT_DETAIL.CLD_SYS_ID ,
CATEGORY_LIMIT_DETAIL.CLDH_SYS_ID ,
CATEGORY_LIMIT_DETAIL.CLD_COVER_CD ,
CATEGORY_LIMIT_DETAIL.CLD_END_IDX ,
CATEGORY_LIMIT_DETAIL.CLD_COVER_DESC ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_TYPE_LIMIT ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_REL ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_AGE_FROM ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_AGE_TO ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_RB_LIMIT ,
CATEGORY_LIMIT_DETAIL.CLD_CATEGORY_LIMIT_FC ,
CATEGORY_LIMIT_DETAIL.CLD_CATEGORY_PREM_FC 
FROM
DB2ADMIN.HEALTH_INSURANCE AS HEALTH_INSURANCE 
    INNER JOIN DB2ADMIN.POLICY_RISK_COVER AS POLICY_RISK_COVER 
    ON HEALTH_INSURANCE.RD_POLICY_SYSTEM_NO = POLICY_RISK_COVER.
    RC_POLICY_SYSTEM_NO AND
    TRIM(RD_LEVEL1_ID) = TRIM(RC_LEVEL1_ID) 
        INNER JOIN DB2ADMIN.CATEGORY_LIMIT_HEADER AS CATEGORY_LIMIT_HEADER 
        ON HEALTH_INSURANCE.RD_POLICY_ID = CATEGORY_LIMIT_HEADER.
        CTH_POL_SYS_ID AND
        HEALTH_INSURANCE.EMP_CATG = CATEGORY_LIMIT_HEADER.CTH_CATEGORY 
            INNER JOIN DB2ADMIN.CATEGORY_LIMIT_DETAIL AS 
            CATEGORY_LIMIT_DETAIL 
            ON CATEGORY_LIMIT_HEADER.CTH_SYS_ID = CATEGORY_LIMIT_DETAIL.
            CLDH_SYS_ID AND
            POLICY_RISK_COVER.RISK_COVER_CD = CATEGORY_LIMIT_DETAIL.
            CLD_COVER_CD AND
            HEALTH_INSURANCE.RELATIONSHIP_CD = CATEGORY_LIMIT_DETAIL.
            CLD_CLM_REL 
WHERE
COALESCE(HEALTH_INSURANCE.AGE, 1) BETWEEN CATEGORY_LIMIT_DETAIL.
CLD_CLM_AGE_FROM AND
CATEGORY_LIMIT_DETAIL.CLD_CLM_AGE_TO
14
  • 5
    The first step is always to check the execution plan.
    – user1822
    Sep 9, 2013 at 11:29
  • Are the other JOIN columns like RISK_COVER_CD indexed? Have you tried it without the TRIM function? Also test the query with a subset of the data. Maybe you have already done that.
    – Wietze314
    Sep 9, 2013 at 11:34
  • Can you let us know the approx no of records for each of the tables involved please.
    – ChrisProsser
    Sep 9, 2013 at 11:34
  • @a_horse_with_no_name: Hi mate, how can I check the execution plan?
    – user2761201
    Sep 9, 2013 at 11:40
  • @Wietze314: Yes, joining members are indexed. I haven't tried it without the trim function. Will do that now. Thanks
    – user2761201
    Sep 9, 2013 at 11:42

4 Answers 4

5

Some rules of thumb that might be relevant to you . . .

  • Never try to tune a query without a representative EXPLAIN plan. Representative means to get an EXPLAIN plan either from the production database or from a dev/test database that have nearly the same number of rows and nearly the same distribution of values, ideally running on similar hardware. Different numbers of rows and different distribution of values often means different execution plans.

  • Make sure statistics are up to date.

  • Index (or verify indexes) on every column used in a JOIN clause and every column used in a WHERE clause. I think every dbms implements primary key constraints and foreign key constraints with indexes. There used to be some that wouldn't let you drop such indexes, and one or two that would let you. Since I deal with a lot of different dbms, I don't bother trying to remember which is which. I just look it up when I need to know. So just look it up.

  • One multi-column index might perform better than three single-column indexes. Think about consolidating indexes.

  • Write sargable WHERE clauses. Your expression COALESCE(HEALTH_INSURANCE.AGE, 1) BETWEEN one_column AND another_column has to evaluate the COALESCE() function for every row--it can't use an index. If it's possible, replace the NULLs with the literal integer 1, and make that column non-nullable. (This isn't always practical, but non-sargable WHERE clauses are often a performance killer, especially if other considerations provoke a full table scan.)

0
1

Make sure you have the best indexes available to support your query.

1) If you are using DB2 for IBM i or z/OS, then you can create indexes based on expressions. If you are using DB2 for LUW, then it seems you don't have this feature (at least as of v10.1):

If you cannot join properly without using trim(), then consider creating an indexes on RD_POLICY_SYSTEM_NO, RD_LEVEL1_ID, TRIM(RD_LEVEL1_ID), and another on RC_POLICY_SYSTEM_NO, RC_LEVEL1_ID, TRIM(RC_LEVEL1_ID), or some variation of this concept.

2) If you are using DB2 for IBM i:

If you have low cardinality on some join columns, such as code values, then consider creating Encoded Vector Indexes [EVI's]. You might, for example create an EVI on RD_LEVEL_ID.

0

I would try to change the BETWEEN in your where clause to

COALESCE(HEALTH_INSURANCE.AGE, 1) >= CATEGORY_LIMIT_DETAIL.CLD_CLM_AGE_FROM 
AND COALESCE(HEALTH_INSURANCE.AGE, 1) < CATEGORY_LIMIT_DETAIL.CLD_CLM_AGE_TO+1

In my experience this has been faster than between over large record sets. YMMV.

6
  • I would really hope that the optimiser would be smart enough to know that these are the effectively the same thing.
    – ChrisProsser
    Sep 9, 2013 at 12:04
  • I second Chris, based on my readings regarding similar questions being answered here. Although to minimize the doubt I tried that suggestion.
    – user2761201
    Sep 9, 2013 at 12:13
  • This has been an urban legend since ages. I don't know any DBMS where the claim is true that between is slower than the corresponding >= and <=. (Btw betwee is inclusive on both ends, so you should at least have used <= not <)
    – user1822
    Sep 9, 2013 at 12:15
  • It is inclusive on both ends, there is a +1 at the end. I have seen speedups on very large sets of data, more on date fields then on other types. OP asked for fine tuning suggestions.
    – Daniel E.
    Sep 9, 2013 at 12:28
  • So you are saying that incrementing a value+1 and using < is faster than merely comparing with <=?? Why? Is DB2 evaluating < seperately from =?? How about NOT( a > b ) instead?
    – WarrenT
    Sep 9, 2013 at 22:57
0

One rule of thumb is to not use functions in your where clause like this:

where function(field) = something.

That sort of construct will always be slower than using the field directly.

3
  • Understood, I guess you are referring to the COALESCE function. I'll try to sort that out with the advice given by 'CatCall'
    – user2761201
    Sep 9, 2013 at 18:22
  • Joining on TRIM(RD_LEVEL1_ID) = TRIM(RC_LEVEL1_ID) would be another place.
    – WarrenT
    Sep 9, 2013 at 22:50
  • Hi Warren, I just removed TRIM from the query and the runtime reduces 10 times! Additionally I also removed COALESCE (Added it back in ETL process) And again the runtime reduces a little more. Now its totally affordable query-time. Thanks ti you and Everyone who helped me out. Just for the learning, why that TRIM function was being the bottle neck to the whole query? Whats the catch inside? Sep 10, 2013 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.