Query Optimazation from Cursor

Hi All,
I have a small T-SQL Bloack which is taking a long time to run. How can I
optimize this?
I have 2 tables "Donor" and "Donations"
Donor Table has "DonorID" and many columns and "SourceID" field. Donations
table has "DonationID", "DonorID", "DatePaid" and "SourceID" and many other
columns.
I want to update the Donor's SourceID (Only for the STATE = "CA") with
Donation's SourceID for that Donor. (Donor may have multiple donations - so
the donation record is the earliest datepaid of the donation)
My Block is below: ---
SET NOCOUNT ON
BEGIN
DECLARE @.DONORID INT,
@.SOURCEID VARCHAR(4),
@.DON_SOURCE VARCHAR(4),
@.TOTAL_UPDT INT,
@.PA_SF_CTR INT,
@.SF_PA_CTR INT,
@.SF_CTR INT,
@.PA_CTR INT,
@.PA_SF_ID INT,
@.SF_PA_ID INT,
@.MSD_ID INT,
@.PA_ID INT
DECLARE @.CONST_FLCC VARCHAR(4),
@.CONST_PA12 VARCHAR(4),
@.FOR_STATE VARCHAR(2)
SELECT @.CONST_FLCC = 'CC', @.CONST_PA12 = 'PA12', @.FOR_STATE = 'CA'
SELECT @.TOTAL_UPDT = 0, @.PA_SF_CTR = 0, @.SF_PA_CTR = 0, @.SF_CTR = 0,
@.PA_CTR = 0
SELECT @.PA_SF_ID = NULL, @.SF_PA_ID = NULL, @.MSD_ID = NULL, @.PA_ID = NULL
CREATE TABLE #DONORLIST
(
ID INT IDENTITY (1,1),
PA_SF_ID INT,
SF_PA_ID INT,
SF_ID INT,
PA_ID INT
)
DECLARE cur INSENSITIVE CURSOR FOR
SELECT DONORID, SOURCEID FROM DONOR WHERE STATE = @.FOR_STATE
FOR READ ONLY
OPEN cur
FETCH NEXT FROM cur INTO @.DONORID, @.DON_SOURCE
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT @.SOURCEID = NULL
SELECT TOP 1 @.SOURCEID = SOURCEID FROM DONATIONS
WHERE DONORID = @.DONORID
AND DATEPAID = (SELECT MIN(DATEPAID) FROM DONATIONS WHERE DONORID =
@.DONORID)
-- PA39 AND PA43 DONATION WILL MOVE TO PA12 FOR OLDEST DONATIONS
IF ((@.SOURCEID = 'PA39') OR (@.SOURCEID = 'PA43'))
SELECT @.SOURCEID = @.CONST_PA12
-- UPDATE THE DONOR SOURCE.
IF (@.DON_SOURCE <> @.SOURCEID)
BEGIN
--UPDATE DONOR SET SOURCEID = @.SOURCEID WHERE DONORID = @.DONORID
SELECT @.TOTAL_UPDT = @.TOTAL_UPDT + 1
END
-- PA TO SF
IF (@.DON_SOURCE = @.CONST_PA12 AND @.SOURCEID = @.CONST_FLCC)
BEGIN
SELECT @.PA_SF_CTR = @.PA_SF_CTR + 1
SELECT @.PA_SF_ID = @.DONORID
END
-- SF TO PA
ELSE IF (@.DON_SOURCE = @.CONST_FLCC AND @.SOURCEID = @.CONST_PA12)
BEGIN
SELECT @.SF_PA_CTR = @.SF_PA_CTR + 1
SELECT @.SF_PA_ID = @.DONORID
-- FIND DONATIONS THAT ARE HAVING PLEDGEDATE > '01 DEC 2004'.
END
-- SF TO SF (NO CHANGE)
ELSE IF (@.DON_SOURCE = @.CONST_FLCC AND @.SOURCEID = @.CONST_FLCC)
BEGIN
SELECT @.SF_CTR = @.SF_CTR + 1
SELECT @.MSD_ID = @.DONORID
END
-- PA TO PA (NO CHANGE)
ELSE IF (@.DON_SOURCE = @.CONST_PA12 AND @.SOURCEID = @.CONST_PA12)
BEGIN
SELECT @.PA_CTR = @.PA_CTR + 1
SELECT @.PA_ID = @.DONORID
END
INSERT INTO #DONORLIST (PA_SF_ID, SF_PA_ID, SF_ID, PA_ID)
VALUES (@.PA_SF_ID, @.SF_PA_ID, @.MSD_ID, @.PA_ID)
SELECT @.PA_SF_ID = NULL, @.SF_PA_ID = NULL, @.MSD_ID = NULL, @.PA_ID = NULL
SELECT @.DONORID = NULL, @.DON_SOURCE = NULL
FETCH NEXT FROM cur INTO @.DONORID, @.DON_SOURCE
END
CLOSE cur
DEALLOCATE cur
PRINT '1) Total DONOR SOURCE updated (SF to PA / PA to SF): ' +
CAST(@.TOTAL_UPDT AS VARCHAR)
PRINT '2) Total DONOR SOURCE updated from PA to SF: ' + CAST(@.PA_SF_CTR AS
VARCHAR)
PRINT '3) Total DONOR SOURCE updated from SF to PA: ' + CAST(@.SF_PA_CTR AS
VARCHAR)
PRINT '4) DONOR SOURCE was SF and did not change: ' + CAST(@.SF_CTR AS
VARCHAR)
PRINT '5) DONOR SOURCE was PA and did not change: ' + CAST(@.PA_CTR AS
VARCHAR)
PRINT REPLICATE('-', 100)
SELECT PA_SF_ID FROM #DONORLIST WHERE PA_SF_ID IS NOT NULL -- DONORS
CONVERTED FROM PA TO SF
SELECT SF_PA_ID FROM #DONORLIST WHERE SF_PA_ID IS NOT NULL -- DONORS
CONVERTED FROM SF TO PA
SELECT SF_ID FROM #DONORLIST WHERE SF_ID IS NOT NULL -- DONORS REMAINS AS
SF
SELECT PA_ID FROM #DONORLIST WHERE PA_ID IS NOT NULL -- DONORS REMAINS AS
PA
-- DROP TABLE #DONORLIST
END
GO
SET NOCOUNT OFF
=================================
The Donor and Donation table are very large table.
Please help.
Thanks
PrabhatPrabhat,
Don't use a cursor From what I see, you're only doing a single INSERT? You
could change this logic to use a
INSERT INTO #DONORLIST (...)
SELECT (...)
Your select statement would very likely use quite a number of CASE and
COALESCE, but it would most certainly be faster.
Try refactoring it that way.
-- Alex Papadimoulis
"Prabhat" wrote:

> Hi All,
> I have a small T-SQL Bloack which is taking a long time to run. How can I
> optimize this?
> I have 2 tables "Donor" and "Donations"
> Donor Table has "DonorID" and many columns and "SourceID" field. Donations
> table has "DonationID", "DonorID", "DatePaid" and "SourceID" and many othe
r
> columns.
> I want to update the Donor's SourceID (Only for the STATE = "CA") with
> Donation's SourceID for that Donor. (Donor may have multiple donations - s
o
> the donation record is the earliest datepaid of the donation)
> My Block is below: ---
> SET NOCOUNT ON
> BEGIN
> DECLARE @.DONORID INT,
> @.SOURCEID VARCHAR(4),
> @.DON_SOURCE VARCHAR(4),
> @.TOTAL_UPDT INT,
> @.PA_SF_CTR INT,
> @.SF_PA_CTR INT,
> @.SF_CTR INT,
> @.PA_CTR INT,
> @.PA_SF_ID INT,
> @.SF_PA_ID INT,
> @.MSD_ID INT,
> @.PA_ID INT
> DECLARE @.CONST_FLCC VARCHAR(4),
> @.CONST_PA12 VARCHAR(4),
> @.FOR_STATE VARCHAR(2)
> SELECT @.CONST_FLCC = 'CC', @.CONST_PA12 = 'PA12', @.FOR_STATE = 'CA'
> SELECT @.TOTAL_UPDT = 0, @.PA_SF_CTR = 0, @.SF_PA_CTR = 0, @.SF_CTR = 0,
> @.PA_CTR = 0
> SELECT @.PA_SF_ID = NULL, @.SF_PA_ID = NULL, @.MSD_ID = NULL, @.PA_ID = NULL
> CREATE TABLE #DONORLIST
> (
> ID INT IDENTITY (1,1),
> PA_SF_ID INT,
> SF_PA_ID INT,
> SF_ID INT,
> PA_ID INT
> )
> DECLARE cur INSENSITIVE CURSOR FOR
> SELECT DONORID, SOURCEID FROM DONOR WHERE STATE = @.FOR_STATE
> FOR READ ONLY
> OPEN cur
> FETCH NEXT FROM cur INTO @.DONORID, @.DON_SOURCE
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> SELECT @.SOURCEID = NULL
> SELECT TOP 1 @.SOURCEID = SOURCEID FROM DONATIONS
> WHERE DONORID = @.DONORID
> AND DATEPAID = (SELECT MIN(DATEPAID) FROM DONATIONS WHERE DONORID =
> @.DONORID)
> -- PA39 AND PA43 DONATION WILL MOVE TO PA12 FOR OLDEST DONATIONS
> IF ((@.SOURCEID = 'PA39') OR (@.SOURCEID = 'PA43'))
> SELECT @.SOURCEID = @.CONST_PA12
> -- UPDATE THE DONOR SOURCE.
> IF (@.DON_SOURCE <> @.SOURCEID)
> BEGIN
> --UPDATE DONOR SET SOURCEID = @.SOURCEID WHERE DONORID = @.DONORID
> SELECT @.TOTAL_UPDT = @.TOTAL_UPDT + 1
> END
> -- PA TO SF
> IF (@.DON_SOURCE = @.CONST_PA12 AND @.SOURCEID = @.CONST_FLCC)
> BEGIN
> SELECT @.PA_SF_CTR = @.PA_SF_CTR + 1
> SELECT @.PA_SF_ID = @.DONORID
> END
> -- SF TO PA
> ELSE IF (@.DON_SOURCE = @.CONST_FLCC AND @.SOURCEID = @.CONST_PA12)
> BEGIN
> SELECT @.SF_PA_CTR = @.SF_PA_CTR + 1
> SELECT @.SF_PA_ID = @.DONORID
> -- FIND DONATIONS THAT ARE HAVING PLEDGEDATE > '01 DEC 2004'.
> END
> -- SF TO SF (NO CHANGE)
> ELSE IF (@.DON_SOURCE = @.CONST_FLCC AND @.SOURCEID = @.CONST_FLCC)
> BEGIN
> SELECT @.SF_CTR = @.SF_CTR + 1
> SELECT @.MSD_ID = @.DONORID
> END
> -- PA TO PA (NO CHANGE)
> ELSE IF (@.DON_SOURCE = @.CONST_PA12 AND @.SOURCEID = @.CONST_PA12)
> BEGIN
> SELECT @.PA_CTR = @.PA_CTR + 1
> SELECT @.PA_ID = @.DONORID
> END
> INSERT INTO #DONORLIST (PA_SF_ID, SF_PA_ID, SF_ID, PA_ID)
> VALUES (@.PA_SF_ID, @.SF_PA_ID, @.MSD_ID, @.PA_ID)
> SELECT @.PA_SF_ID = NULL, @.SF_PA_ID = NULL, @.MSD_ID = NULL, @.PA_ID = NULL
> SELECT @.DONORID = NULL, @.DON_SOURCE = NULL
> FETCH NEXT FROM cur INTO @.DONORID, @.DON_SOURCE
> END
> CLOSE cur
> DEALLOCATE cur
> PRINT '1) Total DONOR SOURCE updated (SF to PA / PA to SF): ' +
> CAST(@.TOTAL_UPDT AS VARCHAR)
> PRINT '2) Total DONOR SOURCE updated from PA to SF: ' + CAST(@.PA_SF_CTR A
S
> VARCHAR)
> PRINT '3) Total DONOR SOURCE updated from SF to PA: ' + CAST(@.SF_PA_CTR A
S
> VARCHAR)
> PRINT '4) DONOR SOURCE was SF and did not change: ' + CAST(@.SF_CTR AS
> VARCHAR)
> PRINT '5) DONOR SOURCE was PA and did not change: ' + CAST(@.PA_CTR AS
> VARCHAR)
> PRINT REPLICATE('-', 100)
> SELECT PA_SF_ID FROM #DONORLIST WHERE PA_SF_ID IS NOT NULL -- DONORS
> CONVERTED FROM PA TO SF
> SELECT SF_PA_ID FROM #DONORLIST WHERE SF_PA_ID IS NOT NULL -- DONORS
> CONVERTED FROM SF TO PA
> SELECT SF_ID FROM #DONORLIST WHERE SF_ID IS NOT NULL -- DONORS REMAINS
AS
> SF
> SELECT PA_ID FROM #DONORLIST WHERE PA_ID IS NOT NULL -- DONORS REMAINS
AS
> PA
> -- DROP TABLE #DONORLIST
> END
> GO
> SET NOCOUNT OFF
>
> =================================
> The Donor and Donation table are very large table.
> Please help.
> Thanks
> Prabhat
>
>|||Hi,
The Insert is not Important. I am inserting to the Temp table to know the
Donor Records that are getting updated.
That is my fault. The Update statement was commented by mistake. Actually I
wanted to Update the Donrs SourceID from the Donations SourceID as per the
Logic in the Cursor.
The Insert is Just to keep track of the Donor Records that are getting
updated for different Instance only.
Thanks
Prabhat
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:D2866C6E-6255-4E9F-95BC-EB1E99B72947@.microsoft.com...
> Prabhat,
> Don't use a cursor From what I see, you're only doing a single INSERT? You
> could change this logic to use a
> INSERT INTO #DONORLIST (...)
> SELECT (...)
> Your select statement would very likely use quite a number of CASE and
> COALESCE, but it would most certainly be faster.
> Try refactoring it that way.
> -- Alex Papadimoulis
> "Prabhat" wrote:
>
I
Donations
other
so
NULL
NULL
AS
AS
REMAINS AS
REMAINS AS|||The cursor and loop is surely unnecessary but it's difficult to
understand what you want from this incomplete fragment of code. The
best way to get help is to post DDL (CREATE TABLE statements), sample
data (INSERT statements) and show your required end result. See:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Hi David,
Just see the "Help need for Query - Urgent" Subject in this group for the
details.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1116319917.557377.111950@.f14g2000cwb.googlegroups.com...

> The cursor and loop is surely unnecessary but it's difficult to
> understand what you want from this incomplete fragment of code. The
> best way to get help is to post DDL (CREATE TABLE statements), sample
> data (INSERT statements) and show your required end result. See:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>