0

Requirement: Number of billing records created Year and provincewise.

Database: DB2 9.5

Below is the query which is used to fetch the details, Its taking huge time, more then 1 hour and getting timedout.

Number of records are as follows in each table. billing : 9 milion cover : 1 milion Customer: 3 milion address: 4 milion.

select year(bill.created), addr.province,count(1) as yearprovicecount  from billing bill 
inner join cover cov  on ( bill.coveragecid = cov.coveragecid) 
inner join customer cust on (cust.customercid= cov.customercid)
inner join address addr on (cust.customercid=addr.customercid)
group by year(bill.created), addr.province;

Created is a timestamp coulmn.

Since there is no where clause in query, The query has become slow. Please let me know if there is any ways I can optimize the query.

Vinayak Dornala
  • 1,609
  • 1
  • 21
  • 27
  • Just by looking at the query you are joining to the customer table unnecessarily, since address has the customercid as well. Try removing this join and see if you get any noticeable performance improvements. – TMNT2014 Jun 16 '14 at 19:34
  • Without additional information – DDL, explain plan, etc. you're not going to get much useful help -- only guesses at potential solutions. – Ian Bjorhovde Jun 16 '14 at 20:37
  • if billing has customercid in it as well, then you might also remove the join to cover. i hope we can assume you have indexes over all the id fields involved. – WarrenT Jun 16 '14 at 20:38
  • Too much is missing. We need more info in order to guess, though there seems to be no point in having the `customer` table in there. We can't tell anything about the results from the `inner joins` since we know nothing about the data relationships. For example, do **all** `billing` rows always match with rows from 'cover`? If all tables always match all other tables, it _might_ help to reorder the `inner joins`, but optimization might do it automatically anyway. We can't be sure from the lack of info. We don't even know what indexes exist. – user2338816 Jun 17 '14 at 02:52
  • @Ian Bjorhovde: The current version of DB 9.5 does not support explain plan, Hence was not able to provide. – Vinayak Dornala Jun 17 '14 at 12:48
  • @vinayak, 9.5 do support explain, what error message did you get when trying? – Lennart - Slava Ukraini Jun 17 '14 at 19:12
  • @IanBjorhovde : I m using below query. **explain plan for SELECT count(*) as totalcount FROM history;** [Error Code: -220, SQL State: 55002] DB2 SQL Error: SQLCODE=-220, SQLSTATE=55002, SQLERRMC=DB2ADMIN.EXPLAIN_OBJECT;PAGES, DRIVER=3.57.82 – Vinayak Dornala Jun 20 '14 at 19:29
  • The -220 error (SQL0220N) means that you have old versions of the explain tables in the DB2ADMIN schema. You can either drop/recreate the explain tables or use the `db2exmig` utility to migrate the explain tables to the current version, and then run run explain again. – Ian Bjorhovde Jun 20 '14 at 23:17

1 Answers1

0

Thanks Folks,

I m able extract reports now, Thanks for the suggestions.

1. I removed unwanted joins

2. I added where clause to limit number of rows scanned.

select year(bill.created), addr.province,count(1) as yearprovicecount  from billing bill 
inner join cover cov  on ( bill.coveragecid = cov.coveragecid) 
-- inner join customer cust on (cust.customercid= cov.customercid)
inner join address addr on (cust.customercid=addr.customercid)
where year(billhis.created) = 2014
group by year(bill.created), addr.province;

Note: The current version of DB 9.5 does not support explain plan, Hence was not able to provide.

Vinayak Dornala
  • 1,609
  • 1
  • 21
  • 27