The Other SLOWWW Cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

The Other SLOWWW Cursor

Author

Topic

MontyMagic
Starting Member

12 Posts

Posted - 2011-09-20 : 03:43:16

Hi,

Thank you for the help, amazinly the last process was reduced from 8.5 hours to 3.5 minutes by ditching the cursor. As mentioned in my previous post here is the 2nd extremely slow cursor. I am currently trying to apply my new found logic to see if I can speed it up, but I am also posting here to see what sugesstions you have.

Thank you in advance for your thoughts, help, suggestions.

Monty

Stored Proc Code below:

/*** UNIVERSE MATCHING ***/
DECLARE @ACCOUNTID varchar (20)
DECLARE @CONTACTID varchar(20)
DECLARE @BRANCHID varchar (20)
DECLARE @TYPE varchar (12)
DECLARE @ACCOUNT01 varchar (128)
DECLARE @ACCOUNT02 varchar (128)
DECLARE @ACCOUNT03 varchar (128)
DECLARE @ACCOUNT04 varchar (128)
DECLARE @ACCOUNT05 varchar (64)
DECLARE @ACCOUNT06 varchar (64)
DECLARE @ACCOUNT07 varchar (128)
DECLARE @ACCOUNT08 varchar (64)
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)
DECLARE @WORKPHONE01 varchar (16)
DECLARE @WORKPHONE02 varchar (16)
DECLARE @WORKPHONE03 varchar (16)
DECLARE @WORKPHONE04 varchar (16)
DECLARE @WORKPHONE05 varchar (16)
DECLARE @FAX01 varchar (16)
DECLARE @FAX02 varchar (16)
DECLARE @FAX03 varchar (16)
DECLARE @FAX04 varchar (16)
DECLARE @FAX05 varchar (16)
DECLARE @PC01 varchar (12)
DECLARE @PC02 varchar (12)
DECLARE @PC03 varchar (12)
DECLARE @PC04 varchar (12)
DECLARE @PC05 varchar (12)
DECLARE @ADD1NUM varchar (32)
DECLARE @ADD2NUM varchar (32)
DECLARE @ADD1UNIT varchar (32)
DECLARE @ADD2UNIT varchar (32)
DECLARE @ADD1_01 varchar (128)
DECLARE @ADD1_02 varchar (128)
DECLARE @ADD1_03 varchar (128)
DECLARE @ADD2_01 varchar (128)
DECLARE @ADD2_02 varchar (128)
DECLARE @ADD2_03 varchar (128)
DECLARE @CITY01 varchar (64)
DECLARE @CITY02 varchar (64)
DECLARE @CITY03 varchar (64)
DECLARE @GENDER varchar(1)

DECLARE c1 CURSOR READ_ONLY FOR
SELECT
CONSOURCE1.UNIACCREF as ACCOUNTID, CONSOURCE1.RECORDID as SOURCEID,CONSOURCE1.SOURCECONID as SOURCEBRANCHID, 'Prospect' as SOURCERATING,
ACCOUNT01,ACCOUNT02,ACCOUNT03,ACCOUNT04,ACCOUNT05,ACCOUNT06,ACCOUNT07,ACCOUNT08,
PREFIX01,PREFIX02,PREFIX03,PREFIX04,PREFIX05,
FNAME01,FNAME02,FNAME03,FNAME04,FNAME05,FNAME06,
SNAME01,SNAME02,SNAME03,SNAME04,SNAME05,
WORKPHONE01,WORKPHONE02,WORKPHONE03,WORKPHONE04,WORKPHONE05,
FAX01,FAX02,FAX03,FAX04,FAX05,
ADD1NUM, ADD2NUM,
ADD1UNIT, ADD2UNIT,
ADD1_01,ADD1_02,ADD1_03,
ADD2_01,ADD2_02,ADD2_03,
CITY01,CITY02,CITY03,
PC01,PC02,PC03,C_MATCH1.GENDER
from
PROCESSING.dbo.con CONSOURCE1
inner join PROCESSING.dbo.C_MATCH C_MATCH1 on CONSOURCE1.recordid = C_MATCH1.CONTACTID
left join PROCESSING.dbo.ADD_MATCH ADD_MATCH1 on CONSOURCE1.RECORDID = ADD_MATCH1.BRANCHID and 'CONTACT' = ADD_MATCH1.TYPE
where CONSOURCE1.UNIACCREF is not null and CONSOURCE1.UNIACCREF <> ''

OPEN c1

FETCH NEXT FROM c1 into
@ACCOUNTID,
@CONTACTID, @BRANCHID, @TYPE,
@ACCOUNT01, @ACCOUNT02, @ACCOUNT03, @ACCOUNT04, @ACCOUNT05, @ACCOUNT06, @ACCOUNT07, @ACCOUNT08,
@PREFIX01, @PREFIX02, @PREFIX03, @PREFIX04, @PREFIX05,
@FNAME01, @FNAME02, @FNAME03, @FNAME04, @FNAME05, @FNAME06,
@SNAME01, @SNAME02, @SNAME03, @SNAME04, @SNAME05,
@WORKPHONE01,@WORKPHONE02,@WORKPHONE03,@WORKPHONE04,@WORKPHONE05,
@FAX01,@FAX02,@FAX03,@FAX04,@FAX05,
@ADD1NUM, @ADD2NUM,
@ADD1UNIT, @ADD2UNIT,
@ADD1_01,@ADD1_02,@ADD1_03,@ADD2_01,@ADD2_02,@ADD2_03,
@CITY01,@CITY02,@CITY03,
@PC01,@PC02,@PC03,@GENDER

WHILE @@FETCH_STATUS = 0

BEGIN

/*** PUT CODE HERE TO MATCH UP ***/
insert into PROCESSING.dbo.C_SCORE (SOURCEID,SOURCEBRANCHID,SOURCERATING,TARGETID,TARGETACCID,
SCOREACCOUNT01,SCOREACCOUNT02,SCOREACCOUNT03,SCOREACCOUNT04,SCOREACCOUNT05,SCOREACCOUNT06,SCOREACCOUNT07,SCOREACCOUNT08,
SCOREPREFIX01,SCOREPREFIX02,SCOREPREFIX03,SCOREPREFIX04,SCOREPREFIX05,
SCOREFNAME01,SCOREFNAME02,SCOREFNAME03,SCOREFNAME04,SCOREFNAME05,SCOREFNAME06,
SCORESNAME01,SCORESNAME02,SCORESNAME03,SCORESNAME04,SCORESNAME05,
SCOREWORKPHONE01,SCOREWORKPHONE02,SCOREWORKPHONE03,SCOREWORKPHONE04,SCOREWORKPHONE05,
SCOREFAX01,SCOREFAX02,SCOREFAX03,SCOREFAX04,SCOREFAX05,
SCOREADDNUM1, SCOREADDNUM2, SCOREADDNUMX,
SCOREADD1UNIT, SCOREADD2UNIT, SCOREADDXUNIT,
SCOREADD101,SCOREADD102,SCOREADD103,
SCOREADD201,SCOREADD202,SCOREADD203,
SCOREADDX01,SCOREADDX02,SCOREADDX03,
SCORECITY01,SCORECITY02,SCORECITY03,
SCOREPC01,SCOREPC02,SCOREPC03,SCOREGENDER01)
select
@CONTACTID,
@BRANCHID,
@TYPE,
CONTACT2.CONTACTREF,
CONTACT2.COMPANYREF,
SCOREACC01 = case IsNull(@ACCOUNT01,'|||') when IsNull(C_MATCH2.ACCOUNT01,'~~~') then 1 else 0 end,
SCOREACC02 = case IsNull(@ACCOUNT02,'|||') when IsNull(C_MATCH2.ACCOUNT02,'~~~') then 1 else 0 end,
SCOREACC03 = case IsNull(@ACCOUNT03,'|||') when IsNull(C_MATCH2.ACCOUNT03,'~~~') then 1 else 0 end,
SCOREACC04 = case IsNull(@ACCOUNT04,'|||') when IsNull(C_MATCH2.ACCOUNT04,'~~~') then 1 else 0 end,
SCOREACC05 = case IsNull(@ACCOUNT05,'|||') when IsNull(C_MATCH2.ACCOUNT05,'~~~') then 1 else 0 end,
SCOREACC06 = case IsNull(@ACCOUNT06,'|||') when IsNull(C_MATCH2.ACCOUNT06,'~~~') then 1 else 0 end,
SCOREACC07 = case IsNull(@ACCOUNT07,'|||') when IsNull(C_MATCH2.ACCOUNT07,'~~~') then 1 else 0 end,
SCOREACC08 = case IsNull(@ACCOUNT08,'|||') when IsNull(C_MATCH2.ACCOUNT08,'~~~') then 1 else 0 end,
SCOREPREFIX01 = case IsNull(@PREFIX01,'|||') when IsNull(C_MATCH2.PREFIX01,'~~~') then 2 else case IsNull(@PREFIX01,'') when '' then 1 else case IsNull(C_MATCH2.PREFIX01,'') when '' then 1 else 0 end end end,
SCOREPREFIX02 = case IsNull(@PREFIX02,'|||') when IsNull(C_MATCH2.PREFIX02,'~~~') then 2 else case IsNull(@PREFIX02,'') when '' then 1 else case IsNull(C_MATCH2.PREFIX02,'') when '' then 1 else 0 end end end,
SCOREPREFIX03 = case IsNull(@PREFIX03,'|||') when IsNull(C_MATCH2.PREFIX03,'~~~') then 2 else case IsNull(@PREFIX03,'') when '' then 1 else case IsNull(C_MATCH2.PREFIX03,'') when '' then 1 else 0 end end end,
SCOREPREFIX04 = case IsNull(@PREFIX04,'|||') when IsNull(C_MATCH2.PREFIX04,'~~~') then 2 else case IsNull(@PREFIX04,'') when '' then 1 else case IsNull(C_MATCH2.PREFIX04,'') when '' then 1 else 0 end end end,
SCOREPREFIX05 = case IsNull(@PREFIX05,'|||') when IsNull(C_MATCH2.PREFIX05,'~~~') then 2 else case IsNull(@PREFIX05,'') when '' then 1 else case IsNull(C_MATCH2.PREFIX05,'') when '' then 1 else 0 end end end,
SCOREFNAME01 = case IsNull(@FNAME01,'|||') when IsNull(C_MATCH2.FNAME01,'~~~') then 2 else case IsNull(@FNAME01,'') when '' then 1 else case IsNull(C_MATCH2.FNAME01,'') when '' then 1 else 0 end end end,
SCOREFNAME02 = case IsNull(@FNAME02,'|||') when IsNull(C_MATCH2.FNAME02,'~~~') then 2 else case IsNull(@FNAME02,'') when '' then 1 else case IsNull(C_MATCH2.FNAME02,'') when '' then 1 else 0 end end end,
SCOREFNAME03 = case IsNull(@FNAME03,'|||') when IsNull(C_MATCH2.FNAME03,'~~~') then 2 else case IsNull(@FNAME03,'') when '' then 1 else case IsNull(C_MATCH2.FNAME03,'') when '' then 1 else 0 end end end,
SCOREFNAME04 = case IsNull(@FNAME04,'|||') when IsNull(C_MATCH2.FNAME04,'~~~') then 2 else case IsNull(@FNAME04,'') when '' then 1 else case IsNull(C_MATCH2.FNAME04,'') when '' then 1 else 0 end end end,
SCOREFNAME05 = case IsNull(@FNAME05,'|||') when IsNull(C_MATCH2.FNAME05,'~~~') then 2 else case IsNull(@FNAME05,'') when '' then 1 else case IsNull(C_MATCH2.FNAME05,'') when '' then 1 else 0 end end end,
SCOREFNAME06 = case IsNull(@FNAME06,'|||') when IsNull(C_MATCH2.FNAME06,'~~~') then 2 else case IsNull(@FNAME06,'') when '' then 1 else case IsNull(C_MATCH2.FNAME06,'') when '' then 1 else 0 end end end,
SCORESNAME01 = case IsNull(@SNAME01,'|||') when IsNull(C_MATCH2.SNAME01,'~~~') then 2 else case IsNull(@SNAME01,'') when '' then 1 else case IsNull(C_MATCH2.SNAME01,'') when '' then 1 else 0 end end end,
SCORESNAME02 = case IsNull(@SNAME02,'|||') when IsNull(C_MATCH2.SNAME02,'~~~') then 2 else case IsNull(@SNAME02,'') when '' then 1 else case IsNull(C_MATCH2.SNAME02,'') when '' then 1 else 0 end end end,
SCORESNAME03 = case IsNull(@SNAME03,'|||') when IsNull(C_MATCH2.SNAME03,'~~~') then 2 else case IsNull(@SNAME03,'') when '' then 1 else case IsNull(C_MATCH2.SNAME03,'') when '' then 1 else 0 end end end,
SCORESNAME04 = case IsNull(@SNAME04,'|||') when IsNull(C_MATCH2.SNAME04,'~~~') then 2 else case IsNull(@SNAME04,'') when '' then 1 else case IsNull(C_MATCH2.SNAME04,'') when '' then 1 else 0 end end end,
SCORESNAME05 = case IsNull(@SNAME05,'|||') when IsNull(C_MATCH2.SNAME05,'~~~') then 2 else case IsNull(@SNAME05,'') when '' then 1 else case IsNull(C_MATCH2.SNAME05,'') when '' then 1 else 0 end end end,
SCOREWORKPHONE01 = case IsNull(@WORKPHONE01,'|||') when IsNull(C_MATCH2.WORKPHONE01,'~~~') then 1 else 0 end,
SCOREWORKPHONE02 = case IsNull(@WORKPHONE02,'|||') when IsNull(C_MATCH2.WORKPHONE02,'~~~') then 1 else 0 end,
SCOREWORKPHONE03 = case IsNull(@WORKPHONE03,'|||') when IsNull(C_MATCH2.WORKPHONE03,'~~~') then 1 else 0 end,
SCOREWORKPHONE04 = case IsNull(@WORKPHONE04,'|||') when IsNull(C_MATCH2.WORKPHONE04,'~~~') then 1 else 0 end,
SCOREWORKPHONE05 = case IsNull(@WORKPHONE05,'|||') when IsNull(C_MATCH2.WORKPHONE05,'~~~') then 1 else 0 end,
SCOREFAX01 = case IsNull(@FAX01,'|||') when IsNull(C_MATCH2.FAX01,'~~~') then 1 else 0 end,
SCOREFAX02 = case IsNull(@FAX02,'|||') when IsNull(C_MATCH2.FAX02,'~~~') then 1 else 0 end,
SCOREFAX03 = case IsNull(@FAX03,'|||') when IsNull(C_MATCH2.FAX03,'~~~') then 1 else 0 end,
SCOREFAX04 = case IsNull(@FAX04,'|||') when IsNull(C_MATCH2.FAX04,'~~~') then 1 else 0 end,
SCOREFAX05 = case IsNull(@FAX05,'|||') when IsNull(C_MATCH2.FAX05,'~~~') then 1 else 0 end,
SCOREADDNUM1 = case IsNull(@ADD1NUM,'|||') when IsNull(ADD_MATCH2.ADD1NUM,'~~~') then 1 else 0 end,
SCOREADDNUM2 = case IsNull(@ADD2NUM,'|||') when IsNull(ADD_MATCH2.ADD2NUM,'~~~') then 1 else 0 end,
SCOREADDNUMX = case IsNull(@ADD1NUM,'|||') when IsNull(ADD_MATCH2.ADD2NUM,'~~~') then 1 else 0 end,
SCOREADD1UNIT = case IsNull(@ADD1UNIT,'|||') when IsNull(ADD_MATCH2.ADD1UNIT,'~~~') then 1 else 0 end,
SCOREADD2UNIT = case IsNull(@ADD2UNIT,'|||') when IsNull(ADD_MATCH2.ADD2UNIT,'~~~') then 1 else 0 end,
SCOREADDXUNIT = case IsNull(@ADD1UNIT,'|||') when IsNull(ADD_MATCH2.ADD2UNIT,'~~~') then 1 else 0 end,
SCOREADD101 = case IsNull(@ADD1_01,'|||') when IsNull(ADD_MATCH2.ADD1_01,'~~~') then 1 else 0 end,
SCOREADD102 = case IsNull(@ADD1_02,'|||') when IsNull(ADD_MATCH2.ADD1_02,'~~~') then 1 else 0 end,
SCOREADD103 = case IsNull(@ADD1_03,'|||') when IsNull(ADD_MATCH2.ADD1_03,'~~~') then 1 else 0 end,
SCOREADD201 = case IsNull(@ADD2_01,'|||') when IsNull(ADD_MATCH2.ADD2_01,'~~~') then 1 else 0 end,
SCOREADD202 = case IsNull(@ADD2_02,'|||') when IsNull(ADD_MATCH2.ADD2_02,'~~~') then 1 else 0 end,
SCOREADD203 = case IsNull(@ADD2_03,'|||') when IsNull(ADD_MATCH2.ADD2_03,'~~~') then 1 else 0 end,
SCOREADDX01 = case IsNull(@ADD1_01,'|||') when IsNull(ADD_MATCH2.ADD2_01,'~~~') then 1 else 0 end,
SCOREADDX02 = case IsNull(@ADD1_02,'|||') when IsNull(ADD_MATCH2.ADD2_02,'~~~') then 1 else 0 end,
SCOREADDX03 = case IsNull(@ADD1_03,'|||') when IsNull(ADD_MATCH2.ADD2_03,'~~~') then 1 else 0 end,
SCORECITY01 = case IsNull(@CITY01,'|||') when IsNull(ADD_MATCH2.CITY01,'~~~') then 1 else 0 end,
SCORECITY02 = case IsNull(@CITY02,'|||') when IsNull(ADD_MATCH2.CITY02,'~~~') then 1 else 0 end,
SCORECITY03 = case IsNull(@CITY03,'|||') when IsNull(ADD_MATCH2.CITY03,'~~~') then 1 else 0 end,
SCOREPC01 = case IsNull(@PC01,'|||') when IsNull(ADD_MATCH2.PC01,'~~~') then 1 else 0 end,
SCOREPC02 = case IsNull(@PC02,'|||') when IsNull(ADD_MATCH2.PC02,'~~~') then 1 else 0 end,
SCOREPC03 = case IsNull(@PC03,'|||') when IsNull(ADD_MATCH2.PC03,'~~~') then 1 else 0 end,
GENDER01 = case IsNull(@GENDER,'|||') when IsNull(C_MATCH2.GENDER,'~~~') then 1 else 0 end
from
UNIVERSE.dbo.contacts CONTACT2
inner join UNIVERSE.dbo.C_MATCH C_MATCH2 on CONTACT2.contactref = C_MATCH2.CONTACTID
inner join UNIVERSE.dbo.ADD_MATCH ADD_MATCH2 on CONTACT2.contactref = ADD_MATCH2.ADDRESSID
where

/*** THERE HAS TO BE AT LEAST AN ACCOUNT NAME MATCH MATCH ***/
(
((@ACCOUNTID = CONTACT2.COMPANYREF))
and
/*** AND THERE HAS TO BE AT LEAST A FIRST OR LAST NAME MATCH ***/
((@FNAME01 <> '' and @FNAME01 is not null and @FNAME01 = C_MATCH2.FNAME01) or
(@FNAME02 <> '' and @FNAME02 is not null and @FNAME02 = C_MATCH2.FNAME02) or
(@FNAME03 <> '' and @FNAME03 is not null and @FNAME03 = C_MATCH2.FNAME03) or
(@FNAME04 <> '' and @FNAME04 is not null and @FNAME04 = C_MATCH2.FNAME04) or
(@FNAME05 <> '' and @FNAME05 is not null and @FNAME05 = C_MATCH2.FNAME05) or
(@FNAME06 <> '' and @FNAME06 is not null and @FNAME06 = C_MATCH2.FNAME06) or
(@SNAME01 <> '' and @SNAME01 is not null and @SNAME01 = C_MATCH2.SNAME01) or
(@SNAME02 <> '' and @SNAME02 is not null and @SNAME02 = C_MATCH2.SNAME02) or
(@SNAME03 <> '' and @SNAME03 is not null and @SNAME03 = C_MATCH2.SNAME03) or
(@SNAME04 <> '' and @SNAME04 is not null and @SNAME04 = C_MATCH2.SNAME04) or
(@SNAME05 <> '' and @SNAME05 is not null and @SNAME05 = C_MATCH2.SNAME05))
)

FETCH NEXT FROM c1 into
@ACCOUNTID,
@CONTACTID, @BRANCHID, @TYPE,
@ACCOUNT01, @ACCOUNT02, @ACCOUNT03, @ACCOUNT04, @ACCOUNT05, @ACCOUNT06, @ACCOUNT07, @ACCOUNT08,
@PREFIX01, @PREFIX02, @PREFIX03, @PREFIX04, @PREFIX05,
@FNAME01, @FNAME02, @FNAME03, @FNAME04, @FNAME05, @FNAME06,
@SNAME01, @SNAME02, @SNAME03, @SNAME04, @SNAME05,
@WORKPHONE01,@WORKPHONE02,@WORKPHONE03,@WORKPHONE04,@WORKPHONE05,
@FAX01,@FAX02,@FAX03,@FAX04,@FAX05,
@ADD1NUM, @ADD2NUM,
@ADD1UNIT, @ADD2UNIT,
@ADD1_01,@ADD1_02,@ADD1_03,@ADD2_01,@ADD2_02,@ADD2_03,
@CITY01,@CITY02,@CITY03,
@PC01,@PC02,@PC03,@GENDER

END

CLOSE c1
DEALLOCATE c1

Every Day's a School Day

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-09-20 : 04:50:17

get yorr select query right and then prefix it with an insert along the style of below.

insert into tabled
select
col1,
col2,
case
when col3 is null then abc
else def
end as col3,
etc
from
tablea
inner join tableb b
on a.cola=b.colb
where a.value1 = x
or b.value2 = y

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-20 : 05:20:50

Start to think set-based instead of RBAR.

INSERT		Processing.dbo.C_Score
		(
			SourceID,
			SourceBranchID,
			SourceRating,
			TargetID,
			TargetAccID,
			SCOREACCOUNT01,SCOREACCOUNT02,SCOREACCOUNT03,SCOREACCOUNT04,SCOREACCOUNT05,SCOREACCOUNT06,SCOREACCOUNT07,SCOREACCOUNT08,
			SCOREPREFIX01,SCOREPREFIX02,SCOREPREFIX03,SCOREPREFIX04,SCOREPREFIX05,
			SCOREFNAME01,SCOREFNAME02,SCOREFNAME03,SCOREFNAME04,SCOREFNAME05,SCOREFNAME06,
			SCORESNAME01,SCORESNAME02,SCORESNAME03,SCORESNAME04,SCORESNAME05,
			SCOREWORKPHONE01,SCOREWORKPHONE02,SCOREWORKPHONE03,SCOREWORKPHONE04,SCOREWORKPHONE05,
			SCOREFAX01,SCOREFAX02,SCOREFAX03,SCOREFAX04,SCOREFAX05,
			SCOREADDNUM1, SCOREADDNUM2, SCOREADDNUMX,
			SCOREADD1UNIT, SCOREADD2UNIT, SCOREADDXUNIT,
			SCOREADD101,SCOREADD102,SCOREADD103,
			SCOREADD201,SCOREADD202,SCOREADD203,
			SCOREADDX01,SCOREADDX02,SCOREADDX03,
			SCORECITY01,SCORECITY02,SCORECITY03,
			SCOREPC01,SCOREPC02,SCOREPC03,
			SCOREGENDER01
		)
SELECT		s.CONTACTID AS SOURCEID,
		s.BRANCHID AS SOURCEBRANCHID,
		s.TYPE AS SOURCERATING,
		a.CONTACTREF AS TARGETID,
		a.COMPANYREF AS TARGETACCID,
		CASE WHEN s.ACCOUNT01 = a.ACCOUNT01 THEN 1 ELSE 0 END AS SCOREACCOUNT01,
		CASE WHEN s.ACCOUNT02 = a.ACCOUNT02 THEN 1 ELSE 0 END AS SCOREACCOUNT02,
		CASE WHEN s.ACCOUNT03 = a.ACCOUNT03 THEN 1 ELSE 0 END AS SCOREACCOUNT03,
		CASE WHEN s.ACCOUNT04 = a.ACCOUNT04 THEN 1 ELSE 0 END AS SCOREACCOUNT04,
		CASE WHEN s.ACCOUNT05 = a.ACCOUNT05 THEN 1 ELSE 0 END AS SCOREACCOUNT05,
		CASE WHEN s.ACCOUNT06 = a.ACCOUNT06 THEN 1 ELSE 0 END AS SCOREACCOUNT06,
		CASE WHEN s.ACCOUNT07 = a.ACCOUNT07 THEN 1 ELSE 0 END AS SCOREACCOUNT07,
		CASE WHEN s.ACCOUNT08 = a.ACCOUNT08 THEN 1 ELSE 0 END AS SCOREACCOUNT08,
		CASE WHEN s.PREFIX01 = a.PREFIX01 THEN 2 WHEN COALESCE(s.PREFIX01, a.PREFIX01, '') = '' THEN 1 ELSE 0 END AS SCOREPREFIX01,
		CASE WHEN s.PREFIX02 = a.PREFIX02 THEN 2 WHEN COALESCE(s.PREFIX02, a.PREFIX02, '') = '' THEN 1 ELSE 0 END AS SCOREPREFIX02,
		CASE WHEN s.PREFIX03 = a.PREFIX03 THEN 2 WHEN COALESCE(s.PREFIX03, a.PREFIX03, '') = '' THEN 1 ELSE 0 END AS SCOREPREFIX03,
		CASE WHEN s.PREFIX04 = a.PREFIX04 THEN 2 WHEN COALESCE(s.PREFIX04, a.PREFIX04, '') = '' THEN 1 ELSE 0 END AS SCOREPREFIX04,
		CASE WHEN s.PREFIX05 = a.PREFIX05 THEN 2 WHEN COALESCE(s.PREFIX05, a.PREFIX05, '') = '' THEN 1 ELSE 0 END AS SCOREPREFIX05,
		CASE WHEN s.FNAME01 = a.FNAME01 THEN 2 WHEN COALESCE(s.FNAME01, a.FNAME01, '') = '' THEN 1 ELSE 0 END AS SCOREFNAME01,
		CASE WHEN s.FNAME02 = a.FNAME02 THEN 2 WHEN COALESCE(s.FNAME02, a.FNAME02, '') = '' THEN 1 ELSE 0 END AS SCOREFNAME02,
		CASE WHEN s.FNAME03 = a.FNAME03 THEN 2 WHEN COALESCE(s.FNAME03, a.FNAME03, '') = '' THEN 1 ELSE 0 END AS SCOREFNAME03,
		CASE WHEN s.FNAME04 = a.FNAME04 THEN 2 WHEN COALESCE(s.FNAME04, a.FNAME04, '') = '' THEN 1 ELSE 0 END AS SCOREFNAME04,
		CASE WHEN s.FNAME05 = a.FNAME05 THEN 2 WHEN COALESCE(s.FNAME05, a.FNAME05, '') = '' THEN 1 ELSE 0 END AS SCOREFNAME05,
		CASE WHEN s.FNAME06 = a.FNAME06 THEN 2 WHEN COALESCE(s.FNAME06, a.FNAME06, '') = '' THEN 1 ELSE 0 END AS SCOREFNAME06,
		CASE WHEN s.SNAME01 = a.SNAME01 THEN 2 WHEN COALESCE(s.SNAME01, a.SNAME01, '') = '' THEN 1 ELSE 0 END AS SCORESNAME01,
		CASE WHEN s.SNAME02 = a.SNAME02 THEN 2 WHEN COALESCE(s.SNAME02, a.SNAME02, '') = '' THEN 1 ELSE 0 END AS SCORESNAME02,
		CASE WHEN s.SNAME03 = a.SNAME03 THEN 2 WHEN COALESCE(s.SNAME03, a.SNAME03, '') = '' THEN 1 ELSE 0 END AS SCORESNAME03,
		CASE WHEN s.SNAME04 = a.SNAME04 THEN 2 WHEN COALESCE(s.SNAME04, a.SNAME04, '') = '' THEN 1 ELSE 0 END AS SCORESNAME04,
		CASE WHEN s.SNAME05 = a.SNAME05 THEN 2 WHEN COALESCE(s.SNAME05, a.SNAME05, '') = '' THEN 1 ELSE 0 END AS SCORESNAME05,
		CASE WHEN s.WORKPHONE01 = a.WORKPHONE01 THEN 1 ELSE 0 END AS SCOREWORKPHONE01,
		CASE WHEN s.WORKPHONE02 = a.WORKPHONE02 THEN 1 ELSE 0 END AS SCOREWORKPHONE02,
		CASE WHEN s.WORKPHONE03 = a.WORKPHONE03 THEN 1 ELSE 0 END AS SCOREWORKPHONE03,
		CASE WHEN s.WORKPHONE04 = a.WORKPHONE04 THEN 1 ELSE 0 END AS SCOREWORKPHONE04,
		CASE WHEN s.WORKPHONE05 = a.WORKPHONE05 THEN 1 ELSE 0 END AS SCOREWORKPHONE05,
		CASE WHEN s.FAX01 = a.FAX01 THEN 1 ELSE 0 END AS SCOREFAX01,
		CASE WHEN s.FAX02 = a.FAX02 THEN 1 ELSE 0 END AS SCOREFAX02,
		CASE WHEN s.FAX03 = a.FAX03 THEN 1 ELSE 0 END AS SCOREFAX03,
		CASE WHEN s.FAX04 = a.FAX04 THEN 1 ELSE 0 END AS SCOREFAX04,
		CASE WHEN s.FAX05 = a.FAX05 THEN 1 ELSE 0 END AS SCOREFAX05,
		CASE WHEN s.ADD1NUM = a.ADD1NUM THEN 1 ELSE 0 END AS SCOREADDNUM1,
		CASE WHEN s.ADD2NUM = a.ADD2NUM THEN 1 ELSE 0 END AS SCOREADDNUM2,
		CASE WHEN s.ADD1NUM = a.ADD2NUM THEN 1 ELSE 0 END AS SCOREADDNUMX,
		CASE WHEN s.ADD1UNIT = a.ADD1UNIT THEN 1 ELSE 0 END AS SCOREADD1UNIT,
		CASE WHEN s.ADD2UNIT = a.ADD2UNIT THEN 1 ELSE 0 END AS SSCOREADD2UNIT,
		CASE WHEN s.ADD1UNIT = a.ADD2UNIT THEN 1 ELSE 0 END AS SCOREADDXUNIT,
		CASE WHEN s.ADD1_01 = a.ADD1_01 THEN 1 ELSE 0 END AS SCOREADD101,
		CASE WHEN s.ADD1_02 = a.ADD1_02 THEN 1 ELSE 0 END AS SCOREADD102,
		CASE WHEN s.ADD1_03 = a.ADD1_03 THEN 1 ELSE 0 END AS SCOREADD103,
		CASE WHEN s.ADD2_01 = a.ADD2_01 THEN 1 ELSE 0 END AS SCOREADD201,
		CASE WHEN s.ADD2_02 = a.ADD2_02 THEN 1 ELSE 0 END AS SCOREADD202,
		CASE WHEN s.ADD2_03 = a.ADD2_03 THEN 1 ELSE 0 END AS SCOREADD203,
		CASE WHEN s.ADDX_01 = a.ADDX_01 THEN 1 ELSE 0 END AS SCOREADDX01,
		CASE WHEN s.ADDX_02 = a.ADDX_02 THEN 1 ELSE 0 END AS SCOREADDX02,
		CASE WHEN s.ADDX_03 = a.ADDX_03 THEN 1 ELSE 0 END AS SCOREADDX03,
		CASE WHEN s.CITY01 = a.CITY01 THEN 1 ELSE 0 END AS SCORECITY01,
		CASE WHEN s.CITY02 = a.CITY02 THEN 1 ELSE 0 END AS SCORECITY02,
		CASE WHEN s.CITY03 = a.CITY03 THEN 1 ELSE 0 END AS SCORECITY03,
		CASE WHEN s.PC01 = a.PC01 THEN 1 ELSE 0 END AS SCOREPC01,
		CASE WHEN s.PC02 = a.PC02 THEN 1 ELSE 0 END AS SCOREPC02,
		CASE WHEN s.PC03 = a.PC03 THEN 1 ELSE 0 END AS SCOREPC03,
		CASE WHEN s.GENDER = a.GENDER THEN 1 ELSE 0 END AS GENDER01 
FROM		(
			SELECT		c.UniAccRef AS AccountID,
					c.RecordID AS SourceID,
					c.SourceConID AS SourceBranchID,
					'Prospect' AS SourceRating,
					Account01, Account02, Account03, Account04, Account05, Account06, Account07, Account08,
					Prefix01, Prefix02, Prefix03, Prefix04, Prefix05,
					FName01, FName02, FName03, FName04, FName05, FName06,
					SName01, SName02, SName03, SName04, SName05,
					Workphone01, Workphone02, Workphone03, Workphone04, Workphone05,
					Fax01, Fax02, Fax03, Fax04, Fax05,
					Add1Num, Add2Num,
					Add1Unit, Add2Unit,
					Add1_01, Add1_02, Add1_03,
					Add2_01, Add2_02, Add2_03,
					AddX_01, AddX_02, AddX_03,
					City01, City02, City03,
					Pc01, Pc02, Pc03,
					cm.Gender
			FROM		Processing.dbo.Con AS c
			INNER JOIN	Processing.dbo.C_Match AS cm ON cm.ContactID = c.RecordID
			LEFT JOIN	Processing.dbo.Add_Match AS am ON am.BranchID = c.RecordID
						AND m.Type = 'CONTACT'
			WHERE		c.UniAccRef > ''
		) AS s
INNER JOIN	(
			SELECT		c.ContactRef AS TargetID,
					c.CompanyRef AS TargetAccID,
					cm.Account01, cm.Account02, cm.Account03, cm.Account04, cm.Account05, cm.Account06, cm.Account07, cm.Account08,
					cm.Prefix02, cm.Prefix02, cm.Prefix03, cm.Prefix04, cm.Prefix05,
					cm.FName01, cm.FName02, cm.FName03, cm.FName04, cm.FName05, cm.FName06,
					cm.SName01, cm.SName02, cm.SName03, cm.SName04, cm.SName05,
					cm.WorkPhone01, cm.WorkPhone02, cm.WorkPhone03, cm.WorkPhone04, cm.WorkPhone05,
					cm.Fax01, cm.Fax02, cm.Fax03, cm.Fax04, cm.Fax05,
					am.Add1Num, am.Add2Num, am.Add2Num,
					am.Add1Unit, am.Add2Unit,am.AddXUnit,
					am.Add1_01, am.Add1_02, am.Add1_03,
					am.Add2_01, am.Add2_02,	am.Add2_03,
					am.AddX_01, am.AddX_02,	am.AddX_03,
					am.City01, am.City02,am.City03,
					am.Pc01, am.Pc02, am.Pc03,
					cm.GENDER
			FROM		UNIVERSE.dbo.Contacts AS c
			INNER JOIN	UNIVERSE.dbo.C_Match AS cm ON cm.ContactID = c.ContactRef
			INNER JOIN	UNIVERSE.dbo.Add_Match AS am ON am.AddressID = c.ContactRef
		) AS a ON a.TargetAccID = s.AccountID
WHERE		s.FName01 > '' AND s.FName01 = a.FName01
		OR s.FName02 > '' AND s.FName02 = a.FName02
		OR s.FName03 > '' AND s.FName03 = a.FName03
		OR s.FName04 > '' AND s.FName04 = a.FName04
		OR s.FName05 > '' AND s.FName05 = a.FName05
		OR s.FName06 > '' AND s.FName06 = a.FName06
		OR s.SName01 > '' AND s.SName01 = a.SName01
		OR s.SName02 > '' AND s.SName02 = a.SName02
		OR s.SName03 > '' AND s.SName03 = a.SName03
		OR s.SName04 > '' AND s.SName04 = a.SName04
		OR s.SName05 > '' AND s.SName05 = a.SName05

N 56°04'39.26"
E 12°55'05.63"

MontyMagic
Starting Member

12 Posts

Posted - 2011-09-20 : 07:12:07

Hi SWEPESO

I monkeyed a little with the script to get it working on my server, but it worked beautifully. What a difference, again minutes rather than hours. I am definately converted to replacing cursors wherever possible.

Many thanks for your suggestions and assistance.

Monty

Every Day's a School Day

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-20 : 08:37:11

You're welcome.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources