on 2013 Dec 13 4:37 AM
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 🙂
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Gratulation, that's "proxy debugging the hard way"...
BTW, 'Response-time' is an undocumented value for that option, possibly semantially equal to 'First-row'.
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.
User | Count |
---|---|
72 | |
9 | |
9 | |
8 | |
8 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.