Yet Another SLOWWW Cursor.....

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)

Yet Another SLOWWW Cursor.....

Author

Topic

MontyMagic
Starting Member

12 Posts

Posted - 2011-09-20 : 10:59:09

Hi Guys,

Once again thank you for all your help with my previous posts about specific slow cursors, your answers are excellent and using approaches that I wouldnt have thought of. I am trying to understand them in isolation to improve my knowledge, but that will have to take a back seat until I can finish speeding up this application.

My slowwwwwww cursor is as follows. I would appreciate your continued assistance if possible with any suggestions, comments or examples that you may offer.

Once again thanks.

Cursor Below:

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

/**********************************/
/*** THIS IS LEVEL 2 ACCC MATCH ***/
/**********************************/
DECLARE c2 CURSOR READ_ONLY FOR
SELECT
CONSOURCE1.MASTERSOURCEACCID_LEV2 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
where
MASTERSOURCEACCID_LEV2 <> MASTERSOURCEACCID_LEV1
OPEN c2
FETCH NEXT FROM c2 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_LEV2,
2
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_LEV2 = @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 c2 into
@ACCOUNTID,
@CONTACTID, @BRANCHID, @TYPE,
@PREFIX01, @PREFIX02, @PREFIX03, @PREFIX04, @PREFIX05,
@FNAME01, @FNAME02, @FNAME03, @FNAME04, @FNAME05, @FNAME06,
@SNAME01, @SNAME02, @SNAME03, @SNAME04, @SNAME05

END

CLOSE c2
DEALLOCATE c2

/**********************************/
/*** THIS IS LEVEL 3 ACCC MATCH ***/
/**********************************/
DECLARE c3 CURSOR READ_ONLY FOR
SELECT
CONSOURCE1.MASTERSOURCEACCID_LEV3 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
where
MASTERSOURCEACCID_LEV3 <> MASTERSOURCEACCID_LEV2
and
MASTERSOURCEACCID_LEV3 <> MASTERSOURCEACCID_LEV1
OPEN c3
FETCH NEXT FROM c3 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_LEV3,
3
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_LEV3 = @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 c3 into
@ACCOUNTID,
@CONTACTID, @BRANCHID, @TYPE,
@PREFIX01, @PREFIX02, @PREFIX03, @PREFIX04, @PREFIX05,
@FNAME01, @FNAME02, @FNAME03, @FNAME04, @FNAME05, @FNAME06,
@SNAME01, @SNAME02, @SNAME03, @SNAME04, @SNAME05

END

CLOSE c3
DEALLOCATE c3

Every Day's a School Day

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 11:02:24

would be much better if you can explain your scenario using sample data and required output rather than posting whole query here.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MontyMagic
Starting Member

12 Posts

Posted - 2011-09-20 : 11:27:59

Hi visakh16,

Thank you for your suggestion, I totally agree that it must be a nightmare to understand. To clarify my position I am not the DBA that wrote this code, he unfortunately has left and left us with a slow application and very little in the way of commented code. I am struggling to understand exactly what these stored procedures are doing, so its very difficult for me to understand how to rewrite or how to explain them.

As far as I am able to understand this code is designed to compare fields from the source table against the target table to identify potential matches. It then inserts these into the C_SCORE table where a later procedure will identify real matching records based upon the number of fields/items that have matched and their importance. For example the FNAME field is FirstName and has 6 entries for matching FNAME01, FNAME02, etc... These contain the following:
FNAME01 = FIRSTNAME(complete), FNAME02 = SOUNDEX of FIRSTNAME, FNAME03 = METAPHONE of FIRSTNAME, FNAME04 = NORMALISED VERSION OF FIRSTNAME (e.g. ROBERT, BOBBY, ROB, ROBBIE etc all go to BOB), FNAME05 is the LEFT CHAR 1 of FIRSTNAME, FNAME06 is the LEFT CHAR1 of the normalised firstname.

We cross compare these to idenify all potential matches in firstname, the apporach is similar for surname etc.

I am sorry that I am unable to explain more as to what exactly this code is supposed to do, as I am struggling to understand more myself.

I greatly appreciate any help you can give. Once again thank you for your time...

Monty

PS. If it helps, I believe that the LEV 1, 2, 3 is the level of confidence we have in the matching, e.g. 1 is a tight match, 2 is a normal match and 3 is a very fuzzy match. I believe these are selected based upon the types of fields that match, e.g. a FNAME01 exact match is better than a FNAME06 normalised initials match etc...
I am sorry I don't understand more at this stage.

Every Day's a School Day

MontyMagic
Starting Member

12 Posts

Posted - 2011-09-21 : 03:28:26

Hi Guys,

Did anyone have any thoughts on how I can replace the cursor on this query?

I am struggling somewhat and would greatly appreciate any help available.

Thanks

Monty

Every Day's a School Day

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources