Sql-server – Executing query is too slow when accessing SQL Server located in an other country

sql serversql-server-2005ssrs

I'am doing an investigation about a slow running query (see below info) when accessing our production SQL Server located in an other country. Now I'm looking for tweaks on how to solve this and make it fast.

See below information:

  • Database Transaction Table Records: 17,000 +

  • SQL Server(2005), Web Server, Report Server (where reports are on our ASP.NET Web Application) location: Illinois, USA (no jobs or any linked server on the Database)

  • Query executed: select * from TransactionTable

Test results (all locations have only one domain):

|  Location       | Time Elapse  | Executed Using
   Illinois         00:00:00       SQL Management Studio,Web App,RS(Prod Server,local)
   Brazil           00:2:21 +      SQL Management Studio,Web App,RS 
   Philippines      00:2:21 +      SQL Management Studio,Web App,RS 
   Others           00:2:21 +      SQL Management Studio,Web App,RS

This is what I did: restart production SQL Server, stop Web and Report Server then I tried to query the SQL Server on different locations with the same result.

I found that the problem is not the query but it is caused by network

Anyone of you guys encountered this? What have you done? I need some recommendation.

Best Answer

Any reason why you need to download the entire table every time to every location? select * from table w/o filters across geo locations is an uphill battle you're bound to loose. Filter the data remotely before download. Or make local read-only copies (eg. via log shipping).

Related Question