Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Updating 566787 Records takes forever


Updating 566787 Records takes forever

Author
Message
KavitaA
KavitaA
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 106
Hi,

I am writing a script to update bad data in a column of a table. The Table has 566787 rows and this script is taking forever to complete. It updated 1000 records in 5 mins which I think is too much.

There are two triggers and 4 indexes on this table but for now I have deleted the indexes and disabled the triggers to speed up the process



-- The script updates the FullPath column of the table according to the values
-- in Title column and hirNodeParent Column


DECLARE
@hirNode int,
@hirNodeParent int,
@Title varchar(500),
@Return varchar(500),
@LasthirNode int,
@Node int,
@FullPath varchar(1000)

Select hirNode into #hirNodes from hirNodes

While exists (Select * From #hirNodes)
Begin

Set @hirNode = (Select Top 1 hirnode from #hirNodes)
Set @Node = @hirNode
Set @FullPath = (select FullPath from hirNodes where hirNode = @hirNode)

SET @Return = ''
WHILE @hirNode > 0
BEGIN
SELECT @hirNodeParent = hirNodeParent, @Title = Title
FROM hirNodes
WHERE hirNode = @hirNode
SET @LasthirNode = @hirNode
SET @Return = @Title + '\' + @Return
SET @hirNode = @hirNodeParent
END

SET @Return = '\\' + @Return

if(@FullPath <> @Return)
begin
Update hirNodes set FullPath = @Return where hirNode = @Node
end

Delete from #hirNodes where hirNode = @Node

print(@Node)
End

Drop table #hirNodes


--Table Script

CREATE TABLE [dbo].[hirNodes](
[hirNode] [int] IDENTITY(1,1) NOT NULL,
[hirNodeParent] [int] NULL,
[hirLevel] [int] NOT NULL,
[Title] [varchar](500) NULL,
[Brief] [varchar](16) NULL,
[Description] [varchar](256) NULL,
[Active] [bit] NOT NULL,
[DisplayOrder] [varchar](50) NULL,
[FullPath] [varchar](500) NULL,
CONSTRAINT [PK_hirNodes_1] PRIMARY KEY CLUSTERED
(
[hirNode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]



Attachments
SampleData.xlsx (6 views, 8.00 KB)
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6091 Visits: 6069
The problem is that this is RBAR as your only ever updating 1 row at a time instead of trying to update 566787 records in one set based operation, meaning that the triggers fire 566787 times instead of once etc etc.

If you would provide some sample data prior to the update and what the expected result after the update should be along with the trigger and index definitions there might be a quicker set based operation to performing the task.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
This looks like you're trying to build a hierachy list using a while loop.

Can you provide some sample data for #HirNodes, as I think you're using a sledgehammer to crack nut, and a Recursive CTE may help but with out sample data its guesswork.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
yuvipoy
yuvipoy
Old Hand
Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)

Group: General Forum Members
Points: 375 Visits: 1311
What you are trying to do will obviously takes time since you have used loop statement which means it will consume more time.
can you give some sample records at-least 5 records for this table.

Thanks!
KavitaA
KavitaA
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 106
I just added the Sample data..

Do you want the code for Triggers?

The triggers only fire on Update of hirNodeParent column and Title column and I am only updating the full path column and anyway I have disabled the triggers for now.

I am attaching the code for indexes but for now I have deleted them too



/****** Object: Index [idx_hirNodes_fullPath] Script Date: 10/31/2012 19:39:16 ******/
CREATE NONCLUSTERED INDEX [idx_hirNodes_fullPath] ON [dbo].[hirNodes]
(
[FullPath] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object: Index [idx_hirNodes_hirLevel] Script Date: 10/31/2012 19:39:52 ******/
CREATE NONCLUSTERED INDEX [idx_hirNodes_hirLevel] ON [dbo].[hirNodes]
(
[hirLevel] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object: Index [idx_hirNodes_hirNodeParent] Script Date: 10/31/2012 19:41:47 ******/
CREATE NONCLUSTERED INDEX [idx_hirNodes_hirNodeParent] ON [dbo].[hirNodes]
(
[hirNodeParent] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object: Index [idx_hirNodes_Title] Script Date: 10/31/2012 19:47:55 ******/
CREATE NONCLUSTERED INDEX [idx_hirNodes_Title] ON [dbo].[hirNodes]
(
[Title] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO



Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
Definately looks like a job for a recursive CTE in order to build the Path then a simple update after, this is a simple test rig for a select, but the update isnt too bad.


drop table #hirNode
go
Create Table #hirNode(
hirNode Int
,hirNodeParent int
,Title varchar(100)
)
Insert into #hirNode
values (1,NULL,'Start')
,(2,1,'Second')
,(3,2,'Third')
,(4,NULL,'Start2')
,(5,4,'Second2');


WITH BuildPath(hirNode,hirNodeParent, NodeLevel, NodePath)
AS (SELECT hirNode,
hirNodeParent,
1,
CONVERT(varchar(255), '\\'+Title)
FROM #hirNode
WHERE hirNodeParent IS NULL --Start at the Top
UNION ALL
SELECT
h.hirNode,
h.hirNodeParent,
NodeLevel + 1,
CONVERT (varchar(255), RTRIM(NodePath) + '\' + Title)
FROM #hirNode AS h
JOIN BuildPath AS p ON h.hirNodeParent = p.hirNode
)
Select * from BuildPath




The output for NodePath is


\\Start
\\Start\Second
\\Start\Second\Third
\\Start2
\\Start2\Second2

It might need ordering but its just an example, is that what you are looking for?

_________________________________________________________________________
SSC Guide to Posting and Best Practices
KavitaA
KavitaA
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 106
Jason this looks right. But how to update FullPath column using Recursive CTE? I have to update all the rows of the table for FullPath column. Actually the right way would be to check if the FullPath column has correct value if not then update it.
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
this should work


drop table #hirNode
go
Create Table #hirNode(
hirNode Int
,hirNodeParent int
,Title varchar(100)
)
Insert into #hirNode
values (1,NULL,'Start')
,(2,1,'Second')
,(3,2,'Third')
,(4,NULL,'Start2')
,(5,4,'Second2');


WITH BuildPath(hirNode,hirNodeParent, NodeLevel, NodePath)
AS (SELECT hirNode,
hirNodeParent,
1,
CONVERT(varchar(255), '\\'+Title)
FROM #hirNode
WHERE hirNodeParent IS NULL --Start at the Top
UNION ALL
SELECT
h.hirNode,
h.hirNodeParent,
NodeLevel + 1,
CONVERT (varchar(255), RTRIM(NodePath) + '\' + Title)
FROM #hirNode AS h
JOIN BuildPath AS p ON h.hirNodeParent = p.hirNode
)
Update
hr
Set
FullPath=bp.NodePath
From
hirNodes hr
JOIN BuildPath bp on hr.hirNode=bp.hirNode



but you should check it

_________________________________________________________________________
SSC Guide to Posting and Best Practices
KavitaA
KavitaA
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 106
This works like a charm.. updated 70k records in 24 sec.

Thanks Jason :-)
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
Glad to Help,

You could add a where clause to check the NodePath with the FullPath column eg

WHERE hr.FullPath!=NodePath

Which should reduce the dataset, and save you having unnecessary updates.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search