准备好的报表没有速度效益吗?

I'd heard Prepared Statements with a MySQL database can offer speed increases if the query is being done multiple times, and I thought I had an ideal case for that in a project. But I ran some benchmarks and found the exact opposite. Am I using these statements wrong (not an ideal situation for Prepared Statements), or are they just not as fast as I thought they were?

我听说，使用MySQL数据库编写的语句可以在多次执行查询时提高速度，我认为我在项目中有一个理想的例子。但我进行了一些基准测试，发现结果恰恰相反。我使用这些语句是错误的(不是准备好的语句的理想情况)，还是它们没有我想象的那么快?

The situation is a tournament result grid. There's multiple schools entered in multiple events, and each school has a score for each event. In order to get an individual school's scores for all events, it takes a SQL query with a LEFT JOIN like:

情况是一个比赛结果网格。有多个学校参加了多个活动，每个学校都有每个活动的分数。为了获得单个学校对所有事件的分数，它需要一个SQL查询，并使用左连接:

SELECT e.`id`, e.`name`, c.`competing`, c.`raw`, c.`final` FROM `events` e LEFT JOIN `scores` c ON e.`id`=c.`event_id` WHERE c.`school_id`=:school_id;

I wrote two PHP test scripts to run against sample data (200 events), using the native PDO objects (prepare()/bindValue()/execute() versus query()):

我使用本地PDO对象(prepare()/bindValue()/execute() / query()):

EDIT Modified tests with below suggestions (vanilla query needs a fetch, fetch different IDs, and bind the prepare outside the loop). Only gives a modest speed advantage to prepared statements now:

使用以下建议编辑修改后的测试(一般的查询需要获取不同的id，并在循环外部绑定prepare)。现在只给准备好的声明提供一个适度的速度优势:

Prepared Statement:

事先准备好的声明中:

$start = microtime(true);
$sql = 'SELECT e.`id`, e.`name`, c.`competing`, c.`raw`, c.`final` FROM `events` e LEFT JOIN `scores` c ON e.`id`=c.`event_id` WHERE c.`school_id`=:school_id';
echo $sql."<br />\n";
$stmt = $db->prepare($sql);
$sid = 0;
$stmt->bindParam(':school_id', $sid);
for ($i=0; $i<$max; $i++) {
    $sid = rand(1,499);
    $stmt->execute();
    $rs = $stmt->fetchAll();
}
$delta = bcsub(microtime(true), $start, 4);
echo "<strong>Overall time:</strong> $delta<br />\n";
echo "<strong>Average time:</strong> ".($delta/$max)."<br />\n";

Vanilla Query:

香草查询:

set_time_limit(15); // Add time for each run
$start = microtime(true);
$sql = 'SELECT e.`id`, e.`name`, c.`competing`, c.`raw`, c.`final` FROM `events` e LEFT JOIN `scores` c ON e.`id`=c.`event_id` WHERE c.`school_id`={$sid}';
echo $sql."<br />\n";
for ($i=0; $i<$max; $i++) {
    $sid = rand(1,499);
    $stmt = $db->query("SELECT e.`id`, e.`name`, c.`competing`, c.`raw`, c.`final` FROM `events` e LEFT JOIN `scores` c ON e.`id`=c.`event_id` WHERE c.`school_id`={$sid}");
    $rs = $stmt->fetchAll();
}
$delta = bcsub(microtime(true), $start, 4);
echo "<strong>Overall time:</strong> $delta<br />\n";
echo "<strong>Average time:</strong> ".($delta/$max)."<br />\n";

I am fetching the same school's event scores (School ID# 10) over and over again, and setting $max to 10,000, I get results that show the vanilla queries being 30% faster (25.72 seconds versus 36.79). Am I doing it wrong, or is this accurate that Prepared Statements aren't faster even in a repeating situation?

我一遍又一遍地获取同一所学校的事件分数(学校ID# 10)，并将$max设置为10,000，我得到的结果显示普通查询的速度要快30%(25.72秒而36.79秒)。是我做错了，还是说准备好的语句即使在重复的情况下也不会更快?

EDIT the updated tests now get 33.95 seconds prepared versus 34.10 vanilla. Huzzah, the prepared statements are faster. But only by a fraction of a second for 10,000 iterations. Possibly because my query is not that complex (Prepared statements cache the parse tree for their advantage)? Or is there more optimizing to do here yet?

编辑更新后的测试现在得到33.95秒的准备时间而不是34.10秒。Huzzah，准备好的报表更快。但是在10,000次迭代中只有几分之一秒。可能是因为我的查询不是那么复杂(准备好的语句缓存解析树以获得优势)?或者这里还有更多的优化吗?

3 个解决方案

#1

-2

It appears that you may not be comparing apples to apples.

看来你不是在拿苹果和苹果做比较。

PDO::query() Executes an SQL statement, returning a result set as a PDOStatement object.

query()执行SQL语句，返回一个作为PDOStatement对象的结果集。

To get the actual results, you need to iterate over the returned object or, as with the prepared statement, call fetchAll() to load the entire result set into an array

要获得实际结果，需要迭代返回的对象，或者像准备好的语句一样，调用fetchAll()将整个结果集加载到一个数组中

The correct vanilla query loop should likely then be:

正确的查询循环应该是:

for ($i=0; $i<$max; $i++) {
    $stmt = $db->query($sql);
    $rs = $stmt->fetchAll();
}

or alternatively removing the fetchAll() call from the prepared statement loop.

或者从准备语句循环中删除fetchAll()调用。

You could also reduce the method calls required for the prepared statement by using bindParam() instead of bindValue()

您还可以使用bindParam()而不是bindValue()来减少准备语句所需的方法调用

$school_id = null;
$stmt->bindParam(':school_id', $school_id);
for ($i=0; $i<$max; $i++) {
    $school_id = 10;
    $stmt->execute();
    $rs = $stmt->fetchAll();
}

3 个解决方案

#1

更多相关文章

随机推荐