Search Postgresql Archives

design question - repeated updates on temp or perm table.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.Little@xxxxxxxxxx

 cid:image001.jpg@01CABEC8.D4980670  orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux