Sql cursor problem...
-
Every morning I have a temporary table that gets updated with Customer Sales data (year to date, month to day, last year to date, etc), from another database. One problem I have is that I get every single customer from that database, even if a customer's MTD, YTD,LYTD figures have no changed, resulting in getting 625,000 records. Another is that I need update Existing customer with the data in my temp table. I tried using a while loop and looping through my table to update records. I also tried select the records from the temp table and comparing the values to what is in the existing, the placing those records in a table variable and using a cursor on the temp table. Both of these take entirely too much time. Here is the T-sql for reference... Declare @ARbal money, @CustTreeNodeID uniqueidentifier, @CustomerNumber varchar(50), @CustomerUpdateID int, @LYSales money, @LYTDSales money, @YTDgm money, @YTDSales money, @MTDSales money, @LMTDSales money Declare @t table ( CustomerUpdateID int, CustomerNumber varchar(50), YTDSales money, LYSales money, ARBal money, LYTDSales money, MTDSales money, LMTDSales money ) insert into @t(CustomerUpdateID, CustomerNumber,YTDSales, LYSales,ARBal, LYTDsales,MTDSales,LMTDSales) select CustomerUpdateID, CustomerNumber, YTDSales, LYSales, ARBal, LYTDSales, MTDSales, LMTDSales from _CustomerUpdate u where exists (select 1 from CustTreeNode c join CustTreeNodeaccountAssoc ca on ca.CustTreeNodeID = c.CustTreeNodeID and (u.LYTDSales != ca.LYTD or u.YTDSales != ca.YTD or u.MTDSales!= ca.MTD or u.LMTDSales !=ca.LMTD )) declare mycursor cursor forward_only for select CustomerUpdateID from @t open mycursor while (1=1) begin fetch next from mycursor into @CustomerUpdateID if @@Fetch_status <>0 break; set @CustomerNumber = (Select CustomerNumber from _CustomerUpdate where CustomerUpdateID = @CustomerUpdateID) if isnull(@CustomerNumber,'')<>'' begin set @CustTreeNodeID =(select CustTreeNodeID from CustTreeNode where CustomerNumber = @CustomerNumber) end select top 1 @YTDSales = YTDSales, @LYSales = LYSales, @ARbal = ARbal, @LYTDSales = LYTDSAles, @MTDSales = MTDSales, @LMTDSales = LMTDSales from @t where CustomerUpdateID = @CustomerUpdateID update CustTreeNodeAccountAssoc set YTD = @YTDSales, LYTD= @LYTDSales,Balance = @ARbal where CustTreeNodeID = @CustTreeNodeID end close mycursor deallocate mycursor
-
Every morning I have a temporary table that gets updated with Customer Sales data (year to date, month to day, last year to date, etc), from another database. One problem I have is that I get every single customer from that database, even if a customer's MTD, YTD,LYTD figures have no changed, resulting in getting 625,000 records. Another is that I need update Existing customer with the data in my temp table. I tried using a while loop and looping through my table to update records. I also tried select the records from the temp table and comparing the values to what is in the existing, the placing those records in a table variable and using a cursor on the temp table. Both of these take entirely too much time. Here is the T-sql for reference... Declare @ARbal money, @CustTreeNodeID uniqueidentifier, @CustomerNumber varchar(50), @CustomerUpdateID int, @LYSales money, @LYTDSales money, @YTDgm money, @YTDSales money, @MTDSales money, @LMTDSales money Declare @t table ( CustomerUpdateID int, CustomerNumber varchar(50), YTDSales money, LYSales money, ARBal money, LYTDSales money, MTDSales money, LMTDSales money ) insert into @t(CustomerUpdateID, CustomerNumber,YTDSales, LYSales,ARBal, LYTDsales,MTDSales,LMTDSales) select CustomerUpdateID, CustomerNumber, YTDSales, LYSales, ARBal, LYTDSales, MTDSales, LMTDSales from _CustomerUpdate u where exists (select 1 from CustTreeNode c join CustTreeNodeaccountAssoc ca on ca.CustTreeNodeID = c.CustTreeNodeID and (u.LYTDSales != ca.LYTD or u.YTDSales != ca.YTD or u.MTDSales!= ca.MTD or u.LMTDSales !=ca.LMTD )) declare mycursor cursor forward_only for select CustomerUpdateID from @t open mycursor while (1=1) begin fetch next from mycursor into @CustomerUpdateID if @@Fetch_status <>0 break; set @CustomerNumber = (Select CustomerNumber from _CustomerUpdate where CustomerUpdateID = @CustomerUpdateID) if isnull(@CustomerNumber,'')<>'' begin set @CustTreeNodeID =(select CustTreeNodeID from CustTreeNode where CustomerNumber = @CustomerNumber) end select top 1 @YTDSales = YTDSales, @LYSales = LYSales, @ARbal = ARbal, @LYTDSales = LYTDSAles, @MTDSales = MTDSales, @LMTDSales = LMTDSales from @t where CustomerUpdateID = @CustomerUpdateID update CustTreeNodeAccountAssoc set YTD = @YTDSales, LYTD= @LYTDSales,Balance = @ARbal where CustTreeNodeID = @CustTreeNodeID end close mycursor deallocate mycursor
-
Like I said I tried using a loop using a status field on the temp table: something like this while(select top 1 from _customerUpdate where [Status] ='INC') = 'INC' begin Begin Try perform customer update logic here... End try Begin Catch End Catch Update _CustomerUpdate set Status ='CMP' where CustomerUpdateID = @CustomerUpdateID. End Either way both of these ran incredibily slow...
-
Every morning I have a temporary table that gets updated with Customer Sales data (year to date, month to day, last year to date, etc), from another database. One problem I have is that I get every single customer from that database, even if a customer's MTD, YTD,LYTD figures have no changed, resulting in getting 625,000 records. Another is that I need update Existing customer with the data in my temp table. I tried using a while loop and looping through my table to update records. I also tried select the records from the temp table and comparing the values to what is in the existing, the placing those records in a table variable and using a cursor on the temp table. Both of these take entirely too much time. Here is the T-sql for reference... Declare @ARbal money, @CustTreeNodeID uniqueidentifier, @CustomerNumber varchar(50), @CustomerUpdateID int, @LYSales money, @LYTDSales money, @YTDgm money, @YTDSales money, @MTDSales money, @LMTDSales money Declare @t table ( CustomerUpdateID int, CustomerNumber varchar(50), YTDSales money, LYSales money, ARBal money, LYTDSales money, MTDSales money, LMTDSales money ) insert into @t(CustomerUpdateID, CustomerNumber,YTDSales, LYSales,ARBal, LYTDsales,MTDSales,LMTDSales) select CustomerUpdateID, CustomerNumber, YTDSales, LYSales, ARBal, LYTDSales, MTDSales, LMTDSales from _CustomerUpdate u where exists (select 1 from CustTreeNode c join CustTreeNodeaccountAssoc ca on ca.CustTreeNodeID = c.CustTreeNodeID and (u.LYTDSales != ca.LYTD or u.YTDSales != ca.YTD or u.MTDSales!= ca.MTD or u.LMTDSales !=ca.LMTD )) declare mycursor cursor forward_only for select CustomerUpdateID from @t open mycursor while (1=1) begin fetch next from mycursor into @CustomerUpdateID if @@Fetch_status <>0 break; set @CustomerNumber = (Select CustomerNumber from _CustomerUpdate where CustomerUpdateID = @CustomerUpdateID) if isnull(@CustomerNumber,'')<>'' begin set @CustTreeNodeID =(select CustTreeNodeID from CustTreeNode where CustomerNumber = @CustomerNumber) end select top 1 @YTDSales = YTDSales, @LYSales = LYSales, @ARbal = ARbal, @LYTDSales = LYTDSAles, @MTDSales = MTDSales, @LMTDSales = LMTDSales from @t where CustomerUpdateID = @CustomerUpdateID update CustTreeNodeAccountAssoc set YTD = @YTDSales, LYTD= @LYTDSales,Balance = @ARbal where CustTreeNodeID = @CustTreeNodeID end close mycursor deallocate mycursor
I don't know your schema so consider this as psudocode, but this will be faster due to the fact that it isn't using a cursor (as suggested previously). Then, if possible, I would add datetime columns which you can use to compare the date your _CustomerUpdate table was changed with the date your CustTreeNodeAccountAssoc table was updated. This will prevent the need to retrieve all 600K+ records, that will also considerably speed things up and it will scale better as the number of records increases.
Declare @ARbal money, @CustTreeNodeID uniqueidentifier, @CustomerNumber varchar(50), @CustomerUpdateID int, @LYSales money, @LYTDSales money, @YTDgm money, @YTDSales money, @MTDSales money, @LMTDSales money Declare @t table ( CustomerUpdateID int, CustomerNumber varchar(50), YTDSales money, LYSales money, ARBal money, LYTDSales money, MTDSales money, LMTDSales money ) insert into @t(CustomerUpdateID, CustomerNumber,YTDSales, LYSales,ARBal, LYTDsales,MTDSales,LMTDSales) select CustomerUpdateID, CustomerNumber, YTDSales, LYSales, ARBal, LYTDSales, MTDSales, LMTDSales from _CustomerUpdate u where exists (select 1 from CustTreeNode c join CustTreeNodeaccountAssoc ca on ca.CustTreeNodeID = c.CustTreeNodeID and (u.LYTDSales != ca.LYTD or u.YTDSales != ca.YTD or u.MTDSales!= ca.MTD or u.LMTDSales !=ca.LMTD )) -- REPLACE YOUR CURSOR WITH THIS CODE BELOW (or something similar that better fits your logic) UPDATE A YTD = T.YTDSales, LYTD = T.LYSales, Balance = T.ARBal FROM @t T INNER JOIN CustTreeNode N ON N.CustomerNumber = T.CustomerNumber INNER JOIN CustTreeNodeAccountAssoc A ON A.CustTreeNodeID = N.CustTreeNodeIDMark's blog: developMENTALmadness.blogspot.com