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%20Bungert's gravatar image

Roy Bungert
106228
accept rate: 0%

edited 18 Dec '13, 08:23

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275

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

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%20Culp's gravatar image

Mark Culp
23.4k9132275
accept rate: 40%

Your answer
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:

×248
×209
×63
×21
×20

question asked: 18 Dec '13, 04:11

question was seen: 2,073 times

last updated: 18 Dec '13, 11:30