Speed Issues with joining to Proxy Tables

[former_SQLA_member1694871]

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!

[justin_willey]

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.

[VolkerBarth]

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...)

[VolkerBarth]

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?

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...