用存储过程比php是不是效率快,php+mysql中存储过程性能容易比较

php+mysql中存储过程性能简单比较

PHP+MYSQL中，使用MYSQL的存储过程其实是很好的，而且效率还会快点的，

在这里，小结复习下MYSQL的用法吧，使用的是PDO

1

//不使用存储过程

$time = microtime(TRUE);

$mem = memory_get_usage();

$dsn = 'pgsql:host=localhost;dbname=gonzalo;port=5432';

$user = 'user';

$password = 'password';

$conn = new PDO($dsn, $user, $password);

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$conn->beginTransaction();

$stmt = $conn->prepare('delete from web.tbltest');

$stmt->execute();

$stmt = $conn->prepare('INSERT INTO web.tbltest (field1) values (?)');

foreach (range(0,1000) as $i) {

$stmt->execute(array($i));

}

$conn->commit();

print_r(array('memory' => (memory_get_usage() - $mem) / (1024 * 1024), 'seconds' => microtime(TRUE) - $time));

2 使用存储过程

存储过程的写法：

CREATE OR REPLACE FUNCTION web.method1()

RETURNS numeric AS

$BODY$

BEGIN

DELETE FROM web.tbltest;

FOR i IN 0..1000 LOOP

INSERT INTO web.tbltest (field1) values (i);

END LOOP;

RETURN 1;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

$time = microtime(TRUE);

$mem = memory_get_usage();

$dsn = 'pgsql:host=localhost;dbname=gonzalo;port=5432';

$user = 'user';

$password = 'password';

$conn = new PDO($dsn, $user, $password);

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$conn->beginTransaction();

$stmt = $conn->prepare('SELECT web.method1()');

$stmt->execute();

$stmt->setFetchMode(PDO::FETCH_ASSOC);

$out = $stmt->fetchAll();

$conn->commit();

print_r(array('memory' => (memory_get_usage() - $mem) / (1024 * 1024), 'seconds' => microtime(TRUE) - $time));

可以看到结果对比如下：

without stored procedures

memory: 0.0023880004882812

seconds: 0.31109309196472

with stored procedures

memory: 0.0020713806152344

Seconds: 0.065021991729736

可以看到，用了存储过程的性能还是不错的

微信 赏一包辣条吧~

支付宝 赏一听可乐吧~