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.

SQL Anywhere 10 performance Tunning

7 posts in General Discussion Last posting was on 2008-12-18 19:34:21.0Z
John Posted on 2008-12-17 19:22:12.0Z
Sender: 792f.49494bd5.846930886@sybase.com
From: John
Newsgroups: ianywhere.public.general
Subject: SQL Anywhere 10 performance Tunning
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <49495164.79d6.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 17 Dec 2008 11:22:12 -0800
X-Trace: forums-1-dub 1229541732 10.22.241.41 (17 Dec 2008 11:22:12 -0800)
X-Original-Trace: 17 Dec 2008 11:22:12 -0800, 10.22.241.41
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7267
Article PK: 5484

We have SQL Anywhere 10.0 (10.0.1.3619) server on a XP
Embedded OS with 1GB memory and 250GB disk space. We insert
4 long values for a 4 field table every minute. Yes there is
a Primary Key as well.

Over time this has grown to be 3.6GB by now. A SQL query
like SELECT FIELD1 FROM TABLEA WHERE ID=1 take about 20
minutes.

------------------------------- Server Log -----------------
I. 12/11 12:54:16. 4 logical processor(s) on 1 physical
processor(s) detected.
I. 12/11 12:54:16. Networked Seat (per-seat) model. Access
to the server is limited to 5 seat(s).
I. 12/11 12:54:17. Minimum cache size: 2048K, maximum cache
size: 938632K
I. 12/11 12:54:17. Using a maximum page size of 2048 bytes
I. 12/11 12:54:17. Performance warning: Page size too small
for database "PRODUCTDB"
I. 12/11 12:54:17. Performance warning: Database file
"Z:\SA10\PRODUCTDB.DB" consists of 622 disk fragments
-----------------------------------------------------------------

If I copy the database file to a computer with (same SQL
Anywhere software) but XP Pro with 2GB memory it would take
3 minutes to get the same data.
-------------------------------------------------------------
I. 12/17 17:43:38. 524160K of memory used for caching
I. 12/17 17:43:38. Minimum cache size: 2048K, maximum cache
size: 1530132K
I. 12/17 17:43:38. Using a maximum page size of 2048 bytes
I. 12/17 17:43:40. Performance warning: Page size too small
for database "PRODUCTDB"
I. 12/17 17:45:33. Cache size adjusted to 1274000K
I. 12/17 17:47:51. Cache size adjusted to 1460304K
I. 12/17 17:48:51. Cache size adjusted to 1506596K
I. 12/17 17:49:51. Cache size adjusted to 1518096K
I. 12/17 17:50:52. Cache size adjusted to 1520952K
I. 12/17 17:51:52. Cache size adjusted to 1521664K
-----------------------------------------------------------------

What can we do to improve the performance, response time!

What should be the page size that I should be using
Thank you


Stephen Rice [Sybase] Posted on 2008-12-17 20:02:09.0Z
From: "Stephen Rice [Sybase]" <stevedotriceatsybasedotcom>
Newsgroups: ianywhere.public.general
References: <49495164.79d6.1681692777@sybase.com>
Subject: Re: SQL Anywhere 10 performance Tunning
Lines: 71
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49495ac1$1@forums-1-dub>
Date: 17 Dec 2008 12:02:09 -0800
X-Trace: forums-1-dub 1229544129 10.22.241.152 (17 Dec 2008 12:02:09 -0800)
X-Original-Trace: 17 Dec 2008 12:02:09 -0800, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7268
Article PK: 5487

What does the query plan say? If I had to hazard a guess, I'd say you where
doing a sequential scan of the table and the XP Pro machine blows the doors
off the XP Embedded computer. It certainly does for cache since the 2nd
machine shows it has room for the cache to grow. I also suspect the disk
I/O subsystem on the second machine has better throughput. The file on the
Embedded XP machine is seriously fragmented which won't help either.

Hard to say what the best option is without examining the query plan, but I
suspect an index will be involved.

In the absence of any other data a 4K page is best.

--
/steve
Stephen Rice, Sybase iAnywhere
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Docs : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

<John> wrote in message news:49495164.79d6.1681692777@sybase.com...
> We have SQL Anywhere 10.0 (10.0.1.3619) server on a XP
> Embedded OS with 1GB memory and 250GB disk space. We insert
> 4 long values for a 4 field table every minute. Yes there is
> a Primary Key as well.
>
> Over time this has grown to be 3.6GB by now. A SQL query
> like SELECT FIELD1 FROM TABLEA WHERE ID=1 take about 20
> minutes.
>
> ------------------------------- Server Log -----------------
> I. 12/11 12:54:16. 4 logical processor(s) on 1 physical
> processor(s) detected.
> I. 12/11 12:54:16. Networked Seat (per-seat) model. Access
> to the server is limited to 5 seat(s).
> I. 12/11 12:54:17. Minimum cache size: 2048K, maximum cache
> size: 938632K
> I. 12/11 12:54:17. Using a maximum page size of 2048 bytes
> I. 12/11 12:54:17. Performance warning: Page size too small
> for database "PRODUCTDB"
> I. 12/11 12:54:17. Performance warning: Database file
> "Z:\SA10\PRODUCTDB.DB" consists of 622 disk fragments
> -----------------------------------------------------------------
>
> If I copy the database file to a computer with (same SQL
> Anywhere software) but XP Pro with 2GB memory it would take
> 3 minutes to get the same data.
> -------------------------------------------------------------
> I. 12/17 17:43:38. 524160K of memory used for caching
> I. 12/17 17:43:38. Minimum cache size: 2048K, maximum cache
> size: 1530132K
> I. 12/17 17:43:38. Using a maximum page size of 2048 bytes
> I. 12/17 17:43:40. Performance warning: Page size too small
> for database "PRODUCTDB"
> I. 12/17 17:45:33. Cache size adjusted to 1274000K
> I. 12/17 17:47:51. Cache size adjusted to 1460304K
> I. 12/17 17:48:51. Cache size adjusted to 1506596K
> I. 12/17 17:49:51. Cache size adjusted to 1518096K
> I. 12/17 17:50:52. Cache size adjusted to 1520952K
> I. 12/17 17:51:52. Cache size adjusted to 1521664K
> -----------------------------------------------------------------
>
> What can we do to improve the performance, response time!
>
> What should be the page size that I should be using
> Thank you


Josh Savill [Sybase iAnywhere] Posted on 2008-12-17 20:37:19.0Z
From: "Josh Savill [Sybase iAnywhere]" <no_spam_jsavill@ianywhere.com>
Organization: Sybase iAnywhere
User-Agent: Thunderbird 2.0.0.18 (Windows/20081105)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: SQL Anywhere 10 performance Tunning
References: <49495164.79d6.1681692777@sybase.com> <49495ac1$1@forums-1-dub>
In-Reply-To: <49495ac1$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <494962ff@forums-1-dub>
Date: 17 Dec 2008 12:37:19 -0800
X-Trace: forums-1-dub 1229546239 10.22.241.152 (17 Dec 2008 12:37:19 -0800)
X-Original-Trace: 17 Dec 2008 12:37:19 -0800, vip152.sybase.com
Lines: 29
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7269
Article PK: 5486

To add to what Steve said, you'll want to investiage the message:

I. 12/11 12:54:17. Using a maximum page size of 2048 bytes
I. 12/17 17:43:40. Performance warning: Page size too small for database "PRODUCTDB"

I would look at rebuilding the database using a 4K page size (or possibly larger) to see if the
performance is any different. Here's some information on performance and page size:
http://dcx.sybase.com/index.php#http%3A%2F%2Fdcx.sybase.com%2Fhtml%2Fdbugen10%2Fug-performance-s-5691504.html

You might also want to read the following white paper: http://www.sybase.com/detail?id=1056535

--
Joshua Savill
Sybase iAnywhere - Product Manager

Stephen Rice [Sybase] wrote:
> What does the query plan say? If I had to hazard a guess, I'd say you where
> doing a sequential scan of the table and the XP Pro machine blows the doors
> off the XP Embedded computer. It certainly does for cache since the 2nd
> machine shows it has room for the cache to grow. I also suspect the disk
> I/O subsystem on the second machine has better throughput. The file on the
> Embedded XP machine is seriously fragmented which won't help either.
>
> Hard to say what the best option is without examining the query plan, but I
> suspect an index will be involved.
>
> In the absence of any other data a 4K page is best.
>


anil k goel Posted on 2008-12-17 21:37:16.0Z
From: "anil k goel" <anilgoel@nowhere.com>
Newsgroups: ianywhere.public.general
References: <49495164.79d6.1681692777@sybase.com>
Subject: Re: SQL Anywhere 10 performance Tunning
Lines: 57
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4949710c$1@forums-1-dub>
Date: 17 Dec 2008 13:37:16 -0800
X-Trace: forums-1-dub 1229549836 10.22.241.152 (17 Dec 2008 13:37:16 -0800)
X-Original-Trace: 17 Dec 2008 13:37:16 -0800, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7271
Article PK: 5488

1. Take note of the 622 disk fragments. Given that we are likely doing a
sequential scan the file fragmentation will hurt. Try defragging the file.

2. The next thing to consider is if the seq scan is appropriate for the
query. How many rows does the query return and how many rows are in the
table?

-anil

<John> wrote in message news:49495164.79d6.1681692777@sybase.com...
> We have SQL Anywhere 10.0 (10.0.1.3619) server on a XP
> Embedded OS with 1GB memory and 250GB disk space. We insert
> 4 long values for a 4 field table every minute. Yes there is
> a Primary Key as well.
>
> Over time this has grown to be 3.6GB by now. A SQL query
> like SELECT FIELD1 FROM TABLEA WHERE ID=1 take about 20
> minutes.
>
> ------------------------------- Server Log -----------------
> I. 12/11 12:54:16. 4 logical processor(s) on 1 physical
> processor(s) detected.
> I. 12/11 12:54:16. Networked Seat (per-seat) model. Access
> to the server is limited to 5 seat(s).
> I. 12/11 12:54:17. Minimum cache size: 2048K, maximum cache
> size: 938632K
> I. 12/11 12:54:17. Using a maximum page size of 2048 bytes
> I. 12/11 12:54:17. Performance warning: Page size too small
> for database "PRODUCTDB"
> I. 12/11 12:54:17. Performance warning: Database file
> "Z:\SA10\PRODUCTDB.DB" consists of 622 disk fragments
> -----------------------------------------------------------------
>
> If I copy the database file to a computer with (same SQL
> Anywhere software) but XP Pro with 2GB memory it would take
> 3 minutes to get the same data.
> -------------------------------------------------------------
> I. 12/17 17:43:38. 524160K of memory used for caching
> I. 12/17 17:43:38. Minimum cache size: 2048K, maximum cache
> size: 1530132K
> I. 12/17 17:43:38. Using a maximum page size of 2048 bytes
> I. 12/17 17:43:40. Performance warning: Page size too small
> for database "PRODUCTDB"
> I. 12/17 17:45:33. Cache size adjusted to 1274000K
> I. 12/17 17:47:51. Cache size adjusted to 1460304K
> I. 12/17 17:48:51. Cache size adjusted to 1506596K
> I. 12/17 17:49:51. Cache size adjusted to 1518096K
> I. 12/17 17:50:52. Cache size adjusted to 1520952K
> I. 12/17 17:51:52. Cache size adjusted to 1521664K
> -----------------------------------------------------------------
>
> What can we do to improve the performance, response time!
>
> What should be the page size that I should be using
> Thank you


John Posted on 2008-12-18 16:59:40.0Z
Sender: 792f.49494bd5.846930886@sybase.com
From: John
Newsgroups: ianywhere.public.general
Subject: Re: SQL Anywhere 10 performance Tunning
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <494a817c.158d.1681692777@sybase.com>
References: <4949710c$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 18 Dec 2008 08:59:40 -0800
X-Trace: forums-1-dub 1229619580 10.22.241.41 (18 Dec 2008 08:59:40 -0800)
X-Original-Trace: 18 Dec 2008 08:59:40 -0800, 10.22.241.41
Lines: 68
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7278
Article PK: 5495

We insert 4 long values for a 4 field table every minute
(ex: 10:30AM). For each given time (ex: 10:30AM) there are
1249 values are rows are stored. If we want a daily graph,
we get 1440 records. If it is a monthly 1440 X 30.

Do you mean fragment the File or Disk?

> 1. Take note of the 622 disk fragments. Given that we are
> likely doing a sequential scan the file fragmentation
> will hurt. Try defragging the file.
>
> 2. The next thing to consider is if the seq scan is
> appropriate for the query. How many rows does the query
> return and how many rows are in the table?
>
> -anil
>
> <John> wrote in message
> > news:49495164.79d6.1681692777@sybase.com... We have SQL
> > Anywhere 10.0 (10.0.1.3619) server on a XP Embedded OS
> > with 1GB memory and 250GB disk space. We insert 4 long
> > values for a 4 field table every minute. Yes there is a
> Primary Key as well. >
> > Over time this has grown to be 3.6GB by now. A SQL query
> > like SELECT FIELD1 FROM TABLEA WHERE ID=1 take about 20
> > minutes.
> >
> > ------------------------------- Server Log
> > ----------------- I. 12/11 12:54:16. 4 logical
> > processor(s) on 1 physical processor(s) detected.
> > I. 12/11 12:54:16. Networked Seat (per-seat) model.
> > Access to the server is limited to 5 seat(s).
> > I. 12/11 12:54:17. Minimum cache size: 2048K, maximum
> > cache size: 938632K
> > I. 12/11 12:54:17. Using a maximum page size of 2048
> > bytes I. 12/11 12:54:17. Performance warning: Page size
> > too small for database "PRODUCTDB"
> > I. 12/11 12:54:17. Performance warning: Database file
> > "Z:\SA10\PRODUCTDB.DB" consists of 622 disk fragments
> >
> ----------------------------------------------------------
> ------- >
> > If I copy the database file to a computer with (same SQL
> > Anywhere software) but XP Pro with 2GB memory it would
> > take 3 minutes to get the same data.
> >
> ----------------------------------------------------------
> > --- I. 12/17 17:43:38. 524160K of memory used for
> > caching I. 12/17 17:43:38. Minimum cache size: 2048K,
> > maximum cache size: 1530132K
> > I. 12/17 17:43:38. Using a maximum page size of 2048
> > bytes I. 12/17 17:43:40. Performance warning: Page size
> > too small for database "PRODUCTDB"
> > I. 12/17 17:45:33. Cache size adjusted to 1274000K
> > I. 12/17 17:47:51. Cache size adjusted to 1460304K
> > I. 12/17 17:48:51. Cache size adjusted to 1506596K
> > I. 12/17 17:49:51. Cache size adjusted to 1518096K
> > I. 12/17 17:50:52. Cache size adjusted to 1520952K
> > I. 12/17 17:51:52. Cache size adjusted to 1521664K
> >
> ----------------------------------------------------------
> ------- >
> > What can we do to improve the performance, response
> time! >
> > What should be the page size that I should be using
> > Thank you
>
>


anil k goel Posted on 2008-12-18 19:34:21.0Z
From: "anil k goel" <anilgoel@nowhere.com>
Newsgroups: ianywhere.public.general
References: <4949710c$1@forums-1-dub> <494a817c.158d.1681692777@sybase.com>
Subject: Re: SQL Anywhere 10 performance Tunning
Lines: 87
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <494aa5bd@forums-1-dub>
Date: 18 Dec 2008 11:34:21 -0800
X-Trace: forums-1-dub 1229628861 10.22.241.152 (18 Dec 2008 11:34:21 -0800)
X-Original-Trace: 18 Dec 2008 11:34:21 -0800, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7282
Article PK: 5497

Okay, so it looks like "ID=1" returns 1440 or 1440*30 records which may be
too many for a non-clustered index.

Are the rows for a given ID all created chronologically? I.e., are these
records inserted in a clustered manner.

I suspect that the real problem is that your data is clustered but the index
is not declared as such.

Try changing the index on ID to be clustered. Don't bother reorganizing the
table. Just use ALTER INDEX to declare the index on ID to be clustered and
then try your query again.

-anil

<John> wrote in message news:494a817c.158d.1681692777@sybase.com...
> We insert 4 long values for a 4 field table every minute
> (ex: 10:30AM). For each given time (ex: 10:30AM) there are
> 1249 values are rows are stored. If we want a daily graph,
> we get 1440 records. If it is a monthly 1440 X 30.
>
> Do you mean fragment the File or Disk?
>
>> 1. Take note of the 622 disk fragments. Given that we are
>> likely doing a sequential scan the file fragmentation
>> will hurt. Try defragging the file.
>>
>> 2. The next thing to consider is if the seq scan is
>> appropriate for the query. How many rows does the query
>> return and how many rows are in the table?
>>
>> -anil
>>
>> <John> wrote in message
>> > news:49495164.79d6.1681692777@sybase.com... We have SQL
>> > Anywhere 10.0 (10.0.1.3619) server on a XP Embedded OS
>> > with 1GB memory and 250GB disk space. We insert 4 long
>> > values for a 4 field table every minute. Yes there is a
>> Primary Key as well. >
>> > Over time this has grown to be 3.6GB by now. A SQL query
>> > like SELECT FIELD1 FROM TABLEA WHERE ID=1 take about 20
>> > minutes.
>> >
>> > ------------------------------- Server Log
>> > ----------------- I. 12/11 12:54:16. 4 logical
>> > processor(s) on 1 physical processor(s) detected.
>> > I. 12/11 12:54:16. Networked Seat (per-seat) model.
>> > Access to the server is limited to 5 seat(s).
>> > I. 12/11 12:54:17. Minimum cache size: 2048K, maximum
>> > cache size: 938632K
>> > I. 12/11 12:54:17. Using a maximum page size of 2048
>> > bytes I. 12/11 12:54:17. Performance warning: Page size
>> > too small for database "PRODUCTDB"
>> > I. 12/11 12:54:17. Performance warning: Database file
>> > "Z:\SA10\PRODUCTDB.DB" consists of 622 disk fragments
>> >
>> ----------------------------------------------------------
>> ------- >
>> > If I copy the database file to a computer with (same SQL
>> > Anywhere software) but XP Pro with 2GB memory it would
>> > take 3 minutes to get the same data.
>> >
>> ----------------------------------------------------------
>> > --- I. 12/17 17:43:38. 524160K of memory used for
>> > caching I. 12/17 17:43:38. Minimum cache size: 2048K,
>> > maximum cache size: 1530132K
>> > I. 12/17 17:43:38. Using a maximum page size of 2048
>> > bytes I. 12/17 17:43:40. Performance warning: Page size
>> > too small for database "PRODUCTDB"
>> > I. 12/17 17:45:33. Cache size adjusted to 1274000K
>> > I. 12/17 17:47:51. Cache size adjusted to 1460304K
>> > I. 12/17 17:48:51. Cache size adjusted to 1506596K
>> > I. 12/17 17:49:51. Cache size adjusted to 1518096K
>> > I. 12/17 17:50:52. Cache size adjusted to 1520952K
>> > I. 12/17 17:51:52. Cache size adjusted to 1521664K
>> >
>> ----------------------------------------------------------
>> ------- >
>> > What can we do to improve the performance, response
>> time! >
>> > What should be the page size that I should be using
>> > Thank you
>>
>>