Question Re: SQL Anywhere 16 performance issue with proxy tables in Technology Q&A

SQL Anywhere 16 performance issue with proxy tables

Former Member — Fri, 13 Dec 2013 04:37:00 GMT

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?

Re: SQL Anywhere 16 performance issue with proxy tables

MCMartin — Fri, 13 Dec 2013 06:05:26 GMT

Can you provide execution plans for the two statements?

Re: SQL Anywhere 16 performance issue with proxy tables

Former Member — Fri, 13 Dec 2013 06:11:18 GMT

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

Re: SQL Anywhere 16 performance issue with proxy tables

VolkerBarth — Fri, 13 Dec 2013 06:13:18 GMT

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...

Re: SQL Anywhere 16 performance issue with proxy tables

Former Member — Fri, 13 Dec 2013 06:56:54 GMT

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

Re: SQL Anywhere 16 performance issue with proxy tables

Former Member — Fri, 13 Dec 2013 07:26:33 GMT

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!!!!!!!!!

Re: SQL Anywhere 16 performance issue with proxy tables

MarkCulp — Fri, 13 Dec 2013 09:11:29 GMT

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

Re: SQL Anywhere 16 performance issue with proxy tables

Former Member — Fri, 13 Dec 2013 10:18:11 GMT

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

Re: SQL Anywhere 16 performance issue with proxy tables

Former Member — Tue, 17 Dec 2013 08:24:00 GMT

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 🙂

Re: SQL Anywhere 16 performance issue with proxy tables

VolkerBarth — Tue, 17 Dec 2013 08:59:40 GMT

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

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

Re: SQL Anywhere 16 performance issue with proxy tables

MarkCulp — Tue, 17 Dec 2013 09:24:23 GMT

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.

Re: SQL Anywhere 16 performance issue with proxy tables

VolkerBarth — Tue, 17 Dec 2013 09:27:31 GMT

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

I share that impression:)