We help IT Professionals succeed at work.

Sybase iAnywhere SQL database performance issues

Medium Priority
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?
Watch Question

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.


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.