07 Performance Improvement Consideration

Use UNION instead of OR

Consider using UNION when you have to use OR condition on 2 index columns

Example

Select s.orderid, s.salesdate,s. totalamount, s.productname

from saleshistory s  where

s.productname="panasonic" or s.totalamount > 100

instead use

Select s.orderid, s.salesdate,s. totalamount, s.productname

from saleshistory s  where

s.productname='panasonic'

union all

Select s.orderid, s.salesdate,s. totalamount, s.productname

from saleshistory s  where

s.totalamount > 100

Remember this only applies only when both the columns are indexed.

Use UNION ALL instead of UNION

• Avoid un-necessary sorts
• don’t sort if order is not important
• Union performs an extra operation called SORT UNIQUE, which is really expensive

Note that this is recommended when the result set for both the operations are the same.

Select s.orderid, s.salesdate,s. totalamount, s.productname

from saleshistory s  where

s.productname='panasonic'

Union

Select s.orderid, s.salesdate,s. totalamount, s.productname

from saleshistory s  where

s.totalamount > 100

instead use

Select s.orderid, s.salesdate,s. totalamount, s.productname

from saleshistory s  where

s.productname='panasonic'

Union all

Select s.orderid, s.salesdate,s. totalamount, s.productname

from saleshistory s where

s.totalamount > 100

Minimize Table lookups in a query

• Where eve possible try to reduce the number of  table lookups in a query
• this mostly applies to sub queries

Example

Select s.orderid, s.salesdate,s. totalamount, s.productname
from saleshistory s where
s.productID in
(select productID from product where name='mobile')
and
s.productID in
(select productID from product where color='black')
// The product table is used twice for look ups in 2 separate queries

instead use

Select s.orderid, s.salesdate,s. totalamount, s.productname
from saleshistory s where
s.productID in (select productID from product where name='mobile'
and color='black')
// instead use the product table once to do both the lookups

EXIST vs IN & NOT EXIST vs NOT IN

General rule of thumb

• Big Outer Query and small inner Query -> IN
• Small Outer query and big Inner Query ->  EXIST

IN CLAUSE

Small Table  T2  and big table T1

select * from T1 where  x in (select y from T2)

is processed as

select * from t1, (select  distinct y from T2) t2  where t1.x=t2.y;

Exists Clause

Good index on T2.y

Small table T1 and big Table T2

select * from  t1 where exists (select null from t2 where y=x)

is processed as

for x  in (select * from T1)

loop

if (exists(select null from t2 where y=x.x)

then

output the record

end if

end loop

Use Exist Instead of Distinct

When eve you are joining tables with one to many relationship and trying to find DISTINCT values, try to use EXISTS instead of DISTINCT clause.

In the example below product has about 7 rows where as Sales has about a million rows.

SELECT distinct p.product_name

FROM sales a , product p

where p.product_ID=s.product_ID

It has used 3% of cpu, did a hash unique, hash join etc

Better SQL

select p.product_name 

FROM product p

where EXISTS (Select 'x' from sales s where s.product_ID=p.product_ID)

Notice the obvious differences. So when ever you are joining a one to many relationship try to find distinct values using the DISTINCT clause.

Reading same table multiple times

Reading identical data multiple times slows down the query . Why would you query the same table multiple times?

• Used to get detail and summary
• Used to get current and prior data

SELECT s.order_id, s.Sales_date, s. productname, s.total_amount, sr.total_jan_amount
from sales s (select product_name,  sum (totalamount) as total_jan_amount from sales 
where salesdate between '1-1-2011' and '1-1-2012'
Grou by productname) SR
where salesdate between '1-1-2011' and '1-1-2012') SR
where salesdate between '1-1-2011' and '1-1-2012' and
s.productname =sr.productname
order by salesdate

instead use analytic functions which require only one pass through the table and no join

Select orderid, salesdate, productname, totalamount, sum(totalamount) over  (partition by productname) as totalJanamount

FROM saleshistory

 WHERE salesdate between  '1-1-2011' and '1-1-2012'

ORDER BY salesdate

Notice that when you use the analytical function the table and its index column are only used once. Also notice the difference in cost and time. Use analytical functions as much as possible.

Truncate instead of Delete

Whenever we  delete data from table , rollback segments  are used to UNDO information. If you do not commit  the transaction,  Oracle restores the data to the state it was in before the transaction

 

With Truncate, no undo information is generated. Once the table is truncated, the data cannot be restored back. hence its faster and needs fewer resources.

so use, TRUNCATE  rather than DELETE when you need to UNDO information.

For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)

Alternatively click through the link   if you found this article interesting. (This will help  elevate my companies Search engine rankings)