I'm trying to get some clues on why I get (much) slower responses from
my PHP applications when dealing with a remote sql server as opposed to
a local sql server.
Here's my situation:
Server #1
MSSQL 2000 sp4
Windows 2000 Server
PHP4 on IIS5 (CGI)
Server #2
MSSQL 2000 sp4
Windows 2003 Server
PHP4 on IIS6 (CGI)
Server #1 and #2 are on the same local network and the same switch. We
use TCP/IP as the connection protocol and our connections use IPs
instead of domain names.
Server#1PHP --> Server#2 MSSQL = slow
Server#2PHP --> Server#1 MSSQL = slow
Server#1PHP --> Server#1 MSSQL = fast
Server#2PHP --> Server#2 MSSQL = fast
This slower behavior is most noticable when many stored procedures
(10-15) are being executed on one php page. When the database is local
on a page such as this, the response time is around 2 seconds. When the
sql server is remote, the response time is between 8 and 15 seconds.
Does this make sense at all? Does anyone have any ideas of what the
bottleneck could be?
Hi
You may want to look at how your network is performing using an network
monitoring tool and perfmon. A local SQL Server could be using shared memory
which may be more significant on a poorly performing network.
You can also use SQL profiler to compare the times taken to execute the
stored procedures on each of the servers, this will allow you to check if
they are similar or acceptable if they are on different hardware.
Check that indexes are not fragmented and statistics are up to date on each
machine. Make sure that you have either a maintenance plan or some job that
will run periodically to maintain your indexes and statistics.
You may also what to check fragmentation of the physical disc. If you HDD is
very fragmented you may want to stop SQL Server and defrag it.
John
"gharmel" wrote:
> I'm trying to get some clues on why I get (much) slower responses from
> my PHP applications when dealing with a remote sql server as opposed to
> a local sql server.
> Here's my situation:
> Server #1
> MSSQL 2000 sp4
> Windows 2000 Server
> PHP4 on IIS5 (CGI)
> Server #2
> MSSQL 2000 sp4
> Windows 2003 Server
> PHP4 on IIS6 (CGI)
> Server #1 and #2 are on the same local network and the same switch. We
> use TCP/IP as the connection protocol and our connections use IPs
> instead of domain names.
> Server#1PHP --> Server#2 MSSQL = slow
> Server#2PHP --> Server#1 MSSQL = slow
> Server#1PHP --> Server#1 MSSQL = fast
> Server#2PHP --> Server#2 MSSQL = fast
> This slower behavior is most noticable when many stored procedures
> (10-15) are being executed on one php page. When the database is local
> on a page such as this, the response time is around 2 seconds. When the
> sql server is remote, the response time is between 8 and 15 seconds.
> Does this make sense at all? Does anyone have any ideas of what the
> bottleneck could be?
>
|||After more investigation it seems that every query had a connect and
disconnect. The act of connecting/disconnecting seems to be much more
expensive when the SQL server is not local.
|||Hi
Usually connection pooling is used to overcome this.
John
"gharmel" wrote:
> After more investigation it seems that every query had a connect and
> disconnect. The act of connecting/disconnecting seems to be much more
> expensive when the SQL server is not local.
>
Showing posts with label php. Show all posts
Showing posts with label php. Show all posts
Sunday, March 11, 2012
Connection from PHP slower when server is remote
I'm trying to get some clues on why I get (much) slower responses from
my php applications when dealing with a remote sql server as opposed to
a local sql server.
Here's my situation:
Server #1
MSSQL 2000 sp4
Windows 2000 Server
PHP4 on IIS5 (CGI)
Server #2
MSSQL 2000 sp4
Windows 2003 Server
PHP4 on IIS6 (CGI)
Server #1 and #2 are on the same local network and the same switch. We
use TCP/IP as the connection protocol and our connections use IPs
instead of domain names.
Server#1PHP --> Server#2 MSSQL = slow
Server#2PHP --> Server#1 MSSQL = slow
Server#1PHP --> Server#1 MSSQL = fast
Server#2PHP --> Server#2 MSSQL = fast
This slower behavior is most noticable when many stored procedures
(10-15) are being executed on one php page. When the database is local
on a page such as this, the response time is around 2 seconds. When the
sql server is remote, the response time is between 8 and 15 seconds.
Does this make sense at all? Does anyone have any ideas of what the
bottleneck could be?Hi
You may want to look at how your network is performing using an network
monitoring tool and perfmon. A local SQL Server could be using shared memory
which may be more significant on a poorly performing network.
You can also use SQL profiler to compare the times taken to execute the
stored procedures on each of the servers, this will allow you to check if
they are similar or acceptable if they are on different hardware.
Check that indexes are not fragmented and statistics are up to date on each
machine. Make sure that you have either a maintenance plan or some job that
will run periodically to maintain your indexes and statistics.
You may also what to check fragmentation of the physical disc. If you HDD is
very fragmented you may want to stop SQL Server and defrag it.
John
"gharmel" wrote:
> I'm trying to get some clues on why I get (much) slower responses from
> my php applications when dealing with a remote sql server as opposed to
> a local sql server.
> Here's my situation:
> Server #1
> MSSQL 2000 sp4
> Windows 2000 Server
> PHP4 on IIS5 (CGI)
> Server #2
> MSSQL 2000 sp4
> Windows 2003 Server
> PHP4 on IIS6 (CGI)
> Server #1 and #2 are on the same local network and the same switch. We
> use TCP/IP as the connection protocol and our connections use IPs
> instead of domain names.
> Server#1PHP --> Server#2 MSSQL = slow
> Server#2PHP --> Server#1 MSSQL = slow
> Server#1PHP --> Server#1 MSSQL = fast
> Server#2PHP --> Server#2 MSSQL = fast
> This slower behavior is most noticable when many stored procedures
> (10-15) are being executed on one php page. When the database is local
> on a page such as this, the response time is around 2 seconds. When the
> sql server is remote, the response time is between 8 and 15 seconds.
> Does this make sense at all? Does anyone have any ideas of what the
> bottleneck could be?
>|||After more investigation it seems that every query had a connect and
disconnect. The act of connecting/disconnecting seems to be much more
expensive when the SQL server is not local.|||Hi
Usually connection pooling is used to overcome this.
John
"gharmel" wrote:
> After more investigation it seems that every query had a connect and
> disconnect. The act of connecting/disconnecting seems to be much more
> expensive when the SQL server is not local.
>
my php applications when dealing with a remote sql server as opposed to
a local sql server.
Here's my situation:
Server #1
MSSQL 2000 sp4
Windows 2000 Server
PHP4 on IIS5 (CGI)
Server #2
MSSQL 2000 sp4
Windows 2003 Server
PHP4 on IIS6 (CGI)
Server #1 and #2 are on the same local network and the same switch. We
use TCP/IP as the connection protocol and our connections use IPs
instead of domain names.
Server#1PHP --> Server#2 MSSQL = slow
Server#2PHP --> Server#1 MSSQL = slow
Server#1PHP --> Server#1 MSSQL = fast
Server#2PHP --> Server#2 MSSQL = fast
This slower behavior is most noticable when many stored procedures
(10-15) are being executed on one php page. When the database is local
on a page such as this, the response time is around 2 seconds. When the
sql server is remote, the response time is between 8 and 15 seconds.
Does this make sense at all? Does anyone have any ideas of what the
bottleneck could be?Hi
You may want to look at how your network is performing using an network
monitoring tool and perfmon. A local SQL Server could be using shared memory
which may be more significant on a poorly performing network.
You can also use SQL profiler to compare the times taken to execute the
stored procedures on each of the servers, this will allow you to check if
they are similar or acceptable if they are on different hardware.
Check that indexes are not fragmented and statistics are up to date on each
machine. Make sure that you have either a maintenance plan or some job that
will run periodically to maintain your indexes and statistics.
You may also what to check fragmentation of the physical disc. If you HDD is
very fragmented you may want to stop SQL Server and defrag it.
John
"gharmel" wrote:
> I'm trying to get some clues on why I get (much) slower responses from
> my php applications when dealing with a remote sql server as opposed to
> a local sql server.
> Here's my situation:
> Server #1
> MSSQL 2000 sp4
> Windows 2000 Server
> PHP4 on IIS5 (CGI)
> Server #2
> MSSQL 2000 sp4
> Windows 2003 Server
> PHP4 on IIS6 (CGI)
> Server #1 and #2 are on the same local network and the same switch. We
> use TCP/IP as the connection protocol and our connections use IPs
> instead of domain names.
> Server#1PHP --> Server#2 MSSQL = slow
> Server#2PHP --> Server#1 MSSQL = slow
> Server#1PHP --> Server#1 MSSQL = fast
> Server#2PHP --> Server#2 MSSQL = fast
> This slower behavior is most noticable when many stored procedures
> (10-15) are being executed on one php page. When the database is local
> on a page such as this, the response time is around 2 seconds. When the
> sql server is remote, the response time is between 8 and 15 seconds.
> Does this make sense at all? Does anyone have any ideas of what the
> bottleneck could be?
>|||After more investigation it seems that every query had a connect and
disconnect. The act of connecting/disconnecting seems to be much more
expensive when the SQL server is not local.|||Hi
Usually connection pooling is used to overcome this.
John
"gharmel" wrote:
> After more investigation it seems that every query had a connect and
> disconnect. The act of connecting/disconnecting seems to be much more
> expensive when the SQL server is not local.
>
Connection from PHP slower when server is remote
I'm trying to get some clues on why I get (much) slower responses from
my PHP applications when dealing with a remote sql server as opposed to
a local sql server.
Here's my situation:
Server #1
MSSQL 2000 sp4
Windows 2000 Server
PHP4 on IIS5 (CGI)
Server #2
MSSQL 2000 sp4
Windows 2003 Server
PHP4 on IIS6 (CGI)
Server #1 and #2 are on the same local network and the same switch. We
use TCP/IP as the connection protocol and our connections use IPs
instead of domain names.
Server#1PHP --> Server#2 MSSQL = slow
Server#2PHP --> Server#1 MSSQL = slow
Server#1PHP --> Server#1 MSSQL = fast
Server#2PHP --> Server#2 MSSQL = fast
This slower behavior is most noticable when many stored procedures
(10-15) are being executed on one php page. When the database is local
on a page such as this, the response time is around 2 seconds. When the
sql server is remote, the response time is between 8 and 15 seconds.
Does this make sense at all? Does anyone have any ideas of what the
bottleneck could be?Hi
You may want to look at how your network is performing using an network
monitoring tool and perfmon. A local SQL Server could be using shared memory
which may be more significant on a poorly performing network.
You can also use SQL profiler to compare the times taken to execute the
stored procedures on each of the servers, this will allow you to check if
they are similar or acceptable if they are on different hardware.
Check that indexes are not fragmented and statistics are up to date on each
machine. Make sure that you have either a maintenance plan or some job that
will run periodically to maintain your indexes and statistics.
You may also what to check fragmentation of the physical disc. If you HDD is
very fragmented you may want to stop SQL Server and defrag it.
John
"gharmel" wrote:
> I'm trying to get some clues on why I get (much) slower responses from
> my PHP applications when dealing with a remote sql server as opposed to
> a local sql server.
> Here's my situation:
> Server #1
> MSSQL 2000 sp4
> Windows 2000 Server
> PHP4 on IIS5 (CGI)
> Server #2
> MSSQL 2000 sp4
> Windows 2003 Server
> PHP4 on IIS6 (CGI)
> Server #1 and #2 are on the same local network and the same switch. We
> use TCP/IP as the connection protocol and our connections use IPs
> instead of domain names.
> Server#1PHP --> Server#2 MSSQL = slow
> Server#2PHP --> Server#1 MSSQL = slow
> Server#1PHP --> Server#1 MSSQL = fast
> Server#2PHP --> Server#2 MSSQL = fast
> This slower behavior is most noticable when many stored procedures
> (10-15) are being executed on one php page. When the database is local
> on a page such as this, the response time is around 2 seconds. When the
> sql server is remote, the response time is between 8 and 15 seconds.
> Does this make sense at all? Does anyone have any ideas of what the
> bottleneck could be?
>|||After more investigation it seems that every query had a connect and
disconnect. The act of connecting/disconnecting seems to be much more
expensive when the SQL server is not local.|||Hi
Usually connection pooling is used to overcome this.
John
"gharmel" wrote:
> After more investigation it seems that every query had a connect and
> disconnect. The act of connecting/disconnecting seems to be much more
> expensive when the SQL server is not local.
>
my PHP applications when dealing with a remote sql server as opposed to
a local sql server.
Here's my situation:
Server #1
MSSQL 2000 sp4
Windows 2000 Server
PHP4 on IIS5 (CGI)
Server #2
MSSQL 2000 sp4
Windows 2003 Server
PHP4 on IIS6 (CGI)
Server #1 and #2 are on the same local network and the same switch. We
use TCP/IP as the connection protocol and our connections use IPs
instead of domain names.
Server#1PHP --> Server#2 MSSQL = slow
Server#2PHP --> Server#1 MSSQL = slow
Server#1PHP --> Server#1 MSSQL = fast
Server#2PHP --> Server#2 MSSQL = fast
This slower behavior is most noticable when many stored procedures
(10-15) are being executed on one php page. When the database is local
on a page such as this, the response time is around 2 seconds. When the
sql server is remote, the response time is between 8 and 15 seconds.
Does this make sense at all? Does anyone have any ideas of what the
bottleneck could be?Hi
You may want to look at how your network is performing using an network
monitoring tool and perfmon. A local SQL Server could be using shared memory
which may be more significant on a poorly performing network.
You can also use SQL profiler to compare the times taken to execute the
stored procedures on each of the servers, this will allow you to check if
they are similar or acceptable if they are on different hardware.
Check that indexes are not fragmented and statistics are up to date on each
machine. Make sure that you have either a maintenance plan or some job that
will run periodically to maintain your indexes and statistics.
You may also what to check fragmentation of the physical disc. If you HDD is
very fragmented you may want to stop SQL Server and defrag it.
John
"gharmel" wrote:
> I'm trying to get some clues on why I get (much) slower responses from
> my PHP applications when dealing with a remote sql server as opposed to
> a local sql server.
> Here's my situation:
> Server #1
> MSSQL 2000 sp4
> Windows 2000 Server
> PHP4 on IIS5 (CGI)
> Server #2
> MSSQL 2000 sp4
> Windows 2003 Server
> PHP4 on IIS6 (CGI)
> Server #1 and #2 are on the same local network and the same switch. We
> use TCP/IP as the connection protocol and our connections use IPs
> instead of domain names.
> Server#1PHP --> Server#2 MSSQL = slow
> Server#2PHP --> Server#1 MSSQL = slow
> Server#1PHP --> Server#1 MSSQL = fast
> Server#2PHP --> Server#2 MSSQL = fast
> This slower behavior is most noticable when many stored procedures
> (10-15) are being executed on one php page. When the database is local
> on a page such as this, the response time is around 2 seconds. When the
> sql server is remote, the response time is between 8 and 15 seconds.
> Does this make sense at all? Does anyone have any ideas of what the
> bottleneck could be?
>|||After more investigation it seems that every query had a connect and
disconnect. The act of connecting/disconnecting seems to be much more
expensive when the SQL server is not local.|||Hi
Usually connection pooling is used to overcome this.
John
"gharmel" wrote:
> After more investigation it seems that every query had a connect and
> disconnect. The act of connecting/disconnecting seems to be much more
> expensive when the SQL server is not local.
>
Sunday, February 12, 2012
connecting to SQL Server with PHP issue
Hi all,
I've tried googling this.
I'm attempting to connect to a DB on SQL Server on a MS 2003 server box. The DB is for a different application that the office uses, and I'm not the owner of it. I'm attempting to connect without making any changes to the server side of things.
The sql server I'm attempting to connect to is set up with: "with Windows NT Authentication using the Network Login ID"
On my Linux box in PHP, I've tried using a valid Network Login ID and it's not working. I can connect to other data sources that use other login types on the same box.
I can from other boxes use MS Access and connect to the data.
Is there something special I need to do in PHP to connect to the DB?
here's some of my php code:
$con = mssql_connect('192.168.111.2', 'xxxxxxx', 'yyyyyyy');
$result = mssql_query ('select * from policyregister');
while ($row=mssql_fetch_array($result))
{ print $row[1]; }
mssql_close($numero);I think you are going to need to use SQL Server authentication for web apps. When I code ASP that is how I always do it. Which means the SQL Server might have to changed to mixed mode. Windows Authentication may not work off of the LAN. I am not 100% on this.
Hi all,
I've tried googling this.
I'm attempting to connect to a DB on SQL Server on a MS 2003 server box. The DB is for a different application that the office uses, and I'm not the owner of it. I'm attempting to connect without making any changes to the server side of things.
The sql server I'm attempting to connect to is set up with: "with Windows NT Authentication using the Network Login ID"
On my Linux box in PHP, I've tried using a valid Network Login ID and it's not working. I can connect to other data sources that use other login types on the same box.
I can from other boxes use MS Access and connect to the data.
Is there something special I need to do in PHP to connect to the DB?
here's some of my php code:
$con = mssql_connect('192.168.111.2', 'xxxxxxx', 'yyyyyyy');
$result = mssql_query ('select * from policyregister');
while ($row=mssql_fetch_array($result))
{ print $row[1]; }
mssql_close($numero);|||ahhh OK. thanks. I have a windows program that works that does a "backup" of the sql server data to mysql. Maybe I just cron job that to run once a minute since my data isnt that critical. :)
I didnt see any stuff on for PHP on SQL Server authentication for web apps.
thanks. you confirmed what I was dreading. heheh
I've tried googling this.
I'm attempting to connect to a DB on SQL Server on a MS 2003 server box. The DB is for a different application that the office uses, and I'm not the owner of it. I'm attempting to connect without making any changes to the server side of things.
The sql server I'm attempting to connect to is set up with: "with Windows NT Authentication using the Network Login ID"
On my Linux box in PHP, I've tried using a valid Network Login ID and it's not working. I can connect to other data sources that use other login types on the same box.
I can from other boxes use MS Access and connect to the data.
Is there something special I need to do in PHP to connect to the DB?
here's some of my php code:
$con = mssql_connect('192.168.111.2', 'xxxxxxx', 'yyyyyyy');
$result = mssql_query ('select * from policyregister');
while ($row=mssql_fetch_array($result))
{ print $row[1]; }
mssql_close($numero);I think you are going to need to use SQL Server authentication for web apps. When I code ASP that is how I always do it. Which means the SQL Server might have to changed to mixed mode. Windows Authentication may not work off of the LAN. I am not 100% on this.
Hi all,
I've tried googling this.
I'm attempting to connect to a DB on SQL Server on a MS 2003 server box. The DB is for a different application that the office uses, and I'm not the owner of it. I'm attempting to connect without making any changes to the server side of things.
The sql server I'm attempting to connect to is set up with: "with Windows NT Authentication using the Network Login ID"
On my Linux box in PHP, I've tried using a valid Network Login ID and it's not working. I can connect to other data sources that use other login types on the same box.
I can from other boxes use MS Access and connect to the data.
Is there something special I need to do in PHP to connect to the DB?
here's some of my php code:
$con = mssql_connect('192.168.111.2', 'xxxxxxx', 'yyyyyyy');
$result = mssql_query ('select * from policyregister');
while ($row=mssql_fetch_array($result))
{ print $row[1]; }
mssql_close($numero);|||ahhh OK. thanks. I have a windows program that works that does a "backup" of the sql server data to mysql. Maybe I just cron job that to run once a minute since my data isnt that critical. :)
I didnt see any stuff on for PHP on SQL Server authentication for web apps.
thanks. you confirmed what I was dreading. heheh
Subscribe to:
Posts (Atom)