To log or not to log

Hello foremen,

on a large database, trillions of SELECTs and DELETEs are going to be 
performed. After that, the database is going to be unloaded. The database 
uses a transaction log.

Should I keep the log there along this process or "unmount" it after some 
parts of it (or even before the beginning)? In this case, nothing but the 
speed is important.

The database is ASA 7.0.4.3498.

Thanks
Pavel 

0 Pavel 2/22/2006 8:18:39 PM

LOG... ALWAYS LOG. Performance is dependent on the use of the log.

Recommendations:
1) Ensure that the file system is not excessively fragmented
2) Host the db file and log file on different physical disks on the same 
machine -- reducing the contention
3) Optionally, you may consider truncating the log periodically if you 
expect the log to grow very large. If you opt for this, it may impair 
overall performance.
-- 

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

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

0 Chris 2/22/2006 8:29:51 PM

I would also pre-grow your files.

Adaptive Server Anywhere automatically grows database files as needed. 
Rapidly-changing database files can lead to excessive file fragmentation on 
the disk, resulting in potential performance problems. Unless you are 
working with a database with a high rate of change, you do not need to worry 
about explicitly allocating space for database files. If you are working 
with a database with a high rate of change, you may pre-allocate disk space 
for dbspaces or for transaction logs using either Sybase Central or the 
ALTER DBSPACE statement.



Performance Tip
Running a disk defragmentation utility after pre-allocating disk space helps 
ensure that the database file is not fragmented over many disjoint areas of 
the disk drive. Performance can suffer if there is excessive fragmentation 
of database files.

0 Rick 2/23/2006 4:13:53 PM

On 22 Feb 2006 12:29:51 -0800, "Chris Keating(iAnywhere Solutions)"
 wrote:

>3) Optionally, you may consider truncating the log periodically if you 
>expect the log to grow very large. If you opt for this, it may impair 
>overall performance.

AFAIK there is no performance penalty for occasionally running
dbbackup to rename and restart the log... what method are you
referring to?

Breck

--
breck.carter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

0 Breck 2/23/2006 5:58:54 PM

In addition to what Chris and Rick said, a large number of deletes may
cause internal fragmentation of tables and indexes. This is a database
issue rather than a disk fragmentation issue. Unload/reload certainly
solves it, but REORGANIZE can also be used... *if* you notice a
performance problem.

Breck

On 22 Feb 2006 12:18:39 -0800, "Pavel Karady"
 wrote:

>Hello foremen,
>
>on a large database, trillions of SELECTs and DELETEs are going to be 
>performed. After that, the database is going to be unloaded. The database 
>uses a transaction log.
>
>Should I keep the log there along this process or "unmount" it after some 
>parts of it (or even before the beginning)? In this case, nothing but the 
>speed is important.
>
>The database is ASA 7.0.4.3498.
>
>Thanks
>Pavel 
>

--
breck.carter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

0 Breck 2/23/2006 6:01:05 PM

Thanks to all advices, thanks for all who participated.

Now there's a catch in the above sentence (two words swapped). Can you see 
it?

Pavel

"Breck Carter"  wrote in message 
news:s4qrv117i7pumkghnu6vcmnfqv792t6g6h@4ax.com...
> In addition to what Chris and Rick said, a large number of deletes may
> cause internal fragmentation of tables and indexes. This is a database
> issue rather than a disk fragmentation issue. Unload/reload certainly
> solves it, but REORGANIZE can also be used... *if* you notice a
> performance problem.
>
> Breck
>
> On 22 Feb 2006 12:18:39 -0800, "Pavel Karady"
>  wrote:
>
>>Hello foremen,
>>
>>on a large database, trillions of SELECTs and DELETEs are going to be
>>performed. After that, the database is going to be unloaded. The database
>>uses a transaction log.
>>
>>Should I keep the log there along this process or "unmount" it after some
>>parts of it (or even before the beginning)? In this case, nothing but the
>>speed is important.
>>
>>The database is ASA 7.0.4.3498.
>>
>>Thanks
>>Pavel
>>
>
> --
> breck.carter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com 

0 Pavel 2/24/2006 10:52:45 AM