retrieving database over a network | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

retrieving database over a network

Hi
What is the fastest way to retrieve data over a network? The main server is on networkA and when retrieving data using networkA it takes about 1 seconds to retrieve 200000 rows of four columns. However, networkB connects to networkA through a tunnel. If you retrieve the same set of data using networkB it takes about 30 seconds. This is seriously effecting the work of users on networkB. Any ideas how to improve it? At the moment i just use a SQL statement in visual basics to select all four columns within a certain boundary and the open the connection with a client side cursor and read the data into an array in VB. I have already tried using a server side cursor but it still takes the same amount of time but in a different area of the query. Thanks
CE
You need to limit your recordset with stored procedures and application logic. It’s not like someone can actually use 200000 rows for anything. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Unfortunately i need all of the 200000 rows as i use this information in a program. I need all the X, Y, Z points in a certain area using a 50m interval. The program i use it for is computational and needs this level of accurancy for the output. Any ideas?
CE
What kind of constraints are we talking about on this network? I’m assuming it’s another network on the WAN and you just have a trust set up between the domains? Or, is this another domain on the LAN? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Seperate domain with 2 way trust relationship. This domain is accessible over a WAN link (VPN Tunnel via internet). The theoretical maximum speed of data transfer is 2Mbits/s, but the actual link is considerably slower.
CE
Well, that’s going to be a problem no matter what you do probably. Take those recordsets, bcp them out, and use robocopy to see how long they take to transfer over. This is the best possible response time you could look at and SQL Server will be slower because it actually has to process those records, etc. If that’s slow though, there’s nothing SQL Server or your application can do to speed it up. You might want to look at changing your appliction architecture so they can be using an app server that sets on NetworkA and just "views" it. Take these results and give them to management. They need to buy a bigger pipe with burst capability. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
]]>