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
