I've got PHP application I'm developing where I'm required to use MSSQL Server as my database source. As the sole developer of the application, I have the luxury of selecting what technologies I want outside of the database server, as its required by my company. So here's the problem.
I'm running PHP 7.0.6 on Zend Server 9.0 connected to a MSSQL SQL Server 2014 SP2 CU1 (IT Controlled Resource). The problem I have, is when I execute a query to table x, the database call completes in 3.62 ms (according to Zend Server). However, PDOStatement::fetchAll() takes on average 15 seconds to return the 27k results. I have tried various connection ways of retrieving the data, but nothing seemed to work.
Has anyone had any problems like this, and if so what was their solution. I am using the MSSQL DB Driver from here: https://www.microsoft.com/en-us/download/details.aspx?id=20098.
My connection setup for PDO is this:
$this->pdo = new PDO("sqlsrv:Server=".$this->host.";ConnectionPooling=0;Database=".$this->table
, $this->user
, $this->password
, array(PDO::ATTR_PERSISTENT => true)
);
Any help would be greatly appreciated. Thanks!
may be the query don't take too long but fetchAll builds an array of 27k results that it take a while to process. You will need to find another approach for this, you can paginate the query and show a range of elements per page. Here is a tutorial of how to do this. Pagination with PHP and PDO