List:General Discussion« Previous MessageNext Message »
From:Randall Price Date:March 11 2010 3:27pm
Subject:Very slow delete for Master / Child tables with millions of rows
View as plain text  
I am experiencing very slow deletes when I delete a record from a master table and have
cascading deletes on two detail tables.

I have an application that looks for records in the master table that are older than "X"
days and delete them.  The cascasing deletes then handles deleting all the child records
in the other tables.  However, this process is very slow.  Depending on how many records
are found to delete, this process takes anywhere from 30-40 minutes to several hours.

Due to the nature of my application, I must loop through the records to delete, do some
stuff for each record, then delete it.  I suspect at this point, each tables' indexes
need to be rebuilt.  There are several indexes and the ones for the tables with
4,000,000+ rows probably takes a while.

My question is:  What is the best way to handle deleting master/detail records in this
scenario?

I have a brief diagram of my tables and the CREATE TABLE statements follow.

Thanks,

Randall Price



                           +-------------------+
                           | tblwsusclientinfo |
+-----------------+        +-------------------+
| tblwsusclients  |        | ID                |
+-----------------+        | UpdateGUID        |
| SusClientId     |<-----oo| SusClientId       |
| ...             |   |    | ...               |
+-----------------+   |    +-------------------+
 ( ~ 3,000  rows)     |     (~ 4,000,000 rows )
                      |
                      |
                      |    +-------------------------+
                      |    | tblwsusevents           |
                      |    +-------------------------|
                      |    | EventGUID               |
                      |    | ...                     |
                      +--oo| EventAssociatedComputer |
                           | ...                     |
                           +-------------------------|
                            (~ 4,300,000 rows )


CREATE TABLE `tblwsusclients` (
  `SusClientId` varchar(36) NOT NULL default '',
  `DNSName` varchar(256) NOT NULL default '',
  `ServerGUID` varchar(36) NOT NULL default '',
  `IPAddress` varchar(15) NOT NULL default '',
  `LastReportTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `LastSyncTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `DetectionResult` varchar(256) default NULL,
  `ResponsiblePerson` varchar(16) default NULL,
  `TargetGroup` varchar(45) default NULL,
  `Affiliation` varchar(45) default NULL,
  `AddedDate` datetime default NULL,
  `IsActive` tinyint(1) default NULL,
  `UnRegisteredDate` datetime default NULL,
  `SCVersion` double default NULL,
  `BiosName` varchar(256) default NULL,
  `BiosVersion` varchar(45) default NULL,
  `OSVersion` varchar(45) default NULL,
  `SPVersion` varchar(45) default NULL,
  `Make` varchar(256) default NULL,
  `Model` varchar(256) default NULL,
  `ProcArchitecture` varchar(45) default NULL,
  `OSLongName` varchar(256) default NULL,
  `TimedOutDate` datetime default NULL,
  PRIMARY KEY  (`SusClientId`),
  KEY `FK_tblwsusclients_1` (`ServerGUID`),
  KEY `IX_DNSName` (`DNSName`),
  KEY `IX_IsActive` (`IsActive`),
  CONSTRAINT `FK_tblwsusclients_1` FOREIGN KEY (`ServerGUID`) REFERENCES `tblwsusservers`
(`ServerGUID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1



CREATE TABLE `tblwsusclientinfo` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `UpdateGUID` varchar(36) NOT NULL default '',
  `SusClientId` varchar(36) NOT NULL default '',
  `UpdateState` varchar(256) NOT NULL default '',
  `LastTimeChanged` datetime default NULL,
  PRIMARY KEY  (`ID`),
  KEY `IX_UpdateState` (`UpdateState`),
  KEY `IX_SusClientId_UpdateState` (`SusClientId`,`UpdateState`),
  KEY `FK_tblwsusclientinfo_1` (`UpdateGUID`),
  KEY `FK_tblwsusclientinfo_2` (`SusClientId`),
  CONSTRAINT `FK_tblwsusclientinfo_1` FOREIGN KEY (`UpdateGUID`) REFERENCES
`tblupdateinformation` (`UpdateGUID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_tblwsusclientinfo_2` FOREIGN KEY (`SusClientId`) REFERENCES
`tblwsusclients` (`SusClientId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1



CREATE TABLE `tblwsusevents` (
  `EventGUID` varchar(36) NOT NULL default '',
  `EventCreationDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `EventMessage` text NOT NULL,
  `EventId` varchar(256) NOT NULL default '',
  `EventSource` varchar(45) NOT NULL default '',
  `EventErrorCode` varchar(45) default NULL,
  `EventIsError` tinyint(1) NOT NULL default '0',
  `EventAssociatedUpdate` varchar(36) default NULL,
  `EventAssociatedComputer` varchar(36) default NULL,
  `EventAssociatedWSUSServer` varchar(36) NOT NULL default '',
  PRIMARY KEY  (`EventGUID`),
  KEY `IX_EventId` (`EventId`),
  KEY `IX_EventCreationDate` (`EventCreationDate`),
  KEY `FK_tblwsusevents_1` (`EventAssociatedComputer`),
  CONSTRAINT `FK_tblwsusevents_1` FOREIGN KEY (`EventAssociatedComputer`) REFERENCES
`tblwsusclients` (`SusClientId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1


Thread
Very slow delete for Master / Child tables with millions of rowsRandall Price11 Mar
  • Re: Very slow delete for Master / Child tables with millions of rowsJohan De Meersman11 Mar
    • RE: Very slow delete for Master / Child tables with millions of rowsDaevid Vincent11 Mar
  • Re: Very slow delete for Master / Child tables with millions of rowsKrishna Chandra Prajapati11 Mar