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

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

asked 18 Dec '13, 04:11

Roy Bungert
106●2●2●8
accept rate: 0%

edited 18 Dec '13, 08:23

Mark Culp
24.7k●9●138●294

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

(18 Dec '13, 08:50) Volker Barth

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

(18 Dec '13, 10:35) Roy Bungert

Replies hidden

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

(18 Dec '13, 10:37) Roy Bungert

flat view

One Answer:

active answers oldest answers newest answers popular answers

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.

permanent link

answered 18 Dec '13, 09:20

Mark Culp
24.7k●9●138●294
accept rate: 41%

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

performance ×272
sa-16 ×258
proxy-tables ×67
database-option ×27
optimization ×21

question asked: 18 Dec '13, 04:11

question was seen: 3,735 times

last updated: 18 Dec '13, 11:30

SAP SQL Anywhere

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

Follow this question

Related questions