Db2 – Issue with one query – can some Index help to make it faster

db2performancequery-performanceselect

I am working on some IBM web application and it has one query hard coded in some part of the application which I cannot change or optimize – so modification of the SELECT statement is not possible.
This is DB2 database.

Just to mention one important thing that query always returns 0 or 1 ROW!!!

Query is:

select * 
from workorder  
where parent= '682845'  
and siteid= 'NY'   
and woclass in ( 'WORKORDER' ) 
order by wosequence, taskid

Can someone please help me with definition of the proper index for this because this query is slowing the application too much and customer has big complaint.

I am not sure if I create index with three columns PARENT,SITEID,WOCLASS it will help because of the order by on which I do not have influence_
Thanks

Best Answer

A 3-column index on (parent, siteid, woclass) will most probably improve the efficiency of the query.

It will certainly help to avoid the table scan. Most likely, with conditions on 3 columns, the rows that match will be very few or at least a very small percentage of the table and an index seek will be far more efficient than a table scan.

Even better, if the number of rows is small, the required sort will be efficient as well so you won't need to include the order by columns in the index (which may not be an option anyway, as the order by could be arbitrary, for all we know). If the query always returns 1 row, as you say, you certainly don't need the additional columns in the index. Why is there an ORDER BY in the query, if that's the case?

So, test the behaviour (of the query but also the overall behaviour of the applications and other queries) in your production/stage environment after you add the index and if the efficiency gain is good enough, roll the change in production.

(By the way, I'm not 100% sure about DB2 but you don't need to stop anything to add an index in most DBMS, I don't think you'd need it in DB2 either. You might get some locking off course and applications could be interrupted due to the locks, especially if the table is big, and various DBMS have different options to address these side effects. So it might be good to add the index in some "quiet" period, say between 04:00 and 06:00 that you know the database / applications have the least usage.)

Related Question