Potential Performance issue with PDOStatement->execute with 30+ bound parameters

[sbtsrbayer]

Driver version or file name

pdo_sqlsrv.so
(I had had the sqlsrv.so active at one time but disabling it didn't seem to make a difference)
I had more or less followed the instruction here:
https://blogs.msdn.microsoft.com/sqlnativeclient/2017/02/04/odbc-driver-13-1-for-linux-released/
Followed by
sudo pecl install sqlsrv pdo_sqlsrv

SQL Server version

2012 Developer Edition

Client operating system

Ubuntu 16.04

PHP version

7.1 and 7.2

Expected behavior and actual behavior

I am running a php framework called laravel, and for most of the queries everything worked fine. However, for some large queries (with mutliple joins and 30+ bound parameters) the request was returning very slowly. With freetds on php56 the response would come back in a few seconds, but using these drivers the request would take minutes or just time out. I also found that depending on how I executed the db call from the php would also change the time of execution. If I called the query with a PDOStatment like this:
$conn = new PDO( "dblib:host=$host:1433;dbname=$db;", $uid, $pwd); $stmt = $conn->prepare( $query ); $stmt->execute($param);

it would run in minutes or basically never return. However, if I ran it with the parameters already input like this:
$conn = new PDO( "dblib:host=$host:1433;dbname=$db;", $uid, $pwd); $results = $conn->query( $query_with_parameter_already_bound );

Then it would return in a couple of seconds. Unfortunately the php framework larave uses the slow method.

I ran sql server profiler and saw that the slow method was eventually making the call (I replaced my actual query with the string 'MY_LARGE_QUERY_HERE'):
RPC:STARTING
declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 nvarchar(4000),@P4 nvarchar(4000),@p5 nvarchar(4000),@p6 nvarchar(4000),@P7 nvarchar(4000),@p8 nvarchar(4000),@p9 nvarchar(4000),@p10 nvarchar(4000),@p11 nvarchar(4000),@p12
nvarchar(4000),@P13 nvarchar(4000),@p14 nvarchar(4000),@P15 nvarchar(4000),@p16 nvarchar(4000),@p17 nvarchar(4000),@p18 nvarchar(4000),@p19 nvarchar(4000),@p20 nvarchar(4000),@p21 nvarchar(4000),@p22 nvarchar(4000),@p23 nvarchar(4000),@p24
nvarchar(4000),@p25 nvarchar(4000),@P26 nvarchar(4000),@p27 nvarchar(4000),@p28 nvarchar(4000),@p29 nvarchar(4000),@p30 nvarchar(4000),@p31 nvarchar(4000)',N'MY_LARGE_QUERY_HERE,N'00100',N'02020',N'61045',N'61046',N'02960',N'Y',N'N',N'Y',N'CS',N'NC',N'DP',N'PP',N'Y',N'MP',N'2018-03-06 16:54:49.000',N'2018-03-06 16:54:49.000',N'00100',N'02020',N'61045',N'61046',N'02960',N'Y',N'N',N'Y',N'CS',N'NC',N'DP',N'PP',N'Y',N'MP',N'B'
select @p1

It looks like the driver is causing the php call to be redone as a stored procedure with a boatload of casting slowing the query down drastically. When I ran the same query via freetds and php56 I did not see the above type conversion take place in sql server profiler logs. I also didn't see this behavior when I used the faster executing code that uses the PDO query method (as pasted above). Also on using the pdo_dblib driver in php7 I also was able to get the query to execute in a few seconds.

I might have missed something in my setup, but I wanted to report this in case it was a bug or inefficiency in the driver code.

[yitam]

Thanks @sbtsrbayer for reporting this. It will considerably help us to look into this if you can provide a repro script comparing the difference between the performances of pdo query() and prepare(). The repro doesn't have to involve 30+ parameters as long as it proves that prepare() is significantly slower than running the same query using query(). Looking forward to hearing from you.

[sbtsrbayer]

@yitam, Thanks for the reply. Is it normal for pdo_sqlsrv.pdo to convert an execute() into a stored proc call with casting of all variables to an nvarchar(4000)? That's my guess to where the slow down is.

In terms of repro scripts, I'll take a look at making one if I have more trouble with the pdo_dblib library. The repro script would probably have to require 30 or so parameters because in my query if I remove a few of the parameters then the slow down almost entirely disappears.

[yitam]

Hi @sbtsrbayer
It really depends on how you bind the parameters and how many of which are input or output parameters, strings or integers, etc. As I said before, if you can provide a repro php script, we might be able to figure out why there could be potential performance issues. Thanks!

[yitam]

Any update, @sbtsrbayer ?

[shiva805]

@yitam I have same problem..
declare @p1 int
exec sp_prepexec @p1 output,N'@p1 int,@p2 int',N'SELECT CASE WHEN o.orderstatusoid < 605 THEN ''Open Pending''
WHEN o.orderstatusoid = 605 THEN ''Open Processing''
WHEN o.orderstatusoid = 606 THEN ''Completed''
END AS dstatus,
CASE WHEN datediff(day, o.lastmodified, GETDATE()) < 21
THEN datediff(day, o.lastmodified, GETDATE()) / 7
ELSE 3
END AS title,
COUNT(1) AS totalcount,
SUM(TotalAmount + FreightAmount + TaxAmount) AS total
FROM opsorder o
WHERE o.orderstatusoid in (601, 602, 603, 604, 605, 606)
AND o.merchantoid = @p1
AND o.pendingcancellationflag =0
GROUP BY
CASE WHEN o.orderstatusoid < 605 THEN ''Open Pending''
WHEN o.orderstatusoid = 605 THEN ''Open Processing''
WHEN o.orderstatusoid = 606 THEN ''Completed''
END,
CASE WHEN datediff(day, o.lastmodified, GETDATE()) < 21
THEN datediff(day, o.lastmodified, GETDATE()) / 7
ELSE 3
END
UNION
SELECT ''Pending Cancellation'',
CASE WHEN datediff(day, o.lastmodified, GETDATE()) < 21
THEN datediff(day, o.lastmodified, GETDATE()) / 7
ELSE 3
END AS title,
count(1) AS totalcount,
SUM(TotalAmount + FreightAmount + TaxAmount) AS total
FROM opsorder o
WHERE o.orderstatusoid in (601, 602, 603, 604, 605)
AND o.merchantoid = @p2
AND pendingcancellationflag = 1
GROUP BY
CASE WHEN datediff(day, o.lastmodified, GETDATE()) < 21
THEN datediff(day, o.lastmodified, GETDATE()) / 7
ELSE 3
END
ORDER BY dstatus, title',2125,2125
select @p1

if you see have only 2 parameters still difference is very huge..

[yitam]

Thank you @shiva-050865. Perfect timing! We will be looking into performance issues soon, and thanks for your inputs.

[david-puglielli]

@sbtsrbayer @shiva-050865 We have tried to reproduce the performance issues you are seeing and have not been able to do so. Could you provide some more information about how you are binding the parameters of the stored procedure, and if this happens with all stored procedures or only a specific one? A PHP repro script that calls the stored procedure would help.

[shiva805]

@devid
As I posted it is not a stored procedure.. when am using pdo_sqlsrv as php driver normal query getting executed with sp_prepexec.. the above query information I got from profiler. Even I noticed query going through more record scan compare to normal query

[david-puglielli]

@shiva-050865 Ok thanks for that info. Could you then provide some information about 1. the table schema you are using for your query so that we can try to reproduce the issue, and 2. What you are comparing to when you say difference is very huge?

[david-puglielli]

@sbtsrbayer @shiva-050865 I would also suggest trying your query with sqlcmd and seeing if you get the same performance hit. This issue where parameters get replaced by nvarchar(4000) types crops up occasionally and may be due to an issue in the underlying ODBC driver.

[david-puglielli]

Closing issue. The latest release includes some performance improvements. Please reopen if necessary.