1

I have a problem with a couple of queries running in PDO on PHP connecting to MSSQL server 2019. I have checked the time in SSMS and its fast. I have also added some timers into my code so I can see the time to execute the query and the time to fetch the data.

I have found the fetching of the data is the issue.

The query is about 0.5 seconds, but the data is taking 5-10 seconds to come across to PHP. Its less than 500,000 bytes as text, so its a small dataset really of 9000 rows. its all running in AWS. PHP and SQL are in the same availability zone and on the same subnet. Connection between them is good and seconds for such a small dataset seems crazy... so assuming its a PHP setting?

My prepare statement has the following options:

$stmt = $this->DB->prepare( $query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL) );

Does anyone have any ideas how I can speed it up? TIA!

Edit: I have also used SSMS from the remote machine and the query is very fast; so the issue must be in the PDO connection and/or settings.

Charlie
  • 11
  • 2
  • What is the execution time, when you use the default cursor: `$stmt = $this->DB->prepare( $query);`? – Zhorov Mar 14 '22 at 08:32
  • This solved it! :) Thank you. The fetch time is about 200 (!) times faster with the cursor not set for larger queries! – Charlie Mar 19 '22 at 14:04

0 Answers0