|
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? |
|
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 :-) 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 share that impression:)
(17 Dec '13, 09:27)
Volker Barth
|
Can you provide execution plans for the two statements?
Which information from the execution planyyou exactly need (a lot of informations) and how I can provide these informations here in the forum?
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...
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
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!!!!!!!!!
What DBMS is holding the remote table? Please show us your proxy table definition AND the remote table definition.
Both are SQL Anywhere 16 databases. I'm out-of-the-office now and will provide the informations at Monday.