ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Order of where conditions...

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Order of where conditions...

    Does the order in which I put my conditions in a where clause have any effect on the processing time of an SQL Query?

    Example...

    Code:
    select hord, chbcon, hcust, hcpo from bpcsffg/ech where chbcon like upper('%hospital%') and hcpo = '8' and hcust = 9520 order by hord
    as apposed to...

    Code:
    select hord, chbcon, hcust, hcpo from bpcsffg/ech where hcpo = '8' and hcust = 9520 and chbcon like upper('%hospital%') order by hord
    Will putting the LIKE statement behind my general equality statements speed it up?
    Your future President
    Bryce

    ---------------------------------------------
    http://www.bravobryce.com

  • #2
    Re: Order of where conditions...

    Hi,

    the sequence of the where conditions does not affect the query optimizer's decicions. More over the query optimizer may rewrite your SQL-Statement, adding additional (obivous) information.

    The query optimizer checks all access path (DDS described keyed logical files and SQL Indexes) and evaluates them, in CQE by the estimated number of rows expecten, in SQE by consulting the collected statistics.

    The optimizer checks first all where conditions where the comparative operator is =. Then the IN-Predicate and Like without leading %, between and <=, >= <>.

    In your example the like-predicate will always be the last thing that will be considered, because no index built over this column can be used.

    Birgitta

    Comment


    • #3
      Re: Order of where conditions...

      Both will have equal processing time.....
      Cheers
      PKBUNU

      Comment


      • #4
        Re: Order of where conditions...

        Cool. Sounds great. Its nice that the optimizer takes all the performance guessing out like that. The only reason I asked is because when I looked at my statement, in my head, it looked like it would run slower having the LIKE statement first. Glad that me and the Optimizer are on the same page
        Your future President
        Bryce

        ---------------------------------------------
        http://www.bravobryce.com

        Comment

        Working...
        X