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.

Performance issue after reducing my db file ASA 7.0.4.2788

4 posts in General Discussion Last posting was on 2004-06-24 14:07:44.0Z
Andreas Hjortsberg Posted on 2004-06-24 11:01:55.0Z
From: Andreas Hjortsberg <andreas.hjortsberg@x-change.se>
Reply-To: andreas.hjortsberg@x-change.se
User-Agent: Mozilla Thunderbird 0.6 (Windows/20040502)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Performance issue after reducing my db file ASA 7.0.4.2788
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: 212.75.74.196
Message-ID: <40dab34c$1@forums-2-dub>
X-Original-Trace: 24 Jun 2004 03:56:12 -0700, 212.75.74.196
Lines: 46
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 24 Jun 2004 03:53:08 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 24 Jun 2004 04:01:55 -0700
X-Trace: forums-1-dub 1088074915 10.22.108.75 (24 Jun 2004 04:01:55 -0700)
X-Original-Trace: 24 Jun 2004 04:01:55 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3205
Article PK: 6662

Hello!

Last week I reduced my db file in a little bit different way.
The reason I took this approach is limitation i disk space and I have
tried before to delete rows i the database but the size of the file has
remained.

Anyway:
I wrote my own unload script for each table in the database, whit the
limitation that all the data should be created this year. The result of
this, is that I only unloaded about 10 pro cent from some tables.

The next thing I did was to run the following command line option
dbunload -c "eng=databasename;uid=username;pwd=mypassword;" -n c:\path

This gave me an file with the hole database structure.
I split up this file into two different parts, so I could create the
structure from one part and add the keys from the other.

So I created the structure, loaded the data and added the keys. I did
run the utility validate database and it said that everything was just fine.

Know to my issue: It takes longer time to create my own transactions in
the database. So I wonder if there maybe is wrong page sides for my
tables in the database? because I used the entire structure from the old
db. I'have heard that ASA should be able to increase the page and hash
sizes by itself? If this is true, can it reduce it by itself?

Anyone who has any ideas why booking time is about 1.29 instead of 0,3.

We have ASA 9, but we are far from take it to production.


Best Regards
/Andreas


Robert Waywell Posted on 2004-06-24 13:06:06.0Z
From: "Robert Waywell" <nospam_rwaywell@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <40dab34c$1@forums-2-dub>
Subject: Re: Performance issue after reducing my db file ASA 7.0.4.2788
Lines: 94
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: <40dad1be$1@forums-1-dub>
Date: 24 Jun 2004 06:06:06 -0700
X-Trace: forums-1-dub 1088082366 10.25.100.155 (24 Jun 2004 06:06:06 -0700)
X-Original-Trace: 24 Jun 2004 06:06:06 -0700, rwaywell-pc.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3209
Article PK: 6664

The page size is determined when you create the database file, either
through dbinit or through Sybase Central. You can run the dbinfo utility
against a copy of the old database to verify what page size it was using.

One likely cause of the performance you are seeing is that your new database
has not yet gathered any statistics to assist the optimizer in costing
queries. If this is the cause, then performance should improve fairly
quickly as the database is used.

> I'have heard that ASA should be able to increase the page and hash
> sizes by itself? If this is true, can it reduce it by itself?

ASA can dynamically grow the database file and the cache size. The page size
of an ASA database can not be changed after the database has been
initialized. The hash size for an index is determined when you create the
index, again it does not change without you manually dropping and recreating
the index with a new hash size. Note that with 8.0 and above we do use a
different algorithm for storing long index values.


--
-----------------------------------------------
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?starturl=casemessage.ssc?CASETYPE=B
ug

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

"Andreas Hjortsberg" <andreas.hjortsberg@x-change.se> wrote in message
news:40dab34c$1@forums-2-dub...
> Hello!
>
> Last week I reduced my db file in a little bit different way.
> The reason I took this approach is limitation i disk space and I have
> tried before to delete rows i the database but the size of the file has
> remained.
>
> Anyway:
> I wrote my own unload script for each table in the database, whit the
> limitation that all the data should be created this year. The result of
> this, is that I only unloaded about 10 pro cent from some tables.
>
> The next thing I did was to run the following command line option
> dbunload -c "eng=databasename;uid=username;pwd=mypassword;" -n c:\path
>
> This gave me an file with the hole database structure.
> I split up this file into two different parts, so I could create the
> structure from one part and add the keys from the other.
>
> So I created the structure, loaded the data and added the keys. I did
> run the utility validate database and it said that everything was just
fine.
>
> Know to my issue: It takes longer time to create my own transactions in
> the database. So I wonder if there maybe is wrong page sides for my
> tables in the database? because I used the entire structure from the old
> db. I'have heard that ASA should be able to increase the page and hash
> sizes by itself? If this is true, can it reduce it by itself?
>
> Anyone who has any ideas why booking time is about 1.29 instead of 0,3.
>
> We have ASA 9, but we are far from take it to production.
>
>
> Best Regards
> /Andreas
>
>
>
>
>
>
>
>
>
>
>


Andreas Hjortsberg Posted on 2004-06-24 14:07:44.0Z
From: Andreas Hjortsberg <andreas.hjortsberg@x-change.se>
Reply-To: andreas.hjortsberg@x-change.se
User-Agent: Mozilla Thunderbird 0.6 (Windows/20040502)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Performance issue after reducing my db file ASA 7.0.4.2788
References: <40dab34c$1@forums-2-dub> <40dad1be$1@forums-1-dub>
In-Reply-To: <40dad1be$1@forums-1-dub>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: 212.75.74.196
Message-ID: <40dae0bf$1@forums-2-dub>
X-Original-Trace: 24 Jun 2004 07:10:07 -0700, 212.75.74.196
Lines: 106
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 24 Jun 2004 07:03:42 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 24 Jun 2004 07:07:44 -0700
X-Trace: forums-1-dub 1088086064 10.22.108.75 (24 Jun 2004 07:07:44 -0700)
X-Original-Trace: 24 Jun 2004 07:07:44 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3212
Article PK: 6669

Thank you Robert and Glenn for sharing your experites.

/Andreas

Robert Waywell wrote:

> The page size is determined when you create the database file, either
> through dbinit or through Sybase Central. You can run the dbinfo utility
> against a copy of the old database to verify what page size it was using.
>
> One likely cause of the performance you are seeing is that your new database
> has not yet gathered any statistics to assist the optimizer in costing
> queries. If this is the cause, then performance should improve fairly
> quickly as the database is used.
>
>
>> I'have heard that ASA should be able to increase the page and hash
>> sizes by itself? If this is true, can it reduce it by itself?
>
>
> ASA can dynamically grow the database file and the cache size. The page size
> of an ASA database can not be changed after the database has been
> initialized. The hash size for an index is determined when you create the
> index, again it does not change without you manually dropping and recreating
> the index with a new hash size. Note that with 8.0 and above we do use a
> different algorithm for storing long index values.
>
>
> --
> -----------------------------------------------
> 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?starturl=casemessage.ssc?CASETYPE=B
> ug
>
> 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
>
> "Andreas Hjortsberg" <andreas.hjortsberg@x-change.se> wrote in message
> news:40dab34c$1@forums-2-dub...
>
>>Hello!
>>
>>Last week I reduced my db file in a little bit different way.
>>The reason I took this approach is limitation i disk space and I have
>>tried before to delete rows i the database but the size of the file has
>>remained.
>>
>>Anyway:
>>I wrote my own unload script for each table in the database, whit the
>>limitation that all the data should be created this year. The result of
>>this, is that I only unloaded about 10 pro cent from some tables.
>>
>>The next thing I did was to run the following command line option
>>dbunload -c "eng=databasename;uid=username;pwd=mypassword;" -n c:\path
>>
>>This gave me an file with the hole database structure.
>>I split up this file into two different parts, so I could create the
>>structure from one part and add the keys from the other.
>>
>>So I created the structure, loaded the data and added the keys. I did
>>run the utility validate database and it said that everything was just
>
> fine.
>
>>Know to my issue: It takes longer time to create my own transactions in
>> the database. So I wonder if there maybe is wrong page sides for my
>>tables in the database? because I used the entire structure from the old
>>db. I'have heard that ASA should be able to increase the page and hash
>> sizes by itself? If this is true, can it reduce it by itself?
>>
>>Anyone who has any ideas why booking time is about 1.29 instead of 0,3.
>>
>>We have ASA 9, but we are far from take it to production.
>>
>>
>>Best Regards
>>/Andreas
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
>


Glenn Paulley Posted on 2004-06-24 13:23:36.0Z
Newsgroups: ianywhere.public.general
Subject: Re: Performance issue after reducing my db file ASA 7.0.4.2788
From: Glenn Paulley <paulley@ianywhere.com>
References: <40dab34c$1@forums-2-dub>
Organization: iAnywhere Solutions
Message-ID: <Xns95125EE24D9paulleyianywherecom@10.22.241.106>
User-Agent: Xnews/5.04.25
X-Original-NNTP-Posting-Host: paulley-t41.sybase.com
X-Original-Trace: 24 Jun 2004 06:26:01 -0700, paulley-t41.sybase.com
Lines: 95
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 24 Jun 2004 06:19:37 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 24 Jun 2004 06:23:36 -0700
X-Trace: forums-1-dub 1088083416 10.22.108.75 (24 Jun 2004 06:23:36 -0700)
X-Original-Trace: 24 Jun 2004 06:23:36 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3210
Article PK: 6665

In 7.x and prior releases, a new database created by dbinit will contain
no predicate or column statistics, which may lead to poor access plans.
Statistics on columns and predicates are created as queries are executed.

To populate the statistics, you can either execute your application
against the database (possibly several times), or hand-code specific
queries that will evaluate equality conditions against all possible
values for a specific column in a table. For example, say one wants to
populate accurate statistics for column X of table T. One could execute

Select X, (select X from T as T2 where T2.X = T.X)
From T
Group By X
Having count(*) > 1

which will cause the subquery to be evaluated for every distinct value of
T.X that appears more than once in T (there is no need to do this for
unique columns). When ASA executes the subquery, statistics will be
computed and saved for that value of X in table T.

In later ASA releases, starting with 8.0.0, column statistics are
represented by histograms that are automatically built during LOAD TABLE
and are retained across unload/reload, making this manual tuning effort
quite unnecessary.

Glenn

Andreas Hjortsberg <andreas.hjortsberg@x-change.se> wrote in
news:40dab34c$1@forums-2-dub:

> Hello!
>
> Last week I reduced my db file in a little bit different way.
> The reason I took this approach is limitation i disk space and I have
> tried before to delete rows i the database but the size of the file
> has remained.
>
> Anyway:
> I wrote my own unload script for each table in the database, whit the
> limitation that all the data should be created this year. The result
> of this, is that I only unloaded about 10 pro cent from some tables.
>
> The next thing I did was to run the following command line option
> dbunload -c "eng=databasename;uid=username;pwd=mypassword;" -n c:\path
>
> This gave me an file with the hole database structure.
> I split up this file into two different parts, so I could create the
> structure from one part and add the keys from the other.
>
> So I created the structure, loaded the data and added the keys. I did
> run the utility validate database and it said that everything was just
> fine.
>
> Know to my issue: It takes longer time to create my own transactions
> in
> the database. So I wonder if there maybe is wrong page sides for my
> tables in the database? because I used the entire structure from the
> old db. I'have heard that ASA should be able to increase the page and
> hash
> sizes by itself? If this is true, can it reduce it by itself?
>
> Anyone who has any ideas why booking time is about 1.29 instead of
> 0,3.
>
> We have ASA 9, but we are far from take it to production.
>
>
> Best Regards
> /Andreas
>
>
>
>
>
>
>
>
>
>
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

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