Cursors - Help Needed - 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 2000 Forums

Transact-SQL (2000)

Cursors - Help Needed

Author

Topic

eligiable
Starting Member

4 Posts

Posted - 2009-06-23 : 02:50:59

I am new to this SQL Server Environment and I am really Fed Up of these Cursors but I didn't find a right Solution to overcome this issue, please Help me in this ...

I have build three Cursors as follows, to update my table, its working fine but takes to much time to execute/update the table ... and I really don't know what to do with this ...

--------------------------------------------------
CREATE PROCEDURE rptPhaseD

@YearMonth as int

AS

Declare @Code as int
Declare @PaidMO as nvarchar(10)

Begin

DECLARE rptPhaseD_Cursor CURSOR FOR

SELECT gpo_id AS Code, COUNT(mno) AS PaidMO
FROM masterdata
WHERE (year_month = @YearMonth) AND (service_id = 'B') and (status = 'D') -- Here I am setting the 'Status' as U and Null for other Two Cursors
GROUP BY gpo_id
ORDER BY gpo_id

OPEN rptPhaseD_Cursor

FETCH NEXT FROM rptPhaseD_Cursor into @Code, @PaidMO

WHILE @@FETCH_STATUS = 0

Begin

Update rptPhase
Set PaidMO = @PaidMO where Code=@Code and YearMonth = @YearMonth
FETCH NEXT FROM rptPhaseD_Cursor into @Code, @PaidMO

End

CLOSE rptPhaseD_Cursor
DEALLOCATE rptPhaseD_Cursor

End
GO
--------------------------------------------------

To execute all of the three cursors I am calling another Stored Procedure as;

--------------------------------------------------
CREATE PROCEDURE Phase

@YearMonth as int

AS

Begin
Execute rptPhaseD @YearMonth
Execute rptPhaseU @YearMonth
Execute rptPhaseNull @YearMonth

End
GO
--------------------------------------------------

I have a total amount of 10 billion records and the Procedures I am calling takes a long time to execute and I have to do it daily. Please help me in this ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 03:11:35

10 billion records? 10,000,000,000 records?
And you ask why a CURSOR-based solution is slow?

Try a SET-based solution! This suggesion also allows a parameter for Status so that you don't need three SP for same thing.

CREATE PROCEDURE dbo.uspRptPhase
(
	@YearMonth INT,
	@Status CHAR(1)
)
AS

SET NOCOUNT ON

CREATE TABLE	#RptPhase
		(
			gpo_id BIGINT,
			PaidMO BIGINT
		)

IF @Status IS NULL 
	INSERT		#RptPhase
			(
				gpo_id,
				PaidMO
			)
	SELECT		gpo_id,
			COUNT(mno)
	FROM		masterdata
	WHERE		year_month = @YearMonth
			AND service_id = 'B'
			AND status IS NULL
	GROUP BY	gpo_id
ELSE IF @Status IN ('B', 'D')
	INSERT		#RptPhase
			(
				gpo_id,
				PaidMO
			)
	SELECT		gpo_id,
			COUNT(mno)
	FROM		masterdata
	WHERE		year_month = @YearMonth
			AND service_id = 'B'
			AND status = @Status
	GROUP BY	gpo_id
ELSE
	BEGIN
		DROP TABLE	#RptPhase
		RETURN	
	END

CREATE	NONCLUSTERED INDEX IX_RptPhase ON #RptPhase (gpo_id, PaidMO)

UPDATE		t
SET		t.PaidMO = w.PaidMO 
FROM		rptPhase AS t
INNER JOIN	#RptPhase AS w ON w.gpo_id = t.Code
WHERE		t.YearMonth = @YearMonth

DROP TABLE	#RptPhase
GO

You easily supply 'B', 'D' or NULL as parameter for @status and there is no longer a need for three different stored procedures.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources