how to optimize this query?

Question

hi!

The query is:

select employee_id
       , last_name
       , salary
       , round((salary+(salary*0.15)), 0) as  "NewSalary"
       , (round((salary+(salary*0.15)), 0) - salary) as “IncreaseAmount” 
from employees;

Can I optimize this round((salary+(salary*0.15)), 0) part in anyway, so that it doesn't appear twice? I tried giving it an alias but didn't work :(

Answer 1

To do the calculation once, do this:

SELECT employee_id, 
       last_name, 
       salary, 
       NewSalary, 
       (NewSalary - salary) as “IncreaseAmount” 
FROM (Select employee_id,
             last_name,
             salary,
             round(salary*1.15, 0) as NewSalary
      FROM employees)

You can't use an alias as a neighbor in a select, but you can alias it in a nested select and use the result of that view twice, but more efficiently since it only does the calculation once.

Answer 2

Did you try ROUND(salary*1.15) ?

Answer 3

The best optimisation would be to simply remove the IncreaseAmount from the query.

You don't need to return the difference between the old and new salary from the database query, it can easily be calculated if it's needed. Having the calculation in the query only means that you do the calculation even if it's not needed, and that the query result gets larger than it has to be.

Answer 4

You can not use column aliases at the same level.

Using subquery as Nick Craver suggests will bring its own penalty (execution times end up being comparable, so the optimization is questionable).

Making a view with computed columns might optimize it a bit, but not substantially (again comparable times).

If (select) performance is really that important you'd have to denormalize and write the new salary down somewhere and then maintain integrity through triggers or application logic layer.