Simple efficiency question

[whwar9739]

Which would be faster? I know that using an 'or' can slow down the performance of a for each but would it be significant enough to make the second option faster? Note: field is defined as a non-unique key.

For each db-table
   where db-table.field = 1
        or db-table.field = 2:

or

for each db-table:
   if db-table.field <> 1
    and db-table.field <> 2
   then next.

 

[rpigeyre]

If you've got an index on field, i think that this is faster :

For each db-table
where db-table.field = 1:

For each db-table
where db-table.field = 2:

It depends of the amount of data in your db-table.

 

[whwar9739]

Yes i thought of that too. I was hoping to avoid replication of programming. I know I could use a procedure or include, but i would still like to avoid that.

 

[MaximMonin]

Yes i thought of that too. I was hoping to avoid replication of programming. I know I could use a procedure or include, but i would still like to avoid that.

Use dynamic queries instead.

 

[TomBascom]

Test it. You might be surprised.

OR was a big problem in the v6 days.

It is not so big a problem these days.

Functions have always been a problem in WHERE clauses. User defined functions only get evaluated once. Built in functions are evaluated against every record and force a table scan.

Test it your alternatives