-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
For the nams.XXXX tables the badge is the primary key, and since the
stored procedure and the dynamically generated function generate the
same queries, it shouldn't matter as to performance.
I am using Solaris 8, JDK1.5, mysql 5.0.3
I haven't tested with prepared statements, but I don't know if I will,
as I know there will be a performance hit there, based on past history.
It appears that the stored procedure is 4x slower than dynamically
generating, to almost 7x slower.
Following is the time to do the tests:
[junit] Testcase: testDeassignMultiDB took 0.088 sec
[junit] Testcase: testDeassignMultiDBStoredProcedures took 0.34 sec
[junit] Testcase: testDeassignMultiDBStoredProcedures100Reps took
13.712 sec
[junit] Testcase: testDeassignMultiDB100Reps took 2.266 sec
The last two tests do the exact same tests 100 times, so I can get a
better idea as to numbers.
Each test is:
deassign
assign
deassign
For the assign functions here are the query that is sent:
[junit] INSERT INTO curuse(rid, start, badge, card_type, dept,
college, campus, fullname, ip) SELECT 1999,1112812166, n.badge, a.role,
a.deptid, a.college, a.campus, concat(na.fname, " ", na.lname), 0 FROM
nams.names na, items i, nams.netids n, nams.affiliations a WHERE
i.rid=1999 AND n.netid='jblack' AND na.badge=n.badge AND a.badge=na.badge
AND i.status='A' AND a.source='B' LIMIT 1
[junit] UPDATE items SET status='U' WHERE rid=1999
Here is the stored procedure:
CREATE PROCEDURE assignItem (
user CHAR(15),
rid int,
start int) BEGIN
INSERT INTO curuse(rid, start, badge, card_type, dept, college, campus,
fullname, ip)
SELECT rid, start, n.badge, a.role, a.deptid, a.college, a.campus,
concat(na.fname, " ", na.lname), 0 FROM nams.names na, items i,
nams.netids n, nams.affiliations a WHERE i.rid=rid AND n.netid=user AND
na.badge=n.badge AND a.badge=na.badge AND i.status='A' AND a.source='B'
LIMIT 1;
UPDATE items SET status='U' WHERE rid=rid; END;
For deassign, here is the query:
[junit] INSERT INTO
transactions(start,badge,card_type,dept,college,campus,finish,lid,itemtype,rid)SELECT
c.start, c.badge, c.card_type, c.dept, c.college, c.campus,1112812166,
i.lid, i.itemtype,1999 FROM curuse c, items i WHERE i.rid=1999 AND
c.rid=1999
[junit] UPDATE items SET status='A' WHERE rid=1999
[junit] DELETE FROM curuse WHERE rid=1999
Here is the stored procedure:
CREATE PROCEDURE deassignItem (
rid int,
endtime int) BEGIN
INSERT INTO
transactions(start,badge,card_type,dept,college,campus,finish,lid,itemtype,rid)
SELECT c.start, c.badge, c.card_type, c.dept, c.college, c.campus,
endtime, i.lid, i.itemtype, rid FROM curuse c, items i WHERE i.rid=rid AND
c.rid=rid;
UPDATE items set status='A' where rid=rid;
DELETE FROM curuse WHERE rid=rid; END;
Here are the three main tables that are used:
| curuse | CREATE TABLE `curuse` (
`rid` int(11) NOT NULL default '0',
`start` int(11) default NULL,
`badge` int(11) default NULL,
`card_type` char(2) default NULL,
`dept` char(3) default NULL,
`college` char(2) default NULL,
`campus` char(1) default NULL,
`fullname` varchar(24) default NULL,
`ip` varchar(40) NOT NULL default '',
`alive` int(11) default NULL,
PRIMARY KEY (`rid`,`ip`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
COMMENT='Items that are currently assigned' |
| items | CREATE TABLE `items` (
`rid` int(11) NOT NULL auto_increment,
`lid` int(11) NOT NULL default '0',
`itemtype` char(4) NOT NULL default '',
`label` char(12) NOT NULL default '',
`status` char(1) NOT NULL default '',
`layoutx` int(11) default NULL,
`layouty` int(11) default NULL,
`theta` int(11) default NULL,
PRIMARY KEY (`rid`),
UNIQUE KEY `label_ndx` (`label`),
KEY `itemtype_ndx` (`itemtype`),
KEY `lid_ndx` (`lid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
| transactions | CREATE TABLE `transactions` (
`xid` int(11) NOT NULL auto_increment,
`start` int(11) NOT NULL default '0',
`finish` int(11) NOT NULL default '0',
`lid` int(11) NOT NULL default '0',
`itemtype` char(4) NOT NULL default '',
`rid` int(11) NOT NULL default '0',
`badge` int(11) NOT NULL default '0',
`card_type` char(2) NOT NULL default '',
`dept` char(3) NOT NULL default '',
`college` char(2) NOT NULL default '',
`campus` char(1) NOT NULL default '',
PRIMARY KEY (`xid`),
KEY `start_ndx` (`start`),
KEY `rank_ndx` (`card_type`),
KEY `dept_ndx` (`dept`),
KEY `college_ndx` (`college`),
KEY `campus_ndx` (`campus`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
- --
"Love is mutual self-giving that ends in self-recovery." Fulton Sheen
James Black [EMAIL PROTECTED]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCVC08ikQgpVn8xrARApKUAJ4/VMnH3T4cB7gUDYYLf4SZKbe4XwCfQbZ1
5DUJaPRnmNJs170/UpGl3OA=
=Iuwr
-----END PGP SIGNATURE-----
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
