Click here to Skip to main content
14,459,276 members

Packaging algorithm

SuperAdministrator asked:

Open original thread
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)

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
  4. Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100