CODEWITHSUNDEEP
phpsql-serverperformancepdo
Bill_VA
Bill_VA

Reputation: 973

Slow SQLSRV PDO query performance with parameterized queries

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.

$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

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 

and this = 'somestring'

The query runs in .110 seconds, much faster!

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

Upvotes: 0

Views: 1873

Answers (2)

user2274172
user2274172

Reputation: 11

The problem is, that your database encoding does not match the php encoding and therefore it is not using the index you most probably have on the string. I've encountered the same issue but using the SQLSRV extensions. Database is set to SQL_Latin1_General_CP1_CI_AS but the connection is set to UTF8:

<?php
$conArray['CharacterSet']='UTF-8';
$con = sqlsrv_connect($host,$conArray);
?>

Using Parametrized query:

$query = 'SELECT 1 FROM ExampleTable WHERE Name = ?';
$params = ['Somename'];
$stmt = sqlsrv_query($con, $query, $params);
$ret = sqlsrv_fetch($stmt);
?>

In my case this was 250x slower than using following query:

<?php
$query = "SELECT 1 FROM ExampleTable WHERE Name = 'Somename'";
$stmt = sqlsrv_query($con, $query);
$ret = sqlsrv_fetch($stmt);
?>

I have an Index on 'Name' which in the first case is not used, because the driver converts that to the following query:

exec sp_executesql N'SELECT 1 FROM ExampleTable WHERE Name = @P1',N'@P1 nvarchar(4000)','Somename'

And as nvarchar is UTF-8 and the index is not, it can't use the index.

One way around this is not using UTF-8 as caracter set but SQLSRV_ENC_CHAR (which is also default). I don't want to change that as it changes everything on the underlying application.

Solution: Set the datatype explicitly in the parametrized query

<?php
$query = 'SELECT 1 FROM ExampleTable WHERE Name = ?';
$params = [['Somename', SQLSRV_PARAM_IN, null, SQLSRV_SQLTYPE_VARCHAR]];
$stmt = sqlsrv_query($con, $query, $params);
$ret = sqlsrv_fetch($stmt);
?>
This causes the server to use the following statement:
```sql
exec sp_executesql N'SELECT 1 FROM ExampleTable WHERE Name = @P1',N'@P1 varchar(max)','Somename'

And with this it can take the index.

Btw.: SQLSRV_SQLTYPE_VARCHAR(4000) did not have any effect and still resulted in "varchar(max)", but as this is just the parameter of a parametrized query, it should not have any effect IMO.

Upvotes: 0

cmorrissey
cmorrissey

Reputation: 8583

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

$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

Upvotes: 1

Related Questions