While Loops and performance slow issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

While Loops and performance slow issue

Author

Topic

learntsql

524 Posts

Posted - 2011-07-15 : 00:53:16

Hi All,

I am using 2 while loops in my sp.

Step1: Loads the records from main table to table variable(20000 records apprx.)

step2: take distinct around 800 records and loop through
with each record and enters into inner loop

step3: for each value of outerloop it process 10 - 15 records again loads into another tablevariable

Step 4:finnaly updates main table with this inner loop table variable record values.

The total process is taking around 45 mns to 1 hr.
where is the problem excactly.
please guide me.
TIA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-15 : 00:55:06

You'll need to post the code and DDL (including indexes). We can't help otherwise, but it does sound like you'll need to rewrite this to be set-based instead.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

learntsql

524 Posts

Posted - 2011-07-15 : 01:18:40

Hi,
I am sending sample code here

DECLARE @SampleAll TABLE
(
ID INT,
Person VARCHAR(10),
date VARCHAR(10),
status VARCHAR(10)
)

DECLARE @Sample TABLE
(
ID INT,
Person VARCHAR(10),
date VARCHAR(10),
status VARCHAR(10)
)

DECLARE @SampleToUpdate TABLE
(
Person VARCHAR(10),
date VARCHAR(10),
status VARCHAR(10)
)

--INSERT INTO @SampleAll
--SELECT all records for req. dates in ID,date sort order
--loop through each ID.
--20000 records

--Outer Loop

INSERT @Sample
VALUES (1, 'p1', 'd1', 'A'),
(2, 'P1', 'd2', 'H'),
(3, 'p1', 'd3', 'H'),
(4, 'P1', 'd4', 'A'),
(5, 'p1', 'd5', 'P'),
(6, 'P1', 'd6', 'CL'),
(7, 'p1', 'd7', 'A'),
(8, 'P1', 'd8', 'H'),
(9, 'p1', 'd9', 'A'),
(10, 'P1','d10', 'P')
----20000 records

select * from @Sample

DECLARE @pstatus varchar(10)
DECLARE @cstatus varchar(10)

DECLARE @i int
DECLARE @max int

SET @i = 2
SET @pstatus = ''
SET @cstatus = ''

SELECT @max = COUNT(*) from @Sample
--Inner loop 10 - 15 records
WHILE (@i < = @max)
BEGIN
SELECT @pstatus = status from @Sample where ID = @i-1
SELECT @cstatus = status from @Sample where ID = @i
IF((@pstatus='A' and @cstatus='H') OR((@pstatus='H' and @cstatus='H')))
INSERT INTO @SampleToUpdate
SELECT Person,date,status FROM @Sample
WHERE ID = @i
ELSE
PRINT @i
SET @i = @i+1
END

--UPDATE with main table

learntsql

524 Posts

Posted - 2011-07-15 : 06:38:05

Experts!

Any Idea?

TIA.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-15 : 07:18:39

[code]SELECT curr.Person,
curr.[Date],
curr.[Status]
FROM @Sample AS curr
CROSS APPLY (
SELECT TOP(1) s.[Status]
FROM @Sample AS s
WHERE s.ID < curr.ID
ORDER BY s.ID DESC
) AS prev
WHERE curr.[Status] = 'H'
AND prev.[Status] IN ('A', 'H')[/code]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-15 : 07:22:39

You can even use the query above to update itself

;WITH cteSource(ID, Person, [Date], [Status])
AS (
	SELECT		curr.ID,
			curr.Person,
			curr.[Date],
			curr.[Status]
	FROM		@Sample AS curr
	CROSS APPLY	(
				SELECT TOP(1)	s.[Status]
				FROM		@Sample AS s
				WHERE		s.ID < curr.ID
				ORDER BY	s.ID DESC
			) AS prev
	WHERE		curr.[Status] = 'H'
			AND prev.[Status] IN ('A', 'H')
)
UPDATE	cteSource
SET	ID = -ID

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources