Search code examples
sql-serverdatabaseperformancesql-server-2008php-5.2

How to speed up mssql_connect()

I'm working on a project where a PHP dialog system is communicating with a Microsoft SQL Server 2008 and I need more speed on the PHP side.

After profiling my PHP scripts, I discovered that a call to mssql_connect() needs about 200 milliseconds on that particular system. For some simple dialogs this is about 60% of the whole script runtime. So I could gain a huge performance boost by speeding up this call.

I already assured that only one single connection handle is produced for every request to my PHP scripts.

Is there a way to speed up the initial connection with SQL Server? Some restrictions apply, though:

  • I can't use PDO (there's a lot of legacy code here that won't work with it)
  • I don't have access to the SQL Server configuration, so I need a PHP-side solution
  • I can't upgrade to PHP 5.3.X, again because of crappy legacy code.
Solution

  • Hm. I don't know much about MS SQL, but optimizing that single call may be tough.

    One thing that comes to mind is trying mssql_pconnect(), of course:

    First, when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection.

    But you probably already have thought of that.

    The second thing, you are not saying whether MS SQL is running on the same machine as the PHP part, but if it isn't, maybe there's a basic networking issue at hand? How fast is a classic ping between one host and the other? The same would go for a virtual machine that is not perfectly configured. 200 milliseconds really sounds very, very slow.

    Then, in the User Contributed Notes to mssql_connect(), there is talk about a native PHP driver for MS SQL. I don't know anything about it, whether it will pertain the "old" syntax and whether it is usable in your situation, but it might be worth a look.

    The User Contributed Notes are always worth a look, there are tidbits like this one:

    Just in case it helps people here... We were being run ragged by extremely slow connections from IIS6 --> SQL Server 2000. Switching from CGI to ISAPI fixed it somewhat, but the initial connection still took along the lines of 10 seconds, and eventually the connections wouldn't work any more.

    The solution was to add the database server IP address to the HOST file on the server, pointing it to the internal machine name. Looks like some kind of DNS lookup was the culprit.

    Now connections and queries are flying, and the world is once again right.