Replacing a SLOWWWWW 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)

Replacing a SLOWWWWW Cursor

Author

Topic

MontyMagic
Starting Member

12 Posts

Posted - 2011-09-17 : 10:52:57

Hi All,

I need help with replacing an extremely slow cursor, does anyone have any suggestions? The cursor (pasted below) currently takes about 8.5 hours to cycle through 5.5m records in the A_SCORE table. This is ridiculous, there must be a better way.

I would greatly appreciate any suggestions, examples or advice that you may be able to offer. Thank you in advance.

Stored Proc below:

/***************************************/

set @TARGETDBNAME = Upper(@TARGETDBNAME)
if @TARGETDBNAME is null or @TARGETDBNAME = ''
begin
set @TARGETDBNAME = 'UNIVERSE'
end

declare @SQL as varchar(1024)

/**********************************************/
/*** NOW OPEN A CURSOR AND DO THE COMPARING ***/
/**********************************************/
declare @A_SCOREID as bigint
declare @TARGETID as varchar(32)
declare @SOURCEID as varchar(32)

declare @SCOREACCNAMEWORDMATCH_01 as smallint
declare @SCOREACCNAMEWORDMATCH_02 as smallint
declare @SCOREACCNAMEWORDMATCH_03 as smallint

declare @SCOREACCNORMWORDMATCH_01 as smallint
declare @SCOREACCNORMWORDMATCH_02 as smallint
declare @SCOREACCNORMWORDMATCH_03 as smallint

declare @SCOREACCEXCLWORDMATCH_01 as smallint
declare @SCOREACCEXCLWORDMATCH_02 as smallint
declare @SCOREACCEXCLWORDMATCH_03 as smallint

DECLARE c1 CURSOR FAST_FORWARD FOR

SELECT A_SCOREID, SOURCEID, TARGETID from PROCESSING.dbo.A_SCORE A_SCORE1

OPEN c1

FETCH NEXT FROM c1 into
@A_SCOREID, @SOURCEID, @TARGETID

WHILE @@FETCH_STATUS = 0

BEGIN

BEGIN

/* NAME MATCHES */
select
@SCOREACCNAMEWORDMATCH_01 = Sum(Case when A1.WORD = A2.WORD then 1 else 0 end),
@SCOREACCNAMEWORDMATCH_02 = Sum(Case when A1.WORD02 = A2.WORD02 then 1 else 0 end),
@SCOREACCNAMEWORDMATCH_03 = Sum(Case when A1.WORD02 = A2.WORD03 then 1 else 0 end)
from PROCESSING.dbo.A_MATCH_SPLITACCNAME A1, UNIVERSE.dbo.A_MATCH_SPLITACCNAME A2
where
A1.ACCOUNTID = @SOURCEID
and
A2.ACCOUNTID = @TARGETID

/* NORM MATCHES */
select
@SCOREACCNORMWORDMATCH_01 = Sum(Case when A1.WORD = A2.WORD then 1 else 0 end),
@SCOREACCNORMWORDMATCH_02 = Sum(Case when A1.WORD02 = A2.WORD02 then 1 else 0 end),
@SCOREACCNORMWORDMATCH_03 = Sum(Case when A1.WORD02 = A2.WORD03 then 1 else 0 end)
from PROCESSING.dbo.A_MATCH_SPLITACCNORM A1, UNIVERSE.dbo.A_MATCH_SPLITACCNORM A2
where
A1.ACCOUNTID = @SOURCEID
and
A2.ACCOUNTID = @TARGETID

/* EXCL MATCHES */
select
@SCOREACCEXCLWORDMATCH_01 = Sum(Case when A1.WORD = A2.WORD then 1 else 0 end),
@SCOREACCEXCLWORDMATCH_02 = Sum(Case when A1.WORD02 = A2.WORD02 then 1 else 0 end),
@SCOREACCEXCLWORDMATCH_03 = Sum(Case when A1.WORD02 = A2.WORD03 then 1 else 0 end)
from PROCESSING.dbo.A_MATCH_SPLITACCEXCL A1, UNIVERSE.dbo.A_MATCH_SPLITACCEXCL A2
where
A1.ACCOUNTID = @SOURCEID
and
A2.ACCOUNTID = @TARGETID
END

update PROCESSING.dbo.A_SCORE set
SCOREACCNAMEWORDMATCH_01 = @SCOREACCNAMEWORDMATCH_01,
SCOREACCNAMEWORDMATCH_02 = @SCOREACCNAMEWORDMATCH_02,
SCOREACCNAMEWORDMATCH_03 = @SCOREACCNAMEWORDMATCH_03,
SCOREACCNORMWORDMATCH_01 = @SCOREACCNORMWORDMATCH_01,
SCOREACCNORMWORDMATCH_02 = @SCOREACCNORMWORDMATCH_02,
SCOREACCNORMWORDMATCH_03 = @SCOREACCNORMWORDMATCH_03,
SCOREACCEXCLWORDMATCH_01 = @SCOREACCEXCLWORDMATCH_01,
SCOREACCEXCLWORDMATCH_02 = @SCOREACCEXCLWORDMATCH_02,
SCOREACCEXCLWORDMATCH_03 = @SCOREACCEXCLWORDMATCH_03
where
A_SCOREID = @A_SCOREID

FETCH NEXT FROM c1 into
@A_SCOREID, @SOURCEID, @TARGETID

END

CLOSE c1
DEALLOCATE c1

END

Every Day's a School Day

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-17 : 11:23:42

Hello,

Perhaps the following query will stimulate some ideas towards a restructuring of this cursor;

SELECT 
	A_SCORE1.A_SCOREID, 
	A_SCORE1.SOURCEID, 
	A_SCORE1.TARGETID,
	
	/* NAME MATCHES */
	Sum(Case when A1.WORD = A2.WORD then 1 else 0 end) SCOREACCNAMEWORDMATCH_01,
	Sum(Case when A1.WORD02 = A2.WORD02 then 1 else 0 end) SCOREACCNAMEWORDMATCH_02,
	Sum(Case when A1.WORD02 = A2.WORD03 then 1 else 0 end) SCOREACCNAMEWORDMATCH_03

	/* NORM MATCHES */
	Sum(Case when A3.WORD = A4.WORD then 1 else 0 end) SCOREACCNORMWORDMATCH_01,
	Sum(Case when A3.WORD02 = A4.WORD02 then 1 else 0 end) SCOREACCNORMWORDMATCH_02,
	Sum(Case when A3.WORD02 = A4.WORD03 then 1 else 0 end) SCOREACCNORMWORDMATCH_03,

	/* EXCL MATCHES */
	Sum(Case when A5.WORD = A6.WORD then 1 else 0 end) SCOREACCEXCLWORDMATCH_01,
	Sum(Case when A5.WORD02 = A6.WORD02 then 1 else 0 end) SCOREACCEXCLWORDMATCH_02,
	Sum(Case when A5.WORD02 = A6.WORD03 then 1 else 0 end) SCOREACCEXCLWORDMATCH_03
	
FROM 
	PROCESSING.dbo.A_SCORE A_SCORE1
	
	/* NAME MATCHES */
	LEFT JOIN PROCESSING.dbo.A_MATCH_SPLITACCNAME A1 ON A1.AccountID = A_SCORE1.SourceID
	LEFT JOIN UNIVERSE.dbo.A_MATCH_SPLITACCNAME A2 ON A2.ACCOUNTID = A_SCORE1.TargetID

	/* NORM MATCHES */
	LEFT JOIN PROCESSING.dbo.A_MATCH_SPLITACCNORM A3  ON A3.ACCOUNTID = A_SCORE1.SourceID
	LEFT JOIN UNIVERSE.dbo.A_MATCH_SPLITACCNORM A4 ON A4.ACCOUNTID = A_SCORE1.TargetID

	/* EXCL MATCHES */
	LEFT JOIN PROCESSING.dbo.A_MATCH_SPLITACCEXCL A5 ON A5.ACCOUNTID = A_SCORE1.SourceID
	LEFT JOIN UNIVERSE.dbo.A_MATCH_SPLITACCEXCL A6 ON A6.ACCOUNTID = A_SCORE1.TargetID

GROUP BY 
	A_SCORE1.A_SCOREID, 
	A_SCORE1.SOURCEID, 
	A_SCORE1.TARGETID

HTH.

MontyMagic
Starting Member

12 Posts

Posted - 2011-09-17 : 11:35:31

Thank you for your prompt response. I think I understand the point of the query, however when I try to run it I get the following:

Column 'PROCESSING.dbo.A_SCORE.A_SCOREID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I imagine this is becuase of the "sum" statements, however I really need the A_Scoreid, the SOURCEID and the TARGETID returned as well so I can update later on.

Any thoughts?

Every Day's a School Day

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-17 : 11:39:28

quote:
Originally posted by MontyMagic

Thank you for your prompt response. I think I understand the point of the query, however when I try to run it I get the following:

Column 'PROCESSING.dbo.A_SCORE.A_SCOREID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I imagine this is becuase of the "sum" statements, however I really need the A_Scoreid, the SOURCEID and the TARGETID returned as well so I can update later on.

Any thoughts?

Every Day's a School Day

Hello,

Yes, this is an aggregate statement and, as such, requires a grouping. Is it possible you grabbed the query I posted before I added the GROUP BY statement? If so, can you try it again?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-17 : 12:28:27

The entire statement might look something like the following;


UPDATE 	a
SET
	/* NAME MATCHES */
	a.SCOREACCNAMEWORDMATCH_01 = d.SCOREACCNAMEWORDMATCH_01 ,
	a.SCOREACCNAMEWORDMATCH_02 = d.SCOREACCNAMEWORDMATCH_02 ,
	a.SCOREACCNAMEWORDMATCH_03 = d.SCOREACCNAMEWORDMATCH_03 ,

	/* NORM MATCHES */
	a.SCOREACCNORMWORDMATCH_01 = d.SCOREACCNORMWORDMATCH_01 ,
	a.SCOREACCNORMWORDMATCH_02 = d.SCOREACCNORMWORDMATCH_02 ,
	a.SCOREACCNORMWORDMATCH_03 = d.SCOREACCNORMWORDMATCH_03 ,

	/* EXCL MATCHES */
	a.SCOREACCEXCLWORDMATCH_01 = d.SCOREACCEXCLWORDMATCH_01 ,
	a.SCOREACCEXCLWORDMATCH_02 = d.SCOREACCEXCLWORDMATCH_02 ,
	a.SCOREACCEXCLWORDMATCH_03 = d.SCOREACCEXCLWORDMATCH_03 
FROM 
	PROCESSING.dbo.A_SCORE a
JOIN
(
	SELECT 
		A_SCORE1.A_SCOREID, 
		A_SCORE1.SOURCEID, 
		A_SCORE1.TARGETID,
		
		/* NAME MATCHES */
		Sum(Case when A1.WORD = A2.WORD then 1 else 0 end) SCOREACCNAMEWORDMATCH_01,
		Sum(Case when A1.WORD02 = A2.WORD02 then 1 else 0 end) SCOREACCNAMEWORDMATCH_02,
		Sum(Case when A1.WORD02 = A2.WORD03 then 1 else 0 end) SCOREACCNAMEWORDMATCH_03,

		/* NORM MATCHES */
		Sum(Case when A3.WORD = A4.WORD then 1 else 0 end) SCOREACCNORMWORDMATCH_01,
		Sum(Case when A3.WORD02 = A4.WORD02 then 1 else 0 end) SCOREACCNORMWORDMATCH_02,
		Sum(Case when A3.WORD02 = A4.WORD03 then 1 else 0 end) SCOREACCNORMWORDMATCH_03,

		/* EXCL MATCHES */
		Sum(Case when A5.WORD = A6.WORD then 1 else 0 end) SCOREACCEXCLWORDMATCH_01,
		Sum(Case when A5.WORD02 = A6.WORD02 then 1 else 0 end) SCOREACCEXCLWORDMATCH_02,
		Sum(Case when A5.WORD02 = A6.WORD03 then 1 else 0 end) SCOREACCEXCLWORDMATCH_03
		
	FROM 
		PROCESSING.dbo.A_SCORE A_SCORE1
		
		/* NAME MATCHES */
		LEFT JOIN PROCESSING.dbo.A_MATCH_SPLITACCNAME A1 ON A1.AccountID = A_SCORE1.SourceID
		LEFT JOIN UNIVERSE.dbo.A_MATCH_SPLITACCNAME A2 ON A2.ACCOUNTID = A_SCORE1.TargetID

		/* NORM MATCHES */
		LEFT JOIN PROCESSING.dbo.A_MATCH_SPLITACCNORM A3  ON A3.ACCOUNTID = A_SCORE1.SourceID
		LEFT JOIN UNIVERSE.dbo.A_MATCH_SPLITACCNORM A4 ON A4.ACCOUNTID = A_SCORE1.TargetID

		/* EXCL MATCHES */
		LEFT JOIN PROCESSING.dbo.A_MATCH_SPLITACCEXCL A5 ON A5.ACCOUNTID = A_SCORE1.SourceID
		LEFT JOIN UNIVERSE.dbo.A_MATCH_SPLITACCEXCL A6 ON A6.ACCOUNTID = A_SCORE1.TargetID

	--WHERE A_SCORE1.A_SCOREID = <some test values>

	GROUP BY 
		A_SCORE1.A_SCOREID, 
		A_SCORE1.SOURCEID, 
		A_SCORE1.TARGETID

) d ON d.A_SCOREID = a.A_SCOREID

Seeing as you mentioned the data set is somewhat large (5.5M recs), quality and performance testing could be done on a smaller data set to ensure accuracy and indexes are being used.

Perhaps filter to a subset of A_SCOREID to evaluate quality and execution plans (i.e. The commented WHERE Clause on the inner query).

HTH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-17 : 12:49:54

keep it simple
it would be much better to do these updations by means of series of simple updates as below

UPDATE 	a
SET	a.SCOREACCNAMEWORDMATCH_01 = d.SCOREACCNAMEWORDMATCH_01 
FROM PROCESSING.dbo.A_SCORE a
CROSS APPLY (SELECT COUNT(*) AS SCOREACCNAMEWORDMATCH_01
from PROCESSING.dbo.A_MATCH_SPLITACCNAME A1
INNER JOIN UNIVERSE.dbo.A_MATCH_SPLITACCNAME A2
ON A1.WORD = A2.WORD 
where
A1.ACCOUNTID = a.SOURCEID
and
A2.ACCOUNTID = a.TARGETID
)d

UPDATE 	a
SET	a.SCOREACCNAMEWORDMATCH_02 = d.SCOREACCNAMEWORDMATCH_02 
CROSS APPLY (SELECT COUNT(*) AS SCOREACCNAMEWORDMATCH_02
from PROCESSING.dbo.A_MATCH_SPLITACCNAME A1
INNER JOIN UNIVERSE.dbo.A_MATCH_SPLITACCNAME A2
ON A1.WORD02 = A2.WORD02
where
A1.ACCOUNTID = a.SOURCEID
and
A2.ACCOUNTID = a.TARGETID
)d


UPDATE 	a
SET	a.SCOREACCNAMEWORDMATCH_03 = d.SCOREACCNAMEWORDMATCH_03 
CROSS APPLY (SELECT COUNT(*) AS SCOREACCNAMEWORDMATCH_03
from PROCESSING.dbo.A_MATCH_SPLITACCNAME A1
INNER JOIN UNIVERSE.dbo.A_MATCH_SPLITACCNAME A2
ON A1.WORD03 = A2.WORD03
where
A1.ACCOUNTID = a.SOURCEID
and
A2.ACCOUNTID = a.TARGETID
)d

UPDATE 	a
SET	SCOREACCNORMWORDMATCH_01 = d.SCOREACCNORMWORDMATCH_01
CROSS APPLY (SELECT COUNT(*) AS SCOREACCNORMWORDMATCH_01
from PROCESSING.dbo.A_MATCH_SPLITACCNORM A1
INNER JOIN UNIVERSE.dbo.A_MATCH_SPLITACCNORM A2
ON  A1.WORD = A2.WORD 
where
A1.ACCOUNTID = @SOURCEID
and
A2.ACCOUNTID = @TARGETID
)d

...

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

MontyMagic
Starting Member

12 Posts

Posted - 2011-09-17 : 15:52:02

Thank you both for your very comprehensive answers to my dilemma. I will spend the evening testing each solution against the results I achieved with the slow cursor and will post my finding back in case anyone is interested.

Once again thank you for your assistance.

Monty

Every Day's a School Day

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-18 : 03:13:58

welcome.
let us know how you got on

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

MontyMagic
Starting Member

12 Posts

Posted - 2011-09-20 : 03:35:56

Hi,

I have finished my testing and am amazed at the time difference.

I opted to keep the code simple as per your advice and ran thme as seperate statements. This enabled me to add subtle differences to the indexes for maximum performance.

The whole process as a cursor to 8.5 hours over the large dataset, by splitting into nine satements the same process now runs in an amazing 3.5 minutes. The cross comparison of the output results shows them to be identical too, so it is definately doing the job. Thankyou ever so much for your help and advice.

I do have another very slow cursor of a similar veing that I will now tackle using my new found logic, however as I know yo enjoy the challenge I will repost as a new thread to see your thoughts on it.

Once again thank you for your help.

Monty

Every Day's a School Day

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 05:17:20

you're welcome

glad that we could contribute to your querys performance as well as to your learning

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources