I am running a query from PocketBuilder connecting to SQLAnywhere 9 Proxy
Tables which connect to an Oracle 10g database. Normally this works great,
but when I attempt some more complex queries, I have MAJOR issues. Look at
the below query. When I run this while connected directly to Oracle, the
query takes about 31milliseconds (.031 seconds). When I run the exact same
query (with the exception that I change Oracles SYSDATE call to ASA's
Today() function) the query takes 7 minutes to run. Usually queries to the
proxy tables return data nearly as quickly as when connected directly to
Oracle, why is there times this is not the case? Below is the query I am
attempting to run, maybe someone can see something that ASA doesn't like and
might be causing a problem. Thanks in advance for any help.

//**** BEGIN QUERY ****//


select Count(spc_spec_id) AS spec_count
from customer_product_spec
where pcd_product_code = '6260'
and cus_customer_id = '1086'
and cps_approved = 1
and cps_status = 1
and (cps_effective_date) = (
select max(c.cps_effective_date)
from customer_product_spec c,
spec_name s
where c.pcd_product_code = '6260'
and c.cus_customer_id = '1086'
and c.spc_spec_id = s.spc_spec_id
and c.cps_spec_id = customer_product_spec.cps_spec_id
and c.cps_effective_date <= today()
group by c.cps_spec_id, s.sgp_group_id, s.spc_spec_name)

union

select Count(spc_spec_id)
from product_spec
where pcd_product_code = '6260'
and prs_approved = 1
and prs_status = 1
and (prs_effective_date) = (
select max(p.prs_effective_date)
from product_spec p,
spec_name s
where p.spc_spec_id = s.spc_spec_id
and p.prs_effective_date <= today()
and p.prs_spec_id = product_spec.prs_spec_id
group by p.prs_spec_id, s.sgp_group_id, s.spc_spec_name
)
and prs_spec_id not in (
select prs_spec_id
from customer_product_spec
where pcd_product_code = '6260'
and cus_customer_id = '1086'
and cps_approved = 1
and cps_status = 1
and prs_spec_id is not null
and (cps_effective_date) = (
select max(c.cps_effective_date)
from customer_product_spec c,
spec_name s
where c.pcd_product_code = '6260'
and c.cus_customer_id = '1086'
and c.spc_spec_id = s.spc_spec_id
and c.cps_spec_id = customer_product_spec.cps_spec_id
and c.cps_effective_date <= today()
group by c.cps_spec_id, s.sgp_group_id, s.spc_spec_name
)
)



//**** END QUERY ****//