Performance advantages of stored procedures and storage functions

Intermediary transaction http://www.aliyun.com/zixun/aggregation/6858.html ">seo diagnose Taobao guest cloud host technology Hall

With regard to stored procedures, it is difficult for a lot of people to understand what else it brings in addition to convenience.

I used the machine at hand to do the following test. Regardless of machine type, and table structure (in fact, the table structure similar to the inside of this post).

DROP PROCEDURE IF EXISTS insert_n_rows;

Delimiter//

CREATE PROCEDURE insert_n_rows (in Loops INT)

BEGIN

DECLARE Count INT;

SET Count =loops;

While Count > 0 do

INSERT into my_table values (null,0,

' Wwwwwwmmmmmyyyyyysssssqqqqllllcccccoooooooomm ',

' Jjjjjjjjjjjjjjjjjjjjiiiiiiiiiiiiiiiiiiiiiddddddddddddddddeeeeeeeeee ');

SET count = count-1;

End while;

End;

//

delimiter;

I tried to invoke this stored procedure to insert 1 million lines.

Later, the client program was used to insert 1 such ways to perform 1 million times.

Finally use the client program to insert 1 such way to execute 1 million times, and the client uses MySQL proxy to connect MySQL. The following table shows their differences

Stored procedures are faster than the other two ways, because it saves network overhead, SQL language parsing, optimization, and so on.

But after all, the database is not good at procedural code, so using stored procedures can handle some simple and small processes. As for the complex logic, let the external language deal with it.

Specific can see

http://www.mysqlsystems.com/?p=325

Http://www.mysqlsystems.com

Http://www.mysqlsystems.com/bbs