Unanswered: performance issues with 9.0.2 and VPN?
I am troubleshooting a problem at a customer site where they
report that performance is significantly different in their remote
locations than in their main location where the database server is
I am still gathering details, but here is the little I have so far:
Dual Xeon processor server with 4Gigs of RAM running a 500M
database and operating system. They have separate servers that
host other services like Exchange and Terminal Services, so this
server is pretty dedicated. They are running Sybase 9.0.2. They
stop the database nightly to do a backup (they do not have access
to dbbackup tools and it is not yet integrated with our product).
They reboot their server infrequently.
The software is a client-server app which uses ODBC to connect
over TCP/IP. Each site remote site is connected by fractional T-1
(768Kbps) over the internet via VPN. Each office is using the
same workstation hardware.
The client reports a quick response to a standard query while in
the "main office" (where the database is located, LAN), but that the
same query executed from the remote office will peg the
processor (97-100%) usage by dbsrv9.exe.
Things I am trying to eliminate as factors:
user error (of course, right?) -- there may be subtle differences in
what the user does in the remote office that might cause the
General problems in the stored procedure.
Firewall interference -- because they are using VPN, they are using
a firewall to protect themselves. I do not yet know all the details,
but I suspect that there may be a throttle or bottleneck caused by
the firewall, maybe during encryption? Speculating on that one,
but I still don't know why that would cause the dbsrv9.exe to peg
Any suggestions? I will post more details when i have them.
We've had similar remote performance issues on two occasions and both time it was due to this: a stored procedure that called other stored procedures that called other stored procedures... and so on, resulting in an very large number (100s at least) of nested procedure calls. The mere fact that we were calling so many procedures over and over was not in itself a problem -- the thing ran fine "locally" -- the problem only arose "remotely" because Sybase apparently sends along some "debugging" infomation back to the client whenever a stored procedure is executed. If you have a very large number of nexted procedures calls then it's sending a very large amount of data and the problem is amplified for remote (or just generally slower) connections. We solved the problem by rewriting the code a bit (most of the calls proved to be unnecessary if the results were stored in variables and passed down through the call chain). These is also an option to turn off this debugging info -- buried somewhere in some documentation, not sure where any longer. We tried that on for a bit but had other issues and were never able to convince ourselves that it was totally safe.