Hello, I am facing a problem related with triggers and bulk updates/inserts. I have two tables one is having 'transactions' and another is 'documents'. Each record in transactions table relates with a document by DocId. Foreign keys are created. I have activated triggers for transactions table for after insert, afters update, before delete. trigger makes a sum of amount in all transactions for a document referenced by docId in that perticular transaction and stores it in another temporary table and then updates the amounts in documents table. When I will go for inserting 10K records at a time, the insert becomes too slow. Why? Is there any solution to this?
I am using MySQL 5.0.45 on Redhat Ent. Linux 5 - 64bit, 4 GB RAM, Xeon procesors and MySQL Connector ODBC 5.1 Beta, MS Acess 2003 on WindowsXp SP2. I also tried to inster this records directly by passing queries to MySQL, but still it is slow! Please help. Thanks CPK the scripts are as follows- -- ---------------------------- -- Table structure for cb_canebills -- ---------------------------- CREATE TABLE `cb_canebills` ( `ID` int(11) NOT NULL auto_increment, `AssociateSCPId` int(11) default NULL, `PeriodNo` int(11) default NULL, `SlipCount` int(11) default NULL, `TotalWeight` decimal(9,3) default NULL, `NormalWeight` decimal(9,3) default NULL, `JalitWeight` decimal(9,3) default NULL, `NormalAmount` decimal(15,2) default NULL, `JalitAmount` decimal(15,2) default NULL, `NormalRate` decimal(15,2) default NULL, `JalitRate` decimal(15,2) default NULL, `PayRate` decimal(15,2) default NULL, `TotalAmount` decimal(15,2) default '0.00', `HAmount` decimal(15,2) default NULL, `TAmount` decimal(15,2) default NULL, `HComissionAmount` decimal(15,2) default NULL, `TComissionAmount` decimal(15,2) default NULL, `TotalAgainstAmount` decimal(15,2) default '0.00', `NetPayAmount` decimal(15,2) default '0.00', `BankID` int(11) default '0', `BankAccNo` decimal(20,4) default NULL, `CaneBillNo` varchar(20) collate utf8_unicode_ci default NULL, `CaneBillDate` date default NULL, `AssociateType` int(11) default NULL, `CrushSeason` int(11) default NULL, `ChequeNo` varchar(10) collate utf8_unicode_ci default NULL, `ChequeDate` date default NULL, `ChequeAmount` decimal(15,2) default NULL, `BankOrCashAmount` decimal(15,2) default NULL, `InstallmentNo` int(11) default NULL, `tmpTS` timestamp NULL default '1999-11-11 11:11:11', `CreatedBy` int(11) default NULL, `CreatedTimeStamp` datetime default NULL, `LastModifiedBy` int(11) default NULL, `LastModifiedTimeStamp` datetime default NULL, `Locked` tinyint(4) default NULL, `CaneBillRemark` varchar(300) collate utf8_unicode_ci default NULL, `CoBranch` int(11) default NULL, `CoYear` int(11) default NULL, `CaneBillStatus` int(11) default NULL, `AccVoucherCreated` tinyint(4) default NULL, `Approved` tinyint(4) default NULL, `ApprovedBy` int(11) default NULL, `IsTemplate` tinyint(4) default NULL, `ReportH_RCS` int(11) default NULL, `ReportF_RCS` int(11) default NULL, `CaneBillCurrency` int(11) default NULL, `CaneBillExchangeRate` decimal(15,2) default NULL, `LastAccDate` date default NULL, `Billed` tinyint(4) default NULL, `tmpSelect` tinyint(4) default '0', `DocType` int(11) default NULL, `BillFromDate` date default NULL, `BillToDate` date default NULL, `Partial` tinyint(4) default NULL, `IsTemp` tinyint(4) default NULL COMMENT 'Temprory bill or not', `PaidThroughBankAccount` int(4) default NULL COMMENT 'bank account(associates) through which payment is issued', `BCId` int(11) default NULL, `PaymentThroughLedger` int(11) default NULL, `DCLogId` int(11) default NULL, `tmpSelectedByUser` int(11) default NULL, `AllowAllUsersToView` tinyint(4) default '-1' COMMENT 'view this doc to all while browsing except than created/Last', PRIMARY KEY (`ID`), KEY `First_billID` (`ID`), KEY `First_billP_no` (`PeriodNo`), KEY `FKAssociate` (`AssociateSCPId`), KEY `Indbcid` (`BCId`) ) ENGINE=InnoDB AUTO_INCREMENT=9455 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- ---------------------------- -- Table structure for cb_canebilltransactions -- ---------------------------- CREATE TABLE `cb_canebilltransactions` ( `CaneBillTransactionId` int(11) NOT NULL auto_increment, `DocId` int(11) NOT NULL, `DocType` int(11) default NULL, `AgainstId` int(11) NOT NULL, `AgainstAmount` decimal(15,2) unsigned zerofill default '0000000000000.00 ', `LineRemark` varchar(300) collate utf8_unicode_ci default NULL, `CreatedBy` int(11) default NULL, `CreatedTimeStamp` datetime default NULL, `LastModifiedBy` int(11) default NULL, `LastModifiedTimeStamp` datetime default NULL, `Locked` tinyint(4) default NULL, `tmpTS` timestamp NULL default '1999-11-11 11:11:11', `BankId` int(11) default NULL, `LoanSchemeId` int(11) default NULL, `DetailsForExtras` varchar(30) collate utf8_unicode_ci default NULL, `AddOrSubstract` tinyint(4) default NULL, `LinkedAssociateId` int(11) default NULL, PRIMARY KEY (`CaneBillTransactionId`), KEY `FKCaneBills` (`DocId`) ) ENGINE=InnoDB AUTO_INCREMENT=280743 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; ------------- TRIGGER FOR CB_canebilltransactions Same for After Insert, After Update and Before Delete ------------- BEGIN DELETE cb_tmpbills.* from cb_tmpbills; INSERT INTO cb_tmpbills ( TotalAgainstsAmount, BillTansactionId, BillId ) SELECT Sum(cb_canebilltransactions.AgainstAmount) AS SumOfAgainstAmount, cb_canebilltransactions.caneBillTransactionId, cb_canebilltransactions.DocId FROM cb_canebilltransactions INNER JOIN cb_canebills ON cb_canebilltransactions.DocId = cb_canebills.ID GROUP BY cb_canebilltransactions.caneBillTransactionId, cb_canebilltransactions.DocId HAVING (((cb_canebilltransactions.caneBillTransactionId)= new.caneBillTransactionId)); UPDATE cb_canebills INNER JOIN cb_tmpbills ON cb_canebills.ID = cb_tmpbills.BillId SET cb_canebills.TotalAgainstAmount = cb_tmpbills.TotalAgainstsAmount; UPDATE cb_canebills INNER JOIN cb_tmpbills ON cb_canebills.ID = cb_tmpbills.BillId SET cb_canebills.NetPayAmount = cb_canebills.TotalAmount-cb_canebills.TotalAgainstAmount; END -- Keep your Environment clean and green.
