On Thu, 4 Dec 2003 20:57:51 +0200 Ivar Zarans <[EMAIL PROTECTED]> wrote: .
> table1 is updated with new value (done). Update statement itself is > extremely simple: "update table1 set status = 'done' where recid = > ..." > > Most interesting is, that insert takes 0.004 seconds in average, but > update takes 0.255 seconds in average. Processing of 24000 records > took around 1 hour 20 minutes. Do you have an index on recid? and did you vacuum analyze after you loaded up the data? > > Then i changed processing logic not to update every record in table1 > after processing. Instead i did insert recid value into temporary > table and updated records in table1 after all records were processed > and inserted into table2: > UPDATE table1 SET Status = 'done' WHERE recid IN (SELECT recid FROM > temptable) > "IN" queries are terribly slow on versions before 7.4 > Why is UPDATE so slow compared to INSERT? I would expect more or less > similar performance, or slower on insert since table2 has four indexes > in addition to primary key, table1 has only primary key, which is used > on update. Am i doing something wrong or is this normal? > Remember, UPDATE has to do all the work of select and more. And if you have 4 indexes those will also add to the time (Since it has to update/add them to the tree) -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
