Are Proxy Tables causing an issue?

[former_SQLA_member1694892]

SQL Anywhere 16.0.0.2798 Windows 2016 build 17763 I feel that I've referenced this issue in the past, but have some additional info here and we're out of ideas. We are experiencing a huge lag in our client/server Student Billing application on the production server, which we cannot seem to duplicate in test. On every test server that we try, it 'flies'. Our application was written in Clarion 6.3, a software dev tool that you likely haven't heard of. The app is 32 bit (ugh). The student billing database is the only database on the production server but it remotes to 4 other databases on 3 other physical/db servers. Three of these databases are also SQL Anywhere which we have upgraded to the same version as the billing server. The forth remote database is Microsoft SQL Server and belongs to a package system installed locally. In total, there are 97 proxy tables defined. More than the actual billing db itself. If you can imagine, most billing data would come from Admissions, Student Records, Grad School, and Financial Aid (the remote dbs). All pulled together with some billing specific tables to create statements, and other admin functions. When we put the billing db and application on a test server and point to the production remote databases, the billing application performs wonderful. (We're careful with our sql to avoid crossing local and proxy tables.) We have used Foxhound to attempt to find an errant query, but nothing stands out. We had a consultant come in to analyze the whole situation and he's not found anything wrong with the billing db. The database seems to perform well outside of the application. The app performs well on other servers. We considered load, since a few web apps also hit the db. But when we shut down the system to do maintenance, when everyone was out, the app still performed badly ONLY on the production server. There just has to be something in the server configuration. Which I know nothing about. I compared db server settings to our test servers and they look identical to me, so I think that's not a variable. And one more strange thing. If we stop and restart the Student Billing database, it will be okay for awhile, until the next slowdown. We have even dropped and re-added all proxy tables to see if they were out of sync. So what I'm asking here, is can you suggest anything that we could look at on our production server? Could there some sort of memory issue with the app using so many proxy tables? I know this is a database community, but we're running out of options. Considering now moving the whole db and app to another server, which is not a fun job but it may happen.

[MarkCulp]

You could try setting up a simple "ping"-type monitoring system: i.e. create a web service in each of your remote databases that simply accepts the request and immediately responds back (doing no work). Then in your production database create an event that fires periodically, say every 5 or 10 minutes (or whatever you think is reasonable) that hits the ping services in your remotes and records the time that it takes in a table (eg. s=time-of-day; do ping request; duration=time-of-day - s). This may help determine if the time the requests slow down is associated with anything else. eg. amount of time the server has been running, cache size, memory consumption, etc. [ I'll also mentions Breck's Foxhound tool that could be of some use to monitor the above mentioned items! - see https://www.risingroad.com/ ]

[VolkerBarth]

I'm not sure I really understand the issue - but in addition to Mark's suggestion, you might also try to use Remote Access Debugging aka "set cis_option = 7" to find out whether proxy table requests behave differently (although I assume you are aware of that facility...).

FWIW, I lately ran across an issue with a MS SQL Server proxy table that's due to different settings of the ODBC DSN (particularly setting AnsiNPW=No or Yes) resulting in a query with different date order to be either reported as error or to silently return an empty result set, see this older FAQ - and yes, I stumbled over that again.

So you might also make sure the capatibilities of the remote servers are identical on both test and production environment.

[justin_willey]

Two bits of experience that may (or may not help):

We had a similar sounding problem many years ago, which turned out be be caused by differing character sets / collations on the main & proxy servers. This meant that in some cases whole tables were retrieved from the proxy server so that a string comparison in the where clause could be applied locally.

In general, we have found that putting the remote queries into stored procedures on the proxy server and then creating an equivalent remote procedure on the main server works well.

[Breck_Carter]

putting the remote queries into stored procedures on the proxy server

One possible explanation for the "works well" is when the proxy server is able to locally optimize the multi-table query logic before returning the result to the main server. SQL Anywhere doesn't do cross-server optimizations, so complex queries coded on the main server sometimes run slowly when they involve proxy tables.

A similar technique involves "pushing" joins involving proxy tables into a view on the proxy server, then creating a proxy table to query from that view (proxy tables can be based on remote views).

[Breck_Carter]

> We have used Foxhound to attempt to find an errant query, but nothing stands out.

What does Foxhound show when performance is bad? e.g., CPU, response, throughput

See "the first hour is free"

[VolkerBarth]

A similar technique involves "pushing" joins involving proxy tables into a view on the proxy server, then creating a proxy table to query from that view (proxy tables can be based on remote views).

In many cases I have learnt to fetch rows from proxy tables/views into local temporary tables and use the local temporary tables to join with further local data, just because otherwise performance is often bad.

[Breck_Carter]

> use the local temporary tables to join

Yes... the underlying rule seems to be "complex queries should involve data one one side of the remote/main boundary or the other, not both". As Volker observes, simple proxy table "copy" operations often run very fast, so moving the data across the boundary before running a query often helps... especially if the "local temporary table" is smaller and/or simpler than the source proxy table(s).

However... any bottlenecks caused by proxy queries should be apparent in Foxhound.

[Breck_Carter]

What is the architecture of the client-server application in production?

If Foxhound doesn't show any bottlenecks on the SQL Anywhere server side, then perhaps the bottlenecks are on the client side.

Do you use any application server or middleware processes? Those are all "clients" from Foxhound's point of view, and bottlenecks in those processes won't show up.

[former_SQLA_member1694892]

"A similar technique involves "pushing" joins involving proxy tables into a view on the proxy server, then creating a proxy table to query from that view (proxy tables can be based on remote views" Yes thank-you! We do utilize this technique. Also the technique of creating a local table of proxy data within a stored proc when we need to join. We do never join billing tables to any of the proxy tables in queries. No middleware or app servers.

[Breck_Carter]

Check carefully that Foxhound is actually connected to the database that is being used in production, exactly when bad application performance is observed... if you are relying on information from other people about "how production works", look for yourself.

If more than one SQL Anywhere server is used, set up a Foxhound sampling session for each one.

If any part(s) of the production setup is even slightly mysterious to you, look carefully, remove the mystery...

performance bottlenecks like to hide in shrubbery 🙂

[former_SQLA_member1694892]

Thank-you Breck. This involves 3 SQLA servers. Will work on getting this set up and hopefully will be in contact.

[former_SQLA_member1694892]

I'm going to have to wait until the consultant leaves to set up Foxhound. Until then, waiting in the wings and looking into the 'ping' solution. Thanks for your guidance, Breck. Hope to have some Foxhound data soon. Becky Bradley University

[Breck_Carter]

Note that Foxhound offers a "ping" feature whereby it creates and drops a client server connection to the target database each time a sample is collected. This transient ping connection is separate from the persistent Foxhound sampling connection, and it serves to check SQL Anywhere's ability to open a connection as well as respond to requests. The ping process can coexist with the sampling process, but you can also set up a "ping-only" process.

See Monitor Options - Ping Settings.

[former_SQLA_member1694892]

An update: We did not have the current version of ODBC drivers on all client machines. Some had quite old versions. We replaced all of those and have seen some significant improvement. Mea Culpa; I did not know db client drivers versions, down to the ebfs, could make a difference.

[VolkerBarth]

Are the DSN settings on the database server machines for the remote servers identical? As stated above, I ran into issues with MS SQL when settings were slightly different...

That being said, I'm also surprised that client driver versions would have an impact on remote data access...

[former_SQLA_member1694892]

Yes, per your suggestion and relying on Sys Admin for that. But he (and I believe the consultant) said they carefully checked them.

[Breck_Carter]

[former_SQLA_member1694892]

I didn't mean to say that had anything to do with the remote data access, sorry. We just saw an improvement with the app when we updated them. Thanks!