August 20, 2002 at 3:05 am
Can anyone help in trying to speed up this procedure, the Cursor crsPart returns 1.27 million rows, and the update is done on between 5 and 15 rows in the table.
Thanks in advance
Clive
CREATE PROCEDURE UP_TEST_TRANHISTORY_PARSE
AS
DECLARE @ccNo char(16),
@part_id char(10),
@LastStatementDate char(8),
@LastAccountSummary char(8)
DECLARE crsPart CURSOR FAST_FORWARD FOR
SELECT part_id, cc_nbr,
(Select start_dt
from crmsconv.dbo.fulhistory
where fulhistory_id in
(Select max(fulhistory_id)
from crmsconv.dbo.fulhistory_stmnt
where part_id = mbr.Part_ID
and code = 'STMNT'
and co_id = 'GF'
and prog_id = 'LOY')),
(Select max(tran_dt)
from CRMSconv.dbo.TRAN_MASTER
where co_id = 'GF'
and prog_id = 'LOY'
and part_id = mbr.part_id
and tran_desc = 'Conversion - Account Summary')
FROM CRMSConv.dbo.PART_MASTER mbr
OPEN crsPart
FETCH NEXT FROM crsPart INTO @part_id,
@ccNo,
@LastStatementDate,
@LastAccountSummary
WHILE @@fetch_status = 0
BEGIN
UPDATE FDE_TRANHISTORY SET LAST_STMNT_DATE = @LastStatementDate,
LAST_SUMMARY_DATE = @LastAccountSummary
WHERE accountnumber = @cardno
FETCH NEXT FROM crsPart INTO @part_id,
@ccNo,
@LastStatementDate,
@LastAccountSummary
END
CLOSE crsPart
DEALLOCATE crsPart
Clive Phillips
Clive Phillips
August 20, 2002 at 4:00 am
It is not entirely clear to me what this procedure is for. Can you give some answers on the following questions?
What are you trying to do with this procedure? If you say that only 5 to 15 records are updated, does that mean that, for each record from the cursor that many rows get updated? Does it mean that only 5 to 15 records in the FDE_TRANSHISTORY get updated in total (and why is this)?
What is the value of @cardno in the update query?
Anyway, I think it is quite possible to write this procedure in a single statement using the 'UPDATE ... FROM ...' syntax.
August 20, 2002 at 4:03 am
First off these two don't match
FETCH NEXT FROM crsPart INTO @part_id,
@ccNo,
@LastStatementDate,
@LastAccountSummary
UPDATE FDE_TRANHISTORY SET LAST_STMNT_DATE = @LastStatementDate,
LAST_SUMMARY_DATE = @LastAccountSummary
WHERE accountnumber = @cardno
I have four variables of which two are in the update query and a third in the update query I find no match for @cardno. Can you correct please, so I know for sure what I am looking at.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
August 20, 2002 at 4:18 am
Apologies, the procedure has been messed with, here is the correct version.
I have also run this as a Single update, and it succesfully update the first occurance of the accountnumber on the fde_tranhistory table, but left the LAST_STMNT_DATE and LAST_SUMMARY_DATE as NULL for the rest of the records matching the accountnumber.
CREATE PROCEDURE UP_TEST_TRANHISTORY_PARSE
AS
DECLARE @ccNo char(16),
@LastStatementDate char(8),
@LastAccountSummary char(8)
DECLARE crsPart CURSOR FAST_FORWARD FOR
SELECT cc_nbr,
(Select start_dt
from crmsconv.dbo.fulhistory
where fulhistory_id in
(Select max(fulhistory_id)
from crmsconv.dbo.fulhistory_stmnt
where part_id = mbr.Part_ID
and code = 'STMNT'
and co_id = 'GF'
and prog_id = 'LOY')),
(Select max(tran_dt)
from CRMSconv.dbo.TRAN_MASTER
where co_id = 'GF'
and prog_id = 'LOY'
and part_id = mbr.part_id
and tran_desc = 'Conversion - Account Summary')
FROM CRMSConv.dbo.PART_MASTER mbr
OPEN crsPart
FETCH NEXT FROM crsPart INTO @ccNo,
@LastStatementDate,
@LastAccountSummary
WHILE @@fetch_status = 0
BEGIN
UPDATE FDE_TRANHISTORY SET LAST_STMNT_DATE = @LastStatementDate,
LAST_SUMMARY_DATE = @LastAccountSummary
WHERE accountnumber = @ccNo
FETCH NEXT FROM crsPart INTO @ccNo,
@LastStatementDate,
@LastAccountSummary
END
CLOSE crsPart
DEALLOCATE crsPart
Clive Phillips
Clive Phillips
August 20, 2002 at 4:59 am
This should do the trick. However, you should take care that the (select) query only returns a single row for each cc_nbr.
UPDATE FDE_TRANHISTORY SET LAST_STMNT_DATE = temp.LastStatementDate,
LAST_SUMMARY_DATE = temp.LastAccountSummary
FROM
(SELECT cc_nbr,
(Select start_dt
from crmsconv.dbo.fulhistory
where fulhistory_id in
(Select max(fulhistory_id)
from crmsconv.dbo.fulhistory_stmnt
where part_id = mbr.Part_ID
and code = 'STMNT'
and co_id = 'GF'
and prog_id = 'LOY')) LastStatementDate,
(Select max(tran_dt)
from CRMSconv.dbo.TRAN_MASTER
where co_id = 'GF'
and prog_id = 'LOY'
and part_id = mbr.part_id
and tran_desc = 'Conversion - Account Summary') LastAccountSummary
FROM CRMSConv.dbo.PART_MASTER mbr ) temp
WHERE accountnumber = temp.cc_nbr
An additional gain might be possible if you add the following to the final WHERE clause (of the update statement) :
WHERE accountnumber = temp.cc_nbr
AND LAST_STMNT_DATE <> temp.LastStatementDate
AND LAST_SUMMARY_DATE <> temp.LastAccountSummary
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply