Delete query goes on for ever...

[ChrisRutherford]

Hello,

I have two tables in my DB2 7.2 fixpack 5 database...

Table Name - zz_caa_data
Columns
id int
account_id int
first_name char(50)
last_name char(50)
* There is no primary key defined.

Table name - zz_caa_delete
Columns
id int
* There is no primary key defined.

The table zz_caa_data has 14900 records and the table zz_caa_delete has 76 records. When I run either of the following delete queries from within Command Center they just keep on processing, they never complete.

delete from admin.zz_caa_data
where id in (select delete_id from admin.zz_caa_delete)

delete from admin.zz_caa_data
where exists (select '1' from admin.zz_caa_delete where id = delete_id)

Would anyone know why this is. I've looked on the net to no avail. I also have the same problem with update queries as well, they just don't finish.

Thanks again,

Chris Rutherford

 

[MarcLodge]

Hi Chris,
What I think is happening is that for each of the 15,000 rows in the data table the query is having to do a full tablespace scan of the delete table. Although there is not an awful amount of data on the delete table, it is obviously still taking some time.

To improve performance, I would suggest adding an index to both tables on the integer field id.

I would then change the query so that it read:

delete from admin.zz_caa_data a
where a.id in (select b.delete_id from admin.zz_caa_delete b
where b.delete_id = a.id)

You may want to try this query without the index addition as I believe it will improve performance anyway. That said the way to significant improvement is via the index route.

Hope this helps
Marc

 

[ChrisRutherford]

Hi Marc,

Thanks for the reply and now I've indeed learnt the need for indexes even on small tables. Once I put an index on both tables everything worked fine. The delete statements took less than a second. Huge difference considering I cancelled the job last time after it had been processing for 2 hours.

Many thanks,

Chris