Performance advantages of stored procedures and storage functions

Source: Internet
Author: User

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

Insert mode required time Stored procedure 51 SEC Client 142 SEC Client +proxy 181 seconds

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.