‘Where Clause’ may kill application performance

31 July 2012 Amjad Hussain Optimization of Query/SP/DB, 0

Regarding query optimization it is observed that in where clause below mentioned cases are often happened. Due to which existing index on filter column is not used by database engine and table is full scan which kills performance. So try to avoid these cases while writing a query. During Optimization of different NFS projects it is observed that most problematic queries having one or multiple below mentioned cases.

Like Index is on due_date column then never use these cases

1. Function usage on filter column in where clause. like where to_char(due_date,’yyyymmdd’)=to_char(v_date,’yyyymmdd’)  the correct is simple where due_Date=v_date
2. Expression with filter column like where due_Date+3=v_date the solution is due_date=v_date-3
3. Expression like to_char(year(due_date))||to_char(month(due_date))=v_year||v_month the solution is due_date=to_date(v_year||v_month||to_char(due_date,’dd’),’yyyymmdd’)

Similar with varchar column

• function usage on filter column upper(name)=upper(v_name) possible solution are
  • allow to save value in upper always
  • use upper() function while creating index on column.
  • Allow to save value in upper, lower or in proper format and use where name=upper(v_name) or name=lower(v_name) or name=proper(

Try to avoid following cases otherwise indexes never be used to filter data. The said solution are some time possible and some time not.

1. Null value filtration in where clauses like where name is null possible solution is use default value on null able columns like ‘—‘ or 0 or ‘1900-01-01’, etc and filter on those values.
2. Logical Not or relevant <> operator in where clause. Like where name not in (‘ABC’,’XYZ’) or name<>’ABC’ possible solution is use in operator with all other values.
3. Wild card as first character in like operator value like where name like ‘%abc’  the solution is, use reverse function to save reverse value in a column and while filtering use like reverse(‘abc’)||’%’

This post was viewed (35) times.

HSIN KUANG Chinese Restaurant, Gulberg, Lahore Multi-tasking: Time Saver or Time Waster?