ODBC Settings - Latency

[former_SQLA_member1694868]

In a question on the Fuji Beta Forum about using a local application to connect to the Fuji cloud version of the database, Eric points out that using a local application connecting over the WAN to the cloud database is really the same problem-to-solve/issue/challenge that you would face connecting a local application to a regular SQL Anywhere database over a WAN connection, regardless of whether it is in a Fuji cloud setup.

What I am wondering is beyond any changes to the application (fewer requests, more logic in the DB, fewer client-side joins etc.):

1. Are there any ODBC settings or network settings anyone has found to improve a connection over the WAN between app and DB?

2. Are there any options set by default either in the DB or on the ODBC connection that assume a local connection, that if changed would provide a better experience for a WAN connection?

3. Does anyone have any other general ideas to improve the user experience knowing that some things are going to take longer for the greater good?

For our application and testing we have narrowed our options down to:

• Figuring out the above in order to provide a hosted cloud database for a locally installed application

• Publishing the app itself from the hosted cloud using an app-publishing protocol so that it is still "local" to the database

• Rewriting the app completely to be some sort of web app

The middle option is currently the most attractive, unless we can make some changes to get the first option to perform better.

I would appreciate any thoughts, hints, pointers, ideas...

[Breck_Carter · Accepted solution]

If you're dealing with a WAN connection that has crappy latency (response time) then... generally... you have to deal with it.

There's no magic SET OPTION PUBLIC.latency = '0'.

No LINKS=TCPIP(RESPONSETIME=0)

(well, there are some suggestions in the article Volker recommended, but...)

If your applications needs to make 100 database requests to build one display page, then sending the display page across the WAN will probably give a much better user experience than sending the database requests across the WAN... EVEN IF the display page is huge, much larger than all the database traffic put together (which is quite common, what with all the images and video and text and advertising that clutters up the typical web page).

In other words, put the application / web server close to the database. Or (the solution I love) put the application IN the database 🙂

Or, put the database out in the field where the app lives... thinking MobiLink here...

Or, at the very least, gather up the 100 database requests into fewer, larger batches. Reduce the number of requests to improve the overall response time.

AFAIK Fuji is latency-agnostic, it will manage your setup no matter how badly it sucks, end-user-experience-wise :)Blockquote

[justin_willey · Accepted solution]

Just to add to what Breck says - we've been able to improve WAN performance drastically over the years concentrating on :

Or, at the very least, gather up the 100 database requests into fewer, larger batches. Reduce the number of requests to improve the overall response time.

Combine requests, put logic into the database, limit the data you return to what is asked for / necessary, cache reused data locally etc etc. Final suggestion, super-glue a network simulator (eg Network Nightmare) with realisctic settings between the developers' PCs and the server - that can work wonders 🙂

re LINKS=TCPIP(RESPONSETIME=0) - can we get that in as a feature request, failing which an update of the v8 document.

[former_SQLA_member1694868 · Accepted solution]

There's no magic SET OPTION PUBLIC.latency = '0'. No LINKS=TCPIP(RESPONSETIME=0)

Well it was worth a shot.

[VolkerBarth · Accepted solution]

I'm sure it's already in the development's request queue - immediately behind the -go_fast server option.

[VolkerBarth]

I don't have an answer by myself, but would like to point to the following official whitepaper:

Optimizing Adaptive Server Anywhere Performance Over a WAN

It's based on v8 and therefore rather old but I have the impression the points and recommended options are still valid.