Query on Proxy Tables Painfully Slow

[alse...@hotmail.com]

Hi guys,
I have an interesting situation regarding Proxy tables and a query.
The query is something like the following:

SELECT * from p_Table1 WHERE colA in (SELECT colB from p_Table2 where
processingdate>= DATEADD(mi,-10, CURRENT UTC TIMESTAMP))

Where p_Table1 and p_Table2 are the Proxy tables to a remote database.

The Subquery returns zero rows (it may return one or two at the most)

The query itself should return a few hundred rows at most.
Running it locally, it executes in < 1 second, as I'd expect, as it's a
pretty simple query.

Running using the Proxy tables (in Interactive SQL) takes several
minutes. It's almost as though it is transferring the entire contents of
the table(s) locally before running the query, since it just sits there
with "Fetching rows" displayed.

Re-running the query with the results of the subquery entered directly,
eg:
SELECT * from p_Table1 WHERE colA in ('XXXXXXXX')
results in the query running in a couple of seconds, as expected, so it
seems like the "query optimizer" is not intelligent enough to "optimize
away" the subquery?

Re-writing the subquery using a JOIN didn't help, and there are indexes
on all the right columns (colA, colB, processingdate etc)

Any ideas? p_Table1 has 435000 rows, and p_Table2 has 5500 rows, so if
it is transferring everything locally, this would explain the huge
delay.

Any ways around this?

Thanks

[Breck Carter [TeamSybase]]

You can point a proxy table at a view, so one technique is to put the
query in a view on the remote database so *that* engine will optimize
the query.

You can also create proxy procedures, if the logic becomes extremely
complex.

You can even dynamically *create* views on the remote database via
EXECUTE IMMEDIATE ... FORWARD. Icky details available on request.

Breck

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck....@risingroad.com

[fals...@hotmail.com]

Hi Breck,

I had realized that creating procedures or views on the remote side
would probably work better, but I have 2 objections to this:

1) We only have read-only access to the remote database - asking for
Procedures or Views to be created may be a lengthy beaurocratic
process...

2) I was hoping for an explanation of why the query I wrote is taking so
long, rather than a "workaround". Is it really copying huge amounts of
data locally for no good reason? If so, the usefulness of Proxy tables
seems rather limited.

Thanks!

"Breck Carter [TeamSybase]" <NOSPAM__br...@gmail.com> wrote in
news:2hbct4hmg9huvipob...@4ax.com:

[Karim Khamis [Sybase iAnywhere]]

I guess the question is, what is the remote server? I tried the test
connecting on SA server to another, and in that case, the entire query
gets pushed to the remote server which is what I would expect. Is the
remote server in your case another SA or is it something else?

Also, to see how the query is being decomposed (and get an idea as to
why things are not being fully pushed to the remote), try the following...

1) disconnect and reconnect to the local SA database (the one with the
proxy tables)using DBISQL
2) set the cis_option as follows: SET TEMPORARY OPTION cis_option=7
3) execute your query and have a look at the SA console window, you
should see how the query gets decomposed.

When I try the above using one SA connect to another SA, I get the
following:

The query is being processed in FULL PASSTHRU mode
The Original Statement is
select foo.c1 from foo where foo.c1 = any(select foo2.c1 from foo2

where foo2.c2 >= DATEADD(mi,-10,current utc timestamp))
The Virtual Statement is
select vt_1.expr_1 from vt_1
The Remote Statement for vt_1 is
select foo.c1 from foo where foo.c1 = any(select foo2.c1 from foo2
where foo2.c2 >= DATEADD(mi,-10,current utc timestamp))
Execute (rem):
SELECT t1."c1" FROM "foo" t1 WHERE EXISTS ( SELECT * FROM "foo2" t2
WHERE t1."c1" = t2."c1" AND t2."c2" >= DATEADD(mi , - 10 , CURRENT UTC
TIMESTAMP ) )

Notice that the entire query is being passed to the remote in this case.

Karim

[fals...@hotmail.com]

Hi Karim,
Thanks for your suggestion. As far as I know, the remote is another
ASA database.

The output I get in the console window is:

The query is being processed in NO PASSTHRU mode

The capability check of 80 failed
The Original Statement is
select [Columns] from p_Table1 where p_Table1.colA
= any(select p_Table2.ColB from p_Table2 where
p_Table2.processingdate >= DATEADD(mi,-10,current utc timestamp))
The Virtual Statement is
select vt_1.expr_1,vt_1.expr_2,vt_1.expr_3,vt_1.expr_4,vt_1.expr_5
from vt_1 where vt_1.expr_6 =
any(select vt_2.expr_1 from vt_2 where vt_2.expr_2 >= DATEADD(mi,-
10,current utc timestamp))

The Remote Statement for vt_1 is

select [Columns] from p_Table1
The Remote Statement for vt_2 is
(
select p_Table2.ColB,p_Table2.processingdate from p_Table2)

I have replaced column selections with [Columns] above, for
confidentiality reasons.

So, it seems that it is not being executed remotely (NO PASSTHRU)
I guess the "capability check of 80 failed" message is a clue?

Thanks!

"Karim Khamis [Sybase iAnywhere]" <kkh...@sybase.com> wrote in
news:49d63eda$1@forums-1-dub:

[fals...@hotmail.com]

Well, it seems like capability 80 is the "Current Constants" Capability,

I replaced the "Current UTC TIMESTAMP" with a hard-coded date string,
and the query executed in PASSTHRU mode and returned results in under a
second, so it seems like the problem is solved for now.

Thanks for your help guys!

"fals...@hotmail.com" <fals...@hotmail.com> wrote in
news:Xns9BE28E09DBD7fa...@216.196.97.131:

[Karim Khamis [Sybase iAnywhere]]

If you are certain that the remote is another SA, then you can force the
query to push the CURRENT UTC TIMESTAMP constant by turning capability
80 on. To do that, you need to execute an alter server statement as follows:

ALTER SERVER ... CAPABILITY 'current constants' ON

where the ... represents the name you gave the remote server when you
executed the CREATE SERVER statement. Note that if this works, then you
are good to go and will not need to execute the ALTER SERVER statement
again. If this does not work, then re-execute the above statement
turning the capability to OFF and try using a host variable containing
the CURRENT UTC TIMESTAMP value instead.

Karim