Friday, December 23, 2011

Performance Tunings tips : Inlist vs Between

There are lot of questions running around guys who do tuning . Sometimes they do suggest use of temporary tables instead of using large in list values, as the optimizer would go for value in XXX or Value in XXY or Value in XXZ to generate a explain plain.

What if the column compared against large in-list values was part of any index say PI,SI,JI.... ?  Sometimes it so happens that even after using a temp table with list of values you would still get same performance issue, why so? Did you ever consider to use  "between" clause to check if the query performed better ??  Did you...

I would say give it a try to see if this would be much better option compared to standard "temp table " against the in list

Say for example:

SELECT customer_number, customer_name
FROM customer
WHERE customer_number in (1000, 1001, 1002, 1003, 1004);
is much less efficient than:

SELECT customer_number, customer_name
FROM customer
WHERE customer_number BETWEEN 1000 and 1004

We are assuming that an index on customer_number, the Query Optimizer can locate a range of numbers much faster (using BETWEEN) than it can find a series of numbers using the IN clause.

Check for explain to compare difference,  if still the same...  refresh/collect stats on index column it would help.   If still you would find some kind of issue try to find out skewness of column  using following query and try to rectify the issue.

Sel hashamp(hashbucket(hashrow(customer_number))), count(*) from Customer  group by 1;

Over all i would say trying the query with between would be of great help.  But query tuning is such a complex thing you will never get what you want unless you understand the data ;-)