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
可以看到，用了存储过程的性能还是不错的
