Subquery Performance

[bharadwajrv]

Hi...

i need to know which approach is good in-terms of performance while deleting the records from two tables...

Here is my table struct:

Master table (table1)
-------------------------------
MasterTable_ID (PK)
App_ID (FK)
 
 
Tran table (table2)
-------------------------------
Tran_ID (PK)
MasterTable_ID (FK)
 

I need to delete list of records (from both table1 and table2) associated for a given App_ID...

so there are two possibility

[1] First get list of records from Table1 for a given App_ID and delete its associated records in table2 (using loop) and in last-step delete records from table1


[2] use Subquery to delete all records from Table2 and delete records from table1

(there will be max 30 trans-records in table2 for a given MasterTable record)

I think [2] is appropriate, but how do i convince my QA team subquery does not have performance issue (or is i'm wrong here)

Cheers
Venu

[docdiesel]

Hi Venu,

why don't you alter the foreign key constraint to ON DELETE CASCADE ? That way you just have to delete the rows on the main table and DB2 will delete all related rows in other tables automatically. Additionally, this is done in one transaction so you're safe to have an always integer database.

Regards, Bernd

[bharadwajrv]

Hi Bernd,

thanks for your reply...

Sorry i forgot to mention that - there is limitation on database, i'm not allowed to create any stored-procedure, triggers and on on in database..... i only need to handle this using SQL statement from the front-end application... ( I know it is weird, but QA does not allow to do this…They say it is maintenance overhead )

Can u advise me on using the Subquery performance...

Thanks
Venu

Hi Venu,

why don't you alter the foreign key constraint to ON DELETE CASCADE ? That way you just have to delete the rows on the main table and DB2 will delete all related rows in other tables automatically. Additionally, this is done in one transaction so you're safe to have an always integer database.

Regards, Bernd

[docdiesel]

Hi,

sounds to me that the overhead is not in maintenance but in bureaucracy, but however ...

Always leave the control to DB2. Don't try to put this into your program, using loops etc. If your program crashes, you'll never know what happend (or not) to your tables. Further, use a transaction to make sure the database doesn't get messed up. Try something like this:

START    TRANSACTION;
DELETE   from Tran_table
  WHERE  Tran_table.MasterTable_ID = Master_table.MasterTable_ID
  AND    Master_table.App_ID = x ;
DELETE   from Master_table where App_ID = x ;
COMMIT;

If there're 30 or so tran records per master record, and there's just a couple of rows to delete in Master_table, this should be no problem. Besides, if there's no CASCADE DELETE in the database design, I hope there's at least an index to speed things up?

If there's still a problem with QA, then tell them that it's part of their job to ensure the databases integrity, and that this is always best done from within the database, e.g. using transactions and proper database design.

Good luck,

Bernd