If you are unable to create a new account, please email support@bspsoftware.com

 

Increasing Performance via NOT LOGGED INITIALLY

Started by eknight, 10 Sep 2012 07:11:12 AM

Previous topic - Next topic

eknight

Hello there,

I'm currently looking at an option to increase the performance of our builds. This involves calling the statement:

ALTER TABLE <tabname> ACTIVATE NOT LOGGED INITIALLY

This deactivates logging on the table for the current unit of work. The benefit is obviously significant performance improvement because there is not need to write log files. The problem is that the statement needs to be called within the same unit of work as the INSERT/UPDATE/APPEND statements.

At the moment it seems that if I call the statement from a procedure node before the build node DS adds an automatic commit at the end of the procedure node, which ends the unit of work and reactivates logging automatically. Is there anyway to deactivate this auto commit on procedure nodes?

Are there any other ideas how I could set NOT LOGGED INITIALLY within the same unit of work as a delivery?

MFGF

You might try setting up a variable in the jobstream which has an initial value coded as an expression calling a SQL() or lookup() function against the same data source. The variable gets set up in memory at the beginning of the jobstream, and stays in memory until the jobstream completes. I'm hoping that it might start a transaction against the database which remains current (and uncommitted) and is inherited by the nodes within the jobstream referencing the same database. It's just a wild hunch, though, and may be a complete red herring - although I guess it can't harm anything to try it?

Good luck!!

MF.
Meep!

eknight

#2
I've tried calling it from a build variable intialization field. But that closes the transaction between variables.

Edit: I've also tried it at the job level but each line of initialization (or maybe function call) is it's own unit of work, and I don't see anyway to configure that  :-\

MFGF

It would have been on the jobstream if it was going to work at all. Oh well. It was worth a try I suppose.

MF.
Meep!

eknight

Ya, thanks for the help.  :)

It really should be a checkbox in the Module properties of a datasource delivery.