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 .
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.