how can i improve response time from remote machines?

sastry_sagi
sastry_sagi used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi,

Can you give more information please.
Are you using SQL Server Management Studio from remote machine?
Or openquery?

Author

Commented:
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.
 
Jason YousefSr. BI  Developer

Commented:
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.
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Author

Commented:
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?
Commented:
Hi,

Maybe it is related with your SQL Server setup in your machine(not server machine).
So can you try another tools for querying other than SQL Server Management Studio.

It is better to test SQL Server SQLcmd utility.
It is a command prompt based application and you can connect, query your database.

For sqlcmd, follow this steps;

Start cmd:
Start/Run/cmd

Connect your database with sqlcmd:
sqlcmd -S YourDBServer -u yourdbusername -p yourdbpassword -d yourdatabase

Write a query:
select count(*) from Users;
GO

Author

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

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

Author

Commented:
bunch of information

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial