使用参数化查询缓慢SQLSRV PDO查询性能

Question

我在我的Web应用程序中有很多查询，并且注意到性能不像我认为的那样。所以我删除了参数化变量，查询运行速度更快。

$conn = new PDO("sqlsrv:Server=myserver;Database=mydb; MultipleActiveResultSets=false", "user", "pw"); 
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); 
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 

$getData_query = $conn->prepare("select several_columns 
    from myTable 
    where severalstatements = severalstatements 
    and this = :that 
"); 
$getData_query->bindValue(':that', 'somestring'); 
$getData_query->execute(); 

or 

$getData_query = $conn->prepare("select several_columns 
    from myTable 
    where severalstatements = severalstatements 
    and this = :that 
"); 
$getData_query->execute(array('that'=>'somestring')); 

服务器正在运行PHP7和SQLServer 2016年，使用PDO_SQLSRV版本4.0.8.0

如果我运行上面的两种以上的查询，它需要1.15秒的平均运行。 如果我删除了参数部分，只是使用

and this = 'somestring' 

查询在0.110秒运行，速度更快！

我在做什么错？为什么参数化方法要慢得多？

Answer 1

那么你试图做的是不正确的。

您需要使用bindParam，第二条语句你正在试图绑定this当它应该是that

$conn = new PDO("sqlsrv:Server=myserver;Database=mydb; MultipleActiveResultSets=false", "user", "pw"); 
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); 
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 

$getData_query = $conn->prepare("select several_columns 
    from myTable 
    where severalstatements = severalstatements 
    and this = :that 
"); 
$getData_query->bindParam(':that', 'somestring'); //this line changed 
$getData_query->execute(); 

or 

$getData_query = $conn->prepare("select several_columns 
    from myTable 
    where severalstatements = severalstatements 
    and this = :that 
"); 
$getData_query->execute(array('that'=>'somestring')); // this line changed