Score:1

PHP PDO Very Slow on Fetch

in flag

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.

Zhorov avatar
cn flag
What is the execution time, when you use the default cursor: `$stmt = $this->DB->prepare( $query);`?
in flag
This solved it! :) Thank you. The fetch time is about 200 (!) times faster with the cursor not set for larger queries!
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.