[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

update and locks:prioritizing it without single user mode

we can't afford to bring the system down to single user to do the update.. but when i last ran it, it just was running for 40 minutes, and users also were not able to browse.. so i cancelled it..

is there anything i can add to the below, that can help to accomplish better with the updates, rather than doing it in single user mode ..
0
25112
Asked:
25112
  • 9
  • 5
4 Solutions
 
25112Author Commented:
the code is as below:

could you give what would be the general standards in doing something like this on a live system, when users are also accessing the table.. regardless of how many rows and indexes are in it..
begin tran
update a set poll_ID = b.poll_ID8 from pollppp a join pollppp8 b on a.poll_ID = b.poll_ID


update a set poll_ID = b.poll_ID8 from aruscv a join pollppp8 b on a.poll_ID = b.poll_ID
update a set poll_ID = b.poll_ID8 from aruscvh a join pollppp8 b on a.poll_ID = b.poll_ID
update a set poll_ID = b.poll_ID8 from aruscvi a join pollppp8 b on a.poll_ID = b.poll_ID
update a set poll_ID = b.poll_ID8 from aruscvk a join pollppp8 b on a.poll_ID = b.poll_ID

update a set cinstalledby = b.poll_ID8 from aruscv a join pollppp8 b on a.cinstalledby = b.poll_ID
update a set cinstalledby = b.poll_ID8 from aruscvh a join pollppp8 b on a.cinstalledby = b.poll_ID
update a set cinstalledby = b.poll_ID8 from aruscvi a join pollppp8 b on a.cinstalledby = b.poll_ID
update a set cinstalledby = b.poll_ID8 from aruscvk a join pollppp8 b on a.cinstalledby = b.poll_ID

--commit
--rollback

Open in new window

0
 
TempDBACommented:
Do you have proper indexes on the required column? What indexes you have on the tables being used?

And instead of updating in a single shot, you can do batch updation.
0
 
25112Author Commented:
i have nonclustered index on all the join columns..

but if a user is using, the lock won't be released, right.. so beyond the fact of index and if it is small or large number of rows, could you suggest what are the standard on how to effectively avoid locks or run it behind the scenes and waiting for the user to stop using the table and then updating it as soon as the lock is released?

i can run them in batch, still the same problem, though..
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Anthony PerkinsCommented:
>>could you suggest what are the standard on how to effectively avoid locks or run it behind the scenes and waiting for the user to stop using the table and then updating it as soon as the lock is released?<<
As always you cannot avoid "locks" the best you can do is minimize there effect.  One problem I can see with the code you posted is that the Transaction is never stopped:  You have both the COMMIT and the ROLLBACK commented out.  That query as is will never complete and there is no wonder the users are locked out.

If that was a typo and you did not actually do that (hopefully that is the case) then can you tell us how many rows do you anticipate updating in each query?
0
 
25112Author Commented:
Hi, I am having commit uncommented in code here.. sorry for the code typo above..

I am expecting the following # of updates
353
213662
1028241
60569
179969
24598
26987
1920
1457
0
 
25112Author Commented:
after 3.5 hours today, it did not complete.. so i just stopped it.. below is the results in the 'messages' part.. i see that it is doing one record at at time.. i thought it will be more like 9 batches.. but seems like not.. why is it updating one row at time instead of the whole things...

there are 488925 rows like the below when i cancelled it, in the message box..  and the last line is "Query was cancelled by user." like the below...


(1 row(s) affected)

(1 row(s) affected)

(0 row(s) affected)
Query was cancelled by user.
(1 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Open in new window

0
 
25112Author Commented:
today being sunday, the crowd is small on the database.. (only 49 rows in master..sysprocesses  for this database).. but still not able to quickly..
0
 
Anthony PerkinsCommented:
>> i see that it is doing one record at at time.<<
I would have to guess you have a very badly written TRIGGER on at least one of the tables.

Instead of this:
update a set poll_ID = b.poll_ID8 from pollppp a join pollppp8 b on a.poll_ID = b.poll_ID

Do something like this:
SET ROWCOUNT 100000

SELECT GETDATE()

WHILE @@ROWCOUNT > 0
      UPDATE  a
      SET     poll_ID = b.poll_ID8
      FROM    pollppp a
                  JOIN pollppp8 b ON a.poll_ID = b.poll_ID
      WHERE      a.poll_ID != b.poll_ID8

SELECT GETDATE()

SET ROWCOUNT 0


>>but still not able to quickly.. <<
You cannot have Indexes setup correctly.
0
 
Anthony PerkinsCommented:
Incidentally, I suspect you may want to check out your Transaction Log.  I suspect it has grown somewhat bigger than you expect, especially if the database is in Full Recovery Model.
0
 
25112Author Commented:
>>I would have to guess you have a very badly written TRIGGER on at least one of the tables.
I checked and you are right on.. we need to look into it separately.. ( it is a custom package - we did not write it)

thanks for the looping idea.. will try it

>>You cannot have Indexes setup correctly.
did you mean "you don't have indexes setup correctly'?
0
 
25112Author Commented:
>>I suspect you may want to check out your Transaction Log.  I suspect it has grown somewhat bigger than you expect, especially if the database is in Full Recovery Model.

thanks for the scare! it helps- you never know.. but thankfully, this is in simple mode.. and only 220mb is being used at the moment.. (total size is 10GB)
0
 
Anthony PerkinsCommented:
>>did you mean "you don't have indexes setup correctly'? <<
You could say that, I cannot:
My point was that if it is taking that long it must be because "You cannot have Indexes setup correctly".  Since I am not infront of your computer, I cannot state that unequivocally as in "you don't have indexes setup correctly".  But English is not my first language so I am willing to be corrected.
0
 
25112Author Commented:
acperkins, i understand your point.. you don't have the advantage of seeing the schema etc. but generally, if you have an index on all the join columns, that is not enough?

>>ROWCOUNT
the code you gave is smart.. but the main advantage is when it is on FULL mode, right (for log management).. i am sure it will help in simple mode also.  Apart from log management, it will take the same time to as the straight update, right..

the thing is we need to have a all or nothing  update, in this case.. that is why i hesitated to use the ROWCOUNT approach... i am sure it will be handy in other updates..

the query i ran ran for 11 hours on sunday and sitll did not finish.. so i had to cancel..
0
 
Anthony PerkinsCommented:
>>that is not enough?<<
It should be.

>> i am sure it will help in simple mode also.<<
Yes, it will.

>>that is why i hesitated to use the ROWCOUNT approach<<
That is irrelevant.  That is why you are using a transaction.  If there is an error you can roll-back.

>>the query i ran ran for 11 hours on sunday and sitll did not finish.. so i had to cancel..<<
If the tables are appropriately indexed and you are only updating a couple of million rows, than it should not take that long, 30 minute should be more than enough.  I suspect the problem you are having is that you have badly written TRIGGERs that are killing you.  So either disable the TRIGGERs or fix them.  You have no other choice.
0
 
25112Author Commented:
thx 4 helping to isolate.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now