SQL Anywhere database - DBSpaces and Performance

Author

DBSpaces and Performance

SRLoka

2005-12-20, 8:24 pm

I have read some statements(could be facts) in this forum that additional
DBSpaces provide no advantage whatsoever except for overcoming physical size
limitations.
I have 2 tables that are highly transactional. Every row inserted into table
A inserts another record into table B (after some processing) and table B
gets its own records in parallel. We have been having indexing problems,
particularly with date indexes on both tables. Our plan is to periodically
archive the data from this tables into copies of these tables and use
special reports to access the archives.
Does it not provide advantage to have the A & B in their own dbspaces ? The
indexes could be placed in the main dbspace.
If both tables are in the same dbspace, isn't the data interleaved and cause
more pages to be read for queries than if they were continual in one dbspace
? Wouldn't that give any performance gain ? There are about 15K to 25K of
rows inserted per day into each of these tables.
FYI: Its a raided system. Using SQLAny 8.02. Will be moved to a dual
processor blade server with a SAN storage.

TIA

Srinivas

Chris Keating(iAnywhere Solutions)

2005-12-21, 9:23 am

It is absolutely true that you will not see benefit to having multiple
dbspaces, beyond increasing the possible size of the database if you
store the dbspaces on a single physical device. However, there could be
a negative performance impact depending on how hard the disk needs to
work to read the multiple files.

If your dbspaces are hosted on different physical disks on the machine,
you may see an benefit. However, this will depend heavily on your
application design and usage. To fully understand the impact, you should
benchmark the application with those configurations.
--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

********************
********************
********************
*****************
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

********************
********************
********************
*****************

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use Case Express to report bugs http://case-express.sybase.com

********************
********************
********************
*****************

Glenn Paulley

2005-12-21, 11:23 am

Further to Chris's points: since you're running your installation on RAID
hardware, you're already taking advantage of mutliple disk heads for the
entire database - so partitioning the database into multiple dbspaces
won't give you significant advantages.

Splitting your database into multiple dbspaces (particularly so that
large indexes are in a separate dbspace) may assist with reducing
fragmentation of the database file (that is, the mixing of index pages
with adjacent table pages). This may provide some marginal performance
improvements, but likely not significant ones.

Glenn

"SRLoka" <ls_reddy@hotmail.com> wrote in news:43a8703e$1@foru
ms-2-dub:

> I have read some statements(could be facts) in this forum that
> additional DBSpaces provide no advantage whatsoever except for
> overcoming physical size limitations.
> I have 2 tables that are highly transactional. Every row inserted into
> table A inserts another record into table B (after some processing)
> and table B gets its own records in parallel. We have been having
> indexing problems, particularly with date indexes on both tables. Our
> plan is to periodically archive the data from this tables into copies
> of these tables and use special reports to access the archives.
> Does it not provide advantage to have the A & B in their own dbspaces
> ? The indexes could be placed in the main dbspace.
> If both tables are in the same dbspace, isn't the data interleaved and
> cause more pages to be read for queries than if they were continual in
> one dbspace ? Wouldn't that give any performance gain ? There are
> about 15K to 25K of rows inserted per day into each of these tables.
> FYI: Its a raided system. Using SQLAny 8.02. Will be moved to a dual
> processor blade server with a SAN storage.
>
> TIA
>
> Srinivas
>
>

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

SRLoka

2005-12-21, 1:23 pm

But wouldn't page caching be a factor in this equation ? Two highly
transactional tables(say A & B) would have interwoven data in the physical
file. When you do queries against a table, if all the rows of that table
were contiguous, the probability of the next row hit being from a cache
should be higher, correct ?

We have been using it since Watcom days. I agree version 8.0 is very fast
and efficient but some of our queries have been having problem because the
database server has chosen to ignore query hints [like (x=y,1)] and has been
causing periodic problems. Its not easy to tune over 10 years of development
because the server has changed its strategy(even if for better). The db
option to take hints(I cant remember the name) is also being ignored.
Are there any DBA specific books or resources for SQLAny that dig deep into
the low level stuff ?

Thanks

"Glenn Paulley" <paulley@ianywhere.com> wrote in message
news:Xns97336C78A875
paulleyianywherecom@
10.22.241.106...
> Further to Chris's points: since you're running your installation on RAID
> hardware, you're already taking advantage of mutliple disk heads for the
> entire database - so partitioning the database into multiple dbspaces
> won't give you significant advantages.
>
> Splitting your database into multiple dbspaces (particularly so that
> large indexes are in a separate dbspace) may assist with reducing
> fragmentation of the database file (that is, the mixing of index pages
> with adjacent table pages). This may provide some marginal performance
> improvements, but likely not significant ones.
>
> Glenn
>
> "SRLoka" <ls_reddy@hotmail.com> wrote in news:43a8703e$1@foru
ms-2-dub:
>
>
>
>
> --
> 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

Breck Carter [TeamSybase]

2005-12-21, 1:23 pm

Rows are not interwoven within pages; a single page can contain data
from only one table. Thus, putting tables in the same or different
dbspaces does not affect page usage or (AFAIK) cache usage.

Breck

On 21 Dec 2005 09:33:05 -0800, "SRLoka" <ls_reddy@hotmail.com> wrote:

>But wouldn't page caching be a factor in this equation ? Two highly
>transactional tables(say A & B) would have interwoven data in the physical
>file. When you do queries against a table, if all the rows of that table
>were contiguous, the probability of the next row hit being from a cache
>should be higher, correct ?
>
>We have been using it since Watcom days. I agree version 8.0 is very fast
>and efficient but some of our queries have been having problem because the
>database server has chosen to ignore query hints [like (x=y,1)] and has been
>causing periodic problems. Its not easy to tune over 10 years of development
>because the server has changed its strategy(even if for better). The db
>option to take hints(I cant remember the name) is also being ignored.
>Are there any DBA specific books or resources for SQLAny that dig deep into
>the low level stuff ?
>
>
>Thanks
>
>
>"Glenn Paulley" <paulley@ianywhere.com> wrote in message
> news:Xns97336C78A875
paulleyianywherecom@
10.22.241.106...
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Glenn Paulley

2005-12-21, 1:23 pm

The 9.0.2 documentation contains more material on the optimization of
queries, but you may find this misleading since you're still using 8.0.2
which lacks some of the newer features. You can also look in Breck's book
for additional hints on tuning.

The option that controls user estimates is USER_ESTIMATES and it has
three settings:

OFF - ignore all user estimates
ON - believe all user estimates as true measures of predicate selectivity
OVERRIDE-MAGIC (default) - only use the user estimate if the predicate is
such that the optimizer cannot determine an estimate through some other
means (column histogram, index lookup, analysis of FK-PK constraints,
etc.).

This is all documented in the help.

Glenn

"SRLoka" <ls_reddy@hotmail.com> wrote in news:43a991d1@forums
-1-dub:

> But wouldn't page caching be a factor in this equation ? Two highly
> transactional tables(say A & B) would have interwoven data in the
> physical file. When you do queries against a table, if all the rows of
> that table were contiguous, the probability of the next row hit being
> from a cache should be higher, correct ?
>
> We have been using it since Watcom days. I agree version 8.0 is very
> fast and efficient but some of our queries have been having problem
> because the database server has chosen to ignore query hints [like
> (x=y,1)] and has been causing periodic problems. Its not easy to tune
> over 10 years of development because the server has changed its
> strategy(even if for better). The db option to take hints(I cant
> remember the name) is also being ignored. Are there any DBA specific
> books or resources for SQLAny that dig deep into the low level stuff ?
>
>
> Thanks
>
>
> "Glenn Paulley" <paulley@ianywhere.com> wrote in message
> news:Xns97336C78A875
paulleyianywherecom@
10.22.241.106...
>
>

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

Chris Keating(iAnywhere Solutions)

2005-12-21, 1:23 pm

You may want to consider focusing on the problem queries to determine
the specifics of why they are running slowly.
--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

********************
********************
********************
*****************
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

********************
********************
********************
*****************

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use Case Express to report bugs http://case-express.sybase.com

********************
********************
********************
*****************

Sponsored Links