Tough Consolidation of Rows
-
Thinking that you are going through the whole table you may need to make sure that you have enough space on tempdb also!!
* Noel -
Stopped the query at 16 mins, I have the tempDB to automatically grow.
What about data types, the numset1 and 2 are now floats
-
Can you change them to int ?
the fact that you have tempdb to autogrow just means that you will not run out of space but you may not want it to expand in the middle of your query either !!!
can you post the execution plan that you are getting ?
* Noel -
This is the cursor version that perform only one pass to the table:
create table YourTable (NumSet1 int, NumSet2 int, SetVal char(1) )
go
insert into YourTable (NumSet1, NumSet2, SetVal) values ( 201645112, 201645311, 'A')
insert into YourTable (NumSet1, NumSet2, SetVal) values ( 201645312, 201645567, 'A')
insert into YourTable (NumSet1, NumSet2, SetVal) values ( 201645568, 201645823, 'A')
insert into YourTable (NumSet1, NumSet2, SetVal) values ( 201645824, 201646070, 'B')
insert into YourTable (NumSet1, NumSet2, SetVal) values ( 201646071, 201646075, 'A')
insert into YourTable (NumSet1, NumSet2, SetVal) values ( 201646076, 201646079, 'B')
insert into YourTable (NumSet1, NumSet2, SetVal) values ( 201646080, 201646111, 'A')
insert into YourTable (NumSet1, NumSet2, SetVal) values ( 201646112, 201646143, 'A')
insert into YourTable (NumSet1, NumSet2, SetVal) values ( 201646144, 201646190, 'W')
insert into YourTable (NumSet1, NumSet2, SetVal) values ( 201646191, 201646200, 'A')
----------------------------------------------------------------------
create table #Results (NumSet1 int, NumSet2 int, SetVal char(1))
declare mycur cursor fast_forward for select Numset1, Numset2 ,SetVal from YourTable order by setval, NumSet1, NumSet2
OPEN mycur
declare @NumSet1a int, @NumSet1b int, @Numset2a int, @NumSet2b int, @LastVala char(1), @LastValb char(1)
select @NumSet1a = 0, @NumSet1b = 0, @Numset2a =0, @NumSet2b = 0, @LastVala ='', @LastValb = '' --init variables
FETCH NEXT FROM mycur INTO @NumSet1a, @Numset2a, @LastVala
select @numSet1b = @Numset1a, @LastValb = @LastVala
WHILE @@FETCH_STATUS = 0
BEGIN
if @NumSet2b > 0 and (@numset1a - 1 <> @Numset2b or @LastVala <> @LastValb )
begin
insert into #Results (NumSet1, NumSet2, SetVal) Values ( @NumSet1b, @NumSet2b, @LastValb )
select @numSet1b = @Numset1a, @LastValb = @LastVala
end
set @NumSet2b = @NumSet2a
FETCH NEXT FROM mycur INTO @NumSet1a, @Numset2a, @LastVala
END
-- account for the last
if @NumSet2b > 0 and (@numset1a - 1 <> @Numset2b or @LastVala <> @LastValb )
begin
insert into #Results (NumSet1, NumSet2, SetVal) Values ( @NumSet1b, @NumSet2b, @LastValb )
end
CLOSE mycur
DEALLOCATE mycur
Select * from #Results order by SetVal , NumSet1, NumSet2
drop table #Results
----------------------------------------------------------------------
Results:
NumSet1 NumSet2 SetVal
201645112 201645823 A
201646071 201646075 A
201646080 201646143 A
201646191 201646200 A
201645824 201646070 B
201646076 201646079 B
201646144 201646190 W
Now, I still believe that you have other problems. This may be required only for very very large tables or very very poor hardware
Good Weekend!!
* Noel
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply