x

Replacing very slow Cursor

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
more ▼

asked Sep 22 '11 at 01:52 AM in Default

MontyMagic gravatar image

MontyMagic
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

To me it looks like you could place the code for the cursor inside a common table expression and then use it as a source for your insert statement. Something like the code below:

;WITH 
cte1 AS (
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
)
insert into C_SCORE (SOURCEID,SOURCEBRANCHID,SOURCERATING,TARGETID,TARGETACCID,MASTERSOURCEACCID_LEV)
select 
cte1.CONTACTID,
cte1.BRANCHID,
cte1.TYPE,
CONTACT2.RECORDID,
CONTACT2.MASTERSOURCEACCID_LEV1,
1
from 
con CONTACT2
inner join C_MATCH C_MATCH2 on CONTACT2.recordid = C_MATCH2.CONTACTID
INNER JOIN cte1 ON 
CONTACT2.MASTERSOURCEACCID_LEV1 = cte1.ACCOUNTID
and
CONTACT2.RECORDID <> cte1.CONTACTID
and
/*** AND THERE HAS TO BE AT LEAST A FIRST OR LAST NAME MATCH ***/
(
IsNull(cte1.FNAME01,'') <> '' and cte1.FNAME01 = C_MATCH2.FNAME01)
)
more ▼

answered Sep 22 '11 at 02:18 AM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

(comments are locked)
10|1200 characters needed characters left
I would heartily recommend reading the blog post here http://www.sqlservercentral.com/articles/T-SQL/73887/ and its follow up article about how to tackle cursors and replace them with set based solutions
more ▼

answered Sep 22 '11 at 02:42 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x57

asked: Sep 22 '11 at 01:52 AM

Seen: 918 times

Last Updated: Sep 22 '11 at 02:12 AM