Search code examples
sqldql

Efficiency check in sql query

I'm struggle between 2 ways of execute my mission.

I have set of conditions, and when they are all true - I need to set "true" in an "x" column attribute of that record.

What is more efficient & recommended, and why?

  1. set this column into "false" for all records in the table, and then run another query to set "true" under some conditions.
  2. set "true" if all conditions are "true", then run another query to set "false" on all records where one or more of the conditions fails.

I cannot assume that there is some default value of the "x" column need to be changed, because the query should run also one in a while, when its needed, after initial values were inserted to that column, and some conditions may be changed from the last time I ran this query.

Perhaps there is also another idea, more efficient that the 2 above?

Also I'd like to understand how to calculate efficiently of a query, something similar to the way of efficient calculation in programming.

Solution

  • Probably the most efficient way is to use a case Statement, since you have to evaluate the condition only once per row and also have to modify every row only once, e.g.

    UPDATE tablename SET fieldname = (CASE WHEN conditions THEN 'true' ELSE 'false' END)

    Case Statements are at least available in Oracle and MS-SQL. Dont know about other DB vendors.