x

Update large database

Hi,

If GlbStock greater then M1 then it will perform this @TDoc=@TDoc+30 and GlbStock=GlbStock-M1 check if remianing GlbStock is greater then M2 then it will perform this @TDoc=@TDoc+30 and GlbStock=GlbStock-M2 and it continue the comparison till GlbStock become less then Month

Once GlbStock become less them Month it will perform @IncDoc=GlbStock/Month*30 @TDoc=@TDoc+@IncDoc and stop the calculation Row

Suppose GlbStock is become less then M3 then @IncDoc=GlbStock/M3*30

The below calculation is working perfectly but as the update is performing row by row it is time consuming. I have more then 50L records, so it takes more then 8hr to update

Is there any alternate way to implement above case

Below is the code :

 CREATE TABLE [dbo].[StockDetails](
     [Name] [varchar](100) NULL,
     [GlbStock] [int] NULL,
     [M1] [int] NULL,
     [M2] [int] NULL,
     [M3] [int] NULL,
     [M4] [int] NULL,
     [M5] [int] NULL,
     [M6] [int] NULL,
     [M7] [int] NULL,
     [M8] [int] NULL,
     [M9] [int] NULL,
     [M10] [int] NULL,
     [M11] [int] NULL,
     [M12] [int] NULL,
 )
 
 INSERT [dbo].[StockDetails] ([Name], [GlbStock], [M1], [M2], [M3], [M4], [M5], [M6], [M7], [M8], [M9], [M10], [M11], [M12]) VALUES (N'ABC', 100, 10, 50, 60, 20, 80, 90, 10, 20, 80, 90, 10, 10)
 INSERT [dbo].[StockDetails] ([Name], [GlbStock], [M1], [M2], [M3], [M4], [M5], [M6], [M7], [M8], [M9], [M10], [M11], [M12]) VALUES (N'XYZ', 20, 50, 20, 10, 20, 30, 10, 80, 90, 10, 40, 50, 60)
 INSERT [dbo].[StockDetails] ([Name], [GlbStock], [M1], [M2], [M3], [M4], [M5], [M6], [M7], [M8], [M9], [M10], [M11], [M12]) VALUES (N'PQR', 1000, 10, 50, 60, 70, 10, 10, 20, 30, 40, 20, 10, 10)
 INSERT [dbo].[StockDetails] ([Name], [GlbStock], [M1], [M2], [M3], [M4], [M5], [M6], [M7], [M8], [M9], [M10], [M11], [M12]) VALUES (N'PQR', 0, 10, 20, 30, 40, 50, 80, 10, 2, 0, 0, 0, 0)
 INSERT [dbo].[StockDetails] ([Name], [GlbStock], [M1], [M2], [M3], [M4], [M5], [M6], [M7], [M8], [M9], [M10], [M11], [M12]) VALUES (N'P', 1, 10, 20, 30, 40, 50, 80, 10, 2, 0, 0, 0, 0)
 
 CREATE TABLE #STOCK_DETAIL
 (
                 RowID int IDENTITY(1, 1),
                 NAME VARCHAR(100),
                 GlbStock int,
                 M1 int, M2 int, M3 int, M4 int, M5 int, M6 int, M7 int, M8 int, M9 int, M10 int, M11 int,  M12 int, IncDoc int,
                 TDoc int
 )
 
 DECLARE @NumberRecords int, @RowCount int
 DECLARE @NAME varchar(50)
 DECLARE @GS int, @IncDoc int,@TDoc int
 DECLARE @M1 BIGINT,@M2 BIGINT,@M3 BIGINT,@M4 BIGINT,@M5 BIGINT,@M6 BIGINT,@M7 BIGINT,@M8 BIGINT,@M9 BIGINT,@M10 BIGINT,@M11 BIGINT,@M12 BIGINT
 
 INSERT INTO #STOCK_DETAIL(NAME,GlbStock,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12)
 
 SELECT [NAME],[GlbStock],[M1],[M2],[M3],[M4],[M5],[M6],[M7],[M8],[M9],[M10],[M11],[M12]   FROM [dbo].STOCK_DETAIL
 
 SET @NumberRecords = @@ROWCOUNT
 SET @RowCount = 1
 SET @GS=0
 WHILE @RowCount <= @NumberRecords
 BEGIN
 
        SELECT @NAME=NAME,@GS=GlbStock,@M1=M1,@M2=M2,@M3=M3,@M4=M4,@M5=M5,@M6=M6,@M7=M7,@M8=M8,@M9=M9,@M10=M10,@M11=M11,@M12=M12
 
        FROM #STOCK_DETAIL   WHERE RowID = @RowCount
 
        set @TDoc=0
 
        IF @GS>@M1
 
               BEGIN
                      set @TDoc=@TDoc+30
                      set @GS=@GS-@M1
             end
         else
 
               BEGIN
                      SET @IncDoc=@GS/@M1*30
                      SET @TDoc=@TDoc+@IncDoc
                      GOTO Branch_One
               END
 
         IF @GS>@M2
 
               BEGIN
 
                      SET @TDoc=@TDoc+30
                      set @GS=@GS-@M2
                 end
         else
             BEGIN
                      SET @IncDoc=@GS/@M2*30
                      SET @TDoc=@TDoc+@IncDoc
                      GOTO Branch_One
               END
                                
        IF @GS>@M3
               BEGIN
                      SET @TDoc=@TDoc+30
                      set @GS=@GS-@M3
                 end
         else
               BEGIN
                      SET @IncDoc=@GS/@M3*30
 
                      SET @TDoc=@TDoc+@IncDoc
                      GOTO Branch_One
               END
               
        IF @GS>@M4
               BEGIN
                      SET @TDoc=@TDoc+30
                      set @GS=@GS-@M4
               end
         else
               BEGIN
                      SET @IncDoc=@GS/@M4*30
                      SET @TDoc=@TDoc+@IncDoc
                      GOTO Branch_One
               END
               
        IF @GS>@M5
               BEGIN
                      SET @TDoc=@TDoc+30
                      set @GS=@GS-@M5
 
                 end
         else
 
               BEGIN
                      SET @IncDoc=@GS/@M5*30
                      SET @TDoc=@TDoc+@IncDoc
                      GOTO Branch_One
               END
               
        IF @GS>@M6
               BEGIN
                      SET @TDoc=@TDoc+30
                      set @GS=@GS-@M6
                 end
         else
 
               BEGIN
                      SET @IncDoc=@GS/@M6*30
 
                      SET @TDoc=@TDoc+@IncDoc
                      GOTO Branch_One
               END             
        IF @GS>@M7
 
               BEGIN
 
                      SET @TDoc=@TDoc+30
 
                      set @GS=@GS-@M7
              end
         else
 
               BEGIN
 
                      SET @IncDoc=@GS/@M7*30
 
                      SET @TDoc=@TDoc+@IncDoc
                      GOTO Branch_One
               END
              
        IF @GS>@M8
 
               BEGIN
 
                      SET @TDoc=@TDoc+30
 
                      set @GS=@GS-@M8
             end
         else
 
               BEGIN
 
                      SET @IncDoc=@GS/@M8*30
 
                      SET @TDoc=@TDoc+@IncDoc
                      GOTO Branch_One
               END
             
        IF @GS>@M9
 
               BEGIN
 
                      SET @TDoc=@TDoc+30
 
                      set @GS=@GS-@M9
               end
         else
 
               BEGIN
 
                      SET @IncDoc=@GS/@M9*30
 
                      SET @TDoc=@TDoc+@IncDoc
                      GOTO Branch_One
               END
 
        IF  @GS>@M10
 
               BEGIN
                      SET @TDoc=@TDoc+30
                      set @GS=@GS-@M10
 
                 end
         else
               BEGIN
                      SET @IncDoc=@GS/@M10*30
                      SET @TDoc=@TDoc+@IncDoc
                      GOTO Branch_One
               END
              
        IF  @GS>@M11
 
               BEGIN
 
                      SET @TDoc=@TDoc+30
 
                      set @GS=@GS-@M11
 
                 end
         else
 
               BEGIN
 
                      SET @IncDoc=@GS/@M11*30
 
                      SET @TDoc=@TDoc+@IncDoc
                      GOTO Branch_One
               END
 
 
 
        IF  @GS>@M12
 
            BEGIN
 
               SET @TDoc=@TDoc+30
 
               set @GS=@GS-@M12
 
             end
          else
 
               BEGIN
                     
                     PRINT   @GS
                     --PRINT @M12
                      SET @IncDoc=@GS/@M12*30
 
                      SET @TDoc=@TDoc+@IncDoc
                      GOTO Branch_One
               END
  
  Branch_One:
 
                      UPDATE   #STOCK_DETAIL SET IncDoc=@IncDoc,TDoc=@TDoc WHERE RowID = @RowCount
 
               SET @GS=0
 
        SET @RowCount = @RowCount + 1
 
 
 END
more ▼

asked Feb 02 at 07:46 PM in Default

avatar image

GayatriPanigrahi
1 1

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2207
x447
x69

asked: Feb 02 at 07:46 PM

Seen: 49 times

Last Updated: Feb 08 at 02:48 PM

Copyright 2018 Redgate Software. Privacy Policy