Re: Update Query Runs Forever

"Neil" <nospam@xxxxxxxxxx> wrote in message news:jVn1k.4024$co7.2050@xxxxxxxxxxxxxxxxxxxxxxx
Just curious: what kind of trouble down the road would using * cause? I would think it would prevent trouble down the road since, if the fields change, it automatically uses the current fields at all times. What kind of trouble are you referring to.


If you add a column to one of the tables, the statement using "*" will cause your trigger to fail. You make the assumption that someone who will change one of the tables later on will know about this trigger and the process of archiving to another table, but what if not? Even you did not think about the trigger when seeing this performance problem at first...

Also, a question: do you have any idea why the update query and trigger worked when there were 200 rows being updated, but not 1200?


If you have concurrent users running queries on the table, a transaction that updates 200 rows may have a chance for time span to complete in between user queries. But a transaction that updates 1200 rows one at a time takes more than 10 times longer and gets blocked. A set based update may be very fast for 1200 rows, but the cursor you use is not.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

.

Relevant Pages

  • Re: SP2 - Cable Connection
    ... >> trouble accessing the internet. ... Check for errors before doing a defrag, as defragging a sick HD can ... Empty your Temporary Internet Files and shrink the ... On software updates; unless there is a security / risk aspect, ...
    (microsoft.public.windowsxp.security_admin)
  • Re: Latest Bundle of MS Office Security Patches Willnot Install
    ... Windows updates are more trouble then they are worth. ...
    (microsoft.public.windowsupdate)
  • Re: New version of ZoneAlarm available
    ... I always do 'fresh' reinstalls of ZA updates. ... used/assigned by the router) into ZA's Trusted zone. ... WinXP needs additional Trusted zone entries, both are localhost Ips: ... I Googled the trouble and found one of the mvps recommending adding the localhosts to cure this ...
    (microsoft.public.windowsxp.help_and_support)
  • Re: New version of ZoneAlarm available
    ... I always do 'fresh' reinstalls of ZA updates. ... used/assigned by the router) into ZA's Trusted zone. ... WinXP needs additional Trusted zone entries, both are localhost Ips: ... I Googled the trouble and found one of the mvps recommending adding the localhosts to cure this ...
    (microsoft.public.windowsxp.hardware)
  • Re: New version of ZoneAlarm available
    ... I always do 'fresh' reinstalls of ZA updates. ... used/assigned by the router) into ZA's Trusted zone. ... WinXP needs additional Trusted zone entries, both are localhost Ips: ... I Googled the trouble and found one of the mvps recommending adding the localhosts to cure this ...
    (microsoft.public.windowsxp.general)
Loading