MSSQL vs SQLSRV Speed Comparison

For a good while, I have used PHP with a Microsoft SQL Server 2008 database on the back end. Microsoft have taken a keen interest in PHP and have contributed a great deal to the PHP community, none more so than by creating the Microsoft SQL Server Driver for PHP.

Before starting the latest project for our company, we chose to go down the route of PHP and Microsoft SQL Server 2008. Which creating the foundation for the project I encountered a very simple problem, which is best to use: the bundled mssql extension (php_mssql.dll) or Microsoft's own SQL Server Driver for PHP extension (php_sqlsrv*.dll). After a good while searching on Google for some comparisons between the two, I came up empty. In the end I decided we should use the classic mssql extension as it would be easier to install on client servers than an additional dll.

We have now completed this project, but since the project started, PHP 5.3 has been released and PHP is now compiled both with VC6 and VC9. Eventually the VC6 builds will be phased out (PHP 6 for example). With a bit of spare time, I wrote a simple test that would give me the speeds of the 2 drivers with some surprising results.


The Test

Pretty simple, write 2 classes with the same method names that call pretty much identical functions of the 2 extensions. A third file would be the main test. It would loop round a few hundred times. Each time it would load one of the classes, connect to the database, perform a series of tasks, disconnect and log the time taken to perform the query.

Before the test started, I created a new database with a single table with 4 columns.

The tasks were:


  1. Populate the table with 10,000 rows of data.
  2. Select the sum of one column, count the number of rows, do a calculation on the data.
  3. Select all 10,000 rows of data and put it into an array. Do this 3 times.
  4. Get each row of the table individually.
  5. Delete all data from the table.
The script used to test these would be run from the command line on a Windows machine.
Recording the Results
After a few preliminary test, I thought it best do as many iterations of the test as possible so that the results wouldn't be swayed by the SQL server being busy at a particular time. The extensions were tested alternately hundreds of times and for each iteration the length of time it took was recorded into a CSV file. This was run overnight when the servers were free to give the best possible result.
Getting the Figures
Now, I don't claim to be a statistician, but I knew that a few results could be off because of the server or client processor being used at the time by opening an application or running some task. Because of this the upper and lower 5% of the results have been discounted.


The Results

The test was run 1059 times against each extension. The average time is as follows (time in seconds, lower is better):

MSSQL (PHP bundled MSSQL extension) - 15.41713099
SQLSRV (Microsoft SQL Server Driver for PHP) - 16.76840312

From this test we can conclude that the original MSSQL extension is 8.76% faster than the newer Microsoft SQL Server Driver for PHP.

Now before everyone goes and changes back to MSSQL from SQLSRV, you should remember that the new extension has many new features, which if used correctly could make your database lookups more efficient. The main MSSQL draw back that I have found is that column names are truncated to 30 characters. Going forward, PHP 6 most likely won't support the original MSSQL extension (as PHP 6 will only be built using VC9).


Please remember that I can't guarantee the accuracy of the results, but I'm going to make the most of them just now! Comments always welcome :)


Excellent post

Excellent post! I agree with you on this one. Cheers!!

Thank you

Thank you for your TEST.