Topic
  • 5 replies
  • Latest Post - ‏2013-09-05T11:35:08Z by Zahni
shivrajk
shivrajk
3 Posts

Pinned topic Joins affecting query performance

‏2013-09-03T15:29:02Z |

The following query is taking around 30 hrs to retrieve 16 million records. ETL guys say it used to take 14 hrs to retrieve 14 million records. All indexes are created for the join predicates but still, I am not sure.

SELECT
               ...              
FROM
                 OTD
INNER JOIN  COM ON COM.TX_ID = OTD.TX_ID
INNER JOIN  MST ON MST.TX_ID = OTD.TX_ID
INNER JOIN  CON ON CON.TX_ID = OTD.TX_ID
INNER JOIN PER ON PER.TX_ID = OTD.TX_ID
INNER JOIN  OCD ON OCD.TX_ID = OTD.TX_ID
LEFT OUTER JOIN  MTC ON MTC.COUNTRY_ID = CON.COUNTRY_NAME

 

The above query runs as a part of ETL job. I was wondering if anyone can help me here. One thing I am concerned about the query is the outer join happening on varchar. Is it ok? Let me know if any suggestions to improve the above query's performance.

Updated on 2013-09-04T12:17:08Z at 2013-09-04T12:17:08Z by shivrajk
  • Zahni
    Zahni
    16 Posts

    Re: Joins affecting query performance

    ‏2013-09-04T11:12:54Z  

    Hi,

    use db2advis (look in the Info  Center) or  use the  DB2 Optim Query Workload Tuner (Data Studio Plugin).

    With the  Data Studio, you may  found  a  free Version of it, but index optimization may  not be possible with it (I forget).

    With DB2 10.1 and above you may consider to  compress affected tables (with the right DB2 Edition).

    Updated on 2013-09-04T11:15:45Z at 2013-09-04T11:15:45Z by Zahni
  • shivrajk
    shivrajk
    3 Posts

    Re: Joins affecting query performance

    ‏2013-09-04T12:19:46Z  
    • Zahni
    • ‏2013-09-04T11:12:54Z

    Hi,

    use db2advis (look in the Info  Center) or  use the  DB2 Optim Query Workload Tuner (Data Studio Plugin).

    With the  Data Studio, you may  found  a  free Version of it, but index optimization may  not be possible with it (I forget).

    With DB2 10.1 and above you may consider to  compress affected tables (with the right DB2 Edition).

    Thanks Zahni. I did use Query Workload Tuner to tune the query prior posting here. It suggested me few runstats . However, I am curious whether enabling AUTO_RUNSTATS when query is running ,does affect its performance ?

    Updated on 2013-09-04T12:20:32Z at 2013-09-04T12:20:32Z by shivrajk
  • Zahni
    Zahni
    16 Posts

    Re: Joins affecting query performance

    ‏2013-09-04T13:40:35Z  
    • shivrajk
    • ‏2013-09-04T12:19:46Z

    Thanks Zahni. I did use Query Workload Tuner to tune the query prior posting here. It suggested me few runstats . However, I am curious whether enabling AUTO_RUNSTATS when query is running ,does affect its performance ?

    AUTO_RUNSTATS should update saved statistic profiles.

    What is your  current environment  and DB2 fix  level ?

  • shivrajk
    shivrajk
    3 Posts

    Re: Joins affecting query performance

    ‏2013-09-04T14:13:45Z  
    • Zahni
    • ‏2013-09-04T13:40:35Z

    AUTO_RUNSTATS should update saved statistic profiles.

    What is your  current environment  and DB2 fix  level ?

    I am using db2 v97 fp5 on unix environment

  • Zahni
    Zahni
    16 Posts

    Re: Joins affecting query performance

    ‏2013-09-05T11:35:08Z  
    • shivrajk
    • ‏2013-09-04T14:13:45Z

    I am using db2 v97 fp5 on unix environment

    You may  test a  later fixpack.

    Please search  for JOIN at  http://www-01.ibm.com/support/docview.wss?uid=swg21412438

    Other possible impacts:

    slow I/O  subsystem, buffer pools too short, play with the  max query degree DB  setting ( on systems with intra_parallel = YES).