Link to home
Start Free TrialLog in
Avatar of 25112
25112

asked on

Huge CTE - and log usage in simple mode.,

the below query still goes over the max of log space on disk..

this is a one time operation..

is there a way to break the above code to do in chunks so that the log does not go over (even though it is in simple mode)

there are 50M records in the main table.. so even if we can break into doing by 5M at a time, that will be good... is there a logic possible to break it into batches?
Avatar of 25112
25112

ASKER

the query logic is:


with replaceOrder as (
select
      KeyColumn,column1, column2,column3,column4,column5,
      key_id=row_number() over (order by KeyColumn),
      column1_from=row_number() over (order by newid()),
      column2_from=row_number() over (order by newid()),
      column3_from=row_number() over (order by newid()),
      column4_from=row_number() over (order by newid()),
      column5_from=row_number() over (order by newid())  
from MainTable
)
update r SET
column2=a.column2,
column1 = b.column1,
column3 = LEFT(c.column3,1)+substring(c.column3,charindex (',',c.column3)+2,1),
column4=d.column4,
column5 = e.column5
from replaceOrder r
inner join replaceOrder a on a.column1_from=r.key_id
inner join replaceOrder b on b.column2_from=r.key_id
inner join replaceOrder c on c.column3_from=r.key_id
inner join replaceOrder d on d.column4_from=r.key_id
inner join replaceOrder e on e.column5_from=r.key_id
GO
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 25112

ASKER

Scott, please see below for data type and preferred out put (basically jumbles the data beyond recognition.. below sample has only 6 rows.. but in real table, we will have 50M+ records).

---------------------


CREATE TABLE [dbo].[MainTable](
      [KeyColumn] [numeric](19,0) NULL,
      [column1] [varchar](75) NULL,
      [column2] [varchar](75) NULL,
      [column3] [varchar](85) NULL,
      [column4] [varchar](32) NULL,
      [column5] [datetime] NULL
) ON [PRIMARY]


insert into  [MainTable]
select 1,'abc1','bcd1','cde1','def1',GETDATE()-1 union
select 2,'abc2','bcd2','cde2','def2',GETDATE()-2 union
select 3,'abc3','bcd3','cde3','def3',GETDATE()-3 union
select 4,'abc4','bcd4','cde4','def4',GETDATE()-4 union
select 5,'abc5','bcd5','cde5','def5',GETDATE()-5 union
select 6,'abc6','bcd6','cde6','def6',GETDATE()-6
go

select * from MainTable;
with replaceOrder as (
select
      KeyColumn,column1, column2,column3,column4,column5,
      key_id=row_number() over (order by KeyColumn),
      column1_from=row_number() over (order by newid()),
      column2_from=row_number() over (order by newid()),
      column3_from=row_number() over (order by newid()),
      column4_from=row_number() over (order by newid()),
      column5_from=row_number() over (order by newid())  
from MainTable
)
update r SET
column2=a.column2,
column1 = b.column1,
column3 = c.column3,
column4=d.column4,
column5 = e.column5
from replaceOrder r
inner join replaceOrder a on a.column1_from=r.key_id
inner join replaceOrder b on b.column2_from=r.key_id
inner join replaceOrder c on c.column3_from=r.key_id
inner join replaceOrder d on d.column4_from=r.key_id
inner join replaceOrder e on e.column5_from=r.key_id
GO
select * from MainTable;
drop table MainTable
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 25112

ASKER

scott, using temp table, i filled up tempdb and it quit after 30 minutes.

sharath, if i use CTE, log fills up and breaks.

any other way to have this contained?

if i put in bulk logged, will it help?