SQL Anywhere 16 performance issue with proxy tables (optimization goal)

[Former Member]

Follow up to this question:

There's one question left - perhaps somebody from SAP/Sybase can answer it: Why this option has such a so big impact to the performance with proxy tables? If I do the SELECT without proxy-tables (tables in the same database) there are no performance issue!

You can reproduce the behavior with the sample-db and the following SELECT. The customer-table must be a proxy table. Then you should play around with the optimization-goal option ("First-row" = slow, "All-rows" = fast).

In my case the customer-table has approx. 48000 rows, the salesorders has approx. 150 records.

select salesorders.id, customers.surname
 from salesorders, customers
where salesorders.customerid = Customers.ID
order by salesorders.id;

If you change the order to "order by salesorders.id, customers.surname", it's fast, even with "first-row".

I think there's a bug 🙂

[VolkerBarth]

Could you show us the console log with the according proxy debugging messages for both statements (similar to the ones from the previous question)?

[Former Member]

Here are my two original plans from my db (not from the sample.DB!). But I guess that you can get a clue of what happens. The table fahrzeug is the proxy table with approx. 48000 rows.

Both plans run with optimization_goal='Response-time'

Fast plan

Slow plan

HTH

[Former Member]

For those who jump in now: The problem is solved with optimization_goal='all-rows'. THen both SELECT are fast.

[MarkCulp]

I have not taken the time to repro the issue (a sql script to repro the issue would have been nice) but my 'shot in the dark' guess is that:

• (plan A) Do a table scan of the remote customers table (thus bringing the entire table from the remote to local memory) and then is join (doing probes) to the local salesorder table.
• (plan B) Do a table scan of the local table and then probe the remote table.
• Remote calls will be more expensive so dependent on the of the local vs remove tables the total cost of each plan will be completely dependent on the size of the remote table... and the optimizer does not know this so it takes its best guess depending on other factors that you have put into your query (e.g. order by clause).

Looking at the plans for each query will verify (or show otherwise) why you are getting fast vs slow query execution.