Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
5  
The first step is always to check the execution plan. –  a_horse_with_no_name Sep 9 '13 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 '13 at 11:34
    
Can you let us know the approx no of records for each of the tables involved please. –  ChrisProsser Sep 9 '13 at 11:34
    
@a_horse_with_no_name: Hi mate, how can I check the execution plan? –  user2761201 Sep 9 '13 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 '13 at 11:42
show 9 more comments

migrated from stackoverflow.com Sep 10 '13 at 2:31

This question came from our site for professional and enthusiast programmers.

4 Answers

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.)

share|improve this answer
    
Very valuable man, thanks indeed. Few things you told are a bit beyond my scope but I'll do some research on this for implementation. Point 3 & 5 are understood, I'll apply them as-well. Will post back the results. Thank you guys for you time and effort. Its highly appreciated. –  user2761201 Sep 9 '13 at 18:19
    
Hi Mike, thanks for your insight it was really informational and gave me the direction. By removing the TRIM function and COALESCE, I was able to reduce the timing to about 12 times. Thanks again buddy. –  user2761201 Sep 10 '13 at 10:13
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
I would really hope that the optimiser would be smart enough to know that these are the effectively the same thing. –  ChrisProsser Sep 9 '13 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 '13 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 <) –  a_horse_with_no_name Sep 9 '13 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 '13 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 '13 at 22:57
show 1 more comment

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.

share|improve this answer
    
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 '13 at 18:22
    
Joining on TRIM(RD_LEVEL1_ID) = TRIM(RC_LEVEL1_ID) would be another place. –  WarrenT Sep 9 '13 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? –  user2761201 Sep 10 '13 at 10:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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