Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

Archive Job: CURRENT_TIMESTAMP is altering my recordset.

Given the following scenario, what is the most ideal/optimally performing means of identifying my data for archival?  I want to find the data, transfer it to the TradeHistory table, and then delete it from the Trade table.  I like this procedure below, but unfortuntately, this particular use of current_timestamp alters my recordset.  For example, I run right now and it finds 5000 records.  I transfer those to the history table.  Then the delete runs, but current_timestamp is seconds or minutes different - and the number of records it selects is actually less - so maybe only 4800 are deleted.  How would I change this procedure to just give select the entire previous day - to do the dateadd, but w/out the timestamp, possibly only using the date?  Please advise.


CREATE PROC dbo.usp_ArchiveData
(
      @CutOffDate datetime = NULL
)
AS
BEGIN
      SET NOCOUNT ON

      IF @CutOffDate IS NULL
      BEGIN
            SET @CutOffDate = DATEADD(dd, -1, CURRENT_TIMESTAMP)

      END

      BEGIN TRAN

            INSERT INTO Trading.dbo.TradeHistory
            SELECT *
            FROM dbo.Trade
            WHERE TimeOfExecution < @CutOffDate

            IF @@ERROR <> 0
            BEGIN
                  ROLLBACK TRAN
                  RAISERROR ('Error occured while copying data to Trading.dbo.TradeHistory', 16, 1)
                  RETURN -1
            END


            DELETE dbo.Trade
            WHERE TimeOfExecution < @CutOffDate

            IF @@ERROR <> 0
            BEGIN
                  ROLLBACK TRAN
                  RAISERROR ('Error occured while deleting data from dbo.Trade', 16, 1)
                  RETURN -1
            END

      IF @@TRANCOUNT > 0
      BEGIN
            COMMIT TRAN
            RETURN 0
      END

END
0
RLLewis
Asked:
RLLewis
  • 2
  • 2
1 Solution
 
RaisinJCommented:
Try this... it basically just puts the @IdField of all the records which satisfy your criteria into a temp table.  It then uses that table to perform the Insert and Delete.  This way, you're sure to Insert and Delete the same records.  You just need to replace the @IdField with an field from your dbo.Trade table which is a unquie identifier.

CREATE PROC dbo.usp_ArchiveData
(
     @CutOffDate datetime = NULL
)
AS
BEGIN
     SET NOCOUNT ON

     IF @CutOffDate IS NULL
     BEGIN
          SET @CutOffDate = DATEADD(dd, -1, CURRENT_TIMESTAMP)

     END

     BEGIN TRAN
              SELECT T.@IdField
              INTO #TempTable AS RecordID
          FROM dbo.Trade T
          WHERE T.TimeOfExecution < @CutOffDate

          INSERT INTO Trading.dbo.TradeHistory
          SELECT *
          FROM dbo.Trade T
               INNER JOIN #TempTable X ON X.RecordID = T.@IdField

          IF @@ERROR <> 0
          BEGIN
               ROLLBACK TRAN
               RAISERROR ('Error occured while copying data to Trading.dbo.TradeHistory', 16, 1)
               RETURN -1
          END


          DELETE T
               FROM dbo.Trade T
               INNER JOIN #TempTable X ON X.RecordID = T.@IdField

          IF @@ERROR <> 0
          BEGIN
               ROLLBACK TRAN
               RAISERROR ('Error occured while deleting data from dbo.Trade', 16, 1)
               RETURN -1
          END

     IF @@TRANCOUNT > 0
     BEGIN
          COMMIT TRAN
          RETURN 0
     END

END
0
 
RLLewisAuthor Commented:
Yes, I'd thought about this approach - unfortunately, I'm faced with a unique identifier that is thought to 'not always' be unique.  Understandably, I have a few things to resolve there.  For now, however, let's assume I really like this proc, and I want to revise it such that I can be sure I'm pulling all the data for a day, and that records inserted = records deleted.  Your suggestion seems quite reasonable, so what about concatenating two columns to create my unique identifier?  I don't want to add a new column and fill it w/the concatenation.  Rather, I'd just like to concatenate OrdNum with TimeOfExecution, and use the resulting value as the @IdField in your proposal.  A day of data is anywhere from 70 to 170K -- not really that many records.  And the two fields are varchar and datetime.  So, can this be done?  And if so, how best would I do that?  

Also, might there be a performance hit on this?    My proc is running through the transfer and delete of a day's data in 6 or so seconds.  The overall process will be handled after hours, but I need to keep processing time to a min, such that I can follow it up with my maintenance and backups and leave things clean/ready for the next day to start.

What do you think?
0
 
RLLewisAuthor Commented:
I attempted to use your suggestion - the proc creates, but fails continually with this error:  
Invalid object name '#TempTable'. [SQLSTATE 42S02] (Error 208)  Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1. [SQLSTATE 25000] (Error 266).  The step failed.
0
 
RaisinJCommented:
Sorry I did not get back to this, as for RLLewis's last comment, I beleive the problem was I forgot to include the "DROP TABLE" statement for the TempTable.  When it's created, it will remain in tempdb until you either drop it, or close the current connection your using.  Try this...

As for concant. two fields to form a solid unq_id, that'll work fine if that's the approach you want to take.  You could do the following with your TempTable...

This would go in place of the current statement which fetches the ID into the TempTable.
/**********************************************

SELECT T.@IdField, T.@SecondIDField
INTO #TempTable AS RecordID
FROM dbo.Trade T
WHERE T.TimeOfExecution < @CutOffDate

**********************************************/

Your insert statement would just be created as follows then...

/**********************************************

INSERT INTO Trading.dbo.TradeHistory
SELECT *
FROM dbo.Trade T
INNER JOIN #TempTable X ON
    X.RecordID = T.@IdField AND
    X.SecondRecordID = T.@SecondIDField

**********************************************/

Your delete statement would be...
/**********************************************

DELETE T
FROM dbo.Trade T
INNER JOIN #TempTable X ON
    X.RecordID = T.@IdField AND
    X.SecondRecordID = T.@SecondIDField

**********************************************/

CREATE PROC dbo.usp_ArchiveData
(
     @CutOffDate datetime = NULL
)
AS
BEGIN
     SET NOCOUNT ON

     IF @CutOffDate IS NULL
     BEGIN
          SET @CutOffDate = DATEADD(dd, -1, CURRENT_TIMESTAMP)

     END

     BEGIN TRAN
          SELECT T.@IdField
          INTO #TempTable AS RecordID
          FROM dbo.Trade T
          WHERE T.TimeOfExecution < @CutOffDate

          INSERT INTO Trading.dbo.TradeHistory
          SELECT *
          FROM dbo.Trade T
               INNER JOIN #TempTable X ON
                   X.RecordID = T.@IdField

          IF @@ERROR <> 0
          BEGIN
               ROLLBACK TRAN
               DROP TABLE #TempTable
               RAISERROR ('Error occured while copying data to Trading.dbo.TradeHistory', 16, 1)
               RETURN -1
          END


          DELETE T
          FROM dbo.Trade T
          INNER JOIN #TempTable X ON X.RecordID = T.@IdField

          IF @@ERROR <> 0
          BEGIN
               ROLLBACK TRAN
               DROP TABLE #TempTable
               RAISERROR ('Error occured while deleting data from dbo.Trade', 16, 1)
               RETURN -1
          END

     IF @@TRANCOUNT > 0
     BEGIN
          COMMIT TRAN
          DROP TABLE #TempTable
          RETURN 0
     END
END

Not looking for any points, just thought I'd try to finish what I started.  Hope this helps.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now