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 SMS 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?
I've cross-posted this here in response to the same question on stackoverflow: https://stackoverflow.com/questions/15835623/why-is-php-mssql-very-slow-when-the-db-is-not-on-the-same-machine-as-the-php