Results 1 to 3 of 3

Thread: Query Help

  1. #1
    Join Date
    Jun 2009
    Posts
    272

    Unanswered: Query Help

    Can some one please look in to below

    WorkType is a VARCHAR in the below query

    Is it valid to evaluate using <> operator for checking not equal to condition ? Will it cause any performance degradation ? Any other ways to write the below condition for worktype instead ?



    Query:

    SELECT count(*) AS "Count" from {CLASS:Zur-Center-NA-US-WORK}

    WHERE WORKTYPE <> 'ClaimFile'

    AND WORKTYPE <> 'ClaimUnit'

    AND RESOLVED IS NULL

    AND PYSLADEADLINE <= {MyMetrics.MyMetricsDate}

    AND ASSIGNEDTO = {FilterPage.ASSIGNEDTO}

    AND CLAIMFILEID IS NOT NULL

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    The comparison operator "<>" is valid SQL.

    It is typically less performant than an equality comparison (it is usually faster to search for X than "anything except X").

    There are always other ways to write queries , there is rarely exactly one way.

    Sometimes the DB2 optimizer rewrites the queries dynamically. You can use the db2 explain facilities to view the access-plan and the actual query (after any rewrite).

    Actual run time performance of your query will depend on many factors (db2-version, operating-system that runs the DB2 server, query optimization level and registry settings, indexes and other performance structures, accurate recent statistics for tables and indexes, bufferpool sizes, cardinalities and many other factors).

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you can also write that first portion as:
    Code:
    WHERE WORKTYPE NOT IN ('ClaimFile' , 'ClaimUnit')
    This make the query a little neater and easier to maintain if you have to make alterations in the future, such as adding more values to the list.
    p.s. I think you may want an index that has at minimum the leading column of ASSIGNEDTO.
    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
  •