Re: Stored Procedure too slow

[Cristiano Ghersi]

Hi, there's a single insert in AddPhysNode SP:
CREATE PROCEDURE AddPhysNode(
IN _Accepted BIT,
IN _Creation BIGINT,
IN _IsOnline BIT,
IN _Label VARCHAR(255),
IN _MacAddress LONGBLOB,
IN _NSAP LONGBLOB,
IN _NetID INT,
IN _Type SMALLINT,
IN _ProtType SMALLINT,
OUT _NewID INT,
OUT _CreatedNew BIT
)
BEGIN
INSERT INTO `PhysicalNode` (
`Accepted`, `Creation`, `IsOnline`, `Label`, `MacAddress`,
`NSAP`, `NetworkId`, `ProtType`, `Type`)
VALUES (
_Accepted, _Creation, _IsOnline, _Label, _MacAddress,
_NSAP, _NetID, _ProtType, _Type);

SET _CreatedNew = 1;
SET _NewID = @@IDENTITY;
END $$
DELIMITER ;

Moreover, the table PhysicalNode is the following:
CREATE TABLE `physicalnode` (
`AbsCoordX` double DEFAULT NULL,
`AbsCoordY` double DEFAULT NULL,
`AbsCoordZ` double DEFAULT NULL,
`Accepted` bit(1) NOT NULL,
`AdditionalArgs` varchar(4000) DEFAULT NULL,
`BatteryLevel` smallint(6) DEFAULT NULL,
`CoordX` double DEFAULT NULL,
`CoordY` double DEFAULT NULL,
`CoordZ` double DEFAULT NULL,
`Creation` bigint(20) NOT NULL,
`CurrentPwrSrc` smallint(6) DEFAULT NULL,
`ExpectedLifeTime` bigint(20) DEFAULT NULL,
`Id` int(11) NOT NULL AUTO_INCREMENT,
`IsOnline` tinyint(1) NOT NULL,
`Label` varchar(255) NOT NULL,
`LastBatteryChange` bigint(20) DEFAULT NULL,
`LastMaintenance` bigint(20) DEFAULT NULL,
`LastStatusTime` bigint(20) DEFAULT NULL,
`MacAddress` longblob NOT NULL,
`NSAP` longblob NOT NULL,
`NetworkId` int(11) NOT NULL,
`ProtType` smallint(6) NOT NULL,
`SignalRange` float DEFAULT NULL,
`Type` smallint(6) NOT NULL,
`WakeupPolicy` smallint(6) DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `PhysNode_MacAndType` (`MacAddress`(128),`ProtType`),
KEY `NetworkId` (`NetworkId`),
CONSTRAINT `physicalnode_ibfk_1` FOREIGN KEY (`NetworkId`) REFERENCES `network` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1