How can I tweak this SQL to perform the arithmetic calculation only once

Question

Say I have a query like this:

   select ((amount1 - amount2)/ amount1) as chg from t1
   where
        ((amount1 - amount2)/ amount1) > 1 OR
        ((amount1 - amount2)/ amount1) < 0.3

Is there a way I can perform the arithmetic calculation only once instead of doing it thrice as in the above query ?

EDIT: I am not sure if the database automatically optimizes queries to do such calcs only once? I am using T-SQL on Sybase.

Answer 1

This should work I think:

select ((amount1 - amount2)/ amount1) as chg from t1
where
     chg > 1 OR
     chg < 0.3

EDIT: After a small test I found out this does not work: Invalid column name 'chg'. Chris' answer is a better solution.

Answer 2

Conceptually you could select from your calculation, i.e:

select
    calc.amt
from
    (select (amt1 - amt2) / amt1 as amt from #tmp) calc
where
    calc.amt > 1 OR calc.amt < 0.3

Not sure off the top of my head if SQL would optimise your code to similar anyway - running your query against my query off a basic temp table seems to indicate they execute in the same way.

Answer 3

It's better to have repeated arithmetic operation than more IO

-- ADDED LATER

I've checked all 3 solutions in SQL Server 2000, 2005 and 2008 (over 100,000 random rows), and in all cases they have exactly the same execution plans as well as CPU and IO usage.

Optimizer does good job.

select calc.amtpercent
  from ( select (amount1-amount2)/cast(amount1 as float) as amtpercent from z8Test) calc
 where calc.amtpercent > 1 OR calc.amtpercent < 0.3

select (amount1-amount2)/cast(amount1 as float)  as amtpercent
  from z8Test
 where (amount1-amount2)/cast(amount1 as float)  > 1
    or (amount1-amount2)/cast(amount1 as float)  < 0.3

select (amount1-amount2)/cast(amount1 as float)  as amtpercent
  from z8Test
 where (amount1-amount2)/cast(amount1 as float)  not between 0.3 and 1

Answer on Alex comment: Have you ever worked with databases? I've seen so many times bottlenecks in IO and never in CPU of database server. To be more precise, in few occasions when I had high CPU usage it was caused by IO bottlenecks and only in two cases because of queries that wrongly used encryption techniques (instead of encrypting parameter value and compare against column in huge table query was decrypting column and comparing against parameter) in the first and the awful number of unnecessary conversions (datetime to string and back to datetime) in second case for a query that was triggered really frequent.

Of course you have to avoid unnecessary arithmetic, but be careful if you have to increase IO as a trade off.

Answer 4

SQL is not an imperative language, its a declarative language. You should not approach SQL with a C style optimization mindframe. For instance in your query the repeat of computation, if it occurs, is irelevant compared to the fact that your query is not SARGable. If this kind of search is relevant for your application performance, It would be much more appropriate for an RDBMS to create a computed column with the expression ((ammount1-ammount2)/ammount1) at the table level and index that column, then rewrite your query to use the computed column:

ALTER TABLE t1 ADD chg AS ((ammount1-ammount2)/ammount1);
CREATE INDEX idx_t1_chg ON t1(chg);
GO

SELECT chg FROM t1 WHERE chg > 1 OR chg < 0.3;
GO