14,459,276 members

# Packaging algorithm

Helloo !

i have this problem creating a packaging algorithm, i wrote the one attached but its performance is VERY slow and in adequate. can anyone help me out ?
packaging:
-current levels = 3
-small box level contains boxes with 10 cards each
-medium box level contains boxes with 50 cards each i.e. 5 small boxes
-large boxes level contains 2 medium boxes with ==> the large box has 100 cards. and so on ...

this is applied to a certain number of cards so the whole operation has to loop till no cards are available anymore.

```ALTER PROCEDURE [dbo].[test123]
-- Add the parameters for the stored procedure here

AS
Declare @CardsCount int; -- Number of cards processed
Declare @Iterator int; --
Declare @SmallLoopSize int; -- Number of batches
Declare @MediumLoopSize int; -- Number of batches
Declare @LargeLoopSize int; -- Number of batches
Declare @Quantity int; -- number of records
Declare @FromICCIDl varchar(50); -- Value to be inserted in the batch table
Declare @ToICCID varchar(50);-- Value to be inserted in the batch table
Declare @ToQuantity int;
Declare @ToQuantityMedium int;
Declare @ToQuantityLarge int;
Declare @Name varchar(50);
Declare @LargeIterator varchar(50);
Declare @MediumIterator varchar(50) ;
Declare @SmallIterator varchar(50) ;
Declare @MediumBoxFrom int ;
Declare @FromICCIDMedium varchar(50); -- Value to be inserted in the batch table
Declare @ToICCIDMedium varchar(50);
Declare @FromICCIDLarge varchar(50);
Declare @ToICCIDLarge	varchar(50);
Declare @LargeBoxFrom varchar(50)

Set @MediumBoxFrom = 0;
Set @LargeBoxFrom = 0;
Set @LargeIterator = 0;
Set @MediumIterator = 0;
Set @SmallIterator = 0;
Set @CardsCount = 1;
Set @Quantity =   (Select count(Serial) FROM dbo.IDs); -- total number of cards
Set @LargeLoopSize = ceiling(@Quantity/100);
Set @MediumLoopSize = ceiling(@LargeLoopSize*2 );
Set @SmallLoopSize = ceiling(@MediumLoopSize  * 5);

WHILE @LargeIterator  < @LargeLoopSize Begin
IF((@Quantity -@CardsCount+1) <  1) Begin
Set @ToQuantityLarge = @Quantity;
END
Else Begin
Set @ToQuantityLarge = @CardsCount+99  ;
End

Set @FromICCIDLarge =  (SELECT Serial = MAX(Serial) FROM
(SELECT TOP (100*(@LargeBoxFrom) + 1  ) Serial
FROM dbo.IDs
ORDER BY Serial) sub);

Set @ToICCIDLarge =    (SELECT Serial = MAX(Serial) FROM
(SELECT TOP (@ToQuantityLarge) Serial
FROM dbo.IDs
ORDER BY Serial) sub);

Set @Name =  @LargeIterator;

Insert Into dbo.BoxingAndStats (BoxIndex,[From], [To], BoxType)
Values(@Name, @FromICCIDLarge, @ToICCIDLarge, '100');

WHILE @MediumIterator  < @MediumLoopSize Begin
IF((@Quantity -@CardsCount+1) <  2) Begin
Set @ToQuantityMedium = @Quantity;
END
Else Begin
Set @ToQuantityMedium = @CardsCount+49  ;
End

Set @FromICCIDMedium =  (SELECT Serial = MAX(Serial) FROM
(SELECT TOP (50*(@MediumBoxFrom) + 1  ) Serial
FROM dbo.IDs
ORDER BY Serial) sub);

Set @ToICCIDMedium =    (SELECT Serial = MAX(Serial) FROM
(SELECT TOP (@ToQuantityMedium) Serial
FROM dbo.IDs
ORDER BY Serial) sub);

Set @Name =  @LargeIterator+'-' +@MediumIterator;

Insert Into dbo.BoxingAndStats (BoxIndex,[From], [To], BoxType)
Values(@Name, @FromICCIDMedium, @ToICCIDMedium, '50');

WHILE @SmallIterator  < @SmallLoopSize Begin

IF((@Quantity -@CardsCount+1) <  10) Begin
Set @ToQuantity = @Quantity+1;
END
Else Begin
Set @ToQuantity = @CardsCount + 9 ;
End

Set @FromICCIDl =       (SELECT Serial = MAX(Serial) FROM
(SELECT TOP (@CardsCount) Serial
FROM dbo.IDs
ORDER BY Serial) sub);

Set @ToICCID =          (SELECT Serial = MAX(Serial) FROM
(SELECT TOP (@ToQuantity) Serial
FROM dbo.IDs
ORDER BY Serial) sub);

Set @Name = @LargeIterator +'-' +@MediumIterator + '-' + @SmallIterator;

Insert Into dbo.BoxingAndStats (BoxIndex,[From], [To], BoxType)
Values(@Name, @FromICCIDl, @ToICCID, '10');

SET  @CardsCount = @CardsCount + '10';
SET	@SmallIterator = @SmallIterator + 1
if(@SmallIterator % 5 = 0) begin
Set @SmallIterator =0;
end
if(@SmallIterator % 5 = 0)break
END
SET	@MediumIterator = @MediumIterator + 1;
SET @MediumBoxFrom = @MediumBoxFrom +1;
if(@MediumIterator % 2 = 0)begin
Set @MediumIterator=0
end
if(@MediumIterator % 2 = 0)	break
END
SET @LargeIterator = @LargeIterator + 1;
SET @LargeBoxFrom = @LargeBoxFrom +1

end```
Tags: SQL, SQL-Server (SQL-Server-2005)