php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #21772 mssql speed when using remote server
Submitted: 2003-01-20 07:51 UTC Modified: 2004-05-13 01:00 UTC
Votes:3
Avg. Score:4.3 ± 0.5
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: pvy at novosoft dot ru Assigned:
Status: No Feedback Package: MSSQL related
PHP Version: 4.2.3 OS: windows 2000/sp3
Private report: No CVE-ID: None
 [2003-01-20 07:51 UTC] pvy at novosoft dot ru
Hello!

I made small script for determine difference between remote and local MSSQL server use.
I don't put this script (it is very simple: 1000 loops of db_connect, 1000 loops of mssql_select_db, 1000 loops of mssql_query with load 1000 records rowset, 1000 loops of call stored procedure)
Results of my research:
1. Local DB Server - Local Apache Web server - working about 1000 secounds
2. Remote DB Server - Local Apache Web server - working about 3500 secounds

Remote server connected over TCP/IP network via one 100Mb switch (3com office connect) -network utilization is low when I made test.
Ping to DB server less than 10ms and no packets lost detected when I ping 100 times with block size 65500

Same tests, using MSSQL Query Analyser got same results for remote and local server. So, as far as I understand, there is PHP problem, occurs with remote DB server using.

I want hear any opinion, exept use snap version of php (I tested also in SNAP version two days ago)


Your, 
vladimir,
Novosibirsk,
Russia.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2003-01-20 08:01 UTC] pvy at novosoft dot ru
I forgot one detail:
I test both MSSQL 7 and MSSQL 2K with latest services packs, 
supplied from MS.
 [2003-01-20 13:52 UTC] iliaa@php.net
Sorry, but your problem does not imply a bug in PHP itself.  For a
list of more appropriate places to ask for help using PHP, please
visit http://www.php.net/support.php as this bug system is not the
appropriate forum for asking support questions. 

Thank you for your interest in PHP.

When dealing with i-net connections even if they are local there is a lot more additional work that needs to be done. I wouldn't think this would amount to 3.5 times speed difference, but with Win32 you never know.
PHP does not initiate the connection to the SQL server itself, it does it by using the appropriate libraries. So, I do not believe that PHP could be at fault here.
 [2003-01-21 03:47 UTC] joey@php.net
Actually, we have done extensive testing on this bug.

If I write a C++ program to run the same query results, it operates as expected.

If I run the query in 'Query Analyzer', a Microsoft tool, it operates as expected.

If I run the query via PHP from a LINUX box (compiled with MSSQL support via the FreeTDS drivers), it operates as expected.

If I run the query via PHP from a Windows box, LO AND BEHOLD, 500-600% longer to run the query!

This is most definitely something wrong between PHP and the MSSQL libraries on Win32 machines. I'll add my post from php-db that has better details in a moment.
 [2003-01-21 03:49 UTC] joey@php.net
We've been using PHP hosted on a win32 machine to connect 
to SQL Server 2000.  When the PHP host and the MSSQL host
are the same machine, everything is fine. But when we try
to use seperate hosts for PHP and MSSQL, query times become
unbearably slow.

The odd thing is that I can run the same scripts on the
same network connecting to the same MSSQL server from a Linux box, and see acceptable response times.

As an example, I did a script that does 1000 iterations
of 'sp_sproc_columns @procudure_name = "some_proc"' in three
different setups.


Setup 1:
  MSSQL and PHP on the Win2k host, call it machine 'A'
  Average Response time over 5 iterations of script: 18s

Setup 2:
  MSSQL on host 'A', PHP on Linux host ('B')
  Average Response time over 5 iterations of script: 41s
  (Given the size of the result set, this is an acceptable
   response time.)

Setup 3:
  MSSQL on host 'A', PHP on seperate Win2k host ('C')
  Average Response time over 5 iterations: 3 minutes, 20s

Clearly, there is something wrong here. I had a different admin set up a similar network without looking at my php.ini, etc., and he had similar results.
 [2003-01-21 03:51 UTC] pvy at novosoft dot ru
Thank you Joey!
Good work!
 [2003-01-21 11:24 UTC] fmk@php.net
Hi,

I'm unable to reproduce these diferences on my systems.

I have tested the speed on a SQL Server 7.0 and I get almost the same response times on local and remote connections. In my case the local connects are slower due to the fact that both PHP and MSSQL server is competing on the CPU.

If there is a diference from Linux to Windows this must be caused by old versions of DBLIB used on Windows. I'm working on using FreeTDS on Windows.

Other factors can be how you configure your MSSQL Server and Clients. If you don't do anything MSSQL defaults to named pipes (approx 8 times slover on a network but faster on a local connection compared to TCP/IP).
You should use the Client Network Configuration tool to specify default libraries and configure aliases for your local and remote servers. Then use this alias when connecting to the server.

When you compare PHP and MS Queary Tool you compare apples and oranges. PHP is build on an old database protocol and MS Query Tool uses the most modern technology available.

- Frank
 [2003-01-21 12:27 UTC] iliaa@php.net
Sorry, but your problem does not imply a bug in PHP itself.  For a
list of more appropriate places to ask for help using PHP, please
visit http://www.php.net/support.php as this bug system is not the
appropriate forum for asking support questions. 

Thank you for your interest in PHP.

There are some networking issues when Linux & Win32 machines communicate. For example a stock 100mbit lan only operates at 1/3 - 1/2 speed while if both machines run the same OS all 100mbit are avaliable.
Since this appears to be a networking issue, I am marking this a bogus (not a php bug).
 [2004-05-05 08:47 UTC] reubs at idsdatanet dot com
We have the same problem - but there is an interesting twist. Was running through the test in a progressive manner.

1. Vanilla install on local machine MSSQL2k/PHP433 - works as expected without any service packs --- Machine A

2. Vanilla install on remote machine MSSQL2k - unacceptably slow - response and processing time went from 1 second to 15-20seconds. -- +Machine B - colleague ran a packet sniffer and found fragmented requests

3. Installed latest service packs on Machine B, fragmented packets are reduced. Speed is still slow - improvements were minor.

-- here is where you have to read carefully.

4. Installed latest service pack on Machine A (remember machine A had a version of MSSQL2k but not used as the target DB) - Speed went up to equivelant of MySQL/PHP, impressive, problem solved(?) - assumption must install connector service pack upgrade on Web server as well

5. Client complained - no improvement,

6. Uninstalled the MSSQL server on my webserver - installed just the connectors, ran the MDAC update patches - Result, same as note 2 - 15-20second response time.

Obviously there seems to be a difference in MSSQL Connector DLLs. Have to look into this (unless someone here know what is going on) but I must admit my suggestion to them would be a LAMP config.

--sob story - basic rant start
The excessive finger pointing on bug ownership in the end puts us at risks of losing a key client as we come up to no solution but a lot of doubts by the client on the platform of choice - and the IS department having spent a lot of money on MSSQL will blame it on PHP first before putting the blame on their choice of corporate db.
 [2004-05-05 10:47 UTC] wez@php.net
Are you guys running under IIS ?
 [2004-05-05 10:48 UTC] wez@php.net
Should clarify what I mean... are you using a server module version of PHP: IIS/ISAPI, or Apache/mod_php ? 
 [2004-05-05 16:24 UTC] reubs at idsdatanet dot com
Running IIS5.x/CGI PHP 4.3.3
 [2004-05-13 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sun Nov 10 02:01:27 2024 UTC