Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Unanswered: Tuning a DB2 query

    Hi,
    I have the below query which looks odd to me. I am trying to give it a good shape in terms of performance and reducing the no. of sub queries.


    SELECT SHP_PT_ID FROM TAB1
    WHERE SHP_PT_TYP_CD = 'SSC'
    AND LOC_ID IN ( SELECT LOC_ID FROM TAB2
    WHERE LOC_RLTNSP_TYP_CD = 'SSCRST'
    AND RLTD_LOC_ID IN ( SELECT LOC_ID FROM TAB1 WHERE FCILTY_NB = 2910)
    );

    Any pointers will be helpful..

    Thanks
    MPasha

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I suspect that regardless of whether you use sub-queries or joins, DB2 will re-write the query in the most optimal form for you. Just make sure you have the right indexes set up, and that you have run runstats.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2009
    Posts
    3
    Thanks for your reply Marcus. I was curious because I am using the same table in a separate subquery again.

    If you look at the query, the data I need is from a table(TAB1) which should satisfy a condition in another table(TAB2) which in turn satisfy a different condition in the first table(TAB1). To achieve this, I am using the first table separately in the 2nd sub query, which appears a little dirty/odd to me and If the table is huge, then my query will either DIE or Kill DB2!!!

  4. #4
    Join Date
    May 2003
    Posts
    113
    Quote Originally Posted by pasha@us
    Hi,
    I have the below query which looks odd to me. I am trying to give it a good shape in terms of performance and reducing the no. of sub queries.


    SELECT SHP_PT_ID FROM TAB1
    WHERE SHP_PT_TYP_CD = 'SSC'
    AND LOC_ID IN ( SELECT LOC_ID FROM TAB2
    WHERE LOC_RLTNSP_TYP_CD = 'SSCRST'
    AND RLTD_LOC_ID IN ( SELECT LOC_ID FROM TAB1 WHERE FCILTY_NB = 2910)
    );

    Any pointers will be helpful..

    Thanks
    MPasha
    Q1: REMOVE THE SUB-Q which contains TAB1
    SELECT SHP_PT_ID FROM TAB1
    WHERE SHP_PT_TYP_CD = 'SSC'
    AND LOC_ID IN ( SELECT LOC_ID FROM TAB2
    WHERE LOC_RLTNSP_TYP_CD = 'SSCRST'
    AND RLTD_LOC_ID = LOC_ID AND FCILTY_NB = 2910);

    Q2: REMOVE THE SUB-Q which contains TAB2
    SELECT SHP_PT_ID FROM TAB1, TAB2
    WHERE SHP_PT_TYP_CD = 'SSC'
    AND LOC_ID = TAB2. LOC_ID AND TAB2.LOC_RLTNSP_TYP_CD = 'SSCRST'
    AND RLTD_LOC_ID = LOC_ID AND FCILTY_NB = 2910;

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Nidm,
    I think you might have to look a bit closer at what you gave Pasha, as I am pretty sure it will not return the correct results.

    Pasha,
    Go along with info that Marcus has given you already. The proper indexes should help quite a bit, try using index advisor. Also, the performance will depend greatly on how restrictive the 2 subqueries are. If they return thousands of values, then of course your performance will be a concern. Those items should be the concern rather than how many subqueries you have written in your SQL. Also, I can only see you getting rid of one of those subqueries, but it still involves accessing tab1 twice and the query rewrite is probably already being done by DB2.

    Dave

  6. #6
    Join Date
    Apr 2009
    Posts
    3
    Thanks for your time folks.
    Nidm-
    I am afraid, I had already tried the options you gave and they wont work as the values in the cols are not directly related. Thats why there is no relation between the first tab1 query and 2nd TAB1 sub-query.

    dav1mo - I agree with you and marcus, but something in me still tells that this is not the way I should write a query, which means there is much better a way(I am not sure what )

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by pasha@us
    dav1mo - I agree with you and marcus, but something in me still tells that this is not the way I should write a query, which means there is much better a way(I am not sure what )
    Do an explain and look at the DB2 query re-write.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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