tag:blogger.com,1999:blog-497787815177352569.post2854050924460784401..comments2024-01-03T08:55:04.827-05:00Comments on SQL Anywhere: Set UPDATE Versus WHERE CURRENT OF CursorBreck Carterhttp://www.blogger.com/profile/15975598564711761434noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-497787815177352569.post-53649207795121911252011-10-19T13:49:21.250-04:002011-10-19T13:49:21.250-04:00@Breck: Well, I should've known something big ...@Breck: Well, I should've known something big was coming the way:)<br /><br />My optimism w.r.t. to performance was focussed on the "more frequent commits -> less contention and smaller rollback log" issue. That might still hold. As the original UPDATE wasn't documented here, I have not thought of the implications by an ORDER BY but that's a point, I agree:)<br /><br />Do you have test results with smaller update sizes (100K or the like)?<br /><br />----<br /><br />FWIW: I do use this technique sometimes in a different way: <br /><br />"UPDATE TOP n" of those rows that are not already "handled" - which means the UPDATE will set some condition on the rows that prevent them from being selected for the UPDATE the next time - so there's no need to use an START AT clause, and the refetching might become faster as the regarded set becomes smaller... And one can simply leave the loop when @@rowcount reaches 0...<br /><br />I'm not sure whether this kind of check is apropriate in your situation...<br /><br /><br />VolkerAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-497787815177352569.post-64050103873716071902011-10-19T10:55:13.133-04:002011-10-19T10:55:13.133-04:00@Volker: Funny you should ask :)
http://sqlanywh...@Volker: Funny you should ask :) <br /><br />http://sqlanywhere.blogspot.com/2011/10/set-update-with-top-start-at-and-commit.html<br /><br />Alas, AFAIK your optimism about performance is misplaced... adding an ORDER BY to a query is a rather unlikely way to speed things up :)Breck Carterhttps://www.blogger.com/profile/15975598564711761434noreply@blogger.comtag:blogger.com,1999:blog-497787815177352569.post-27826099335057723222011-10-18T07:09:09.225-04:002011-10-18T07:09:09.225-04:00What about the performance of an UPDATE TOP n STAR...What about the performance of an <b>UPDATE TOP n START AT m ... ORDER BY ...</b> style update, i.e. using a WHILE loop to split up the update in bunches of say, million rows each (or preferably much smaller bunches) and printing messages and commiting inbetween?<br /><br />Unless such an order is difficult to find, this should give you the "best of both worlds", and I would expect the performance to be as good (or possibly better because of lesser locks and a smaller rollback log) than with one huge set-oriented UPDATE.<br /><br />Just my 2,000,000 cents<br /><br />VolkerAnonymousnoreply@blogger.com