Slow Data Delivery speeds up in Check Only execution mode

[Lmorales]

Using Data Manager 8.1 on Windows 32 bit / Oracle Database 11.1 64 bit, separate servers.
My Fact Delivery Module Properties are set as follows:
Refresh type: UPDATE/INSERT
Update Detection Method: UPDATE

When I execute in normal mode, delivery is slow:

[DETAIL     - 22:23:35] 5000 input (so far), 5000 accepted, 5000 delivered
[INTERNAL   - 22:23:35] Mem(M): 255.8 [Peak=255.8] (Ref=255.8 Domain=0.0 Pool=0.0)
[DETAIL     - 22:23:41] 10000 input (so far), 10000 accepted, 10000 delivered
[INTERNAL   - 22:23:41] Mem(M): 255.8 [Peak=255.8] (Ref=255.8 Domain=0.0 Pool=0.0)
...

but, if I execute in Check Only mode, it gets really fast:

[DETAIL     - 00:01:26] 5000 input (so far), 5000 accepted, 5000 delivered
[INTERNAL   - 00:01:26] Mem(M): 255.8 [Peak=255.8] (Ref=255.8 Domain=0.0 Pool=0.0)
[DETAIL     - 00:01:26] 10000 input (so far), 10000 accepted, 10000 delivered
[INTERNAL   - 00:01:26] Mem(M): 255.8 [Peak=255.8] (Ref=255.8 Domain=0.0 Pool=0.0)
...

I have properly indexed my key columns at database level. What other parameters should I consider to improve my delivery performance?

Please advise. Thanks.

Luis

[wyconian]

HI

I would expect the delivery in check only mode to be much faster than the actual delivery as it isn't de;vierying any data just checking that everything works OK.

There are a couple of things to consider that may speed up the delivery;

Are you dropping the non primary key indexes before you run the build?

Have you indexed the column(s) you're updating against as primary keys?

Is the update/insert (update) the right method?  I would use the update method if I was expecting the majority of records coming into the fact to be updated.  If there are fewer records coming in that need to be updated you could look at the select method which may be faster (the update method tries to update all records, the select method works out which records need to be updated and only updates these records).

If a large majority of records need to get updated you could consider using a truncate delivery instead of the update, you will get the current picture but may loose the history of how things have changed.  You could also look at using a dimension build instead of a fact build. You can then use the track changes option to get DM to track the changes (in this case you may be getting into the realm of a slow changing fact which is unusual).

Are you using a delta delivery?  i.e. is there a way you can reduce the number of records being loaded maybe by using an update date or a minus statement.

Depending on how many records you are planning on loading each run you could look at using a SQLloader delivery rather than a standard relational table delivery (could be useful if you're loading 100k + records.

Just a couple of ideas off the top of my head :)

Good luck

[eknight]

How did you select 'check only'? Is 'check only' meant for testing and development?

[wyconian]

HI

Check only is one of the options from the execute screen. You're right it's meant to just check the delivery without loading any data.  I think it checks things like missing columns, it doesn't pick up things like duplicate records which can break the primary key constraint.