June 21, 2010 at 8:23 am
Hi,
I have a temp table (#macs) that contains a list of 188,000 macAddresses. These Macs will be compare with another table called ServiceGroupMapping. Basically both tables contain the same information, but what #macs has is an extra column (IDRow) to compare each row.
I need to group the first 300 hundred records in group 1, then the next 300 records in group 3 and so on...
this is the code that Im using, and it works for what I need, but it took 5 hours to complete and I need to do another 2 different kind of groups.
Can anyone tell me what is wrong?
Regards,
Declare@IdRow int
, @IdRowMax int
, @ServiceGroup int
, @MaxRowsTable int
set @IdRow = 1
set @IdRowMax = 300
set @ServiceGroup = 1
select @MaxRowsTable = Max(IdRow) from #Macs
while @IdRow <= @IdRowMax
Begin
UpdateServiceGroupMapping
setSG300 = @ServiceGroup
From#macs m with(nolock)
JoinServiceGroupMapping mm with(nolock) on m.MacAddress = mm.MacAddress
and m.SGComcast = mm.SGComcast
wherem.IdRow = @IDRow
set @IDRow = @Idrow + 1
If @IdRowMax < @IdRow and @IdRowMax < @MaxRowsTable
Begin
Set @IdRowMax = @IdRowMax + 300
set @ServiceGroup = @ServiceGroup + 1
End
End
drop table #macs
June 21, 2010 at 8:41 am
Please post table definitions, index definitions and execution plan (if possible) as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 21, 2010 at 8:58 am
this is my table definition:
CREATE TABLE [dbo].[ServiceGroupMappingII](
[IdRow] [bigint] NULL,
[MacAddress] [varchar](32) NULL,
[SG300] [int] NULL,
[SG500] [int] NULL,
[SG800] [int] NULL,
[SG1000] [int] NULL
) ON [PRIMARY]
I changed that code for this to scan only one table... I dont have any index on that table
Declare@IdRow int
, @IdRowMax int
, @ServiceGroup int
, @MaxRowsTable int
set @IdRow = 1
set @IdRowMax = 500
set @ServiceGroup = 1
select @MaxRowsTable = Max(IdRow) from ServiceGroupMappingII
while @IdRow <= @IdRowMax
Begin
UpdateServiceGroupMappingII
setSG500 = @ServiceGroup
FromServiceGroupMappingII
whereIdRow = @IdRow
set @IDRow = @Idrow + 1
If @IdRowMax < @IdRow and @IdRowMax < @MaxRowsTable
Begin
Set @IdRowMax = @IdRowMax + 500
set @ServiceGroup = @ServiceGroup + 1
End
End
June 21, 2010 at 9:12 am
Something like this ?
with cteRows
as
(
Select Row_number() over (order by IdRow) as Rown,
SG300,
SG500,
SG800,
SG1000
from ServiceGroupMappingII
)
update cteRows
set SG300 = Rown/300,
SG500 = Rown/500,
SG800 = Rown/800,
SG1000 = Rown/1000
June 21, 2010 at 9:19 am
I went back to basic...
i needes to ad the PK on the row that Im using as a where (IdRow)
it took 10minutes to process..
Thank you
sometimes arent that complicated, we just need to go back to basic paths.. :w00t:
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply