update studemo set passwd = ident
ident is not part of the primary key but there is a unique, non-clustered index using that column
This has been running for over a hour on a table with 25K rows. Am I doing something basically wrong here?
If that is your exact statement, then what you aredoing is updating the password to ident for every row. I assume that that is the purpose, right?
If so, then the next question is a matter of hardware. Is this on a small machine, or a server class machine.
And, is the table clustered on password? ident being clustered wouldn't matter, but passwd would. Either way, if this is something you are doing "offline" then drop or disable indexes (2005 only) and you will certainly get better performance.
Basically, what I am trying to say is that there is no way to know from what you have told us so far, but you certainly do need to look at this being hardware oriented, so check perfmon and see what is being taxed.
|||SQL Server (2005) is on a server class machine. Hardware resources should not be an issue but I'll look into it.The are no indexes using the password column. I'll check perfmon.|||
Add a loop logic and commit after every 1000 rows
The trick is to add a flag to the table (call it anything like UPDATE_FLAG , set it to some value and in where clause do a not equal to the same value :
SET ROWCOUNT 1000
UPDATE
TABLE1
SET
COLUMN = WHATEVER
, UPDATE_FLAG = '1'
FROM
TABLE2 (OR) WHATEVER
WHERE
UPDATE_FLAG != '1'
You can perform the update in batches like below to reduce resource usage:
while(1=1)
begin
-- Update 500 rows at a time
update top(500) studemo set passwd = ident
if @.@.rowcount = 0 break
end
|||
Are your logs and data on the same drive? That could be a problem, especially if the rows are really big, and even worse if the value you are updating the value to is bigger than what it was.
I also should have mentioned to look for blocking :)
In perfmon I would look for large quantities of disk queuing and/or memory stress. Then implement one of the batching mechanisms (and probably drop indexes might help. This is esepecially true when you have large rows as page splits could be very frequent.) On such a small table building the indexes might take less time than maintaining them.
|||The log and data are on separate drives.When I run the script from above:
while(1=1)
begin
-- Update 500 rows at a time
update top(500) studemo set passwd = ident
if @.@.rowcount = 0 break
end
I would have expect to see approximately 50 rows indicating 500 rows
had been updated since the table only has 25K rows. Before I cancelled
the update, I had more than 1000 messages that 500 rows had been
updated.
Weird ... to me.
Thanks for all the reponses so far.
|||What messages did you see? Does the table have any triggers for update? What does (select count(*) from studemo) return?|||Count = 25704.Message: (500 row(s) affected) tr_slvswstuU fired, no change to address fields
There is one trigger that fires:
******************************************************
USE [fakeSISdata]
GO
/****** Object: Trigger [dbo].[tr_slvswstuU] Script Date: 08/30/2006 14:03:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create TRIGGER [dbo].[tr_slvswstuU] ON [dbo].[studemo]
FOR UPDATE
AS
set nocount on
--print 'tr_slvswstuU fired'
declare @.hcheck smallint,
@.mcheck smallint,
@.pcheck smallint,
@.ppass smallint
set @.hcheck = 0
set @.mcheck = 0
set @.pcheck = 0
Check for changes is Physical Address--
if exists (select i.suniq from inserted i,deleted d where
i.suniq = d.suniq and
(i.homeaddr1 <> d.homeaddr1 or
i.homeaddr2 <> d.homeaddr2 or
i.homecity <> d.homecity or
i.homezip <> d.homezip))
set @.hcheck = 1
--select @.hcheck
--Check for changes in Mailing Address-
if exists (select i.suniq from inserted i,deleted d where
i.suniq = d.suniq and
(i.mailaddr1 <> d.mailaddr1 or
i.mailaddr2 <> d.mailaddr2 or
i.mailcity <> d.mailcity or
i.mailzip <> d.mailzip))
set @.mcheck = 1
--Check for changes in Phone Number or Phone Type-
if exists (select i.suniq from inserted i,deleted d where
i.suniq = d.suniq and
(i.phnnumber <> d.phnnumber or
i.phntypec <> d.phntypec or
i.phnunlist <> d.phnunlist or
i.phnmsg <> d.phnmsg))
begin
declare @.oldphone char(10),
@.newphone char(10),
@.unlist smallint,
@.message smallint
set @.pcheck = 1
set @.oldphone = (select phnnumber from deleted)
set @.newphone = (select phnnumber from inserted)
set @.unlist = (select phnunlist from inserted)
set @.message = (select phnmsg from inserted)
--if @.oldphone is null or @.oldphone = ''
--set @.oldphone = 0
if exists(select phnnumber from inserted where (phnnumber is not null or phnnumber <> '') and phntypec = 'R')
set @.ppass = 1
if exists(select i.phnnumber from inserted i,deleted d
where i.suniq = d.suniq and
((i.phnnumber is null or i.phnnumber = '') and
(d.phnnumber is not null or d.phnnumber <> '') or added or
(d.phntypec = 'R' and i.phntypec <> 'R')))
begin
if exists(select phnnumber from deleted where phntypec = 'R')
set @.ppass = 2
end
end
-Call to siSp_syncaddr
--select @.hcheck,@.mcheck,@.pcheck
if @.hcheck = 1 or @.mcheck = 1 or @.pcheck = 1
begin
declare @.ident varchar(10)
set @.ident = (select ident from inserted)
if exists(select * from stucon where ident = @.ident and conmail11 = 1)
begin
--Call for multiple contacts--
if exists(select ident,count(*) from stucon where ident = @.ident and conmail11 = 1
group by ident having count(*) > 1)
begin
print 'tr_slvswstuU fired, called siSp_syncaddr for multiple contacts'
--select @.ident, 0, @.ppass, @.oldphone, @.newphone, @.unlist, @.message
--exec siSp_syncaddr @.ident, 0, @.ppass, @.oldphone, @.newphone, @.unlist, @.message
end
else
begin
--Call for sinlge contact-
declare @.conuniq int
set @.conuniq = (select conuniq from stucon where ident = @.ident and conmail11 = 1)
print 'tr_slvswstuU fired, called siSp_syncaddr for single contact'
--select @.ident, 0, @.ppass, @.oldphone, @.newphone, @.unlist, @.message
exec siSp_syncaddr @.ident, @.conuniq, @.ppass, @.oldphone, @.newphone, @.unlist, @.message
end
end
else
print 'tr_slvswstuU fired no stucon records where conmail11 = 1'
end
else
print 'tr_slvswstuU fired, no change to address fields'
set nocount off
**********************************************************************|||You will have to check the trigger logic. There are lot of SPs that are getting called from trigger code. It is not clear what they are doing. They could probably be adding rows to the same table. In any case, the UPDATE should affect same number of rows as those in the table if there are no new rows being added in the trigger. You can verify by accumulating the @.@.ROWCOUNT value in the batched update code and checking it at the end. You can use SET NOCOUNT ON to suppress the messages.
No comments:
Post a Comment