Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    The Deep South

    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
    the processor.

    Any suggestions? I will post more details when i have them.

    Matt Danger

  2. #2
    Join Date
    Aug 2004
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts