CODEWITHSUNDEEP
sqloracle-databaseoracle10g
jest
jest

Reputation: 699

how to optimize this query?

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 :(

Upvotes: 1

Views: 174

Answers (4)

Unreason
Unreason

Reputation: 12704

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.

Upvotes: 0

Nick Craver
Nick Craver

Reputation: 630627

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.

Upvotes: 7

Guffa
Guffa

Reputation: 700840

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.

Upvotes: 1

Ashish Gupta
Ashish Gupta

Reputation: 15139

Did you try ROUND(salary*1.15) ?

Upvotes: 1

Related Questions