Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2013
    Posts
    2

    Unanswered: What would be better for performance

    Hi all,
    what type of SQL would be better from perfromance point of view

    SELECT id FROM TABLE WHERE (KEY1 = 'ABC' AND NUM <> 5)
    SELECT id FROM TABLE WHERE (KEY1 = 'ABC' AND NUM bettwen 1 and 4)
    SELECT id FROM TABLE WHERE (KEY1 = 'ABC' AND NUM != 5)

    i went though several books and pages (where are covered predictions, Stage1, Indexable and etc.) but still not sure what would be the best?

    Can you share you opinion and considerations?

    P.s.
    here are some of the references
    http://www.neodbug.org/201108/Top25TuningTips.pdf
    http://www.quest.com/whitepapers/10_SQL_Tips.pdf
    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

  2. #2
    Join Date
    Dec 2013
    Posts
    2

    Question What would be better for performance

    Hi all,
    what type of SQL would be better from perfromance point of view

    SELECT id FROM TABLE WHERE (KEY1 = 'ABC' AND NUM <> 5)
    SELECT id FROM TABLE WHERE (KEY1 = 'ABC' AND NUM bettwen 1 and 4)
    SELECT id FROM TABLE WHERE (KEY1 = 'ABC' AND NUM != 5)
    SELECT id FROM TABLE WHERE (KEY1 = 'ABC' AND NUM > 0 and NUM < 4)

    i went through several books and pages (where are covered predictions, Stage1, Indexable and etc.) but still not sure what would be the best?

    I'm checking all these variants on my test DB2 v 9.7 fp8 but according to the "Access plan" all above are the same!

    Can you share you opinion and considerations?

    P.s.
    here are some of the references
    http://www.neodbug.org/201108/Top25TuningTips.pdf
    http://www.quest.com/whitepapers/10_SQL_Tips.pdf
    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    all depends : size of table - indexes - distinct values by column in index - type of predicate
    db2advis can help with recommendations on existing tables
    cost is no always to response time but = cost of execution
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by intcomds View Post
    SELECT id FROM TABLE WHERE (KEY1 = 'ABC' AND NUM <> 5)
    SELECT id FROM TABLE WHERE (KEY1 = 'ABC' AND NUM bettwen 1 and 4)
    These queries are not equivalent, so it's meaningless to compare their performance.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The third one is syntactically incorrect (!= is not valid, use <>). Assuming they all return the same result set, then they all may perform the same. If not, then the second will perform better.

    Andy

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    threads merged.
    OP's were flagged for moderation, two posts were then approved.
    apologies for any confusion
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by ARWinner View Post
    The third one is syntactically incorrect (!= is not valid, use <>).
    I like to use != in my simple queries, works ok.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I learned something new today... Cool.

    Andy

  9. #9
    Join Date
    Nov 2011
    Posts
    334
    hi, intcomds
    plz give the detailed access plan ( the plan generated by db2exfmt) for these 4 sqls。

  10. #10
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    The best SQL is to tell DB2 what you want and not what you do not want.
    So if you can avoid "not" it is always the best.

    As a programmer you should know your data. So when you know that NUM can be 1 to 5 (and db2 does not because you did not code a constraint) then the between 1 and 4 clause is the best. When you as sql-coder do not know which values could be present and you want everything but 5.... yeah... in that case it cannot be avoided and you'll have to code what you do not want <sigh>.

    The correct resultset is always far more important that efficient SQL, right?
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

Tags for this Thread

Posting Permissions

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