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.

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!

asked 04 Oct '23, 17:40

Jeff%20Gibson's gravatar image

Jeff Gibson
1.9k436474
accept rate: 20%

2

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.

(05 Oct '23, 04:05) Justin Willey
1

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

(05 Oct '23, 04:17) Volker Barth
1

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

(05 Oct '23, 04:26) Volker Barth
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • 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:

×70
×25
×18

question asked: 04 Oct '23, 17:40

question was seen: 280 times

last updated: 05 Oct '23, 04:26