Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Updating 566787 Records takes forever Expand / Collapse
Author
Message
Posted Thursday, November 1, 2012 4:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 6:31 AM
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]




  Post Attachments 
SampleData.xlsx (6 views, 8.76 KB)
Post #1379719
Posted Thursday, November 1, 2012 4:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:20 AM
Points: 5,218, Visits: 5,067
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
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

Post #1379723
Posted Thursday, November 1, 2012 4:40 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1379724
Posted Thursday, November 1, 2012 4:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:38 AM
Points: 335, Visits: 1,141
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!
Post #1379727
Posted Thursday, November 1, 2012 4:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 6:31 AM
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


Post #1379730
Posted Thursday, November 1, 2012 5:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1379739
Posted Tuesday, November 6, 2012 5:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 6:31 AM
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.
Post #1381517
Posted Tuesday, November 6, 2012 6:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1381539
Posted Tuesday, November 6, 2012 7:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 6:31 AM
Points: 22, Visits: 106
This works like a charm.. updated 70k records in 24 sec.

Thanks Jason
Post #1381564
Posted Tuesday, November 6, 2012 8:22 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1381596
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse