Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: SQL Performance problem - 'not in' and subselect

    Hi,

    I have a performance problem with bellow SQL. The problem lies in 'not in' and subselect part of the SQL. If I remove the subselect, the SQL executes within 1 secound. With subselect it takes more then 10 secounds.

    Is there any other way to write the SQL to remove undesired recourds? How to rewrite SQL to remove 'not in' and subselect?

    SELECT
    DB2ADMIN.PLAN.DATUM,
    DB2ADMIN.PLAN.IZMENA,
    DB2ADMIN.PLAN.DELNAL,
    DB2ADMIN.DELNAL.ZLITINA,
    CAST(NULL AS CHAR(4)) AS OZLITINA,
    SUBSTR(DB2ADMIN.DELNAL.DIMENZIJ,1,13)DIMENZIJA,
    DB2ADMIN.DELNAL.DOLZINA,
    DB2ADMIN.PLAN.SARZA,
    DB2ADMIN.PLAN.OKOLKG,
    DB2ADMIN.PLAN.OSTFOR,
    DB2ADMIN.NAROC.POREKLO,
    DB2ADMIN.HOMPREGLED.DATPLANHOM,
    DB2ADMIN.HOMPREGLED.PEC
    FROM
    DB2ADMIN.DELNAL,
    DB2ADMIN.NAROC,
    DB2ADMIN.PLAN
    LEFT OUTER JOIN
    DB2ADMIN.HOMPREGLED ON
    ( DB2ADMIN.PLAN.DELNAL = DB2ADMIN.HOMPREGLED.DELNAL AND
    DB2ADMIN.PLAN.SARZA = DB2ADMIN.HOMPREGLED.SARZA)
    WHERE
    DB2ADMIN.DELNAL.DELNAL = DB2ADMIN.PLAN.DELNAL AND
    DB2ADMIN.DELNAL.VARDN = SUBSTR(DB2ADMIN.PLAN.POZVAR,1,1) AND
    DB2ADMIN.NAROC.DELNAL = DB2ADMIN.DELNAL.DELNAL AND
    DB2ADMIN.PLAN.LINIJA = 'J' AND
    DB2ADMIN.PLAN.INDKONCA <> 'P' AND
    DB2ADMIN.PLAN.INDKONCA <> 'D' AND
    DB2ADMIN.DELNAL.FORMAT = 'D' AND
    (DB2ADMIN.DELNAL.DELNAL, DB2ADMIN.DELNAL.VARDN, DB2ADMIN.PLAN.SARZA)
    NOT IN (SELECT DELNAL, VARDN, SARZA FROM DB2ADMIN.RUNDA)

    Thanks,
    Grofaty

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    How many rows are in DB2ADMIN.RUNDA? There is no WHERE clause on the subselect, so it reads all the rows in DB2ADMIN.RUNDA.
    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
    Jan 2003
    Posts
    1,605
    Hi,

    In DB2ADMIN.RUNDA are 27307 records.

    I know there is no WHERE clause on the subselect. I would like to exclude all records from main select that are included in subselect.

    Thanks,
    Grofaty

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would try this:

    WHERE
    DB2ADMIN.DELNAL.DELNAL = DB2ADMIN.PLAN.DELNAL AND
    DB2ADMIN.DELNAL.VARDN = SUBSTR(DB2ADMIN.PLAN.POZVAR,1,1) AND
    DB2ADMIN.NAROC.DELNAL = DB2ADMIN.DELNAL.DELNAL AND
    DB2ADMIN.PLAN.LINIJA = 'J' AND
    DB2ADMIN.PLAN.INDKONCA <> 'P' AND
    DB2ADMIN.PLAN.INDKONCA <> 'D' AND
    DB2ADMIN.DELNAL.FORMAT = 'D' AND
    NOT EXISTS
    (SELECT 1 FROM DB2ADMIN.RUNDA
    WHERE DB2ADMIN.RUNDA.DELNAL = DB2ADMIN.DELNAL.DELNAL AND
    DB2ADMIN.RUNDA.VARDN = DB2ADMIN.DELNAL.VARDN AND
    DB2ADMIN.RUNDA.SARZA = DB2ADMIN.DELNAL.SARZA)

    But I prefer to create table qualifiers like A, B, C, etc after the table names in the FROM clause, and then use them for table name qualification in the rest of the SQL.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Thanks Markus_A. But your SQL doesn't work.

    You have probably overlooked my sintax in "before" subselect tables. There are two different tables. Please see the bolded text.

    Thanks,
    Grofaty

    My sql
    WHERE
    DB2ADMIN.DELNAL.DELNAL = DB2ADMIN.PLAN.DELNAL AND
    DB2ADMIN.DELNAL.VARDN = SUBSTR(DB2ADMIN.PLAN.POZVAR,1,1) AND
    DB2ADMIN.NAROC.DELNAL = DB2ADMIN.DELNAL.DELNAL AND
    DB2ADMIN.PLAN.LINIJA = 'J' AND
    DB2ADMIN.PLAN.INDKONCA <> 'P' AND
    DB2ADMIN.PLAN.INDKONCA <> 'D' AND
    DB2ADMIN.DELNAL.FORMAT = 'D' AND
    (DB2ADMIN.DELNAL.DELNAL, DB2ADMIN.DELNAL.VARDN, DB2ADMIN.PLAN.SARZA)
    NOT IN (SELECT DELNAL, VARDN, SARZA FROM DB2ADMIN.RUNDA)

    Repaired according with your tip
    WHERE
    DB2ADMIN.DELNAL.DELNAL = DB2ADMIN.PLAN.DELNAL AND
    DB2ADMIN.DELNAL.VARDN = SUBSTR(DB2ADMIN.PLAN.POZVAR,1,1) AND
    DB2ADMIN.NAROC.DELNAL = DB2ADMIN.DELNAL.DELNAL AND
    DB2ADMIN.PLAN.LINIJA = 'J' AND
    DB2ADMIN.PLAN.INDKONCA <> 'P' AND
    DB2ADMIN.PLAN.INDKONCA <> 'D' AND
    DB2ADMIN.DELNAL.FORMAT = 'D'AND NOT EXIST
    (SELECT 1 FROM DB2ADMIN.RUNDA
    WHERE DB2ADMIN.RUNDA.DELNAL = DB2ADMIN.DELNAL.DELNAL AND
    DB2ADMIN.RUNDA.VARDN = DB2ADMIN.DELNAL.VARDN AND
    DB2ADMIN.RUNDA.SARZA = DB2ADMIN.PLAN.SARZA)

    Error message:
    SQL0104N An unexpected token "DB2ADMIN.PLAN.DATUM, DB2ADMIN.PLAN.IZMENA,
    DB2ADMIN.PLAN.DEL" was found following "SELECT ". Expected tokens may include:
    "<space>". SQLSTATE=42601
    Last edited by grofaty; 03-24-04 at 07:40.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    'NOT EXISTS' and not 'NOT EXIST'

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Thank you very much sathyaram_s. My typo... I rewrite the SQL according to the sathyaram_s and Marcus_A suggestions and it works fine. I have droped SQL execution time from 10 secounds down to 2.7 secounds.

    I have found the same tip Marcus_A gave me at "SQL Performance and Tuning" from Penny Bowman and Rick McClendon Microsoft PowerPoint presentation in page 41. At this link are more SQL peformance tips...

    Thanks,
    Grofaty

  8. #8
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    A quick point...

    It's not really the EXISTS that is giving the perfomance benefit, it's the correlation that using EXISTS allows. It is possible to use EXISTS without correlation but you would see no better performance than using IN (or NOT IN) as you would find that the subquery would usually run to completion.

  9. #9
    Join Date
    Jan 2003
    Posts
    1,605
    Hi Damian Ibbotson,

    I agree with you. From "SQL Performance and Tuning" link above at page 40 it states:

    For Subquery - when using negation logic:
    Use NOT Exists: DB2 tests non-existence
    Instead of NOT IN: DB2 must materialize the complete result set

    Grofaty

  10. #10
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    I agree with you. From "SQL Performance and Tuning" link above at page 40 it states:

    For Subquery - when using negation logic:
    Use NOT Exists: DB2 tests non-existence
    Instead of NOT IN: DB2 must materialize the complete result set
    That said, it is possible to correlate an IN type query in the same way you would an EXISTS query. The commonly held belief is that EXISTS will return true as soon as it finds a match but this is only true when the query is correlated.

    It would be interesting to see how a correlated IN type query performs against a correlated EXISTS type query given that the correlation would presumably, in both instances prevent the complete materialisation of the result set in the subquery.

    I've never seen such a comparison, so until then I'll assume that EXISTS is the way to go.

    Damian

  11. #11
    Join Date
    Jan 2003
    Posts
    1,605
    Hi Damian Ibbotson,

    That is excellent idea!!!

    I have rewritten SQL from EXISTS to NOT IN and include join (see SQL bellow).

    I have tested both SQLs many times (more then 10 times) and in my case I got result: NOT IN is faster for 0.5 secound. So in my case EXISTS is slower for 0.5 secounds.

    Damian, your teory is very good!!!


    NOT IN with join:
    WHERE
    DB2ADMIN.DELNAL.DELNAL = DB2ADMIN.PLAN.DELNAL AND
    DB2ADMIN.DELNAL.VARDN = SUBSTR(DB2ADMIN.PLAN.POZVAR,1,1) AND
    DB2ADMIN.NAROC.DELNAL = DB2ADMIN.DELNAL.DELNAL AND
    DB2ADMIN.PLAN.LINIJA = 'J' AND
    DB2ADMIN.PLAN.INDKONCA <> 'P' AND
    DB2ADMIN.PLAN.INDKONCA <> 'D' AND
    DB2ADMIN.DELNAL.FORMAT = 'D' AND
    (DB2ADMIN.DELNAL.DELNAL, DB2ADMIN.DELNAL.VARDN, DB2ADMIN.PLAN.SARZA)
    NOT IN (SELECT A.DELNAL, A.VARDN, A.SARZA FROM DB2ADMIN.RUNDA A
    WHERE A.DELNAL=DB2ADMIN.DELNAL.DELNAL AND A.VARDN=DB2ADMIN.DELNAL.VARDN AND A.SARZA=DB2ADMIN.PLAN.SARZA)

  12. #12
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Ahhh... but maybe the result was buffered from previous executions!

  13. #13
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Yes you are right, the results was probably buffered, but I don't care! This SQL will be executed in near future many times per day, so the results in production environment will probably be buffered.

    My test was executing SQL 10 times (first NOT IN sql then EXISTS sql). NOT IN was always better no mather of buffering.

    Thanks for help,
    Grofaty

  14. #14
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Originally posted by grofaty
    Hi,

    Yes you are right, the results was probably buffered, but I don't care! This SQL will be executed in near future many times per day, so the results in production environment will probably be buffered.

    My test was executing SQL 10 times (first NOT IN sql then EXISTS sql). NOT IN was always better no mather of buffering.

    Thanks for help,
    Grofaty
    Yes. The problem with NOT IN and NOT EXISTS is that by definition they involve a full table/index scan. I assume that the performance increase must solely be down to the actual materialisation of the result set! IN and EXISTS is a different story and this is where I would really like to see some kind of comparison.

    Damian

Posting Permissions

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