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