Hi All,
Can anyone offer me their expertis and suggestions/examples for how I can replace this cursor with a query that runs in sensible time frame against millions of records.
Many thanks in advance.
Cursor:
DECLARE @ACCOUNTID varchar (20)
DECLARE @CONTACTID varchar(20)
DECLARE @SOURCEACCID varchar(24)
DECLARE @DESTACCID varchar (24)
DECLARE @BRANCHID varchar (20)
DECLARE @TYPE varchar (12)
DECLARE @PREFIX01 varchar (12)
DECLARE @PREFIX02 varchar (12)
DECLARE @PREFIX03 varchar (12)
DECLARE @PREFIX04 varchar (12)
DECLARE @PREFIX05 varchar (50)
DECLARE @FNAME01 varchar (64)
DECLARE @FNAME02 varchar (64)
DECLARE @FNAME03 varchar (64)
DECLARE @FNAME04 varchar (64)
DECLARE @FNAME05 varchar (64)
DECLARE @FNAME06 varchar (8)
DECLARE @SNAME01 varchar (64)
DECLARE @SNAME02 varchar (64)
DECLARE @SNAME03 varchar (64)
DECLARE @SNAME04 varchar (64)
DECLARE @SNAME05 varchar (64)
/**********************************/
/*** THIS IS LEVEL 1 ACC MATCH ***/
/**********************************/
DECLARE c1 CURSOR READ_ONLY FOR
SELECT
CONSOURCE1.MASTERSOURCEACCID_LEV1 as ACCOUNTID, CONSOURCE1.RECORDID as SOURCEID,CONSOURCE1.SOURCECONID as SOURCEBRANCHID, 'Prospect' as SOURCERATING,
PREFIX01,PREFIX02,PREFIX03,PREFIX04,PREFIX05,
FNAME01,FNAME02,FNAME03,FNAME04,FNAME05,FNAME06,
SNAME01,SNAME02,SNAME03,SNAME04,SNAME05
from
con CONSOURCE1
inner join C_MATCH C_MATCH1 on CONSOURCE1.recordid = C_MATCH1.CONTACTID
inner join ADD_MATCH ADD_MATCH1 on CONSOURCE1.RECORDID = ADD_MATCH1.BRANCHID and 'CONTACT' = ADD_MATCH1.TYPE
OPEN c1
FETCH NEXT FROM c1 into
@ACCOUNTID,
@CONTACTID, @BRANCHID, @TYPE,
@PREFIX01, @PREFIX02, @PREFIX03, @PREFIX04, @PREFIX05,
@FNAME01, @FNAME02, @FNAME03, @FNAME04, @FNAME05, @FNAME06,
@SNAME01, @SNAME02, @SNAME03, @SNAME04, @SNAME05
WHILE @@FETCH_STATUS = 0
BEGIN
/*** PUT CODE HERE TO MATCH UP ***/
insert into C_SCORE (SOURCEID,SOURCEBRANCHID,SOURCERATING,TARGETID,TARGETACCID,MASTERSOURCEACCID_LEV)
select
@CONTACTID,
@BRANCHID,
@TYPE,
CONTACT2.RECORDID,
CONTACT2.MASTERSOURCEACCID_LEV1,
1
from
con CONTACT2
inner join C_MATCH C_MATCH2 on CONTACT2.recordid = C_MATCH2.CONTACTID
where
/*** THERE HAS TO BE AT LEAST AN ACCOUNT NAME MATCH MATCH ***/
(
CONTACT2.MASTERSOURCEACCID_LEV1 = @ACCOUNTID
and
CONTACT2.RECORDID <> @CONTACTID
and
/*** AND THERE HAS TO BE AT LEAST A FIRST OR LAST NAME MATCH ***/
(
IsNull(@FNAME01,'') <> '' and @FNAME01 = C_MATCH2.FNAME01)
)
FETCH NEXT FROM c1 into
@ACCOUNTID,
@CONTACTID, @BRANCHID, @TYPE,
@PREFIX01, @PREFIX02, @PREFIX03, @PREFIX04, @PREFIX05,
@FNAME01, @FNAME02, @FNAME03, @FNAME04, @FNAME05, @FNAME06,
@SNAME01, @SNAME02, @SNAME03, @SNAME04, @SNAME05
END
CLOSE c1
DEALLOCATE c1
