Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

SQLAnywhere Proxy Tables Retrieval Time issues...

3 posts in General Discussion (old) Last posting was on 2007-03-07 19:02:25.0Z
David Hauze Posted on 2007-03-05 14:43:28.0Z
From: "David Hauze" <david.hauze@steeldynamics.com>
Newsgroups: sybase.public.sqlanywhere
Subject: SQLAnywhere Proxy Tables Retrieval Time issues...
Lines: 74
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: rrcs-24-172-173-34.central.biz.rr.com
X-Original-NNTP-Posting-Host: rrcs-24-172-173-34.central.biz.rr.com
Message-ID: <45ec2c90$1@forums-1-dub>
Date: 5 Mar 2007 06:43:28 -0800
X-Trace: forums-1-dub 1173105808 24.172.173.34 (5 Mar 2007 06:43:28 -0800)
X-Original-Trace: 5 Mar 2007 06:43:28 -0800, rrcs-24-172-173-34.central.biz.rr.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:19
Article PK: 866476

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 ****//


Breck Carter [Team iAnywhere] Posted on 2007-03-05 19:53:03.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Re: SQLAnywhere Proxy Tables Retrieval Time issues...
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <9rsou2d6f2k5o6hr2h8v28kkceu2knrgi3@4ax.com>
References: <45ec2c90$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 5 Mar 2007 11:53:03 -0800
X-Trace: forums-1-dub 1173124383 64.7.134.118 (5 Mar 2007 11:53:03 -0800)
X-Original-Trace: 5 Mar 2007 11:53:03 -0800, bcarter.sentex.ca
Lines: 102
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:21
Article PK: 866481

Complex queries against proxy tables often run very slowwwwwwwwly,
partly because SQL Anywhere does not perform much in the way of
cross-server query optimization. If you SET TEMPORARY OPTION
CIS_OPTION = '7' you will get a trace of what the middleware sends to
the remote server; look in the console log file specified by dbsrv9 -o
filespec.txt.

One technique that sometimes helps is to push the complexity into a
VIEW on the Oracle side, and define a proxy table pointing to that
view. SQL Anywhere sometimes pulls all the base rows across and
performs the join on the SQL Anywhere side, and an Oracle-side view
might prevent that.

Another technique is to copy static rows across to permanent non-proxy
tables, to prevent the repeated reading of unchanging rows.

Breck


On 5 Mar 2007 06:43:28 -0800, "David Hauze"

<david.hauze@steeldynamics.com> wrote:

>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 ****//
>

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


David Hauze Posted on 2007-03-07 19:02:25.0Z
From: "David Hauze" <david.hauze@steeldynamics.com>
Newsgroups: sybase.public.sqlanywhere
References: <45ec2c90$1@forums-1-dub> <9rsou2d6f2k5o6hr2h8v28kkceu2knrgi3@4ax.com>
Subject: Re: SQLAnywhere Proxy Tables Retrieval Time issues...
Lines: 113
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
NNTP-Posting-Host: rrcs-24-172-173-34.central.biz.rr.com
X-Original-NNTP-Posting-Host: rrcs-24-172-173-34.central.biz.rr.com
Message-ID: <45ef0c41@forums-1-dub>
Date: 7 Mar 2007 11:02:25 -0800
X-Trace: forums-1-dub 1173294145 24.172.173.34 (7 Mar 2007 11:02:25 -0800)
X-Original-Trace: 7 Mar 2007 11:02:25 -0800, rrcs-24-172-173-34.central.biz.rr.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:23
Article PK: 866478

Thanks for the advice. I think a view might be the way to go in this case.
Thanks!

Dave

"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote in
message news:9rsou2d6f2k5o6hr2h8v28kkceu2knrgi3@4ax.com...
> Complex queries against proxy tables often run very slowwwwwwwwly,
> partly because SQL Anywhere does not perform much in the way of
> cross-server query optimization. If you SET TEMPORARY OPTION
> CIS_OPTION = '7' you will get a trace of what the middleware sends to
> the remote server; look in the console log file specified by dbsrv9 -o
> filespec.txt.
>
> One technique that sometimes helps is to push the complexity into a
> VIEW on the Oracle side, and define a proxy table pointing to that
> view. SQL Anywhere sometimes pulls all the base rows across and
> performs the join on the SQL Anywhere side, and an Oracle-side view
> might prevent that.
>
> Another technique is to copy static rows across to permanent non-proxy
> tables, to prevent the repeated reading of unchanging rows.
>
> Breck
>
>
> On 5 Mar 2007 06:43:28 -0800, "David Hauze"
> <david.hauze@steeldynamics.com> wrote:
>
>>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 ****//
>>
>
> --
> Breck Carter [Team iAnywhere]
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
> The book:
> http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
> breck.carter@risingroad.com