stored proc taking too long - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

stored proc taking too long

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-05-20 : 06:55:08

Dear All

I have a stored proc that is taking way too long to process, over 50 minutes.

What I need to do is go through 180K rows, and if the projId and langid is the same, increment 1 to a value, if not, reset the incrementer

I am doing the following at the moment

DECLARE @Id int, @ProjectId int, @LangCode char(6)
DECLARE @CurProjectId int, @CurLangCode char(6)
DECLARE @incrementer int
DECLARE Order_cursor CURSOR FOR

SELECT Id, langCode, projectid FROM #tempTable

OPEN Order_cursor
SET @incrementer = 1
FETCH NEXT FROM Order_cursor INTO @Id, @LangCode, @ProjectId
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@CurProjectId = @ProjectId)
AND (@CurLangCode = @LangCode)
BEGIN
SET @incrementer = @incrementer + 1
END
ELSE
BEGIN
SET @incrementer = 1
END
UPDATE #tempTable
SET edbOrder = @incrementer
WHERE Id = @id
SET @CurProjectId = @ProjectId
SET @CurLangCode = @LangCode
FETCH NEXT FROM Order_cursor INTO @Id, @LangCode, @ProjectId
END
CLOSE Order_cursor
DEALLOCATE Order_cursor

Is there a better way?

Thanks

Johann

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 07:30:53

It seems like you want only this:-

UPDATE t
SET t.edbOrder = t.RowNo
FROM(SELECT ROW_NUMBER() OVER(PARTITION BY langCode, projectid ORDER BY langCode) AS RowNo,*
FROM #tempTable)t

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-20 : 07:53:19

Try

ORDER BY ID

instead. It seems like ID is identity column.
This suggestion only works if ALL LangCodes are in sequence. Otherwise it will fail.

If LangCode is not sequenced, you will have to use the UPDATE @variable = column approach.

Post table layout with existing indexes and some 10-20 sample data that really reflects real world scenario.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources