January 15, 2010 at 5:21 am
experts,
i need help on the following.
there are two tables that do not have an relationship. one table holds customer data and an open field named code. the code field needs to be filled from another table that holds these unique codes.
after filling the code in the customer data table the other table is updated with the customer id and therefore cannot be used again.
for this purpose I made an batch with use of an cursor and however this works it takes ages to complete for only 15000 records.
there should be an better way to accomplish this with normal set bases rules but i'm stuck. there is no relationship there and any actions are very time consuming. I need to speed up things.
what can i do best to pull an code from one table and update the other and then marking the code as used.
thanks.
January 15, 2010 at 6:03 am
Can you post some sample data with expected result?
Failing to plan is Planning to fail
January 15, 2010 at 6:13 am
here is some sample data
tblcodes:
codeid - code - adr_id
1 - CB4560460D5946C49D9B286048CC11A7 0
2 - 5953BE4521634863B9D03137B7DBA3C4 0
3 - 97D40E6B7AF545AE94ED13DF1AF1C27F 0
tblcustomer
adr_id code
2568922 0
2560812 0
as you can see the adr_id in the first table is 0 and code in the second table is 0 (just for example purposes)
now i want to update tblcustomer and add an unique code from tblcodes and use the adr_id to update tblcodes so that the code cannot be used again.
this with the best performance option. i now have an cursor taking over 20 min for 15000 records
January 15, 2010 at 6:14 am
alex the logic is in your cursor; if you are doing something to one row, you can do the same thing to multiple rows; somewhere int here you've got what needs to be done;
i'm just guessing while we wait for more info, but it sounds like you are updating one table , then with the information from the update, updating another table.
i'm thinking by taking advantage of the OUTPUT clause, you'll be able to do it with a couple of set based statements. get us the CREATE TABLE statemetns, and some example data; also the cursor would show us what you were doing.
alex.planting (1/15/2010)
experts,i need help on the following.
there are two tables that do not have an relationship. one table holds customer data and an open field named code. the code field needs to be filled from another table that holds these unique codes.
after filling the code in the customer data table the other table is updated with the customer id and therefore cannot be used again.
for this purpose I made an batch with use of an cursor and however this works it takes ages to complete for only 15000 records.
there should be an better way to accomplish this with normal set bases rules but i'm stuck. there is no relationship there and any actions are very time consuming. I need to speed up things.
what can i do best to pull an code from one table and update the other and then marking the code as used.
thanks.
Lowell
January 15, 2010 at 6:29 am
Not sure what you need, the tables are existing tables.
the cursor i use is here
DECLARE CRSCODES CURSOR FOR
SELECT NR, POSINFO FROM SELECTIES.DBO.NUT_EXP WHERE CODE=''
OPEN CRSCODES
declare @NR INT
,@AANTALRECS INT
,@POSINFO VARCHAR(2)
,@CODE VARCHAR(32)
,@URL VARCHAR(120)
,@CODEID INT
SELECT @AANTALRECS = COUNT(*)+ 1 FROM SELECTIES.DBO.NUT_EXP
FETCH NEXT FROM CRSCODES INTO @NR, @POSINFO
While @AANTALRECS > 1
BEGIN
SET @AANTALRECS = @AANTALRECS - 1
SET ROWCOUNT @AANTALRECS
SELECT TOP 1 @CODE=CODE, @URL=CODE_URL, @CODEID=CODE_ID FROM SELECTIES.DBO.TBLCODESSYNOVATE WHERE CODE_CHECK=0
UPDATE SELECTIES.DBO.NUT_EXP SET CODE=@CODE, URL=@URL WHERE NR=@NR
UPDATE SELECTIES.DBO.TBLCODESSYNOVATE SET CODE_CHECK=1, CODE_ADR_ID=@NR, CODE_DATUM=GETDATE(),CODE_KINDTYPE=@POSINFO WHERE CODE_ID=@CODEID
SET ROWCOUNT 1
FETCH NEXT FROM CRSCODES INTO @NR, @POSINFO
END
CLOSE CRSCODES
DEALLOCATE CRSCODES
do you need the layout of the tables ?
column_namedata_typecharacter_maximum_length
CODEnvarchar100
NRnvarchar10
INITnvarchar10
TUSSENnvarchar15
NAAMnvarchar50
STRAATnvarchar50
HUISNRnvarchar5
TOEVnvarchar7
PCnvarchar6
WOONPLAATSnvarchar50
TELEFOONnvarchar11
EMAILnvarchar150
KINDERENvarchar50
TYPE_MOEDRvarchar50
LEEFTIJDdecimalNULL
SEXEnvarchar1
ROEPNAAMnvarchar50
GEBDATKdatetimeNULL
GEBDATMdatetimeNULL
column_namedata_typecharacter_maximum_length
code_idintNULL
codevarchar32
code_urlvarchar120
code_seltypevarchar20
code_adr_idintNULL
code_checkintNULL
code_datumdatetimeNULL
CODE_KINDTYPEvarchar2
January 15, 2010 at 6:38 am
this works with the example data you posted; dunno about the data types, but the code works:
==edit i built this based on the first psuedocode, you posted again prior to my answer;
if you posted the data the same way i did, with cREATE TABLE and INSERT INTO, you'd get a better answer. the second post does not have an adr_id in the customers table!, your first post impled it existed(well not impled, explicitly said it was there)
drop table tblcodes
drop table tblcustomer
CREATE TABLE tblcodes(
codeid int,
code varchar(40),
adr_id int )
insert into tblcodes
SELECT 1,'CB4560460D5946C49D9B286048CC11A7',0 UNION ALL
SELECT 2,'5953BE4521634863B9D03137B7DBA3C4', 0 UNION ALL
SELECT 3,'97D40E6B7AF545AE94ED13DF1AF1C27F',0
CREATE TABLE tblcustomer(
adr_id int,
code varchar(40))
INSERT INTO tblcustomer
SELECT 2568922,'0' UNION ALL
SELECT 2560812,'0'
--a temp table to capture teh results of the update
DECLARE @MyResults TABLE(
adr_id int,
code varchar(40))
--update the first table with a join of the available rows from the two tables;
--joining on row number so that we do not need to care about the codeid, just what has not been assigned.
UPDATE myAlias
SET myAlias.code = Y.code
OUTPUT
INSERTED.adr_id,
INSERTED.code
INTO @MyResults
FROM tblcustomer myAlias
INNER JOIN
(SELECT
row_number() over (order by adr_id) AS RW,
tblcustomer.adr_id,
tblcustomer.code
from tblcustomer
where code = '0'
) X ON MyAlias.adr_id = X.adr_id
LEFT OUTER JOIN
(SELECT
row_number() over (order by adr_id) AS RW,
tblcodes.codeid,
tblcodes.code
from tblcodes
where adr_id = 0
) Y ON X.RW = Y.RW
--now that customers is updated, mark codes as being used.
UPDATE tblcodes
SET tblcodes.adr_id=SubAlias.adr_id
FROM @MyResults SubAlias
WHERE tblcodes.code=SubAlias.code
select * from tblcodes
select * from tblcustomer
Lowell
January 15, 2010 at 6:43 am
It would be something like this
declare @tblcodes table(codeid int, code varchar(100), adr_id1 int)
insert into @tblcodes
select 1,' CB4560460D5946C49D9B286048CC11A7', 0 union all
select 2,' 5953BE4521634863B9D03137B7DBA3C4', 0 union all
select 3,' 97D40E6B7AF545AE94ED13DF1AF1C27F', 0
declare @tblcustomer table(adr_id int, code varchar(100))
insert into @tblcustomer
select 2568922, 0 union all
select 2560812 ,0
select * from @tblcodes
select * from @tblcustomer
update cus
set cus.code=code.code
from @tblcustomer as cus inner join (select top 1 percent * from @tblcodes where adr_id1=0 order by newid()) as code on 1=1
update code
set code.adr_id1=1
from @tblcustomer as cus inner join @tblcodes as code on cus.code=code.code
where code.adr_id1=0
select * from @tblcodes
select * from @tblcustomer
Failing to plan is Planning to fail
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply