打包算法 [英] Packaging algorithm

问题描述

Helloo！

我在创建打包算法时遇到了这个问题，我写了一个附带的算法，但是它的性能非常慢而且足够.谁能帮我吗 ?
包装:
-当前水平= 3
-小盒子级别包含每个带有10张卡片的盒子
-中盒子级别包含每个带有50张卡片的盒子，即5个小盒子
-大箱子级别包含2个中号箱子，其中==>大盒子里有100张卡片.等等...

这适用于一定数量的卡，因此整个操作必须循环进行，直到不再有卡可用为止.

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

推荐答案

请检查是否已创建所需的索引.
检查联接和where子句....应该在这些索引上应用索引...这肯定会提高它尚不存在的速度....

谢谢

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

这篇关于打包算法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！