October 21, 2010 at 10:18 am
I need a script as efficient as possible, to assign a unique sequence for each customer asset.
My script it's ok! when I used with a few rows, but it's dead with a lot of rows (about 600,000)
I.e:
Company_CodeCustomer_CodeAsset_IDAsset_ByCustomerROWID
01002-00000003111
01002-00000003222
01002-00000003333
01002-00000003444
01002-00000003555
01002-00000004616
01002-00000004727
01002-00000004838
01002-00000004949
01002-0000000418510
01002-0000000419611
01002-0000000420712
--- ================================================================================
--- bdowns
--- 2010 OCTOBERA
--- ================================================================================
CREATE TABLE dbo.Customer_Assets(
Company_Code varchar(2) NOT NULL,
Customer_Code varchar(20) NOT NULL, -- this is my original code
Asset_ID bigint NOT NULL, -- this is a unique code for each goods
Asset_ByCustomer int NULL, -- I need fill this column from 1 to N restarting for each customer.
ROWID INT IDENTITY -- a column to control my proceses
CONSTRAINT PK_Customer_Assets PRIMARY KEY CLUSTERED
(
Company_Code ASC,
Customer_Code ASC,
Asset_ID ASC
)
)
go
--- Customer_Assets have 600,000 rows and this process could be slow.
-- ================================================
-- BEGIN PROCESS
-- ================================================
DECLARE @TOTROWS INT
DECLARE @i INT
DECLARE @CUSTOMER VARCHAR(20)
DECLARE @PREV_CUSTOMER VARCHAR(20)
DECLARE @mySequence INT
SELECT @TOTROWS = COUNT(0) FROM Customer_Assets
--- in This table --- we will assign the running of client assets ---
create table #asigna
(
ROWID int,
CUSTOMER VARCHAR(20),
SEQUENCE INT
)
SET NOCOUNT ON
SELECT @i = 1;
WHILE @i <= @TOTROWS
BEGIN
SELECT @CUSTOMER = Customer_Code FROM Customer_Assets WHERE ROWID = @i
IF @i = 1
--- ======================================================================================
--- IS THI FIRST ROWS SEQUENCE WILL ALWAYS BE ONE.
--- ======================================================================================
begin
SET @mySequence = 1
end
ELSE
--- ======================================================================================
--- IS THE SECOND row OR ....N, COMPARE PREV vs ACTUAL ROW BY CUSTOMER
--- ======================================================================================
begin
if @CUSTOMER = @PREV_CUSTOMER
SET @mySequence = @mySequence + 1
else
SET @mySequence = 1
end
SET @PREV_CUSTOMER = @CUSTOMER;
insert into #asigna (ROWID, CUSTOMER, SEQUENCE) values (@i,@CUSTOMER,@mySequence );
SELECT @i = @i + 1
END
UPDATE a
SET Asset_ByCustomer = b.SEQUENCE
FROM Customer_Assets a
INNER JOIN #asigna b
ON a.ROWID = b.ROWID;
go
--- END ---
October 21, 2010 at 10:30 am
Something like this is probably what you're looking for. My bet would be that with 600,000 rows a temp table will end up being more efficient than a table variable though.
declare @temp table (rowid int, value int)
insert into @temp
select rowid, row_number() over (partition by customer_code order by rowid) [Asset_ByCustomer] from customer_assets
update customer_assets set asset_bycustomer = value
from customer_assets
inner join @temp t on customer_assets.rowid = t.rowid
select * from customer_assets
October 21, 2010 at 11:31 am
hEY, Thanks so much!!!! Everything it's ok.
THis Is New for me 'cause I was working hard in SQLServer2000.
I was looking for a solucition like you suggest for sqlserver2000 enviroment because my customer just have ss2k.
I think my customer need to migrate his data to SS2k5.
Saludos
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply