help me get ride of my loop please ;)

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)

help me get ride of my loop please ;)

Author

Topic

Jillc
Starting Member

4 Posts

Posted - 2008-03-14 : 00:16:45

I am working on upgrading an application from SQL2000 to 2005. I am not that familiar with many of the new 2005 features and was hoping that they could be utilized to help a poor performing part of our application. The current application is using a loop to create batches of work. There are two limits the max number of rows to be placed in a batch and also the total SUM of another column. FO example a batch will be create for no more that 3 records or if the SUM of column c2 is > 10.


use tempdb
go
create table t1(c1 int IDENTITY(1,1), c2 int)
GO
INSERT t1 (c2) VALUES (1)
INSERT t1 (c2) VALUES (1)
INSERT t1 (c2) VALUES (1)
INSERT t1 (c2) VALUES (5)
INSERT t1 (c2) VALUES (5)
INSERT t1 (c2) VALUES (10)
INSERT t1 (c2) VALUES (5)
INSERT t1 (c2) VALUES (5)
GO


DECLARE @maxrows int
DECLARE @maxsum int
DECLARE @currentSum int
DECLARE @currentRows int
DECLARE @batchID int
DECLARE @Batch table (BatchID int, c1 int)

DECLARE @cnt int, @maxcnt int
SELECT @maxcnt = MAX(c1) FROM t1
SELECT @cnt = 1
SELECT @maxrows = 3
SELECT @maxsum = 10
SELECT @batchID = 1

SELECT @currentSum = 0, @currentRows = 0

WHILE  @cnt <= @maxcnt
BEGIN
	SELECT @currentSum = @currentSum + c2 FROM t1 WHERE c1 = @cnt
	SET @currentRows = @currentRows + 1  
     
	INSERT @Batch (BatchID, c1) VALUES (@batchID, @cnt) 
     
	IF ((@currentSum >= @maxsum) OR (@currentRows >= @maxrows))  
	BEGIN	
		SELECT @currentSum = 0, @currentRows = 0 , @batchID = @batchID + 1
	END

	SELECT @cnt = @cnt + 1
	
END

SELECT a1.BatchID, a2.* FROM @Batch a1 INNER JOIN t1 a2  ON a1.c1 = a2.c1

If you run the sample code you will see the first three records are created as a batch as they hit the @maxrows threshold. The second batch only has two records as it hits the @max sum threshold. I was hoping that I could use a CTE for this? Any help would be appreciated

Thanks
Jill

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 02:14:23

Yes, a CTE can solve your problems.
If not someone beats me to it, I can post a solution within a few hours. Have some matters to attend first.

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

Jillc
Starting Member

4 Posts

Posted - 2008-03-14 : 11:19:56

thanks I would appreciate it.
Jill

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 11:28:32

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
c1 INT,
c2 INT
)

INSERT @Sample VALUES ( 1, 1)
INSERT @Sample VALUES ( 2, 1)
INSERT @Sample VALUES ( 3, 1)
INSERT @Sample VALUES ( 4, 5)
INSERT @Sample VALUES ( 6, 5)
INSERT @Sample VALUES ( 8, 10)
INSERT @Sample VALUES ( 9, 5)
INSERT @Sample VALUES (10, 5)

-- Initialize control variables
DECLARE @MaxRows INT,
@MaxSum INT

-- Set limits
SELECT @MaxRows = 3,
@MaxSum = 10

-- Define CTE's
;WITH Stage (c1, c2, RecID)
AS (
SELECT c1,
c2,
ROW_NUMBER() OVER (ORDER BY c1)
FROM @Sample
), Yak (RecID, BatchID, c1, c2, s, r)
AS (
SELECT RecID,
1,
c1,
c2,
c2,
1
FROM Stage
WHERE RecID = 1

UNION ALL

SELECT y.RecID + 1,
CASE
WHEN y.s >= @MaxSum THEN y.BatchID + 1
WHEN y.r >= @MaxRows THEN y.BatchID + 1
ELSE y.BatchID
END,
s.c1,
s.c2,
CASE
WHEN y.s >= @MaxSum THEN s.c2
WHEN y.r >= @MaxRows THEN s.c2
ELSE y.s + s.c2
END,
CASE
WHEN y.s >= @MaxSum THEN 1
WHEN y.r >= @MaxRows THEN 1
ELSE y.r + 1
END
FROM Yak AS y
INNER JOIN Stage AS s ON s.RecID = y.RecID + 1
)

-- Show the expected result
SELECT BatchID,
c1,
c2
FROM Yak[/code]

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

Jillc
Starting Member

4 Posts

Posted - 2008-03-14 : 14:07:08

wow, thanks so much. It will take me a bit to figure out how it works so I can write them myself but this is exactly what I needed.

Thanks
Jill

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources