Search code examples
vbanetwork-programmingtcplatencysap-ase

Need hints to optimise a sybase access over a big fat pipe

I have the need to access a sybase database (12.5) from oversea. The high latency is definitely a problem.

I already optimized the connection parameters to make better use of the network and achieved a 20x performance increase, but it's still not enough : 1 minute to get 3Mb of data.

We need another 10x or 20x increase for our application.

Technical data :

  • the data are flowing through a single TCP connection using the TDS protocol
  • the client app is an excel sheet with macros, using the default Sybase driver
  • the corporate environment makes it difficult to push big changes in the 10+ years architecture, so solutions need to be the least intrusive. But some changes may be bargained due to the importance of this project.

Can anyone give me pointers ?

I already thought of :

  • splitting SQL requests over several concurrent connections to the database. The problem is data consistency : what if records are modified at the same time since requests will not be exactly executed at the same time ? Is there an existing mechanism to spread a request over several calls on different connections ?
  • using some kind of database "cache" or "local replication" oversea, but I don't know what is possible.

Thanks.

Solution

  • (I know I answer to my own question)

    Eventually, we settled to designing our own database remote access protocol. It's not complicated since we are only using a basic subset of SQL (SELECT and UPDATE), and the protocol doesn't have to understand SQL anyway.

    By using our own protocol, we'll be able to use compression, make the client able to use several TCP links at the same time, maximize network utilisation and add some functionnal caching secific to our application.

    The client will be our app and the server will be a "proxy" to the real database, sitting next to it (like @Tim suggested in the comments).

    It's not the only solution, but we feel that it's a good balance between enormous replication price, development complexity and expected benefits.