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