MySQL Forums
Forum List  »  Stored Procedures

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

Options: ReplyQuote


Subject
Views
Written By
Posted
2274
December 08, 2005 06:43AM
1586
December 08, 2005 09:49AM
Re: Slow Stored Procedures
1587
December 09, 2005 07:06AM
1442
December 12, 2005 04:41AM
1443
December 13, 2005 08:19PM
1482
December 14, 2005 07:56AM
1493
December 14, 2005 08:15AM
1548
December 14, 2005 09:05AM
1662
December 19, 2005 12:57AM
1747
December 15, 2005 05:10PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.