none
Slow sqlsrv_connect times

    Question

  • Hi All, I've just setup the MS SQL PHP (the latest OCT download) driver on my Vista SP1 (x64) box and all seems to work fine except it takes about 5 seconds to connect to the server (SQL Express 2008).
    After that all is fine.

    I've tried:
    - Windows and SQL authentication.
    - Setting the default database in the connection and/or in the user properties.
    - Disabling encryption and trusting the server cert.
    - And of course setting "ConnectionPooling" to true - which is default anyway.

    Is there something I'm missing in the IIS or SQL Server settings?
    I'm using FastCGI for my PHP (5.2.6) setup, should I use ISSAPI? - I hate that for development.
    Is this normal?
    Does connection pooling work?
    Connecting to MySQL is instantaneous....

    Thanks, Bill
     
    Wednesday, November 12, 2008 10:43 PM

Answers

  • Bill,

    Sorry it took me so long to get back to you on this issue.  Could you post the script with the timing calls included?  I'd like to try running the script in house.

    David Sceppa
    Program Manager - Microsoft SQL Server Driver for PHP
    • Proposed as answer by David Sceppa Tuesday, April 07, 2009 5:42 PM
    • Marked as answer by David Sceppa Monday, April 27, 2009 5:22 PM
    Monday, March 23, 2009 5:13 PM

All replies

  • That does seem excessive.  Is it constantly 5 seconds, or do subsequent executions of the script connect faster?

     

    You should be using FastCGI.  That is the recommended configuration.

     

    Connection pooling works on a per PHP process basis.  If I recall correctly, PHP requests are sent round-robin to the existing PHP fast cgi processes.  If this is true, then the first several connections will have this problem based on how many PHP processes you have set up in the FastCGI configuration.

     

    Also, as you probably know but I'll repeat anyways, connection pooling only works if you use the same connection attributes each time.  Otherwise, a new connection is established.

     

    What is the configuration you're using MySQL under?  Identical?

     

    Jay

     

     

     

    Thursday, November 13, 2008 5:02 PM
    Moderator
  • Thanks for the reply Jay, sorry it took me so long to get back to you.

    Well, I've been playing around with it a bit today and found if a disable the php_mysqli.dll extension in the php.ini then the sqlsrv_connect call is instant. Note that the MySQL connections are also instant with or without the MS extension loaded.
    I'm not making any calls to the mysqli interface - but I need it loaded for other pages.  Actually the page I've implemented must connect to both servers - it works fine except for the 6 second connect delay.

    Note that for testing the only line in the script is the connect call:

    Code Snippet

    <html>
    <head>
    <title>PHP Testing</title>
    </head>
    <body>
    <h3>PHP MS SQL Test</h3>
    <?php
    $db = sqlsrv_connect( 'LOCALHOST\SQLEXPRESS');

    if ($db){
    echo "Connected to MS SQL server!<br>";
    sqlsrv_close( $db);
    }
    else{
    echo "Unable to connect to MS SQL server. <br>";
    }
    ?>
    </body>
    </html>


    Easy for me to reproduce, I just enable & disable the mysqli extension:
    ;extension=php_mysqli.dll

    Could you possibly verify this for me? - easy test.
    Again this is under Vista x64, SQL Express 2008 and MySQL v5.0.67 but I get the same problem under Server 2003 - I'll verify this with SQL Sever 2005 under XP.

    PS: I just tested this under XP (sp3) with new installs of MS SQL 2005 Express, PHP and MySQL with only the mysqli and sqlsrv_ts extensions loaded, exact same problem.

    Thanks,

    Monday, November 17, 2008 7:51 PM
  • Bill,

     

    Have you tried adding the current time to your echo calls, and adding another echo call prior to the call to sqlsrv_connect to see whether or not the delay is related to the call to sqlsrv_connect?

     

    David Sceppa

    Program Manager - Microsoft SQL Server Driver for PHP

     

    Wednesday, November 19, 2008 6:25 PM
  • I just tried timing the call and the times show that the page is instantly executed but I still have the 6 second wait before the page is displayed -  Apparently vindicating the sglsrv_connect call.
    However, if I comment out the sqlsrv_connect call the page executes instantly - which does point at the connect call - or at least some kind of late-binding/init problem somewhere.

    I certainly don't know enough about how PHP scripts are executed to comment on this, but I have reproduced this on 3 different machines (Vista, XP and 2003) with SQL Server 2005 & 2008. (php setup with Fast/CGI)

    Again; to reproduce this all I have to do is load the php_mysqli.dll extension OR the php_mysql.dll OR the php_PDO.dll and php_pdo_mysql.dll. 

    Have you tried loading one of the mysql extensions along with the MS extension?
    (should take about a 60 seconds to test...)

    PS: I just tested this using the php5isapi.dll to handle the page (as opposed to php-cgi.exe) and it runs instantly...
    Thursday, November 20, 2008 4:55 PM
  • Bill,

    Sorry it took me so long to get back to you on this issue.  Could you post the script with the timing calls included?  I'd like to try running the script in house.

    David Sceppa
    Program Manager - Microsoft SQL Server Driver for PHP
    • Proposed as answer by David Sceppa Tuesday, April 07, 2009 5:42 PM
    • Marked as answer by David Sceppa Monday, April 27, 2009 5:22 PM
    Monday, March 23, 2009 5:13 PM
  • I am also having this issue.  If I include extension=php_mysql.dll the sqlsrv_connect() function takes around 6 seconds if I comment out the msyql in my php.ini it happens instantly.
    Wednesday, April 29, 2009 5:23 PM
  • I've got the same problem here. Running IIS7.5 on Windows 7  with php 5.2.6. I might have to say, that only the first connection is taking constantly 5 seconds, the second connection (to another database on the same server) connects in a few millis. After that, it's fast for like 5 minutes... and then it's taking 5 seconds again. I have the very same PHP installation on a windows erver 20080 with IIS 7.5, NOT having this issue. Also I might add, that mssql_connect() is taking 5 seconds too.

    Does anyone have any idea how to find the issue here? I deactivated windows firewall, ipv6 and all the other stuff that could cause my connection to be slow. but it's either way 5 seconds all the time.

    Friday, May 21, 2010 1:42 PM
  • Wow, a really old thread. But a disconcerting issue nonetheless.

    Michael (and Ben if he's still subscribed to the thread),

    Is the script/scenario the same as Ben's in the initial post? Do you have one with timing calls?
    Does this repro with PHP 5.3.x?


    Ashay Chaudhary Program Manager, SQL Server Driver for PHP
    Thursday, May 27, 2010 12:51 AM
    Moderator
  • Hi Guys,

    I too have a similar problem and want to post it here because it may offer some enlightenment for all.

    My Server: Win2k3, IIS v6, PHP 5.2.13, MySQL 5.1.

    When the mysql extension is enabled in php.ini then every php page load takes about five seconds regardless of whether it makes any DB calls or not. I have disabled all other extensions.

    There are a number of tricks to help performance, so I've turned on output buffering in PHP and turned off dns lookups in MySQL. No luck.

    Does anyone have any suggestions? This is a very frustrating problem and there is very little info online.

    Ash

    Tuesday, June 01, 2010 6:26 PM
  • Sorry to reply to myself. Seems like the problem is described here: http://forums.cacti.net/about25135.html&highlight=

    And in detail here: http://bugs.php.net/bug.php?id=41350

    And the fix is to use a copy of libmysql.dll from an old version of PHP.

    Hope this helps people,

    Ash

     

    Tuesday, June 01, 2010 6:29 PM
  • Ash - Looks like you found the cause of the problem and the solution. Thanks.
    Ashay Chaudhary
    SQL Server Driver for PHP (http://blogs.msdn.com/sqlphp)
    All information provided "as-is" and without warranty.
    Tuesday, June 01, 2010 9:49 PM
    Moderator