CODEWITHSUNDEEP
php
jdsmith2816
jdsmith2816

Reputation: 325

php sqlsrv_fetch_object performance

Greetings!

I am attempting to solve a few performance issues which are cropping up on our site. As part of this effort I have begun profiling our most trafficed pages according to GA.

Essentially I have determined, as I originally suspected, the majority of time spent is in database related traffic... makes sense. Unfortunately, when looking closer, what I found DOESN'T make quite as much sense.

For example, this particular call, MyAlganon_Model_Library_Item::fetchItemTooltipsByItemIdArray() takes 580.41ms to return on my staging platform.

Upon diving into it 47.27ms of that time is spent on sqlsrv_query() while 533.12ms of it is spent in a fetchAll() function that simply loops doing sqlsrv_fetch_object:

$row = sqlsrv_fetch_object($this->query);

while ($row) {
  $results[] = $row;
  $row = sqlsrv_fetch_object($this->query);
}

return $results;

Looks like, on this particular page, there are 742 rows in the result set. Maybe it's just a matter of me not having a realistic handle on how long it takes to generate an StdClass object? Does 533.12ms seem like a normal time to iterate across 742 results and turn them into an array of objects?

Upvotes: 1

Views: 2117

Answers (3)

hood
hood

Reputation: 141

I've been trying to use sqlsrv too and found it can be really slow, sometimes taking several seconds to connect and perform a simple query - even on a table with a dozen rows and a handful of columns. It was faster on my development system (connecting to a local SQL Express) than on the destination host (real SQL Server, expensive premium hosting).

The web host even did some tests and said "This seems to suggest that the problem lies with the Microsoft SQL Server Driver for PHP, and not necessarily with the web or database servers or network conditions." He was comparing it to using ADODB: "Please note that this is an ODBC connection, this is not using the native PHP MSSQL driver which frankly, is unstable and should be avoided. [...] The test script above is routinely performing significantly better than the other test script that connects using the Microsoft SQL Server Driver for PHP."

However on my own system, sqlsrv_connect was a lot quicker to connect (usually between 0.1 ms and 100 ms) than $conn=ADONewConnection('odbc_mssql');$conn->Connect(); (usually between 30-200ms). Still testing other speeds: this project was more latency-sensitive than anything else.

A little note: you can update your provided snippet: (a little cleaner)

while (($row = sqlsrv_fetch_object($this->query))) {
  $results[] = $row;
}
return $results;

Upvotes: 0

jm.
jm.

Reputation: 41

Depending on what you are doing the mssql_ is about 33-60 % faster than sqlsrv_ in my testing

Upvotes: 4

Jimmy Shelter
Jimmy Shelter

Reputation: 1540

As you said in your comment, you query 742 rows with 41 columns.

That's quite some data you're using. If you use all 41 columns, but just of a few of the rows, you could split your query. More queries is not always inefficient, if you can optimize to get less data in total.

Are the 41 columns of all 742 rows used? Maybe you can do a simple query to select which of the 742 rows you need all data for.

Upvotes: 1

Related Questions