Re: design question - repeated updates on temp or perm table. - Mailing list pgsql-general

<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">Ingeneral, temporary tables are way faster for writing than normal tables as they don't generate WAL
records.<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;"><p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; "> <p style="-qt-paragraph-type:empty;
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> <p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">OnTuesday, February 12, 2013 11:45:22 AM Little, Douglas wrote:<br /><p style=" margin-top:12px;
margin-bottom:12px;margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Hi,<p
style="margin-top:12px; margin-bottom:12px; margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;"> <pstyle=" margin-top:12px; margin-bottom:12px; margin-left:40px; margin-right:40px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">Design question. <p style=" margin-top:12px; margin-bottom:12px;
margin-left:40px;margin-right:40px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Does it make a difference
fora function to repeatedly update a temp table verses the permanent table?<p style=" margin-top:12px;
margin-bottom:12px;margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"> <p
style="margin-top:12px; margin-bottom:12px; margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">Weare working in a data warehousing environment.<p style=" margin-top:12px; margin-bottom:12px;
margin-left:40px;margin-right:40px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">We have daily etl that’s
usedto update our dimension table which has approx. 500k rows. <p style=" margin-top:12px; margin-bottom:12px;
margin-left:40px;margin-right:40px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">A dimension row holds all
ofthe descriptive attributes related to any number of transaction records. <p style=" margin-top:12px;
margin-bottom:12px;margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">The
dimensiontable has about 50 attributes sourced from various lookup tables.    <p style=" margin-top:12px;
margin-bottom:12px;margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">In the
dimensiontable we have the id and the name/description.<p style=" margin-top:12px; margin-bottom:12px;
margin-left:40px;margin-right:40px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">If any of the lookup tables
areupdated, we need to propagate the new description to all dimension rows that use that value. <p style="
margin-top:12px;margin-bottom:12px; margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;"> <pstyle=" margin-top:12px; margin-bottom:12px; margin-left:40px; margin-right:40px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">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. <p style="
margin-top:12px;margin-bottom:12px; margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;"> <pstyle=" margin-top:12px; margin-bottom:12px; margin-left:40px; margin-right:40px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">So the function has about 50 update statements,  each setting 1
columnat a time. <p style=" margin-top:12px; margin-bottom:12px; margin-left:40px; margin-right:40px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">Individual update statements update between a few thousand to
300krows.<p style=" margin-top:12px; margin-bottom:12px; margin-left:40px; margin-right:40px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;"> <p style=" margin-top:12px; margin-bottom:12px; margin-left:40px;
margin-right:40px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">We’re refactoring the code now and
consideringupdating a temp table repeatedly and finally updating the perm table, instead of updating the perm table 50
times.<pstyle=" margin-top:12px; margin-bottom:12px; margin-left:40px; margin-right:40px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;"> <p style=" margin-top:12px; margin-bottom:12px; margin-left:40px;
margin-right:40px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">I don’t see much difference between a temp
andperm table.  I suspect that the update process is the same for both types.   <p style=" margin-top:12px;
margin-bottom:12px;margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Can
anybodycomment if there’d be any difference in update performance?<p style=" margin-top:12px; margin-bottom:12px;
margin-left:40px;margin-right:40px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Would it be worth the
effortto vacuum after each update?<p style=" margin-top:12px; margin-bottom:12px; margin-left:40px; margin-right:40px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;"> <p style=" margin-top:12px; margin-bottom:12px;
margin-left:40px;margin-right:40px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Note – any of the rows in
theperm table may need to be updated,  so the temp table would be a copy of the perm table. <p style=" margin-top:12px;
margin-bottom:12px;margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"> <p
style="margin-top:12px; margin-bottom:12px; margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">Thanksin advance.<p style=" margin-top:12px; margin-bottom:12px; margin-left:40px;
margin-right:40px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;"> <p style=" margin-top:12px;
margin-bottom:12px;margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"> <p
style="margin-top:12px; margin-bottom:12px; margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;"><spanstyle=" font-family:'Arial,sans-serif'; font-weight:600;">Doug Little</span><p style="
margin-top:12px;margin-bottom:12px; margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;"><spanstyle=" font-size:12pt; font-weight:600;"> </span><p style=" margin-top:12px;
margin-bottom:12px;margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"><span
style="font-family:'Arial,sans-serif';">Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz
Worldwide</span><p style=" margin-top:12px; margin-bottom:12px; margin-left:40px; margin-right:40px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;"><span style=" font-family:'Arial,sans-serif';">500 W. Madison,
Suite1000  Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741</span><p style="
margin-top:12px;margin-bottom:12px; margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;"><ahref="mailto:Douglas.Little@orbitz.com"><span style=" font-family:'Arial,sans-serif';
font-weight:600;text-decoration: underline; color:#0000ff;">Douglas.Little@orbitz.com</span></a><p style="
margin-top:12px;margin-bottom:12px; margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;"> <imgheight="61" src="cid:785416783@KDE" width="83" />  <a href="http://www.orbitz.com/"><span
style="font-family:'Arial,sans-serif'; font-size:8pt; text-decoration: underline; color:#0000ff;">orbitz.com</span></a>
<spanstyle=" color:#0000ff;">|</span> <a href="http://www.ebookers.com/"><span style=" font-family:'Arial,sans-serif';
font-size:8pt;text-decoration: underline; color:#0000ff;">ebookers.com</span></a> <span style="
color:#0000ff;">|</span><a href="http://www.hotelclub.com/"><span style=" font-family:'Arial,sans-serif';
font-size:8pt;text-decoration: underline; color:#0000ff;">hotelclub.com</span></a> <span style="
color:#0000ff;">|</span><a href="http://www.cheaptickets.com/"><span style=" font-family:'Arial,sans-serif';
font-size:8pt;text-decoration: underline; color:#0000ff;">cheaptickets.com</span></a> <span style="
color:#0000ff;">|</span><a href="http://www.ratestogo.com/"><span style=" font-family:'Arial,sans-serif';
font-size:8pt;text-decoration: underline; color:#0000ff;">ratestogo.com</span></a><span style=" color:#0000ff;">
|</span><a href="http://www.asiahotels.com/"><span style=" font-family:'Arial,sans-serif'; font-size:8pt;
text-decoration:underline; color:#0000ff;">asiahotels.com</span></a><p style=" margin-top:12px; margin-bottom:12px;
margin-left:40px;margin-right:40px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"> <p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"><br /><br
/><pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">--<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">postgresql.org.mx