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

I have many queries in my web application and was noticing that the performance wasn't quite what I thought it would be. So I removed the parameterized variables and the query ran considerably faster.

我的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'));

Server is running PHP7 and SQLserver 2016, using pdo_sqlsrv version 4.0.8.0

服务器使用pdo_sqlsrv版本4.0.8.0运行PHP7和SQLserver 2016

If I run the above either of the above queries, it takes the average of 1.15 sec to run. If I remove the parameterized part and just use

如果我运行上述任一上述查询，则需要平均1.15秒才能运行。如果我删除参数化部分，只需使用

and this = 'somestring'

The query runs in .110 seconds, much faster!

查询运行时间为.110秒，速度更快！

What am I doing wrong? Why is the parameterized method so much slower?

我究竟做错了什么？为什么参数化方法这么慢？

1 个解决方案

#1

Well what you are attempting to do is incorrect.

那么你想要做的是不正确的。

You need to use bindParam, the second statement you are trying to bind this when it should be that

你需要使用bindParam，你应该尝试绑定它的第二个语句

$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

#1