May 20, 2008 at 4:55 am
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
May 20, 2008 at 5:13 am
Hi Johann,
Use While Loop instead of Cursor.
Cheers!
Sandy.
--
May 20, 2008 at 5:16 am
Hi Sandy
Can you show me how to do that?
Thanks
May 20, 2008 at 5:19 am
A while loop is not going to be massivly more efficient. It's still row-by-row processing of a large number of rows.
Johann, can you give us sample input and output please, as well as the table definition. I'm sure there's a set-based way to do this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 5:25 am
Hi Gail
so here is an example
input:-
ID langCode projectId order
1 en 1
2 en 1
3 fr 1
4 fr 1
5 fr 1
6 en 2
7 en 2
8 en 2
9 fr 2
10 fr 2
output:-
ID langCode projectId order
1 en 1 1
2 en 1 2
3 fr 1 1
4 fr 1 2
5 fr 1 3
6 en 2 1
7 en 2 2
8 en 2 3
9 fr 2 1
10 fr 2 2
So I wish that when the language changes or projectid changes, the incrementer is reset
May 20, 2008 at 5:25 am
Sure,
Check this:
Declare @Temp Table
(
RowId Int identity(1,1),
)
Declare @Count Int, @Start Int
Select @Count = Count(*) from Table1
Set @Start = 1
While @Start <= @Count
BEGIN
...........
...........
Where RowID = @Start
Set @Start= @Start+ 1
END
Hope it will help you.
(All the best 2 you to do the Real Time Code)
let me know if any thing you required.
Cheers!
Sandy.
--
May 20, 2008 at 5:30 am
hi Sandy
But I want to check on criteria, if projectid or langcode changes
May 20, 2008 at 5:31 am
Could you give the table definition please and, if possible, the sample input as insert statements.
Just to make sure, you are using SQL 2005? If so, this looks like something that ROW_Number can do very nicely.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 5:34 am
Hi gail
I am definetly using SQL 2005
May 20, 2008 at 5:35 am
GilaMonster
------------------------------------------------
A while loop is not going to be massivly more efficient. It's still row-by-row processing of a large number of rows.
Johann, can you give us sample input and output please, as well as the table definition. I'm sure there's a set-based way to do this.
hey Gail,
But As far technology concern, Cursor Follows Memory management and Locking Mechanism which is the drawback for Cursor, more over to that While loop does not have any thing with respect to Interacting with server memory. It just a programming Construct to work on buffers in Clients Machine.
I think this is why while loop is better approach rather than a cursor, whether it is a large rows or small amount of rows. I can give you real time example where while loop is 100 times better than a cursor.
Cheers!
Sandy.
--
May 20, 2008 at 5:38 am
Sandy (5/20/2008)
But As far technology concern, Cursor Follows Memory management and Locking Mechanism which is the drawback for Cursor, more over to that While loop does not have any thing with respect to Interacting with server memory. It just a programming Construct to work on buffers in Clients Machine.
It's still row by row processing, which is slow no matter what. SQL is optimised for set processing, for working on multiple rows at once, not for row-by-row. For that, use a front end client.
While loops written in T-SQL don't run in the client. They run on the server, along with everything else.
I think this is why while loop is better approach rather than a cursor, whether it is a large rows or small amount of rows. I can give you real time example where while loop is 100 times better than a cursor.
And I'll give you a set-based solution a thousand times better or more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 5:38 am
I agree that I have to move away from the cursor, since its too much processing time!
However I cannot see how I can do your example with my procedure Sandy.
I have never used the RowCount for these things before
May 20, 2008 at 5:39 am
hey Johann,
Microsoft Is designed While loop in such a way that you can use IF condition with in the loop itself.
I mean to say that your all condition can be checked inside the Loop itself.
Just try it.
Cheers!
Sandy.
--
May 20, 2008 at 5:40 am
Hi Sandy
Yeah but I need to compare the row from the select to the current row
If I do a select before the While, how can i get row by row to check for the current row?
May 20, 2008 at 5:41 am
Johan, if you can give me the table definition and the sample data as insert statements, I'll have a solution for you before you and Sandy stop arguing. 😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply