Re: Stored Proc SLOWER than embedded sql
Per-Erik Martin wrote:
> >I have one question though, does this
> precompilation involve storing the query plan?
>
> No, each statement is executed separately (the
> procedure is basically calling back to the
> execution dispatcher). There's no
> "cross-statement" optimization.
Ok, so, on the one hand, this could speed up things a little bit; on the other hand, we won't have to recompile when we tinker with the table definitions (adding indexes etc)
> >I mean, I know what it sais, but I expected
> that to be precompiled too. Is it? Why would this
> be less efficient
> >than the binary protocol, I mean, we're
> running inside the server? or not?
> I'm no expert on prepared statements or protocol
> issues, but as far as I understand, there are two
> client-server protocols: the old one, and the new
> "binary" protocol. As I read it, when preparing
> statements with the C API, you will use the binary
> protocol. When using the "prepare statement"
> statements (like you do in stored procedures for
> instance), you're using the old protocol. (In both
> cases the statement is precompiled of course.).
> How big the difference is performancewise, I don't
> know.
But would the protocol matter for stuff that's running serverside? I mean, I can imagine that the protocol would be of impact when transferring data, or even the statement (altough the latter would be superceded by storing the prepare inside a stored procedure right?).
Anyway, I did this quick test (5.0.17-nt, WinXP Pro), and although the difference is slight, I think it is significant:
CREATE TABLE `my_rand` (
`col1` int(11) NOT NULL auto_increment,
`col2` double default NULL,
PRIMARY KEY (`col1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Now, I compared this procedure:
create procedure load_my_rand(p_rows int unsigned)
begin
declare v_index int unsigned default 0;
while v_index < p_rows do
set v_index := v_index + 1;
set @rand := rand(1);
insert
into my_rand(col2)
values (@rand)
;
end while;
end;
//
to this one:
create procedure load_my_rand_prepared(p_rows int unsigned)
begin
declare v_index int unsigned default 0;
prepare stmt from 'insert into my_rand(col2) values (?)';
while v_index < p_rows do
set v_index := v_index + 1;
set @rand := rand(1);
execute stmt using @rand;
end while;
deallocate prepare stmt;
end;
//
And here's what I get:
mysql> call load_my_rand(100000)
-> //
Query OK, 1 row affected (3.46 sec)
mysql> delete from my_rand
-> //
Query OK, 100000 rows affected (0.00 sec)
mysql> call load_my_rand(100000)
-> //
Query OK, 1 row affected (3.54 sec)
mysql> delete from my_rand
-> //
Query OK, 100000 rows affected (0.00 sec)
mysql> call load_my_rand(100000)
-> //
Query OK, 1 row affected (3.50 sec)
mysql> delete from my_rand
-> //
Query OK, 100000 rows affected (0.00 sec)
an avage of 3.50 secs for 100,000 rows.
Now for the prepared proc:
mysql> call load_my_rand_prepared(100000)
-> //
Query OK, 0 rows affected (3.61 sec)
mysql> delete from my_rand//
Query OK, 100000 rows affected (0.00 sec)
mysql> call load_my_rand_prepared(100000)
-> //
Query OK, 0 rows affected (3.71 sec)
mysql> delete from my_rand//
Query OK, 100000 rows affected (0.00 sec)
mysql> call load_my_rand_prepared(100000)
-> //
Query OK, 0 rows affected (3.66 sec)
which boils down to an everage of 3.66 secs
so, thats about 4% slower which is puzzling, because if the parameter passing is that much slower, when would the preparation of the statement start to pay off. On the other hand not that big a deal.
Now, I test it like this:
create procedure load_my_rand1(p_rows int unsigned)
begin
declare v_index int unsigned default 0;
declare v_rand double;
while v_index < p_rows do
set v_index := v_index + 1;
set v_rand := rand(1);
insert
into my_rand(col2)
values (v_rand)
;
end while;
end;
//
wich is the same proc as we started out with, but this time, we are using a local variable instead of the @rand user variable (as you would normally):
mysql> call load_my_rand1(100000)//
Query OK, 1 row affected (3.18 sec)
mysql> delete from my_rand;
-> //
Query OK, 100000 rows affected (0.00 sec)
mysql> call load_my_rand1(100000)//
Query OK, 1 row affected (3.29 sec)
mysql> delete from my_rand;//
Query OK, 100000 rows affected (0.00 sec)
mysql> call load_my_rand1(100000)//
Query OK, 1 row affected (3.27 sec)
Which is approx. an average of 3.25 seconds.
The first proc is about 7% slower than this one, and the second proc is about 11% slower than this one. Wich, in my opinion, definitely is starting to approach a number I would start taking into consideration if I were to load large amounts of data.
Also, it suggests that user variables are slow as compared to local variables, (maybe much slower, I should check using two parameters in the insert).
Edited 1 time(s). Last edit at 01/17/2006 04:15AM by Roland Bouman.