Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Question Unanswered: DB2 query - can it written in a different way ??

    Hi Guys

    I have a query which i wrote for my development work.
    I just wanted to know can this query write in a different way, which improve the performance
    This takes lot of time when i execute this in Production environment

    I have DB2 v10.5.0.2 Fix pack 2

    If you can help that will be grate

    SELECT
    Document.docId AS docId,
    Document.status AS status,
    Document.description AS description,
    Document.createTimestamp AS createTimestamp,
    Organization.name AS orgName,
    Organization.orgId AS orgId,
    Document.descriptiveStatus AS descriptiveStatus,
    Document.modifytimestamp AS modifytimestamp,
    Document.folderId AS folderId
    FROM
    Document,
    Folder,
    RegistrationLink,
    Organization
    WHERE
    Document.folderId = folder.folderId
    AND Folder.folderId = registrationLink.folderId
    AND Document.docType = 'RegistrationApplication'
    AND Document.isLatest = 'Y'
    AND Document.status = 'Active'
    AND RegistrationLink.ORGID = ORGANIZATION.ORGID
    AND Folder.folderId NOT IN (
    SELECT
    DISTINCT Folder.folderId AS folderId
    FROM
    Document,
    Folder,
    RegistrationLink
    WHERE
    Folder.folderId = RegistrationLink.folderId
    AND Document.folderId = Folder.folderId
    AND Document.isLatest = 'Y'
    AND Document.docType = 'OfacRegistrationDocument'
    )
    AND (
    Document.createTimestamp >= '2007-01-01 00:00:00.001'
    )
    AND (
    Document.createTimestamp <= '2014-05-16 23:59:59.999'
    )
    ORDER BY
    Document.modifyTimestamp DESC
    Last edited by ai_zaviour; 05-22-14 at 14:21.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    get rid of the NOT IN. NOT IN/IN should not be used with subselects. Instead use NOT EXISTS/EXISTS. Reason being the exists will finish as soon as a single occurance is found or not found, where as the IN resultset will have to be materialized sorted and then compared. So, this:
    Code:
    AND Folder.folderId NOT IN (
    SELECT
    DISTINCT Folder.folderId AS folderId 
    FROM
    Document,
    Folder,
    RegistrationLink 
    WHERE
    Folder.folderId = RegistrationLink.folderId 
    AND Document.folderId = Folder.folderId 
    AND Document.isLatest = 'Y' 
    AND Document.docType = 'OfacRegistrationDocument'
    )
    would become:
    Code:
    AND Folder.folderId NOT EXISTS (
    SELECT
    1 
    FROM
    Document
    WHERE Document.folderId = Folder.folderId 
    AND Document.isLatest = 'Y' 
    AND Document.docType = 'OfacRegistrationDocument'
    )
    This makes the subselect correlated and you only look for your current folderid.

    Other than that there are no other suggestions any of us can provide as you do not provide us with the table and index DDL. I would think you need some indexing help as well. Lastly, I don't know how fast you expect it to be as you are looking thru 7 years worth of data. The active flag and timestamp may help if the majority of your documents are no longer active.
    Dave

  3. #3
    Join Date
    Sep 2011
    Posts
    85
    Thanx for the quick reply Dave

    But im gettig following error when i add the change you suggested

    An unexpected token "EXISTS" was found following "Folder.folderId NOT". Expected tokens may include: "BETWEEN".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.61.75 

    More exceptions ... An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-104", SQLSTATE "42601" and message tokens "EXISTS|Folder.folderId NOT|BETWEEN".. SQLCODE=-727, SQLSTATE=56098, DRIVER=3.61.75

    considering this is the correct change


    SELECT
    Document.docId AS docId,
    Document.status AS status,
    Document.description AS description,
    Document.createTimestamp AS createTimestamp,
    Organization.name AS orgName,
    Organization.orgId AS orgId,
    Document.descriptiveStatus AS descriptiveStatus,
    Document.modifytimestamp AS modifytimestamp,
    Document.folderId AS folderId
    FROM
    Document,
    Folder,
    RegistrationLink,
    Organization
    WHERE
    Document.folderId = folder.folderId
    AND Folder.folderId = registrationLink.folderId
    AND Document.docType = 'RegistrationApplication'
    AND Document.isLatest = 'Y'
    AND Document.status = 'Active'
    AND RegistrationLink.ORGID = ORGANIZATION.ORGID
    AND Folder.folderId NOT EXISTS (
    SELECT
    1
    FROM
    Document
    WHERE Document.folderId = Folder.folderId
    AND Document.isLatest = 'Y'
    AND Document.docType = 'OfacRegistrationDocument'
    )
    AND (
    Document.createTimestamp >= '2007-01-01 00:00:00.001'
    )
    AND (
    Document.createTimestamp <= '2014-05-16 23:59:59.999'
    )
    ORDER BY
    Document.modifyTimestamp DESC

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see the syntax of EXISTS predicate

    EXISTS predicate

    Code:
    >>-EXISTS--(fullselect)----------------------------------------><
    in EXISTS predicate - IBM DB2 9.7 for Linux, UNIX, and Windows

    and search condiion
    Code:
    search-condition
    
    |--+-----+--+-predicate--+-------------------------------+-+---->
       '-NOT-'  |            '-SELECTIVITY--numeric-constant-' |   
                '-(search-condition)---------------------------'
    in Search conditions - IBM DB2 9.7 for Linux, UNIX, and Windows


    and compare with your code
    AND Folder.folderId NOT EXISTS (
    SELECT

    The error message you got might be also suggestive.
    An unexpected token "EXISTS" was found following "Folder.folderId NOT". Expected tokens may include: "BETWEEN".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.61.75

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Code:
    AND Folder.folderId NOT EXISTS (
    I just did quick cut and paste earlier. that line should not be as it is.
    Dave

Posting Permissions

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