I have a table A in my main-db (primary key ID) and table B in a proxy-db. From my table A I can access table B via proxy. The performance is ok.

If I make a joined select using these two tables I get a strange performance issue:

Select a.id, a.number, b.description from a,b where a.id = b.id order by a.id

run very, very slow (in my case up to 60 sec!!!)

Select a.id, a.number, b.description from a,b where a.id = b.id order by a.id, b.description

runs "normal" fast (less then 1 sec)

This means: If I order via the primary key of table A, it is slow, if I order by another field (or add another field to the primary key) it is much faster

Does anybody else has this issue? Is there something wrong with by select-statement? Or is it a known bug?

asked 13 Dec '13, 04:36

Roy%20Bungert's gravatar image

Roy Bungert
106228
accept rate: 0%

edited 17 Dec '13, 09:10

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659

1

Can you provide execution plans for the two statements?

(13 Dec '13, 06:05) Martin

Which information from the execution planyyou exactly need (a lot of informations) and how I can provide these informations here in the forum?

(13 Dec '13, 06:11) Roy Bungert
1

FWIW, "Remote data access debugging" (aka setting the CIS_OPTION might give a clue what part of the statement is sent to the remote server, so you might found out that way why the performance is so different...

(13 Dec '13, 06:13) Volker Barth
Replies hidden

I tried to upload the execution plans. But I need 100 reputation points for uploading :-(

But I noticed some very strange things at the Table Scan page:

Slow SELECT (order by primary key): Rows Returndd: 100 / 43129 / 6.4694e+006 (="Tats├Ąchlich(Gesamt")

Fast SELECT (oder by primary key,second field): Rows returned: 100 / 43129 / 3. column missing

(13 Dec '13, 06:56) Roy Bungert

Strange! Both SELECTS have nearly the same output.

The fast SELECT: The Remote Statement for vt_1 is

select fahrzeug.ZulassungsKz,fahrzeug.FahrzeugID from Fahrzeug

Execute (Proxy): SELECT t2."ZulassungsKz" , t2."FahrzeugID" FROM "bungert"."Fahrzeug" t2

The slow SELECT: The Remote Statement for vt_1 is

select fahrzeug.ZulassungsKz,fahrzeug.FahrzeugID from Fahrzeug Execute (Proxy): SELECT t2."ZulassungsKz" , t2."FahrzeugID" FROM "bungert"."Fahrzeug" t2
Execute (Proxy): SELECT t2."ZulassungsKz" , t2."FahrzeugID" FROM "bungert"."Fahrzeug" t2
Execute (Proxy): SELECT t2."ZulassungsKz" , t2."FahrzeugID" FROM "bungert"."Fahrzeug" t2
. . .

These two lines are repeated a countless time!!!!!!!!!

(13 Dec '13, 07:26) Roy Bungert

What DBMS is holding the remote table? Please show us your proxy table definition AND the remote table definition.

(13 Dec '13, 09:11) Mark Culp
Replies hidden

Both are SQL Anywhere 16 databases. I'm out-of-the-office now and will provide the informations at Monday.

(13 Dec '13, 10:18) Roy Bungert
showing 5 of 7 show all flat view

Finaly I found the solution. By preparing testdata and comparing the DBs I found one difference between a new created DB and my production DB:

I don't now why, but in my production DB there was this option (perhaps from older updates):

SET OPTION "PUBLIC"."optimization_goal"='Response-time';

After the change to (the DEFAULT value):

SET OPTION "PUBLIC"."optimization_goal"='All-rows';

Now my DB is fast again :-)

permanent link

answered 17 Dec '13, 08:24

Roy%20Bungert's gravatar image

Roy Bungert
106228
accept rate: 0%

2

Gratulation, that's "proxy debugging the hard way"...

BTW, 'Response-time' is an undocumented value for that option, possibly semantially equal to 'First-row'.

(17 Dec '13, 08:59) Volker Barth
Replies hidden
3

Correct. I checked the code and 'Response-time' is equivalent to 'First-row' and 'Resource-consumption' is equivalent to 'All-rows'.

I wasn't sure why the aliases for each existed... so I looked at the history of the code and it looks like historically the option started with 'Response-time' and 'Resource-consumption' as the only valid options (in early 1999) and then 'First-row' and 'All-rows' were added almost two years later as aliases (in late 2000). I would imagine that first-row and all-rows are easier to understand and is why these two are the only ones mentioned in the documentation now.

(17 Dec '13, 09:24) Mark Culp

I would imagine that first-row and all-rows are easier to understand

I share that impression:)

(17 Dec '13, 09:27) Volker Barth
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:

×242
×62
×21
×14

question asked: 13 Dec '13, 04:36

question was seen: 1,020 times

last updated: 18 Dec '13, 11:30