August 21, 2008 at 1:33 pm
I need to optimize the below script. The tblTransactionHistoryC has over 210 million rows and tblTransactionsC has over 26million rows. Its SQL 2000 DB on SP4.
-- MWD 08/19/08
-- Update all Transactions sitting incorrectly in BS Status to the appropriate status
-- And eliminate the last BS record
begin tran
-- Declare the cursor variables
Declare @@lngTransactionCnt int
Declare @@strStatus varchar(2)
Declare @@strStatusDate varchar(8)
Declare @@strStatusTime varchar(4)
-- Start the Cursor
-- Get the records where 'BS' follows 'BF'|'BN'|'BP'|'BV|BO'
Declare GISCursor CURSOR
For
Select lngTransactionCnt, strStatus, strStatusDate, strStatusTime
from tblTransactionHistoryC with (nolock)
where strStatus in ('BP', 'BN', 'BF', 'BV', 'BO') and strOperator is NULL and strSource = 'FPBackground' and lngSiteNr = 654 and lngTransactionCnt > 23577441 and lngTransactionCnt in
(select lngTransactionCnt from tblTransactionsC with (nolock) where strStatusDate > '20080412' and strStatus = 'BS' and lngSiteNr = 654)
order by lngTransactionCnt, strStatusDate, strStatusTime
Open GISCursor
-- Loop through
-- If the encountered record is 'BS', then delete it from the History Table
-- If it's one of the other B's, then update the transaction record to that status
Fetch Next from GISCursor into @@lngTransactionCnt, @@strStatus, @@strStatusDate, @@strStatusTime;
While @@Fetch_Status = 0
Begin
Update tblTransactionsC set strStatus = @@strStatus where lngTransactionCnt = @@lngTransactionCnt
Delete from tblTransactionHistoryC
where lngTransactionCnt = @@lngTransactionCnt and
strStatus = 'BS' and
strStatusDate + strStatusTime > @@strStatusDate + @@strStatusTime
Fetch Next from GISCursor into @@lngTransactionCnt, @@strStatus, @@strStatusDate, @@strStatusTime;
End
Close GISCursor
Deallocate GISCursor
Select lngTransactionCnt, strStatus, strStatusDate, strStatusTime from tblTransactionHistoryC where lngTransactionCnt in (26939755, 26903441);
select lngTransactionCnt, strStatus from tblTransactionsC where lngTransactionCnt in (26903441, 26939755);
Thanks in Advance!!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
August 21, 2008 at 8:36 pm
Please provide your table definitions. Some test data, and desired output would be nice too.
See this link for some guidelines on how to do this: http://qa.sqlservercentral.com/articles/Best+Practices/61537/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 22, 2008 at 3:35 am
this could be better implemented as mention below...
IF OBJECT_ID('tempdb..#tbl') IS NOT NULL
DROP TABLE #tbl
CREATE TABLE #tbl(
lngTransactionCnt int PRIMARY KEY CLUSTERED,
strStatus varchar(2),
strStatusDate varchar(8),
strStatusTime varchar(4)
)
INSERT #tbl
SELECT lngTransactionCnt, strStatus, strStatusDate, strStatusTime
FROM tblTransactionHistoryC tmpA
with (nolock)
WHERE strStatus in ('BP', 'BN', 'BF', 'BV', 'BO')
and strOperator is NULL
and strSource = 'FPBackground'
and lngSiteNr = 654
and lngTransactionCnt > 23577441
and EXISTS (select lngTransactionCnt from tblTransactionsC tmpB
with (nolock)
where strStatusDate > '20080412'
and strStatus = 'BS'
and lngSiteNr = 654
and tmpA.lngTransactionCnt = tmpB.lngTransactionCnt
)
BEGIN TRY
BEGIN TRANSACTION
UPDATE tmpA
SET tmpA.strStatus = @@strStatus
FROM tblTransactionsC tmpA
JOIN #tbl tmpB ON tmpA.lngTransactionCnt = tmpB.lngTransactionCnt
DELETE
FROM tmpA
FROM tblTransactionsC tmpA
JOIN #tbl tmpB ON tmpA.lngTransactionCnt = tmpB.lngTransactionCnt
and tmpA.strStatus = 'BS'
and tmpA.strStatusDate + tmpA.strStatusTime > tmpB.strStatusDate + tmpB.strStatusTime
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
Select lngTransactionCnt, strStatus, strStatusDate, strStatusTime from tblTransactionHistoryC where lngTransactionCnt in (26939755, 26903441);
select lngTransactionCnt, strStatus from tblTransactionsC where lngTransactionCnt in (26903441, 26939755);
IF OBJECT_ID('tempdb..#tbl') IS NOT NULL
DROP TABLE #tbl
Abhijit - http://abhijitmore.wordpress.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply