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.
>

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.
>

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.
>

Connection failure OR General network error OR Timeout

We are running some applications and 3 times a day and every night we get the
following error
Connection failure (Number : -2147467259)
OR
[DBNETLIB][ConnectionRead (recv()).]General network error. Check your
network documentation. (Number : -2147467259)
OR
Timeout expired (Number : -2147467259)
It happens random, has nothing to do with backup,...
This is since we use SQLServer 2005, never had any problems with
SQLServer2000.
Everything else is the same.
Any help would be strongly appreciatedDid you get a solution to your problem? I am having a similar porblem, with
home-based users intermittently losing their connection.
Peter
"Multatuli" wrote:
> We are running some applications and 3 times a day and every night we get the
> following error
> Connection failure (Number : -2147467259)
> OR
> [DBNETLIB][ConnectionRead (recv()).]General network error. Check your
> network documentation. (Number : -2147467259)
> OR
> Timeout expired (Number : -2147467259)
> It happens random, has nothing to do with backup,...
> This is since we use SQLServer 2005, never had any problems with
> SQLServer2000.
> Everything else is the same.
> Any help would be strongly appreciated
>

Connection failure on MSDE 2000/XP installation

We have installed MSDE 2000 on XP For Tablet PC. All
applications that use ODBC to connect to the database work
well, however those applications that use either DBLIB or
E/SQL (embedded SQL built on top of DBLIB) return a "Failed
to get DBPROCESS" error. MSDE was installed with
SQL-Authentication and all apps are started locally on the
server running MSDE. All applications run succesfully under
SQL 2000. Using SQL Profiler to trace application-activity
results in NO events being logged/traced, suggesting that
no SQL commands ever reach the server. Any ideas ?
If they are connecting via tcp/ip, do you have an exception in the firewall
(presuming XP SP2 is installed)?
Is the protocol they are connecting on enabled? (see svrnetcn.exe).
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Fintan" <anonymous@.discussions.microsoft.com> wrote in message
news:074001c5356f$36253fe0$a401280a@.phx.gbl...
> We have installed MSDE 2000 on XP For Tablet PC. All
> applications that use ODBC to connect to the database work
> well, however those applications that use either DBLIB or
> E/SQL (embedded SQL built on top of DBLIB) return a "Failed
> to get DBPROCESS" error. MSDE was installed with
> SQL-Authentication and all apps are started locally on the
> server running MSDE. All applications run succesfully under
> SQL 2000. Using SQL Profiler to trace application-activity
> results in NO events being logged/traced, suggesting that
> no SQL commands ever reach the server. Any ideas ?
>

Connection between 2 databases in local sql server

Hi,

I have two different applications based on SQL server database.

In one of the applications I need to use the information stored in the second database, only for read purpose.

As I understand I need how some to make a link between the databases in order to share tables.

Can anybody advise me how to do that, if it is possible at all?

Thanks

Just use a fully qualified name i.e. Server.Database.Owner.Table

for example: Select * from MyServer.Northwind.dbo.Customers

Regards

Paul

|||

Hi Paul,

Thanks for your replay, but maybe I'm not explained myself as well.

Because I'm accessing the database through ODBC, and when I'm using the specific DSN only the tables which are belongs to the specific database are available to me.

That’s the reason I though to make a link between the databases.

There is an option to do that?

|||

The easy way would be to create a stored procedure and have it do the work for you.

The hard way would be to establish 2 connections and query the tables seperately. If you are using .NET you could them combine them in a DataSet as two DataTables and establish a DataRelation.

Regards

Paul

|||

Hi Paul

Unfortunately I'm not using .NET, I'm working with VB6.

I'll appreciate it if you can be more specific about the stored procedure, how can I access the tables in the other database.

Thanks again

|||

Something like:

Create Proc MyProcedure
AS
select * from Db1.dbo.Table1 as tb1

inner join Db2.dbo.OtherTable as tb2
on tb1.id = tb2.id

Set the command type in an ADO Command object to a Stored Procedure and the CommnandText to MyProcedure (the stored procedure name) and call the execute method.

Connecting to Sql Server 2005 on Win2K3 Server

Hello,
I have a Win2K3 Server running .Net Framework 2.0 applications. When I
run an application for the first time on a particular day, the first
connection takes an extremely long time to connect (60 seconds or
more). Other connections afterwards are fine.
I am running the application on the same machine as the db server. So
network issues should not be a factor.
I can connect with Sql Server Management Studio fine. As a matter of
fact, I can connect with Sql Server Management Studio first. But the
first time I run an application on any day, it takes an extremely long
time to connect. I am using the Enterprise Library Data Application
Block for data access in my applications. It's the January 2006 version
of the data application block.
Anyone know why this happens?
Thanks.
IT takes a while for the DAAB to obtain all of the metadata that it requires
to continue to function properly.
I suggest that if you can't live with the 'setup' time issue, that perhaps
you should investigate direct connections using stored procedures instead of
the DAAB.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<rmgalante@.yahoo.com> wrote in message
news:1166105657.334884.75560@.73g2000cwn.googlegrou ps.com...
> Hello,
> I have a Win2K3 Server running .Net Framework 2.0 applications. When I
> run an application for the first time on a particular day, the first
> connection takes an extremely long time to connect (60 seconds or
> more). Other connections afterwards are fine.
> I am running the application on the same machine as the db server. So
> network issues should not be a factor.
> I can connect with Sql Server Management Studio fine. As a matter of
> fact, I can connect with Sql Server Management Studio first. But the
> first time I run an application on any day, it takes an extremely long
> time to connect. I am using the Enterprise Library Data Application
> Block for data access in my applications. It's the January 2006 version
> of the data application block.
> Anyone know why this happens?
> Thanks.
>

Connecting to Sql Server 2005 on Win2K3 Server

Hello,
I have a Win2K3 Server running .Net Framework 2.0 applications. When I
run an application for the first time on a particular day, the first
connection takes an extremely long time to connect (60 seconds or
more). Other connections afterwards are fine.
I am running the application on the same machine as the db server. So
network issues should not be a factor.
I can connect with Sql Server Management Studio fine. As a matter of
fact, I can connect with Sql Server Management Studio first. But the
first time I run an application on any day, it takes an extremely long
time to connect. I am using the Enterprise Library Data Application
Block for data access in my applications. It's the January 2006 version
of the data application block.
Anyone know why this happens?
Thanks.IT takes a while for the DAAB to obtain all of the metadata that it requires
to continue to function properly.
I suggest that if you can't live with the 'setup' time issue, that perhaps
you should investigate direct connections using stored procedures instead of
the DAAB.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<rmgalante@.yahoo.com> wrote in message
news:1166105657.334884.75560@.73g2000cwn.googlegroups.com...
> Hello,
> I have a Win2K3 Server running .Net Framework 2.0 applications. When I
> run an application for the first time on a particular day, the first
> connection takes an extremely long time to connect (60 seconds or
> more). Other connections afterwards are fine.
> I am running the application on the same machine as the db server. So
> network issues should not be a factor.
> I can connect with Sql Server Management Studio fine. As a matter of
> fact, I can connect with Sql Server Management Studio first. But the
> first time I run an application on any day, it takes an extremely long
> time to connect. I am using the Enterprise Library Data Application
> Block for data access in my applications. It's the January 2006 version
> of the data application block.
> Anyone know why this happens?
> Thanks.
>