02-11-07, 11:55 #1Registered User
- Join Date
- May 2004
Unanswered: MSSQL2000 slow performance over VPN
I'm executing a stored procedure in my local LAN which executes another one in a loop and I update a Table. The number of loops is about 6300.
This operation takes about 25 seconds in my local LAN.
Then I try to execute though in a VPN which has an upload speed of 256 kbps. I open query analyser connect to the remote server which is must faster than mine and I just write exec mystoredprocname in order to execute the procedure. The performance is very very slow.
In 7 minutes 180 loops are completed out or 6300.
I really cannot understand this. What is the reason of such slow perfomance?? My ADSL model displays no activity when the procedure is executed. I just use the PRINT method in MSSQL in order to display the progress of the operation. I tried to comment it out but with no difference.
I also use SET NOCOUNT ON in order not to display the update results.
Can someone explain me the cause for this? Are there some tricks in order to improve the performance when a slow connection is used like a ADSL with a static IP? It seems that something wrong is happening here.
02-12-07, 05:18 #2Registered User
- Join Date
- Jan 2003
- Nottinghamshire, UK
Is there any way you can redesign your serial approach - does it use CURSEors (What type of Cursor and what locking is involved.)
Are you using Linked Servers or ADHoc Connections - there can be many different reasons for this poor performance.
Ensure your only pulling minimal data from the remote server and Vice Versa.
You should try and get rid of this Looped approach.
GW"Everything should be made as simple as possible, but not simpler." - Albert Einstein
"Everything should be made as complex as possible, so I look Cleverer." - Application Developer