|
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 |
|
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. Karim, Thanks for the response. Based on this, I'll keep it all in one database. It's too bad. The user base has been craving some capability like this for a long time. (Note Volker Barth's and AlexeyK77's comments.) Personally, I wouldn't care if it were done as separate DBs in the same server or as independently-replaceable DBSPACEs. But that's just me. I realized that I was mis-remembering John Smirnios' comments in Breck's blog (http://sqlanywhere.blogspot.com/2010/12/unload-compressed-in-action.html). That was about a nifty buffer-sharing when two DBs under the same server are connected by a named pipe. Cool, but unrelated. Sorry for the confusion. It's a toss-up, but I'll give you the checkmark, since you could give the inside info. (My apologies, Volker. Your answer was correct, too.)
(19 Aug '13, 13:44)
Dan Konigsbach
Replies hidden
@Dan: So you've come to the conclusion that joins over proxy tables won't work in your case? Have you run into actual problems, or is this primarily a design decision? FWIW, that's not my personal general impression, it's just that joins will possibly work slower and will usually need rewrites in some cases... - However, I've used that generally to share data from already existing different databases, so there was no chance/need to ask whether the data could be contained in the same database:)
(20 Aug '13, 04:29)
Volker Barth
@Karim: That does mean that basically RDA to a SQL Anywhere database is not different from RDA to other DBMSs, right? (Except that the set of supported capabilities might be longer as a SQL Anywhere remote should usually support (almost) the same features as the local SQL Anyhwere engine...) Say, would a local v12 engine treat a remote SA database on a v12 engine (possibly the same engine) different from a SA database on an older engine? (I.e. are these capabilities queried at run time or are they solely based on the remote server type "SAODBC"?)
(20 Aug '13, 06:01)
Volker Barth
Replies hidden
1
The main difference between a SQL Anywhere remote and other DBMSs is that with SQL Anywhere we will use our knowledge of the catalog to more efficiently query the metadata for tables, columns etc. rather than using the standard ODBC metadata calls. Also we support a larger set of datatypes with SQL Anywhere (for example, we will treat geometry columns as true geometry columns instead of dumbing down to long varchar or long binary). In addition, we will do some extra fancy footwork for SQL Anywhere 5.5 and older versions which do not support Unicode entry points in the ODBC driver. But other than those few differences, we do not treat SQL Anywhere remotes any different than any other DBMS.
(20 Aug '13, 12:23)
Karim Khamis
"So you've come to the conclusion that joins over proxy tables won't work in your case? Have you run into actual problems, or is this primarily a design decision?" Thanks, Volker. Splitting things into two databases was something I was tempted to try, but it isn't a requirement. I could definitely use the approach you suggested to keep things efficient with two databases, but for now the benefits wouldn't justify the extra effort. It's definitely something to consider for the future, though!
(20 Aug '13, 18:40)
Dan Konigsbach
|
|
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... 1
Just to add: While I really like SQL Anywhere's architecture (yes, that's no surprise), doing cross-database joins within the same SQL Anywhere database engine is a feature where the "one server - one master database - multiple databases" architecture of Sybase ASE or MS SQL Server (or other DBMSs) seems more appropriate. In my experience, joining data from different databases there usually does not raise performance questions - it "feels" like joining locally...
(16 Aug '13, 05:15)
Volker Barth
Replies hidden
Thanks, Volker! In the general case, I think you're (as usual) dead-on correct. However, I've gotten the impression that there are some special-case optimizations for proxy tables. I'm hoping that someone with inside information can point them out, just in case my situation can be squeezed into one of them.
(16 Aug '13, 15:24)
Dan Konigsbach
Joining tables from different databases running under one DB engine without performance penalties and getting compound SQL plans without voodoo is my dream. Poor, that mssql do both things ok by design.
(17 Aug '13, 06:31)
AlexeyK77
AlexeyK77, we share your dream.
(19 Aug '13, 13:27)
Dan Konigsbach
|
|
FWIW, here's another question with a somewhat similar topic: Best tool for publishing/synchronizing a table between standalone DBs I'm citing this here as it also deals with several methods to share (and update) "manufacturer" data with several customer databases. |
Consider bulk copying the data on server startup from the separate database to local CREATE GLOBAL TEMPORARY TABLE NOT TRANSACTIONAL SHARE BY ALL tables... your own little HANA thing :)
INTERESTING!!!!!!!!