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.

Asa 6.04 ebf 3799 slows down suddenly.

3 posts in General Discussion Last posting was on 2003-08-18 16:04:32.0Z
Pauli Sammalisto Posted on 2003-07-21 17:03:08.0Z
From: "Pauli Sammalisto" <ent@icon.fi>
Subject: Asa 6.04 ebf 3799 slows down suddenly.
Date: Mon, 21 Jul 2003 20:03:08 +0300
Lines: 51
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Message-ID: <OdCEys6TDHA.280@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: MCDXVIII.tdyn.saunalahti.fi 195.197.83.18
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1416
Article PK: 3649

All has been working fast for years but now one most often used query takes
tens of seconds if not minutes. Looking at the hard disk light it seems
that the engine decides that the index in the memory is no good any more and
starts to use the index on the disk and this takes time. Restarting the
engine seems to help for a while. This started when the number of records
where some 95 000 in the main table of the query, the other table has some
50 rows.

Table (appr. 98 000 rows)

CREATE TABLE "Sopta"."WorkHours" ("RecNo" integer NOT NULL DEFAULT
autoincrement,"PersonNo" integer NOT NULL,"StartDate" datetime NOT
NULL,"EndDate" datetime NOT NULL,"WorkType" char(10) NOT NULL,"ReservType"
char(10) NOT NULL,"CustomerNo" integer NULL,"Notes" varchar(250)
NULL,"ModifDate" datetime NULL DEFAULT current timestamp,"Facility"
varchar(16) NULL,"ModifiedBy" varchar(16) NULL,"Arrived" datetime
NULL,PRIMARY KEY ("RecNo"),check(StartDate<=EndDate))

Indexes
CREATE INDEX "WorkPersonTime" ON "Sopta"."WorkHours"( "PersonNo"
ASC,"StartDate" ASC,"EndDate" ASC)
CREATE INDEX "WorkTime" ON "Sopta"."WorkHours"("StartDate" ASC,"EndDate"
ASC)

Query
select min(Date(StartDate)) as WeekBegin from Sopta.workHours w,
sopta.EmployerData e
where w.EndDate>=:EndDate and
MINUTES(StartDate,EndDate)>=:Mins and
w.ReservType='Vapaa' and (w.personno=e.customerno)
and (e.specialitygroup_1='knk') and w.WorkType='Vastotto'

In EmployerData CustomerNo is primary key, some 50 rows

Depending of the order of min and date in min(Date(StartDate)) query plan
shows that both tables are queried using indexes or EmployerData is queried
sequentially. There is no difference in response time.

After moving 30 000 records from WorkHours all has been working well now
about a week, select returns in 2 sec.
After moving the database file to another computer the same happened also
there.

Server is Compaq 2.2 GHz, 512 MB W2000 server, clients max 25, ASA cache 40
MB. Developing environment Delphi 6.

Any ideas what is the problem.

Pauli Sammalisto


Robert Waywell Posted on 2003-07-22 13:30:46.0Z
From: "Robert Waywell" <nospam_rwaywell@ianywhere.com>
References: <OdCEys6TDHA.280@forums-2-dub>
Subject: Re: Asa 6.04 ebf 3799 slows down suddenly.
Date: Tue, 22 Jul 2003 09:30:46 -0400
Lines: 108
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
Message-ID: <#rP1raFUDHA.130@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: rwaywell-pc.sybase.com 172.31.143.74
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1428
Article PK: 3660

One common problem that leads to sudden drops in performance is database
growth. The symptoms match your case where everything has run well for years
and suddenly things are running slowly. The solution is often as simple as
increasing the cache size in recognition of the fact that you are now using
a larger database. The fact that performance picked up again after you
reduced the size of the table "Sopta"."WorkHours" probably confirms that
your problem is an insufficient cache size.

Doing some quick math, given the width of the "Sopta"."WorkHours"
table being up to 350 bytes, not counting overhead or reserved space on
table pages, or page headers, or table fragmentation, but assuming the full
250 byte VARCHAR field is being used, then with 98,000 records you are up to
33MB. You then have to have room for index pages separately, plus the other
table in your query. On top of that, it is unlikely that all 25 users are
hitting exactly the same pages at the same time.

My recommendation at this point would be to try increasing the cache
size. You have lots of memory on the server, so try doubling the cache size
on the database server. If you were on 7.x or 8.x I would say to leave the
dynamic cache sizing turned on and then just check the PeakCacheSize server
property to see how much cache the engine wants.

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

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

Sybase TechWave User Training and Solutions conference -- August 4 - 8, 2003
in Orlando, FL. -- A great place for education and in-depth discussions
on all our technology, future trends in the market, not to mention getting
time with the engineers who create the products! For more information,
visit http://www.ianywhere.com/techwave2003

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

To Submit Bug Reports: http://casexpress.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

"Pauli Sammalisto" <ent@icon.fi> wrote in message
news:OdCEys6TDHA.280@forums-2-dub...
> All has been working fast for years but now one most often used query
takes
> tens of seconds if not minutes. Looking at the hard disk light it seems
> that the engine decides that the index in the memory is no good any more
and
> starts to use the index on the disk and this takes time. Restarting the
> engine seems to help for a while. This started when the number of records
> where some 95 000 in the main table of the query, the other table has some
> 50 rows.
>
> Table (appr. 98 000 rows)
>
> CREATE TABLE "Sopta"."WorkHours" ("RecNo" integer NOT NULL DEFAULT
> autoincrement,"PersonNo" integer NOT NULL,"StartDate" datetime NOT
> NULL,"EndDate" datetime NOT NULL,"WorkType" char(10) NOT NULL,"ReservType"
> char(10) NOT NULL,"CustomerNo" integer NULL,"Notes" varchar(250)
> NULL,"ModifDate" datetime NULL DEFAULT current timestamp,"Facility"
> varchar(16) NULL,"ModifiedBy" varchar(16) NULL,"Arrived" datetime
> NULL,PRIMARY KEY ("RecNo"),check(StartDate<=EndDate))
>
> Indexes
> CREATE INDEX "WorkPersonTime" ON "Sopta"."WorkHours"( "PersonNo"
> ASC,"StartDate" ASC,"EndDate" ASC)
> CREATE INDEX "WorkTime" ON "Sopta"."WorkHours"("StartDate" ASC,"EndDate"
> ASC)
>
> Query
> select min(Date(StartDate)) as WeekBegin from Sopta.workHours w,
> sopta.EmployerData e
> where w.EndDate>=:EndDate and
> MINUTES(StartDate,EndDate)>=:Mins and
> w.ReservType='Vapaa' and (w.personno=e.customerno)
> and (e.specialitygroup_1='knk') and w.WorkType='Vastotto'
>
> In EmployerData CustomerNo is primary key, some 50 rows
>
> Depending of the order of min and date in min(Date(StartDate)) query plan
> shows that both tables are queried using indexes or EmployerData is
queried
> sequentially. There is no difference in response time.
>
> After moving 30 000 records from WorkHours all has been working well now
> about a week, select returns in 2 sec.
> After moving the database file to another computer the same happened also
> there.
>
> Server is Compaq 2.2 GHz, 512 MB W2000 server, clients max 25, ASA cache
40
> MB. Developing environment Delphi 6.
>
> Any ideas what is the problem.
>
> Pauli Sammalisto
>
>


Pauli Sammalisto Posted on 2003-08-18 16:04:32.0Z
From: "Pauli Sammalisto" <ent@icon.fi>
Newsgroups: ianywhere.public.general
References: <OdCEys6TDHA.280@forums-2-dub> <#rP1raFUDHA.130@forums-2-dub>
Subject: Re: Asa 6.04 ebf 3799 slows down suddenly.
Lines: 133
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
X-Original-NNTP-Posting-Host: dclxiii.tdyn.saunalahti.fi
Message-ID: <3f40f923$1@forums-2-dub>
X-Original-Trace: 18 Aug 2003 09:04:51 -0700, dclxiii.tdyn.saunalahti.fi
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 18 Aug 2003 09:03:57 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 18 Aug 2003 09:04:32 -0700
X-Trace: forums-1-dub 1061222672 10.22.108.75 (18 Aug 2003 09:04:32 -0700)
X-Original-Trace: 18 Aug 2003 09:04:32 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1625
Article PK: 3852

Thanks,

I will increase the cache to some 100 MB and see what hapends.

Pauli Sammalisto

"Robert Waywell" <nospam_rwaywell@ianywhere.com> wrote in message
news:#rP1raFUDHA.130@forums-2-dub...
> One common problem that leads to sudden drops in performance is
database
> growth. The symptoms match your case where everything has run well for
years
> and suddenly things are running slowly. The solution is often as simple as
> increasing the cache size in recognition of the fact that you are now
using
> a larger database. The fact that performance picked up again after you
> reduced the size of the table "Sopta"."WorkHours" probably confirms that
> your problem is an insufficient cache size.
>
> Doing some quick math, given the width of the "Sopta"."WorkHours"
> table being up to 350 bytes, not counting overhead or reserved space on
> table pages, or page headers, or table fragmentation, but assuming the
full
> 250 byte VARCHAR field is being used, then with 98,000 records you are up
to
> 33MB. You then have to have room for index pages separately, plus the
other
> table in your query. On top of that, it is unlikely that all 25 users are
> hitting exactly the same pages at the same time.
>
> My recommendation at this point would be to try increasing the cache
> size. You have lots of memory on the server, so try doubling the cache
size
> on the database server. If you were on 7.x or 8.x I would say to leave the
> dynamic cache sizing turned on and then just check the PeakCacheSize
server
> property to see how much cache the engine wants.
>
> --
> -----------------------------------------------
> Robert Waywell
> Sybase Adaptive Server Anywhere Developer - Version 8
> Sybase Certified Professional
>
> Sybase's iAnywhere Solutions
>
> Please respond ONLY to newsgroup
>
> Sybase TechWave User Training and Solutions conference -- August 4 - 8,
2003
> in Orlando, FL. -- A great place for education and in-depth discussions
> on all our technology, future trends in the market, not to mention getting
> time with the engineers who create the products! For more information,
> visit http://www.ianywhere.com/techwave2003
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports: http://casexpress.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
>
> "Pauli Sammalisto" <ent@icon.fi> wrote in message
> news:OdCEys6TDHA.280@forums-2-dub...
> > All has been working fast for years but now one most often used query
> takes
> > tens of seconds if not minutes. Looking at the hard disk light it seems
> > that the engine decides that the index in the memory is no good any more
> and
> > starts to use the index on the disk and this takes time. Restarting the
> > engine seems to help for a while. This started when the number of
records
> > where some 95 000 in the main table of the query, the other table has
some
> > 50 rows.
> >
> > Table (appr. 98 000 rows)
> >
> > CREATE TABLE "Sopta"."WorkHours" ("RecNo" integer NOT NULL DEFAULT
> > autoincrement,"PersonNo" integer NOT NULL,"StartDate" datetime NOT
> > NULL,"EndDate" datetime NOT NULL,"WorkType" char(10) NOT
NULL,"ReservType"
> > char(10) NOT NULL,"CustomerNo" integer NULL,"Notes" varchar(250)
> > NULL,"ModifDate" datetime NULL DEFAULT current timestamp,"Facility"
> > varchar(16) NULL,"ModifiedBy" varchar(16) NULL,"Arrived" datetime
> > NULL,PRIMARY KEY ("RecNo"),check(StartDate<=EndDate))
> >
> > Indexes
> > CREATE INDEX "WorkPersonTime" ON "Sopta"."WorkHours"( "PersonNo"
> > ASC,"StartDate" ASC,"EndDate" ASC)
> > CREATE INDEX "WorkTime" ON "Sopta"."WorkHours"("StartDate" ASC,"EndDate"
> > ASC)
> >
> > Query
> > select min(Date(StartDate)) as WeekBegin from Sopta.workHours w,
> > sopta.EmployerData e
> > where w.EndDate>=:EndDate and
> > MINUTES(StartDate,EndDate)>=:Mins and
> > w.ReservType='Vapaa' and (w.personno=e.customerno)
> > and (e.specialitygroup_1='knk') and w.WorkType='Vastotto'
> >
> > In EmployerData CustomerNo is primary key, some 50 rows
> >
> > Depending of the order of min and date in min(Date(StartDate)) query
plan
> > shows that both tables are queried using indexes or EmployerData is
> queried
> > sequentially. There is no difference in response time.
> >
> > After moving 30 000 records from WorkHours all has been working well now
> > about a week, select returns in 2 sec.
> > After moving the database file to another computer the same happened
also
> > there.
> >
> > Server is Compaq 2.2 GHz, 512 MB W2000 server, clients max 25, ASA cache
> 40
> > MB. Developing environment Delphi 6.
> >
> > Any ideas what is the problem.
> >
> > Pauli Sammalisto
> >
> >
>
>