Optimizing Query

• The_SQL_DBA

  SSChampion

  Points: 10494

  More actions

  August 21, 2008 at 1:33 pm

  #192666

  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."

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  August 21, 2008 at 8:36 pm

  #861639

  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://www.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]

• Abhijit More

  SSCertifiable

  Points: 6897

  More actions

  August 22, 2008 at 3:35 am

  #861730

  this could be better implemented as mention below...

  IF OBJECT_ID('tempdb..#tbl') IS NOT NULL

  DROP TABLE #tbl

  CREATE TABLE #tbl(

  lngTransactionCnt intPRIMARY KEY CLUSTERED,

  strStatus varchar(2),

  strStatusDate varchar(8),

  strStatusTime varchar(4)

  )

  INSERT#tbl

  SELECTlngTransactionCnt, strStatus, strStatusDate, strStatusTime

  FROMtblTransactionHistoryCtmpA

  with (nolock)

  WHEREstrStatus in ('BP', 'BN', 'BF', 'BV', 'BO')

  and strOperator is NULL

  and strSource = 'FPBackground'

  and lngSiteNr = 654

  and lngTransactionCnt > 23577441

  and EXISTS (selectlngTransactionCnt from tblTransactionsC tmpB

  with(nolock)

  wherestrStatusDate > '20080412'

  and strStatus = 'BS'

  and lngSiteNr = 654

  and tmpA.lngTransactionCnt = tmpB.lngTransactionCnt

  )

  BEGIN TRY

  BEGIN TRANSACTION

  UPDATEtmpA

  SETtmpA.strStatus = @@strStatus

  FROMtblTransactionsCtmpA

  JOIN #tbltmpBON tmpA.lngTransactionCnt = tmpB.lngTransactionCnt

  DELETE

  FROMtmpA

  FROMtblTransactionsCtmpA

  JOIN #tbltmpBON 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