Click here to Skip to main content
14,446,960 members
Rate this:
Please Sign up or sign in to vote.
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
Posted
Comments
Nathan St 7-Oct-10 11:34am
   
Can you provide a SQL script to create the underlying tables and populate them with sample data? If so, then I'll have a look.
RDBurmon 1-Nov-10 4:50am
   
What is the value of
<Pre Lang="SQL">(Select count(Serial) FROM dbo.IDs)</pre>

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Please check if you have created required indexes.
Check the joins and where clause....Indexes should be applied on those...It will surely increase the speed it not already there....

Thanks
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




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