Very Slow Results. PHP 5.3 SQLSRV

PHP
1

A client has an application running in PHP 5.3 using SQLSRV. If the results returned to the page are very low < 20 records it runs well enough. As the number of records returned increase so does the time spent waiting. 400 records results in a 40 second wait. 1000 records basically breaks the application.

I’ve tried adding MultipleActiveResultSets to the connect string but that results in 1 record being returned. Much faster though…

I’ve tested the queries via SSMS and Access Pass Through Queries and 4,000 records are returned in less than 1 second.

2

Is there a way you can test it using a newer version of PHP? 5.3 was released in 2009, and supported until 2014. I don’t have any specific knowledge, but it might be that a newer version has some optimisation.

3

As recommended, you should look at upgrading the system to at least 5.6 (Though this one, just have security support at the moment to the end of the year).

For the issue you describe, I would look at the query and also the table(s) in question. It sounds like you are missing an index or perhaps have wrong indexes (compared to what the query need).

4

They say 7.# performs a lot better than 5.#
Certainly 5.3 is too old to be still using.

5

Is there a single particular query that’s slow?

6

I did try PHP 7.1 and the queries ran even slower. It is still installed so all I have to to is flip the handler back to the 7.1 FastCGI. I’ll do that and test again.

7

If the query runs perfectly well directly (i.e. not via the PHP code) but runs very slowly from the PHP code, could there be some kind of issue in the PHP that is contributing to it?

8

I restarted IIS and tested PHP 5 - 23.3 seconds for 79 records.
I changed the handler to PHP 7 - 11.35 seconds for the same 79 records so the upgrade itself is a 12 second boost.

Testing the query as written outside of the original PHP code (in it’s own sqltest.php file) reveals that the query is pulling every item on every order over the last 3 days. It takes 14.36 seconds to pull 2558 records. This seems like a long time for 2.5k records.

Testing the query as an Access Pass Through query I get the same 2558 records in 1 second.

9

Using WAMP 3.1.0 64 bit with PHP 5.6, sqltest.php took 3.23 seconds for 2577 records.

10

Is it perhaps doing a full table scan because a lack of indexes?

Try running the same query with an “EXPLAIN” prepended to it and see if that offers any insight.

11

Do you have phpMyAdmin available to use? If you do, when PHP 7 is used, what’s the time taken for the queries?

12

3 table scans were taking place. Indexes are now in place. SSMS runs the query in a heart beat but PHP site takes 21.22 seconds to pull 245 records.

13

From all that you have said, I have hard to believe this is just “bad” drivers on the PHP side.

Now, with SQL Server it is expensive to connect to the server compared to MySQL.

-Have you verified that the PHP code is not dropping the connection and reconnecting?

-Have you checked how long PHP use on actually creating the connection to SQL Server?

-Have you considered checking if it speeds up if you switch the connection library to PDO?

14

Be weary of just switching an application from php 5.3 to 7 without testing all the other pages. There are major breaking changes from 5.3 to 7 that might not be immediately apparent until things just stop working in production. For a major application with large code base upgrading from 5.3 to 7 should be considered a project itself not just an after thought to a a smaller problem. In this case a problem that doesn’t sound like it has anything to do with the php version. That being said you should try to update the project to a supported version of php in the very near future. Running a project on 5.3 is unideal and a likely security vulnerability.

15

Your problem sounds like it has to do with an unoptimized query. I would recommend dumping the query and running it via the terminal to compare. The other cause of a slow page is running more queries in a loop. This can be fixed by using joins. This is all though speculation.

16

I believe the problem is within the application itself but I don’t know why. Let’s see if I can explain this…

If I put the query into a barebones .php file. Just the bare minimum to connect and display the results so that they look like they were typed out in notepad the query pulls 251 in 1.56 seconds.

Running the query from the application takes 16.32 seconds for the same 251 records.

The app is using Bootstrap, Jquery, Datatables, etc… The application is going to be rebuilt from the ground up using MySQL. I need to get the existing application faster and stable to last another year+ until the switch over takes place.

The comparison of running the same query on the same server inside versus outside of the application producing a 14.76 second time difference… I don’t know if it is capable of repair.

17

Is there an excerpt of code you can share from the app? Starting with the query to the contents of the loop that processes the results from the initial query.

18

Have you checked the points I mentioned above?

It should be easy to review that either by using a debugger, or simple echo/var_dump inside the code handling the affected code.

19

Errors are pretty well cleared up. Found a call that was looking for a version of jquery that no longer existed and missing morris.min.js.

Unfortunately, the system is still slow so we cut back the query to pull 2 days of data. For some this results in 2 records and others 95. Needless to say the 95 records take 20 seconds where the 2 records are pretty instant. The application is as good as it is going to get.

There is one remaining problem but that, I believe, warrants a new question.

21

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.