0

I have a DB2 SELECT query which takes a very long time to complete. Is there a much simpler way to achieve the same result?

Select count(*) 
from leaveprocess,processlog,leave_type, user_master 
where  leaveprocess.process_id = processlog.process_id 
and user_master.user_id = leaveprocess.create_id  
and leaveprocess.leavetype_id = leave_type.serial_no  
and leaveprocess.leave_cust_id=315
and  user_master.user_id!=0 
and user_master.location_id in (2412,2422,2416,2410,2436,2401)  
and user_master.user_id in (
    select employee_id from employee where employee_cust_id=1558 and company_id in (178)
) 
and leaveprocess.leave_id!=0.

There are approx 60,000 records of employee, but when I remove country_id check (company_id in (178)) then it working very fast and properly.

Are there some suggestions for improving the speed and optimization of my DB2 Query?

ﾤ ﾡ
  • 5,669
  • 6
  • 43
  • 57
rawat0157
  • 13
  • 1
  • 1
  • 10

2 Answers2

1

Without seeing the Explanation of your plan (see here for more details on EXPLAIN) or any table/index definitions, it's hard to say for sure.

However, since you said that it's fast without the company_id field in the subquery, I'm guessing that you need an index to cover that on the employee table. Probably something like employee_cust_id, company_id, employee_id.

Also, you don't need to write it as a subquery, you can write it as a JOIN (I converted your comma joins to "real" joins):

SELECT count(*) 
FROM leaveprocess l
JOIN processlog p
  ON l.process_id = p.process_id 

JOIN leave_type lt
  ON l.leavetype_id = lt.serial_no  

JOIN user_master um 
  ON um.user_id = l.create_id  

JOIN employee e
  ON e.employee_id = um.user_id

where l.leave_cust_id = 315
  and um.user_id     <> 0 
  and l.leave_id     <> 0
  and um.location_id in (2412,2422,2416,2410,2436,2401)  

  and e.employee_cust_id=1558 
  and e.company_id in (178)
Community
  • 1
  • 1
bhamby
  • 15,112
  • 1
  • 45
  • 66
  • Just to reiterate, explicit joins are preferred. They make it easier to see what you are doing. It's easy to screw up an implicit join; thus turning it into a cross join. The ANSI SQL Standard calls for explicit joins. – Charles Dec 29 '15 at 17:02
  • @bhamby I tried according to your suggestion but nothing is improve . It still taking too long time – rawat0157 Dec 30 '15 at 04:15
  • @rawat0157 what did you try? the statement changes? Or the index? It's hard to give some more suggestions without further details (table structures, indexes, row counts, an explain). – bhamby Dec 30 '15 at 04:42
  • @bhamby I changed the query, used join instead of subquery. And also create the index for employee table on the following columns(employee_id, employee_cust_id, company_id) – rawat0157 Dec 30 '15 at 05:59
  • @bhamby Employee table have 95 columns and more than 2,00,000 records. there are also many indexes basis on different columns on employee table – rawat0157 Dec 30 '15 at 06:08
0

I highly suggest using the advice mentioned in the above post, but I'm confused why you are using e.company_id in (178) . The IN operator is typically used to specify multiple values. You are only looking for e.company_id 178, so perhaps setting it to and e.company_id = 178 could provide a more efficient query as well. Just a thought.

robblob
  • 9
  • 3