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.

Query Slow 2nd Time ASA 7.0.4

4 posts in General Discussion Last posting was on 2004-01-15 22:32:14.0Z
gdurniak Posted on 2004-01-15 13:59:35.0Z
Sender: 5e02.40069e2a.1804289383@sybase.com
From: gdurniak@aol.com
Newsgroups: ianywhere.public.general
Subject: Query Slow 2nd Time ASA 7.0.4
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40069ef6.5e15.846930886@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 15 Jan 2004 06:08:54 -0800, 10.22.241.42
Lines: 34
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 15 Jan 2004 05:44:18 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 15 Jan 2004 05:59:35 -0800
X-Trace: forums-1-dub 1074175175 10.22.108.75 (15 Jan 2004 05:59:35 -0800)
X-Original-Trace: 15 Jan 2004 05:59:35 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2302
Article PK: 4508

Sybase Adaptive Server Anywhere
Database Engine Version 7.0.4.2788

The following query finds 23 rows, and is very fast, < 1
sec.
However, if the query is immediately run a second time, it
takes > 33 seconds.

I am connecting thru JDBC

Can anyone tell me what might be wrong?


dbinfo shows the total of all index pages is ~400M
Table "Data" is ~1.3G
Cache is 650M

Select
avg("DBA".Data."1qhr_tot") + avg("DBA".Data."2qhr_tot")
FROM "DBA".Data
WHERE
"DBA".Data."cov_samp" = 6218 and
"DBA".Data."telecast" > 0 and
"DBA".Data."start_date" between '2000-1-1' and
'2000-1-31' and
"DBA".Data."half_hr_id" between '19:00' and '19:00'
and
start_date_dow between '1' and '7'


the Plan says:
Sub Query 1
using index cov_samp
Estimate getting here 63,898 times


Robert Waywell Posted on 2004-01-15 16:12:20.0Z
From: "Robert Waywell" <nospam_rwaywell@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <40069ef6.5e15.846930886@sybase.com>
Subject: Re: Query Slow 2nd Time ASA 7.0.4
Lines: 62
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
NNTP-Posting-Host: rwaywell-pc.sybase.com
X-Original-NNTP-Posting-Host: rwaywell-pc.sybase.com
Message-ID: <4006bbe4@forums-1-dub>
Date: 15 Jan 2004 08:12:20 -0800
X-Trace: forums-1-dub 1074183140 172.31.142.236 (15 Jan 2004 08:12:20 -0800)
X-Original-Trace: 15 Jan 2004 08:12:20 -0800, rwaywell-pc.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2306
Article PK: 4511

Is the plan always the same between the 1st and 2nd runs of the query?
Is anyone else connected to the server during this test?

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

<gdurniak@aol.com> wrote in message
news:40069ef6.5e15.846930886@sybase.com...
> Sybase Adaptive Server Anywhere
> Database Engine Version 7.0.4.2788
>
> The following query finds 23 rows, and is very fast, < 1
> sec.
> However, if the query is immediately run a second time, it
> takes > 33 seconds.
>
> I am connecting thru JDBC
>
> Can anyone tell me what might be wrong?
>
>
> dbinfo shows the total of all index pages is ~400M
> Table "Data" is ~1.3G
> Cache is 650M
>
> Select
> avg("DBA".Data."1qhr_tot") + avg("DBA".Data."2qhr_tot")
> FROM "DBA".Data
> WHERE
> "DBA".Data."cov_samp" = 6218 and
> "DBA".Data."telecast" > 0 and
> "DBA".Data."start_date" between '2000-1-1' and
> '2000-1-31' and
> "DBA".Data."half_hr_id" between '19:00' and '19:00'
> and
> start_date_dow between '1' and '7'
>
>
> the Plan says:
> Sub Query 1
> using index cov_samp
> Estimate getting here 63,898 times


gdurniak Posted on 2004-01-15 17:16:18.0Z
Sender: 59fa.4006ca98.1804289383@sybase.com
From: gdurniak@aol.com
Newsgroups: ianywhere.public.general
Subject: Re: Query Slow
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <4006cae2.5a07.846930886@sybase.com>
References: <40069ef6.5e15.846930886@sybase.com><4006bbe4@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Jan 2004 09:16:18 -0800
X-Trace: forums-1-dub 1074186978 10.22.241.41 (15 Jan 2004 09:16:18 -0800)
X-Original-Trace: 15 Jan 2004 09:16:18 -0800, 10.22.241.41
Lines: 10
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2310
Article PK: 4515

yes, the plan is the same both times
(it only mentions the first index. Is this correct? All
fields in the query are indexed)

I am the only one connected, and it is a dedicated server

Windows Task Manager shows I/O read activity the whole time,
which is also strange

All queries are SELECT only. No Inserts, or deletes.


gdurniak Posted on 2004-01-15 22:32:14.0Z
Sender: 67d2.400716f3.1804289383@sybase.com
From: gdurniak@aol.com
Newsgroups: ianywhere.public.general
Subject: Re: Query Slow
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <4007171d.67d4.846930886@sybase.com>
References: <40069ef6.5e15.846930886@sybase.com><4006bbe4@forums-1-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 15 Jan 2004 14:41:33 -0800, 10.22.241.42
Lines: 8
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 15 Jan 2004 14:16:54 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 15 Jan 2004 14:32:14 -0800
X-Trace: forums-1-dub 1074205934 10.22.108.75 (15 Jan 2004 14:32:14 -0800)
X-Original-Trace: 15 Jan 2004 14:32:14 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2314
Article PK: 4519

Sorry, this may not be an ASA issue

We did find a large delay on the client side

The query time is only slightly different

We also changed the OPTIMIZATION GOAL Option from first-row,
to all-rows