For maintainability, I'm tempted to use a separate database for a large quantity of reference data that the customer's data will reference but that only we (the manufacturer) creates and maintains. The customer's DB and this DB would be in the same server. That way, we can easily ship updates. Also, it wouldn't bulk up the customer's backups. This will be for SQL Anywhere 16.
But, I'm concerned about performance. I'm looking for any white papers, documentation, insights, etc., on best practices for proxy tables to another local database in the same server. In particular, I'm interested in anything that helps/hurts performance of a JOIN between a native and a proxy table.
Anything that can help me be more savvy would be appreciated. Thanks, Dan
asked 15 Aug '13, 17:29
When using remote data access, the server does not distinguish between a remote SQL Anywhere database running on a separate server versus one that is running on the same server as the local database. Hence, there are no special optimizations in either situation. If you were using SQL Anywhere 11 or below, then I would have recommended using a separate server just because of the issue of running out of workers. However, the multi programming level feature in SQL Anywhere 12 and above pretty much removes that issue; and so, I would say that it really does not matter whether you go with both databases on one server or on individual servers.
answered 19 Aug '13, 12:42
IMHO, cross-database joins do work, however, they can be real slow - it usually will depend on the join characteristics. RDA debugging (CIS_OPTION = 7) will usually show what parts of a query are executed locally vs. remotely - and that often tells why performance may be slower than expected.
Cross-database updates do not work well - IMHO, for these cases, importing the remote data in a temporary table and then join locally is the way to go - cf. this FAQ. - That might be a topic in case you are going to update data in the customer's tables based on your "system database".
How complex are these joins?
Searching within this forum based on the proxy-tables tag does show relevant topics, methinks.
In the end, it should be rather easy to do some tests on your own - you could just delete/rename a few "real tables" and replace them with identically named proxy tables to the second database - then you'll know whether operations do still work and how big the performance penalty may be...
FWIW, here's another question with a somewhat similar topic:
I'm citing this here as it also deals with several methods to share (and update) "manufacturer" data with several customer databases.
answered 20 Aug '13, 05:54