Archives of the TeradataForum

Message Posted: Tue, 08 Sep 2009 @ 09:18:27 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Tony,

First thing to check is exact type of Inserts being used and some basic info about your fact table.

1) I'm assuming that the DELETE clause will have a WHERE clause that does ** not ** match the fact table's PI or PPI clause ? In this case the DELETE clause will place an all-AMP, table-level Write lock on the fact table. This only one DELETE statement (and no INSERTs) can run concurrently.

2) For the Inserts are you using "Insert/Select" or "Insert Values" statements ? An "Insert/Select" will typically (maybe always) place an an all-AMP, table-level Write lock on the target table, hence only one "insert/select" (and no DELETEs) can run concurrently. An "insert values" statement will usually only lock at the row hash level - you may need to check locking on any secondary indexes or join indexes.

One possible alternative may be to load all transaction data into a separate work table and apply using merge/update logic. There will still be locking but it will be easier for you to manage from a restart/recovery perspective (the DBMS does most of the work for you). Or maybe merge all of your 'transaction' files into one on the client and then run a single Multiload process to update the fact table. Each approach will have pro' and cons.

Start with those questions/thoughts and see where you get to.

Cheers,

Dave

Ward Analytics Ltd: Information in motion ( www.ward-analytics.com )

	<Prev	Next>		<<First	<Prev	Next>	Last>>