{"action":"opened","issue":{"url":"https://api.github.com/repos/Microsoft/msphpsql/issues/743","repository_url":"https://api.github.com/repos/Microsoft/msphpsql","labels_url":"https://api.github.com/repos/Microsoft/msphpsql/issues/743/labels{/name}","comments_url":"https://api.github.com/repos/Microsoft/msphpsql/issues/743/comments","events_url":"https://api.github.com/repos/Microsoft/msphpsql/issues/743/events","html_url":"https://github.com/Microsoft/msphpsql/issues/743","id":312638498,"number":743,"title":"Potential Performance issue with PDOStatement->execute with 30+ bound parameters","user":{"login":"sbtsrbayer","id":5262641,"avatar_url":"https://avatars3.githubusercontent.com/u/5262641?v=4","gravatar_id":"","url":"https://api.github.com/users/sbtsrbayer","html_url":"https://github.com/sbtsrbayer","followers_url":"https://api.github.com/users/sbtsrbayer/followers","following_url":"https://api.github.com/users/sbtsrbayer/following{/other_user}","gists_url":"https://api.github.com/users/sbtsrbayer/gists{/gist_id}","starred_url":"https://api.github.com/users/sbtsrbayer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sbtsrbayer/subscriptions","organizations_url":"https://api.github.com/users/sbtsrbayer/orgs","repos_url":"https://api.github.com/users/sbtsrbayer/repos","events_url":"https://api.github.com/users/sbtsrbayer/events{/privacy}","received_events_url":"https://api.github.com/users/sbtsrbayer/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-04-09T18:30:56Z","updated_at":"2018-04-09T18:30:56Z","closed_at":null,"author_association":"NONE","body":" ## Driver version or file name\r\npdo_sqlsrv.so  \r\n(I had had the sqlsrv.so active at one time but disabling it didn't seem to make a difference)\r\nI had more or less followed the instruction here:\r\nhttps://blogs.msdn.microsoft.com/sqlnativeclient/2017/02/04/odbc-driver-13-1-for-linux-released/\r\nFollowed by \r\n`sudo pecl install sqlsrv pdo_sqlsrv` \r\n\r\n ## SQL Server version\r\n2012 Developer Edition\r\n\r\n ## Client operating system\r\nUbuntu 16.04\r\n\r\n ## PHP version\r\n 7.1 and 7.2\r\n \r\n ## Expected behavior and actual behavior\r\nI 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:\r\n`$conn = new PDO( \"dblib:host=$host:1433;dbname=$db;\", $uid, $pwd);\r\n$stmt = $conn->prepare( $query );\r\n$stmt->execute($param);`\r\n\r\nit would run in minutes or basically never return.  However, if I ran it with the parameters already input like this: \r\n`$conn = new PDO( \"dblib:host=$host:1433;dbname=$db;\", $uid, $pwd);\r\n$results = $conn->query( $query_with_parameter_already_bound );`\r\n\r\nThen it would return in a couple of seconds.  Unfortunately the php framework larave uses the slow method.\r\n\r\nI 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'):\r\nRPC:STARTING\r\ndeclare @p1 int\r\nset @p1=NULL\r\nexec 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 \r\nnvarchar(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 \r\nnvarchar(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'\r\nselect @p1\r\n\r\nIt 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.\r\n\r\nI 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.  "}}
{"actor":{"display_login":"sbtsrbayer"}}