Sybase iAnywhere SQL database performance issues

Posted on 2009-02-19
Last Modified: 2012-06-21
I have a client who I manage the server for.  A company has installed a Sybase iAnywhere database for them that tracks inventory.  The software front end is used to enter transactions, etc.  The problem I am having (as they have laid this at my doorstep) is that the system is slow according to the vendor.  If I run a query in the Advatage Data Architect on the terminal server, it returns in between 7 and 60 seconds.  If I run the same query on the DB server itself, it takes 16 ms - MUCH faster obviously.  I would imagine the terminal server query would be a little slower due to network traffic, but I don't think it should be that slow.  

The DB server is Windows 2003 SBS with 8 GB of ram.  It has MS SQL 2000 and Exchange 2003 on it running.  I also have Trend Micro installed on that server.  This server has two dual-core processors.

The terminal server is Windows 2008 with 4 GB of ram and one dual-core processor.  They have maybe 8 users on their server at most at any one time.

Task manager doesn't show either box being hit hard when I do any queries.  Memory, processory utilization and network traffic are all low.

Any ideas about where I can look?
Question by:pmasseycpa
    1 Comment
    LVL 19

    Accepted Solution

    One experiment that you should do immediately is to install the client on a stand alone desktop and see what kind of response times you get.  They will depend on how much data you are moving, how fast the network is, and what kind of network latencies you have.

    BTW, when you issue the query, how many rows are you returning?  Is it the same problem for a simple query like "select @@version"?

    Another test to run is to do a ping from the TS machine to the database server and see what kind of round trip delays you are getting.  Do it the opposite direction as well, from the db server to the TS machine.  Obviously, if you have network problems at that level, you can stop looking and start browbeating your net admin(s).

    Networks will always add latencies to database operations but is sounds as if yours is extreme.  Another possibility is that it might have something to do with the way Advantage is interacting with the Terminal Server and it's latencies.  For instance, if Advantage paints the rows as they come back and Terminal server is updating the remote screen, there could be a string of cascading latencies that really add up.

    One thing to have your net admin look at is turning off TCPWAIT.  TCPWAIT is an option on the network stack that tells the TCP level to wait up to a second before sending the physical packet out.  It does this so that if there is more data headed to the same destination, it can gang the packets together and, theoretically, reduce the overall traffic and overhead.  Most of the time, you want to turn TCPWAIT OFF but for some reason, various platforms turn it ON by default.

    Let us know what you find.



    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    When replacing some switches recently I started playing with the idea of having admins authenticate with their domain accounts instead of having local users on all switches all over the place. Since I allready had an w2k8R2 NPS running for my acc…
    Data center, now-a-days, is referred as the home of all the advanced technologies. In-fact, most of the businesses are now establishing their entire organizational structure around the IT capabilities.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now