Re: Slow Stored Procedures
Posted by:
Habib Ayob
Date: December 09, 2005 07:06AM
fact table:
DROP TABLE IF EXISTS `w3caccdm`.`w3cacc_fact`;
CREATE TABLE `w3caccdm`.`w3cacc_fact` (
`id` int(11) NOT NULL auto_increment,
`agent_id` int(11) NOT NULL,
`source_id` int(11) NOT NULL default '0',
`user_id` int(11) NOT NULL default '0',
`time_id` int(11) NOT NULL default '0',
`protocol_method_id` int(11) NOT NULL default '0',
`domain_id` int(11) NOT NULL default '0',
`return_code` int(11) NOT NULL default '0',
`url` varchar(255) NOT NULL default '',
`request_size` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `FK2` (`source_id`),
...(KEYS OMMITTED FOR SIMPLICTY)
KEY `FK1` (`agent_id`),
CONSTRAINT `FK1` FOREIGN KEY (`agent_id`) REFERENCES `agent_name` (`id`),
...(CONSTRAINTS OMMITTED FOR SIMPLICITY)
CONSTRAINT `FK7` FOREIGN KEY (`return_code`) REFERENCES `return_code_dimension` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 19456 kB; (`agent_id`) REFER `w3caccdm/agent_na';
dimension table:
DROP TABLE IF EXISTS `w3caccdm`.`protocol_method_dimension`;
CREATE TABLE `w3caccdm`.`protocol_method_dimension` (
`id` int(11) NOT NULL auto_increment,
`protocol` varchar(100) NOT NULL default '',
`request_method` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `Index_2` (`protocol`,`request_method`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
I use functions because I need to get the generated ID. I guess a stored procedure with an OUT variable could work just as well but is not the same thing?
The stored procedure which uses these functions and is accessed by external java applications looks like this:
DELIMITER $$
DROP PROCEDURE IF EXISTS `w3caccdm`.`sp_w3cacc_insert` $$
CREATE PROCEDURE `sp_w3cacc_insert`(
agentid INT(11),
domain VARCHAR(100),
protocol VARCHAR(100),
request VARCHAR(100),
return_code INT(11),
rcode_desc VARCHAR(100),
ipaddress VARCHAR(100),
sql_date DATETIME,
user_cn VARCHAR(100),
url VARCHAR(255),
request_size INT(11))
BEGIN
INSERT INTO w3cacc_fact(agent_id, source_id, user_id, time_id, protocol_method_id, domain_id, return_code, url, request_size)
VALUES ( agentid, sp_source_insert(ipaddress), sp_user_insert(user_cn),
sp_time_selectid(sql_date), sp_protocol_method_insert(protocol, request),
sp_domain_insert(domain), sp_return_code_insert(return_code, rcode_desc),
url, request_size);
END $$
DELIMITER ;
Habib