Re: Inserts very slow

[Greg Hines]

Hi Rick,

Here are the answers to your questions:-

< innodb_buffer_pool_size | 2147483648 |
<-- Is this the same on both?

Development - 536870912
Production - 2147483648


< 32-bit or 64-bit OS? MySQL?

Development - OS X64, MySQL X32
Production - OS & MySQL X64

< Version of MySQL on each. (There were some performance hiccups in recent versions of 5.1.)

Development - 5.1.44
Production - 5.1.36

< SHOW CREATE TABLE AdjTime \G

CREATE TABLE `adjtime` (
`idAdjTime` int(10) unsigned NOT NULL AUTO_INCREMENT,
`StrID` smallint(5) unsigned NOT NULL,
`EmpID` int(10) unsigned DEFAULT NULL,
`ShiftID` smallint(5) unsigned DEFAULT NULL,
`Invalid` char(1) DEFAULT NULL,
`DOB` date DEFAULT NULL,
`SysDateIn` date DEFAULT NULL,
`SysDateOut` date DEFAULT NULL,
`JobCode` int(10) unsigned DEFAULT NULL,
`InHour` tinyint(3) unsigned DEFAULT NULL,
`InMinute` tinyint(3) unsigned DEFAULT NULL,
`OutHour` tinyint(3) unsigned DEFAULT NULL,
`OutMinute` tinyint(3) unsigned DEFAULT NULL,
`Minutes` smallint(5) unsigned DEFAULT NULL,
`Hours` decimal(5,2) unsigned DEFAULT NULL,
`Rate` decimal(15,3) unsigned DEFAULT NULL,
`Pay` decimal(15,2) unsigned DEFAULT NULL,
`OverMin` smallint(5) unsigned DEFAULT NULL,
`OverHrs` decimal(5,2) unsigned DEFAULT NULL,
`OverRate` decimal(15,3) unsigned DEFAULT NULL,
`OverPay` decimal(15,2) unsigned DEFAULT NULL,
`ReGovMin` smallint(5) unsigned DEFAULT NULL,
`ReGovHrs` decimal(5,2) unsigned DEFAULT NULL,
`ReGovPay` decimal(15,2) unsigned DEFAULT NULL,
`DayOvMin` smallint(5) unsigned DEFAULT NULL,
`DayOvHrs` decimal(5,2) unsigned DEFAULT NULL,
`DayOvPay` smallint(5) unsigned DEFAULT NULL,
`TotCCSls` decimal(15,2) unsigned DEFAULT NULL,
`TnocSHSls` decimal(15,2) unsigned DEFAULT NULL,
`GrossSls` decimal(15,2) unsigned DEFAULT NULL,
`NoOfCheck` smallint(5) unsigned DEFAULT NULL,
`NoOfGuest` smallint(5) unsigned DEFAULT NULL,
`TotComp` decimal(15,2) unsigned DEFAULT NULL,
`TotPromo` decimal(15,2) unsigned DEFAULT NULL,
`SpectNdr` decimal(15,2) unsigned DEFAULT NULL,
PRIMARY KEY (`idAdjTime`),
KEY `AdjTime_StrID_DOB` (`StrID`,`DOB`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=27113 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

< Which fields are parameters to the SP?

p_StrID, p_StartDate, p_EndDate