CODEWITHSUNDEEP
phpsql-serverazure
Tony Harrison
Tony Harrison

Reputation: 78

Why is PHP -> MSSQL very slow when the DB is not on the same machine as the PHP code

In the course of trying to move our website to Azure, we've found a problem with our PHP code talking to MSSQL.

On the current dedicated host (hosting both the PHP and the DB), going to a page typically takes less than 2 seconds. I've also run the queries necessary through SSMS and they take less than 1 second. The amount of data being returned by the queries is very small - 10-20 records, certainly less than 100kb in total.

With the website hosted on Azure (connecting to an Azure SQL database) the same page takes around 40 seconds to load. Further investigation showed that this might be caused by the database not being on the same machine as the PHP - when the original website is pointed at a remote database (either another dedicated machine or the Azure SQL instance) the page load time goes up into the 40 second range.

In the Azure case, both the website and db are located in the same region (N Europe). When testing using our machines, both are located in the UK.

The current webhosting machine is running PHP 5.4 and Microsoft SQL Server Express Edition. The Azure website is running on PHP 5.4.

In both cases we're using the 3.0.1 Microsoft SQLSRV drivers

How can we improve the performance when connecting to a remote SQL DB?

This question has been cross-posted to Server Fault at https://serverfault.com/questions/496505/why-is-php-mssql-very-slow-when-the-db-is-not-on-the-same-machine-as-the-php

Upvotes: 0

Views: 4210

Answers (2)

manuel
manuel

Reputation: 1840

In my case it was slow DNS resolving, after putting IP instead of MSSQL name everything worked fine

Upvotes: 2

David Makogon
David Makogon

Reputation: 71120

No clue why your response time jumps from 2 to 40 seconds, but... a few things to consider:

  • If your SQL Database instance is in another data center, you'll see both latency and bandwidth charges. If the data centers are separated by continent (e.g. one in US West, one in Hong Kong), you'll see more latency than, say, US West <--> US East).
  • If you're transferring a significant amount of data as a result of your query, this will be bound by network pipe size. You get 100Mbps per core, but Extra Small (XS) instances only have 5Mbps bandwidth.

You mentioned testing the query in SQL Server Management Studio (at least I'm assuming SMS is really SSMS). What was the query performance like when testing against SQL Database? If it's close to the 40-second mark, quite possibly there's a missing index from when data was ported from local database to your SQL Database instance.

Upvotes: 1

Related Questions