To hazard a guess here: the mysql console typically starts up in autocommit mode. The neccesity of commiting after each statement/execution could be costing you some cycles.
What is the table type of your test data? I would not expect transactions in MyISAM would give you any beifit but under and of the ACID types like InnoDB< it could be a factor. I"m guesing that you are using a transational table type and perl-dbd is smart enough to automatically wrap you in a transaction anyhow. A suggestion: at the start of both scripts add SET AUTOCOMMIT=0'; START TRANSACTION; and then, at the end insert COMMIT See if that improves the numbers on the prepared statement. - michael dykman On Tue, Sep 2, 2008 at 5:41 PM, Jim Lyons <[EMAIL PROTECTED]> wrote: > I have a question about the prepare/execute commands provided by mysql. > > I devised a benchmark, comparing updating a table using a > statement-at-a-time versus using a prepare/execute pair. The table had a > million rows. > > The statement-at-a-time file looked like: > > reset query cache; > update company set data_source_id = data_source_id - 1 where id = > 12256006995591; > update company set data_source_id = data_source_id - 1 where id = > 120749009859430; > . > . a million rows of updates > . > > > The prepare/execute file looked like: > > reset query cache; > prepare upd from > "update company set data_source_id = data_source_id + 1 where id = ?"; > set @id = 12256006995591; > execute upd using @id; > set @id = 120749009859430; > execute upd using @id; > . > . a million rows of the same > . > > I had thought the prepare/execute file would run faster, since supposedly > mysql had already done a hard parse on the command. But it ran > significantly slower (about 25%) than the statement-at-a-time version. This > was consistent over several tries. Also, I tried one version using Perl DBI > and its prepare and execute commands and it just did the same data in half > the time of the statement-at-a-time version. > > There's not any likelihood of extraneous events screwing up the results. I > ran the commands one right after the other, several time, in different > order. Also, they were run on the same machine, my own private server, so > there was no contention from other processes to account for the difference. > There was no query cache defined and I used different operators for the 2 > statements so no command was the same as any other. I even reset the query > cache even though it wasn't being used. So, I'm convinced the results are > real. > > Does anyone know the reason for the significantly better performance for > statement-at-a-time versus prepare/execute? And why prepare/execute in the > mysql commandline program is so much slower than prepare/execute in Perl? > > Thanks. > > Jim > > > > > -- > Jim Lyons > Web developer / Database administrator > http://www.weblyons.com > -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]