|
So I'm working with a company that is handling large amounts of medical data across roughly 10 SQL Anywhere databases. All of these databases are running in close to the latest release of SQL Anywhere 17.0.11. Something around there. Running in Linux. Within these database environments, there are multiple proxy tables being used that allow each of the databases to access data from one database to another. We're not dealing with one SQL Anywhere to SQL Server or Oracle. It's SQL Anywhere to SQL Anywhere. We're dealing with a table that is being hit via a proxy table that has approximately 850,000 rows and 17 columns. Querying from an actual table, and then joining to one of these proxy tables is running extremely slow for them. My gut is telling me that any indexing that is in place in the actual table that contains these 850,000 rows is not available when you are joining to the proxy option of the table. One option they have used is was to create a temporary table in the actual database they are running the query on, and dumping the contents of the proxy table into temporary table. That speeds it up at some level. Are there any options to get any indexing to work across a proxy? Or is that a non-option when you're querying data across a proxy table? Thanks! |
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 04 Oct '23, 17:40
question was seen: 258 times
last updated: 05 Oct '23, 04:26
Have you tried using a selectable stored procedure in the remote database with suitable parameters, and then using a proxy procedure in the join with the local table. We've found this works for us, in ensuring that the right indexes get used in the remote database, rather than everything getting slurped across.
The other thing that we've seen causing problems is a slight mismatch in datatypes between local and remote, resulting in indexes not being used in the remote database.
Have you used Remote data access debugging (aka setting CIS_OPTION = 7) to check what part of your queries are passed through to the remote servers?
(IMHO, SQL Anywhere access to other SQL Anywhere databases is not generally more performant than to other database products like MS SQL Server, and we often use the "import remote data into a local temp table and the join locally" approach...)
The key declarations and index definitions should be taken from the remote table by default. Again, Remote data access debugging should tell you whether such indexes are used appropriately within your queries...