The SQL Server 2022 runs on a Windows server, PHP 8.1 on a Linux Ubuntu server.
The database has the compatibility level 120 (SQL Server 2014)
When SQL queries are created using DQL or PDO, they are very slow compared to running the same SQL in Azure Data Studio.
But when I declare the parameters in the SQL, the speed increases.
Here is an example:
Slow query – Doctrine DQL
<code>$qb = $conn->createQueryBuilder()
->select('*')
->from('users', 'u')
->where('u.lastname = :lastname')
->setParameter('lastname', 'Smith');
</code>
<code>$qb = $conn->createQueryBuilder()
->select('*')
->from('users', 'u')
->where('u.lastname = :lastname')
->setParameter('lastname', 'Smith');
</code>
$qb = $conn->createQueryBuilder()
->select('*')
->from('users', 'u')
->where('u.lastname = :lastname')
->setParameter('lastname', 'Smith');
Slow query – PHP PDO
<code>$sql = '
SELECT
*
FROM users
WHERE lastname = :lastname
';
$stm = $conn->prepare($sql);
$stm->bindValue(':lastname', 'Smith');
</code>
<code>$sql = '
SELECT
*
FROM users
WHERE lastname = :lastname
';
$stm = $conn->prepare($sql);
$stm->bindValue(':lastname', 'Smith');
</code>
$sql = '
SELECT
*
FROM users
WHERE lastname = :lastname
';
$stm = $conn->prepare($sql);
$stm->bindValue(':lastname', 'Smith');
Fast query – PHP PDO with SQL variables
<code>$sql = '
DECLARE @lastname varchar(25)
SET @lastname = :lastname
SELECT
*
FROM users
WHERE lastname = @lastname
';
$stm = $conn->prepare($sql);
$stm->bindValue(':lastname', 'Smith');
</code>
<code>$sql = '
DECLARE @lastname varchar(25)
SET @lastname = :lastname
SELECT
*
FROM users
WHERE lastname = @lastname
';
$stm = $conn->prepare($sql);
$stm->bindValue(':lastname', 'Smith');
</code>
$sql = '
DECLARE @lastname varchar(25)
SET @lastname = :lastname
SELECT
*
FROM users
WHERE lastname = @lastname
';
$stm = $conn->prepare($sql);
$stm->bindValue(':lastname', 'Smith');
Why?
Is it due to the compatibility level?
Or the binding of the variables in PDO?
The PHP SQL driver is pdo_sqlsrv 5.12.0
What is the reason for this?
New contributor
sst is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
4