sastry_sagi
asked on
how can i improve response time from remote machines?
Hi,
I am just trying to fetch top 5 records from sql server 2008 in the production server and it is fetching the data in 1 second. when i try to execute the same quer from the remote machine it is taking 1 minute 53 seconds. the data size is 200 KB and i am using 3 MbPS line. i had tested the same from different high speed networks but the response time is same (Very Slow). How can i improve the response time.
Thanks in Advance.
V.U.M.Sastry Sagi
I am just trying to fetch top 5 records from sql server 2008 in the production server and it is fetching the data in 1 second. when i try to execute the same quer from the remote machine it is taking 1 minute 53 seconds. the data size is 200 KB and i am using 3 MbPS line. i had tested the same from different high speed networks but the response time is same (Very Slow). How can i improve the response time.
Thanks in Advance.
V.U.M.Sastry Sagi
ASKER
i am using SQL Server 2008R2 (management studio) in my local machine
I am having SQL Server 2008R2 in production server as well.
I am trying to query from local machine to fetch the data from server.
I am having SQL Server 2008R2 in production server as well.
I am trying to query from local machine to fetch the data from server.
HI,
check these...
How is the performance when you try to connect to the machine in another way, like remote desktop etc? If everything is slow when you try to reach the the machine you may have some network issues, like a broken network card/cable, router/firewall issues etc. (I have seen a couple of servers with 10Mbit mode on a Gigabit network card :D )
Do you use the same user when you run from the local server as when you do it remote? Is it an AD account or SQL account?
Look for waittypes in Sys.dm_os_waiting_tasks
http://technet.microsoft.com/en-us/library/cc966413.aspx
By default SQL Server connects using shared memory when making a local connection and then either TCP/IP or Named Pipes when connecting remotely. To test if it's something related to the protocol being used try connecting locally but force it to use TCP/IP or Named Pipes. open Management Studio
Click Connect->Database Engine
Click Options
Under Connection Properties change Network Protocol
Does your query still run just as slow?
Also when you connect remotely is the initial connection slow to connect? My thought here is that it could be authentication.
check these...
How is the performance when you try to connect to the machine in another way, like remote desktop etc? If everything is slow when you try to reach the the machine you may have some network issues, like a broken network card/cable, router/firewall issues etc. (I have seen a couple of servers with 10Mbit mode on a Gigabit network card :D )
Do you use the same user when you run from the local server as when you do it remote? Is it an AD account or SQL account?
Look for waittypes in Sys.dm_os_waiting_tasks
http://technet.microsoft.com/en-us/library/cc966413.aspx
By default SQL Server connects using shared memory when making a local connection and then either TCP/IP or Named Pipes when connecting remotely. To test if it's something related to the protocol being used try connecting locally but force it to use TCP/IP or Named Pipes. open Management Studio
Click Connect->Database Engine
Click Options
Under Connection Properties change Network Protocol
Does your query still run just as slow?
Also when you connect remotely is the initial connection slow to connect? My thought here is that it could be authentication.
ASKER
Hi I had tried from other server with sqlserver 2008 R2 cited in a different network and the data is coming faster there as well. In my network download speed is in a good condition. i am using 3Mbps dedicated line, but I didnt understand why it is slow in my network. is there any solution?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi you are right i am able to fetch the result in 1 second from my local machine using command utility you have mentioned. here i would like to ask one more question. is there any way to improve the querying mechanism from my asp.net app?
Thanks in advance.
Thanks in advance.
Hi again,
SQL Provider is better than OleDB Provider.
I guess your are using SQL Provider...
Application development with database concerns many different things.
For example if you have large amount of users;
Connection pooling is important,
table locks are very important(when somebody updates or deletes data, select queries will be slow)
By the way asp.net or windows application, whatever you're using, your queries run in application server and push the result to client.
So in asp.net application, you should check asp.net application server speed, network connection etc...
By the way SQLDataReader is faster than SQLDataAdapter for read-only queries...
So for read-only and forward-only queries it's better to use SQLDataReader.
SQL Provider is better than OleDB Provider.
I guess your are using SQL Provider...
Application development with database concerns many different things.
For example if you have large amount of users;
Connection pooling is important,
table locks are very important(when somebody updates or deletes data, select queries will be slow)
By the way asp.net or windows application, whatever you're using, your queries run in application server and push the result to client.
So in asp.net application, you should check asp.net application server speed, network connection etc...
By the way SQLDataReader is faster than SQLDataAdapter for read-only queries...
So for read-only and forward-only queries it's better to use SQLDataReader.
ASKER
bunch of information
Can you give more information please.
Are you using SQL Server Management Studio from remote machine?
Or openquery?