Re: design question - repeated updates on temp or perm table.

In general, temporary tables are way faster for writing than normal tables as
they don't generate WAL records.

On Tuesday, February 12, 2013 11:45:22 AM Little, Douglas wrote:

Hi,

Design question.
Does it make a difference for a function to repeatedly update a temp table
verses the permanent table?

We are working in a data warehousing environment.
We have daily etl that’s used to update our dimension table which has approx.
500k rows.
A dimension row holds all of the descriptive attributes related to any number
of transaction records.
The dimension table has about 50 attributes sourced from various lookup
tables.
In the dimension table we have the id and the name/description.
If any of the lookup tables are updated, we need to propagate the new
description to all dimension rows that use that value.

Instead of a cursor going thru all of the rows and updating the columns, we
use a column update approach where we update specific columns for all rows that
need a update.

So the function has about 50 update statements, each setting 1 column at a
time.
Individual update statements update between a few thousand to 300k rows.

We’re refactoring the code now and considering updating a temp table
repeatedly and finally updating the perm table, instead of updating the perm
table 50 times.

I don’t see much difference between a temp and perm table. I suspect that the
update process is the same for both types.
Can anybody comment if there’d be any difference in update performance?
Would it be worth the effort to vacuum after each update?

Note – any of the rows in the perm table may need to be updated, so the temp
table would be a copy of the perm table.

Thanks in advance.


Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz
Worldwide
500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax
312.894.5164 | Cell 847-997-5741
Douglas(dot)Little(at)orbitz(dot)com
orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com |
ratestogo.com | asiahotels.com

--
postgresql.org.mx