When to Use the Microsoft SQL Server ODBC Driver for Linux

When I originally wrote the how to, I was frustrated with Microsoft’s PDO driver being so. Incredibly.

Slow…

In my tests, I was using a table with a few hundred thousand rows. This table had up-to-date indexes that were relatively useful and fast.

As a starting point, I tested the query on  SQL Management Studio and I could return a few thousand rows in 0.1 seconds on our test server.
For the second test, I used pdo_sqlsrv (the Microsoft provided PHP driver for SQL Server) to do the same query, and it would take around 20-30 seconds.
As a final test, I accessed the database via pdo_odbc (with the installed Microsoft SQL ODBC drivers), and the same query that had just taken 20-30 seconds returned just as fast as SQL Management Studio. PDO_SQLSRV was far too slow and we needed to move to something faster.

Our results of using pdo_sqlsrv versus pdo_odbc (with the Microsoft SQL Server ODBC Driver for Linux) were pretty staggering

Our results of using pdo_sqlsrv versus pdo_odbc (with the Microsoft SQL Server Native Client 10) were pretty staggering

Here are some items (that I wish someone had told me when I was starting down this path) for you to consider when making your choice between pdo_odbc (using Microsoft SQL Server ODBC drivers), PDO_SQLSRV (Microsoft’s native SQL Server driver for php), or using the open-source freetds driver:

  • When choosing the odbc route – you lose a lot of the data type inference casting that the SQL Server driver for your programming language (such as pdo_sqlsrv) will do for you automatically, such as taking a date in the format of “03/01/2013” (March 1st, 2013) and inserting into a ‘date’ column as ‘2013-03-01’. This is no big deal if you start out knowing that these features aren’t there, but for porting code – it would’ve been nice to know right off the bat.
  • Need to connect to SQL Server older than SQL Server 2005? Forget about using ODBC (pdo_odbc in my case) method; it will continually complain and give you all sorts of fun errors that will be generally meaningless, yet when you switch to pdo_sqlsrv – it will instantly work. Awesome right? Your other choice in this situation is a freetds connection via odbc; this also has worked well for me and I will try to put up a tutorial on getting that up and running easily in the near future.
  • Do you only need to run a few queries here and there or will you be returning less than 1000 results from the database per query? You should probably just use the  driver that comes with your programming language (pdo_sqlsrv in my case) as it’ll be the most painless and the easiest to get started with.
  • Do you have a lot of code that is currently accepting strange formats for date (including slashes?) or do you have a lot of old stored procedures? You’ll probably want to go with (in order of preference): your language’s driver (pdo_sqlsrv in my case), freetds, and lastly the odbc route. Why is odbc last in this case? Because all your code, including stored procedures, will need to be modified in any places that you’re depending on implicit type casting that SQL Server or the current driver is doing for you. The odbc route also causes all input parameters to be accepted in as the C type ‘SQL_C_WCHAR’ more or less, which will only be implicitly cast to one of three SQL character types. If you go the odbc route, you’ll likely have to perform quit a few CAST(CAST(SOMETHING AS VARCHAR) AS SOMEOTHERTYPE) operations.

So – if you’ve gotten this far and still want to read the rest of my original post – here it is (also, this is the ODBC C Datatype to SQL DATA TYPE conversion chart – you’ll likely need this if you go the ODBC route).

3 thoughts on “When to Use the Microsoft SQL Server ODBC Driver for Linux

  1. Pingback: Gregory Smith

Comments are closed.