Trouble w/Stored Proc

[dvader0571]

This is bizarre to me however, hopefully someone can explain what is happening here.
When I check the execution plan of the following query:

SELECT A.TRANDTTM
FROM [imsv7].ACCTTRAN A, [imsv7].CUSTBL C
WHERE A.ACCTKEY = @dwAcctKey AND A.BLKEY = C.BLKEY
AND A.TRANDTTM >= @tsDueDate AND C.DUEDATE <= @tsDueDate

It correctly uses a Clustered index ACTTRANAX in the ACCTTRAN table for the AcctKey field.

CREATE CLUSTERED INDEX [ACTRANAX] ON [IMSV7].[ACCTTRAN]([ACCTKEY]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO



However, as soon as I add this query to a Stored Procedure and check the Execution Plan, it decides to use a completely non-related index ACTRANKX instead.

CREATE UNIQUE INDEX [ACTRANKX] ON [IMSV7].[ACCTTRAN]([ACTRANNO]) ON [PRIMARY]
GO

I am frustrated with this because the 93 milliseconds is going to be a killer with 93,000 accounts it needs to process (roughly 139 minutes), as opposed to the 3 milliseconds when it uses the correct index.

Any ideas would be of great help thanks!

 

[SQLSister]

What else are you doing in the stored procedure?

Questions about posting. See faq183-874

 

[mrdenny]

Is this what you are getting with the estimated execution plan, or the actual executaton plan? They can be different. You can force SQL to use the index you want via index hints.

SELECT  A.TRANDTTM
FROM [imsv7].ACCTTRAN A INDEX(ACTRANKX), [imsv7].CUSTBL C
WHERE A.ACCTKEY = @dwAcctKey AND A.BLKEY = C.BLKEY
 AND A.TRANDTTM >= @tsDueDate AND C.DUEDATE <= @tsDueDate

(My syntax might be off here, check "index hints" in BOL.)

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLSister]

Again I ask, what else are you doing? a simple select should never take 139 minutes no matter what the execution plan is.



Questions about posting. See faq183-874

 

[dvader0571]

I tried a Stored Proc with nothing but the SQL statement and Declaration of the variables used in it. Like this:

CREATE PROCEDURE imsv7.testy ( @dwAcctKey NUMERIC, @tsArchiveDate DATETIME )
AS

DECLARE @tsDueDate datetime

SELECT @tsDueDate = MAX( C.DUEDATE )
FROM [imsv7].CUSTBL C
WHERE C.ACCTKEY = @dwAcctKey AND C.DUEDATE < @tsArchiveDate

SELECT A.TRANDTTM
FROM [imsv7].ACCTTRAN A, [imsv7].CUSTBL C
WHERE A.ACCTKEY = @dwAcctKey AND A.BLKEY = C.BLKEY
AND A.TRANDTTM >= @tsDueDate AND C.DUEDATE <= @tsDueDate


Thanks! I will look into forcing it, however I was just questioning why the Stored Procedure will use a different index then the query via Query Analyzer if they are the same. (Both selects are used in both cases).

 

[dvader0571]

Well this select is run for each account in the database. What it is doing this for is to identify which accounts qualify for being Archived out of the active tables and stored in the archive (history) tables. So in order to know which accounts need to be moved each one has to be checked against this SQL statement. Currently it is a SQL statement executed via ODBC for each acoount ( around 93,000 accounts ). So I was taking a look to see if a compiled Stored Proc would be faster.

 

[SQLSister]

Why are you going to check each account separately?

Why not just look for all the records you need to archive in one step?

Questions about posting. See faq183-874

 

[remulus]

If you want to force the select statement to use a particular index for query optimization, try table-level locking hints on your select statement.

remulus
ecommerce developer

 

[dvader0571]

These are all good suggestions.

SQLSister in answer to yours, this program was written before my time and it is initiated by a C++ program we are writing in house. The user gets to see a display of # accounts Processed. # accounts archived. Sort of thing. I am trying to think of ways to do it in one chunk and still show this, however, nothing seems to speed it up. (Aside from a major DB re-design) I thought I'd try stored procedures.

I am not very familiar with output variables, but if there is a way to output an incremented variable with each loop inside an SP that would yield the same results as well wouldn't it?

 

[dvader0571]

Okay, I'm changing my approach. Going to pull all the accounts , minus the ones I can exclude right away and processes them via a cursor to filter even more out. Then archive the remaining ones.

I appreciate the suggestions!

 

[SQLSister]

Why use a cursor? Cursors are notoriously inefficient. If you share with us the conditions you want to use to filter recods, we can help you get a set-based approach.

The reason why you don;t use cursors or while loops is that they require each record to be processed individualy. 1,000,000 records = 1,000,000 calls to the database. In a set-based approach, 1,00,000 and 1 call to the database. Getting rid of any looping by using set-based SQL will change processes from minutes or hours to millseconds or seconds (Or very possibly minutes if something very complex is done). What you need to do is stop thinking in terms of processing records and start thinking interms of processing a set of records simultaneously.

Questions about posting. See faq183-874

 

[dvader0571]

That is the way I have been thinking for years. I stayed away from cursors for that very reason. However, I used an example of an SP that the company uses and it dropped then processing time from 4 days to 1 hour. It is possible that removing the cursor from this stored procedure may make it even faster than that. Keep in mind that this is an Archiving Utility and it is meant to run after hours on weekends. Getting it to finish in 2 and a half days is my goal ( as opposed to it taking up to 17 days ). The client that is running this has not archived in 5 years and has in excess of 3 million records in there DB. The archived will remove at least 12 million of those. My boss is happy with this SP but I will include the code here anyway, if you have other ideas about it please post them as changing the SP is easy for testing purposes.

if exists (select * from dbo.sysobjects where id = object_id(N'[imsv7].[ArchiveUtility_Interface]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [imsv7].[ArchiveUtility_Interface]
GO


CREATE PROCEDURE IMSV7.ArchiveUtility_Interface ( @tsArchiveDate DATETIME, @accttype varchar(4) )
/*
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
-- Stored Procedure/Function Name - ArchiveUtility_Interface
-- Database - MS Sql Server
-- Client - Bexar
-- Created By: Marcy Landry
-- Created On: 7-12-04
-- Modified By: Adam Murray
-- Last Modified: 8-18-04
--
-- Description: The Interface will process all of the accounts with a DUE DATE thats
-- less than the specified ARCHIVE DATE and a PAIDSTAT of 'Y'. These accounts will be
-- further processed to exclude records where:
-- accounts with TranDate >= MAX(DueDate) & DueDate <= MAX(DueDate)
-- accounts with TranDate >= MAX(DueDate) & PayDate < MAX(DueDate)
-- accounts with higher or less than 0 balance
--
-- Archive will only occur for:
-- only records where DueDate <= MAX(DueDate) & TranDate < MAX(DueDate) will be archived
-- only records where PayDate < MAX(DueDate) & TranDate < MAX(DueDate) will be archived
-- only accounts with 0 balance
--
-- For archived accounts Update the DEPTRAN table SET BLKEY = 1, BLITEMKEY = 1, PAYKEY = 1
-- Delete accounts from tables that were archived
-- ACCTCHG, TRACKRD, BLITEM, ACCTPAY, ACCTTRAN, BLINSR, CUSTBL, BLITEMRT, BBTRAN
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

Return AcctKey, BlKey w/Max Due Date from ACCOUNT and CUSTBL table
for each BlKey check the TranDate
then each BlKey left check the PayDate
for those accounts left check the balances
*/

AS

DECLARE

@lRunNo NUMERIC(9),
@szIntConvName VARCHAR(80),
@szProcName VARCHAR(80),
@tsAddDtTm DATETIME,
@szAddBy VARCHAR(80),
@lCount NUMERIC(9),
@dwAcctKey INT,
@tsDueDate DATETIME,
@dwTranPR FLOAT,
@dwTranPE FLOAT,
@dwTranAdj FLOAT,
@dwTranOvr FLOAT,
@dwTranTotal FLOAT

-- Need AcctKey and MAX(DueDate) for each account that has PAIDSTAT = 'Y'
Declare cStartArchive Cursor For
Select A.AcctKey, MAX(DueDate) As MaxDueDate
From [imsv7].Account A Inner Join [imsv7].CustBl C
On ( A.AcctKey = C.AcctKey )
Where ( ( @AcctType <> '' AND A.AcctType = @AcctType ) OR ( @AcctType = '' ) )
And C.DueDate < @tsArchiveDate And C.PaidStat = 'Y'
Group By A.AcctKey

if exists (select * from dbo.sysobjects where id = object_id(N'[IMSV7].[ARCHIVEACCTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Drop Table IMSV7.ARCHIVEACCTS

-- create a table to hold the acctkeys to be archived
Create Table IMSV7.ARCHIVEACCTS ( ArchID int IDENTITY(1,1), AcctKey int, DueDate datetime )

BEGIN

SET @lRunNo = 0
SET @szIntConvName = 'ARCHIVEUTILITY INTERFACE'
SET @szProcName = 'ARCHIVEUTILITY INTERFACE'
SET @tsAddDtTm = GETDATE()
SET @szADDBY = 'ARCHIVEUTILITY INTERFACE'
------------------------------------------------------

----- init
Set @dwAcctKey = 0
Set @tsDueDate = NULL
Set @dwTranPR = 0.00000
Set @dwTranPE = 0.00000
Set @dwTranAdj = 0.00000
Set @dwTranOvr = 0.00000
Set @dwTranTotal = 0.00000

Open cStartArchive --Open Cursor

Fetch Next From cStartArchive Into
@dwAcctKey, @tsDueDate

IF (@@ERROR <> 0 )
BEGIN
return @@ERROR
END

While @@Fetch_status <> -1
Begin
-- only records where TranDate is < MaxDueDate AND DueDate <= MaxDueDate will be archived
-- transaction falls after due date should not be archived
Select @lCount = Count(1)
From [imsv7].AcctTran A Inner Join [imsv7].CustBl C
On ( A.AcctKey = C.AcctKey And A.BlKey = C.BlKey )
Where A.AcctKey = @dwAcctKey And A.TranDtTm >= @tsDueDate And C.DueDate <= @tsDueDate

IF @lCount = 0
BEGIN
-- This account is good so far
-- only records where PayDate is < MaxDueDate AND DueDate <= MaxDueDate will be archived
-- transaction falls after the pay date should not be archived
Select @lCount = Count(1)
From [imsv7].AcctTran A Inner Join [imsv7].AcctPay P
On ( A.AcctKey = P.AcctKey And A.PayKey = P.PayKey )
Where A.AcctKey = @dwAcctKey And A.TranDtTm >= @tsDueDate And P.PayDtTm <= @tsDueDate
END

IF @lCount = 0
BEGIN
-- and now to check the balances
Select @dwTranPR = SUM(CASE A.TRANDES WHEN 'PR' THEN CAST(A.TRANAMT As DECIMAL(10,2)) ELSE 0.00 END),
@dwTranPE = SUM(CASE A.TRANDES WHEN 'PE' THEN CAST(A.TRANAMT As DECIMAL(10,2)) ELSE 0.00 END),
@dwTranAdj = SUM(CASE WHEN A.TRANTYPE IN ('AADJ', 'APAY', 'AREF', 'ADRP') THEN CAST(A.TRANAMT As DECIMAL(10,2)) ELSE 0.00 END),
@dwTranOvr = SUM(CASE WHEN A.TRANTYPE IN ('OVER', 'OPAY', 'OREF', 'ODRP') THEN CAST(A.TRANAMT As DECIMAL(10,2)) ELSE 0.00 END),
@dwTranTotal = ISNULL(SUM(CAST(A.TRANAMT As DECIMAL(10,2))), 0)
From IMSV7.ACCTTRAN A
Where A.ACCTKEY = @dwAcctKey AND A.TRANDTTM <= @tsDueDate AND A.TRANAMT IS NOT NULL

Select @lCount = @dwTranPR + @dwTranPE + @dwTranAdj + @dwTranOvr + @dwTranTotal
END

IF @lCount = 0
BEGIN
-- account passed begin archiving!
-- dump Archive Status into table
Insert Into IMSV7.ARCHIVEACCTS ( AcctKey, DueDate )
Select @dwAcctKey, @tsDueDate
END

----- re-init
Set @dwAcctKey = 0
Set @tsDueDate = NULL
Set @dwTranPR = 0.00000
Set @dwTranPE = 0.00000
Set @dwTranAdj = 0.00000
Set @dwTranOvr = 0.00000
Set @dwTranTotal = 0.00000


Fetch Next From cStartArchive Into
@dwAcctKey, @tsDueDate


End --While

Close cStartArchive
Deallocate cStartArchive
--COMMIT

END
GO

 

[dvader0571]

The next step is still the slowest. The SP above will identify 37044 accounts out of a total of 97000 accounts that "qualify" for archiving. So now I need to work a stored procedure complete with Selects, Inserts and Deletes to actually clear the tables of 37044 accounts and their individual records ( estimated at around 12-20 million of them total ).

Note: the time signatures are for testing only and won't be in the "Live" version. The commented parts indicate the original approach was 1 account at a time. I am trying to utilize the value of a Clustered Index by trying to process more than one account at a time ( first try 1000 ) but any amount can be used.


if exists (select * from dbo.sysobjects where id = object_id(N'[IMSV7].[ARCHIVEACCT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [IMSV7].[ARCHIVEACCT]
GO


/* PROCEDURE: ARCHIVEACCT */
/* This is called from the Archive Utility for Billing. */
/* PURPOSE: This procedure archives transactions, bills, line items, payments */
/* and service charges for the account. */
/* PARAMETERS: dwAcctKey NUMBER = The account to archive */
/* tsArchiveDate DATE = The user-specified "Archive Prior To" date. */


-- NOTE THIS HAS BEEN MODIFIED TO RUN ARCHIVE FOR BEXAR ON JERRYSQLSERVER!!!!!

CREATE procedure IMSV7.ARCHIVEACCT ( @dwAcctKey NUMERIC, @tsDueDate DATETIME, @dwStartChunk int, @dwCurrChunk int )
AS

BEGIN

/* */
/* ARCHIVE THE ACTIVE RECORDS */
/* */
Declare @seq int, @theDate datetime
Set @seq = (Select (Case When Max(Seq) IS NULL Then 1 Else Max(Seq) + 1 END) From imsv7.ArchiveTracking)
Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'Begin', @seq, @theDate)

-- Setup temp table to hold the acctkeys, duedates to process
DECLARE @tmp TABLE (AcctKey int, DueDate datetime)
Insert Into @tmp (AcctKey, DueDate)
SELECT AcctKey, DueDate
FROM IMSV7.ARCHIVEACCTS
WHERE ArchID BETWEEN @dwStartChunk AND @dwCurrChunk

/* Copy BILLS to the archive table */
/* Stage 2 through 8 process keys have been left out */
/* MESSAGES column is excluded - lvarchar columns may not be copied */
INSERT INTO ARCHBL
(BLKEY, ACCTKEY, BLNO, BLSTGKEY, BLTYPEKEY, ADDBY, ADDDTTM,
ADDR1, ADDR2, ADJAMT, BILLAMT, BLDATE, BLPERFRDT, BLPERTODT, BLTO,
CAREOFKEY, CITY, COUNTRY, CREDFLAG, CURRAMT, DEPAMT, DIRDBKEY,
DLNQAMT, DLNQDATE, DUEDATE, EVENTID, EXCLFLAG, FINALFLAG, INCPREVACT,
INTAMT, MODBY, MODDTTM, NOBILL, OVERAMT, PAIDDTTM, PAIDSTAT, PAYABLETO,
PAYORDER, PENAMT, PENEXEMPT, PRNCPLTOT, PROPADDR, STATE, ZIP, ARNGPAYAMT,
DISPAMT, VARIAMT, ARNGAMT, LIENAMT, COLLAMT, DEPDUEAMT, PENDFLAG, PENDTYPE,
DAYSRDS, PENFLAG, POSITION, AGGRBLKEY, INSTBLKEY, ACTAMT, BANKRPTKEY, COLLKEY, LIENKEY,
ACTUALAMT, ACTUALUSG, AGGRSTGKEY, BGTBLUSG, BILLADJAMT,
CUMVARIAMT, DISCEXPAMT, DISCEXPDT, INSTNO,
INSTTOTAMT, SETLAMT, STG2PRCKEY, STG3PRCKEY, STG4PRCKEY, STG5PRCKEY,
STG6PRCKEY, STG7PRCKEY, STG8PRCKEY)
SELECT
BLKEY, C.ACCTKEY, BLNO, BLSTGKEY, BLTYPEKEY, ADDBY, ADDDTTM,
ADDR1, ADDR2, ADJAMT, BILLAMT, BLDATE, BLPERFRDT, BLPERTODT, BLTO,
CAREOFKEY, CITY, COUNTRY, CREDFLAG, CURRAMT, DEPAMT, DIRDBKEY,
DLNQAMT, DLNQDATE, C.DUEDATE, EVENTID, EXCLFLAG, FINALFLAG, INCPREVACT,
INTAMT, MODBY, MODDTTM, NOBILL, OVERAMT, PAIDDTTM, PAIDSTAT, PAYABLETO,
PAYORDER, PENAMT, PENEXEMPT, PRNCPLTOT, PROPADDR, STATE, ZIP, ARNGPAYAMT,
DISPAMT, VARIAMT, ARNGAMT, LIENAMT, COLLAMT, DEPDUEAMT, PENDFLAG, PENDTYPE,
DAYSRDS, PENFLAG, POSITION, AGGRBLKEY, INSTBLKEY, ACTAMT, BANKRPTKEY, COLLKEY, LIENKEY,
ACTUALAMT, ACTUALUSG, AGGRSTGKEY, BGTBLUSG, BILLADJAMT,
CUMVARIAMT, DISCEXPAMT, DISCEXPDT, INSTNO,
INSTTOTAMT, SETLAMT, STG2PRCKEY, STG3PRCKEY, STG4PRCKEY, STG5PRCKEY,
STG6PRCKEY, STG7PRCKEY, STG8PRCKEY
FROM @tmp A inner join imsv7.CUSTBL C ON (A.AcctKey = C.AcctKey AND A.DueDate = C.DueDate)
--WHERE C.ACCTKEY = @dwAcctkey
--AND C.DUEDATE <= @tsDueDate;

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'S/I CustBl', @seq, @theDate)

/* Copy SERVICE CHARGES to the archive table*/
INSERT INTO ARCHCHG
(ACCHGKEY, ACCTKEY, SRVCHGKEY, ADDBY, ADDDTTM, BLKEY, CHGBY, CHGDTTM,
MODBY, MODDTTM, OVRBY, SERVNO, SRVCHGVAL, WAIVEDBY, WAIVELEV)
SELECT
ACCHGKEY, A.ACCTKEY, SRVCHGKEY, ADDBY, ADDDTTM, A.BLKEY, CHGBY, CHGDTTM,
MODBY, MODDTTM, OVRBY, SERVNO, SRVCHGVAL, WAIVEDBY, WAIVELEV
FROM (
Select A.AcctKey, B.BlKey
FROM @tmp A inner join imsv7.CustBl B ON (A.AcctKey = B.AcctKey AND A.DueDate = B.DueDate)
) A Inner Join imsv7.ACCTCHG C ON (A.AcctKey = C.AcctKey And A.BlKey = C.BlKey)
--WHERE ACCTKEY = @dwAcctkey
--AND BLKEY IN (SELECT B.BLKEY FROM CUSTBL B
--WHERE B.ACCTKEY = @dwAcctkey
--AND B.DUEDATE <= @tsDueDate);

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'S/I AcctChg', @seq, @theDate)

/* Copy TRACKED READINGS to the archive table */
INSERT INTO ARCHTKRD
(ACCTKEY, COMPRDKEY, SRVKEY, ADDBY, ADDDTTM, BILLEDFLAG, BLKEY,
MODBY, MODDTTM, TRACKRDKEY, BLTYPEKEY)
SELECT
A.ACCTKEY, COMPRDKEY, SRVKEY, ADDBY, ADDDTTM, BILLEDFLAG, A.BLKEY,
MODBY, MODDTTM, TRACKRDKEY, BLTYPEKEY
FROM (
Select A.AcctKey, B.BlKey
FROM @tmp A inner join imsv7.CustBl B ON (A.AcctKey = B.AcctKey AND A.DueDate = B.DueDate)
) A Inner Join imsv7.TRACKRD C ON (A.AcctKey = C.AcctKey And A.BlKey = C.BlKey)
/*WHERE ACCTKEY = @dwAcctkey
AND BLKEY IN (SELECT B.BLKEY FROM CUSTBL B
WHERE B.ACCTKEY = @dwAcctkey
AND B.DUEDATE <= @tsDueDate);*/

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'S/I TrackRD', @seq, @theDate)

/* Copy LINE ITEMS to the archive table - no lvarchar column */
/* Deposit, interest and dispute columns are not archived */
INSERT INTO ARCHLI
(BLITEMKEY, ACCTKEY, ARNGFLAG, DUNEXEMPT, BLITEM, BLKEY, BLSTGKEY, ACCHGKEY, ADDBY, ADDDTTM,
ASDVALFLAG, BLITEMAMT, BLITEMGRP, BLITEMRATE, BLITEMTYPE, BLUSG, BLVAL, CALCORDER,
CNSVALFLAG, COMBINED, DONATION, DISCOUNT, EFFLUENT, FCONDKEY, FINTKEY, FPNLTYKEY,
FVALKEY, MODBY, MODDTTM, PAIDSTAT, PENCBGTNO, PENPAYORD, PENPBGTNO,
PRNCCBGTNO, PRNCPAYORD, PRNCPBGTNO, PRNORDER, PRNTTEXT, PRORATE,
RATEFLAG, RDBASED, REFTEXT, SRVAREA, SRVBASED, SRVCLASS,
SRVKEY, SRVTYPE, SRVVAL1, SRVVAL2, SRVVAL3, SURCHG, TAXAREA, PENDFLAG,
PENDTYPE, WINTERAVG, FDSCEXPKEY, DISCEXPDT, ITEMSUKEY, PENCHGFLAG, PENEXEMPT,
DEPBGTNO, DEPOSIT, DSPADDBY, DSPDESC, DSPDTTM, DSPREAS, DSPRES, DSPRESBY, DSPRESDTTM, INTBGTNO, USGFLAG)
SELECT
BLITEMKEY, A.ACCTKEY, ARNGFLAG, DUNEXEMPT, BLITEM, A.BLKEY, BLSTGKEY, ACCHGKEY, ADDBY, ADDDTTM,
ASDVALFLAG, BLITEMAMT, BLITEMGRP, BLITEMRATE, BLITEMTYPE, BLUSG, BLVAL, CALCORDER,
CNSVALFLAG, COMBINED, DONATION, DISCOUNT, EFFLUENT, FCONDKEY, FINTKEY, FPNLTYKEY,
FVALKEY, MODBY, MODDTTM, PAIDSTAT, PENCBGTNO, PENPAYORD, PENPBGTNO,
PRNCCBGTNO, PRNCPAYORD, PRNCPBGTNO, PRNORDER, PRNTTEXT, PRORATE,
RATEFLAG, RDBASED, REFTEXT, SRVAREA, SRVBASED, SRVCLASS,
SRVKEY, SRVTYPE, SRVVAL1, SRVVAL2, SRVVAL3, SURCHG, TAXAREA, PENDFLAG,
PENDTYPE, WINTERAVG, FDSCEXPKEY, DISCEXPDT, ITEMSUKEY, PENCHGFLAG, PENEXEMPT,
DEPBGTNO, DEPOSIT, DSPADDBY, DSPDESC, DSPDTTM, DSPREAS, DSPRES, DSPRESBY, DSPRESDTTM, INTBGTNO, USGFLAG
FROM (
Select A.AcctKey, B.BlKey
FROM @tmp A inner join imsv7.CustBl B ON (A.AcctKey = B.AcctKey AND A.DueDate = B.DueDate)
) A Inner Join imsv7.BLITEM C ON (A.AcctKey = C.AcctKey And A.BlKey = C.BlKey)
/*WHERE B.ACCTKEY = @dwAcctkey
AND B.BLKEY IN (SELECT C.BLKEY FROM CUSTBL C
WHERE C.ACCTKEY = @dwAcctkey
AND C.DUEDATE <= @tsDueDate);*/

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'S/I BlItem', @seq, @theDate)

/* Copy PAYMENTS To the archive table */
INSERT INTO ARCHPAY
(PAYKEY, ACCTKEY, BATCHKEY, TRACENO, ACCTNO, ACCTTYPE, ADDBY,
ADDDTTM, BLTYPEKEY, CARDAUTH, CARDBANK, CARDEXP, CARDNAME,
CARDNO, CARDTYPE, CHECKBANK, CHECKID, CHECKNAME, CHECKNO,
MODBY, MODDTTM, PAYAMT, PAYDTTM, PAYMETHOD, PAYSTAT, RECDBY,
REGTRANNO, RESBGTNO, RESBY, RESCODE, RESDTTM, SRC, UNMATCRES,
DONATEFLAG, DONATEAMT, AGGRBLKEY,
MISCISSUED, MISCREFNO, MISCTYPE, PENDTYPE)
SELECT
PAYKEY, A.ACCTKEY, BATCHKEY, TRACENO, ACCTNO, ACCTTYPE, ADDBY,
ADDDTTM, BLTYPEKEY, CARDAUTH, CARDBANK, CARDEXP, CARDNAME,
CARDNO, CARDTYPE, CHECKBANK, CHECKID, CHECKNAME, CHECKNO,
MODBY, MODDTTM, PAYAMT, PAYDTTM, PAYMETHOD, PAYSTAT, RECDBY,
REGTRANNO, RESBGTNO, RESBY, RESCODE, RESDTTM, SRC, UNMATCRES,
DONATEFLAG, DONATEAMT, AGGRBLKEY,
MISCISSUED, MISCREFNO, MISCTYPE, PENDTYPE
FROM @tmp A inner join imsv7.ACCTPAY C ON (A.AcctKey = C.AcctKey AND A.DueDate = C.PAYDTTM)
/*WHERE P.ACCTKEY = @dwAcctkey
AND P.PAYDTTM <= @tsDueDate;*/

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'S/I AcctPay', @seq, @theDate)

/* Copy TRANSACTIONS to the archive table - no lvarchar column*/
INSERT INTO ARCHTRAN
(ACTRANNO, ACCTKEY, TRANTYPE, ADDBY, ADDDTTM, ADJREAS, ADJTYPE, APPROVBY, AUTH,
BGTNO, BLITEMKEY, BLKEY, BLSTGKEY, BLTYPEKEY, COMMENTS, DLNQDATE, EVENTID,
MASSADJKEY, MODBY, MODDTTM, PAIDSTAT, PAYKEY, PENASMKEY, REFTRANNO,
TRANAMT, TRANBY, TRANDES, TRANDTTM, PENDFLAG, PENDTYPE)
SELECT
ACTRANNO, A.ACCTKEY, TRANTYPE, ADDBY, ADDDTTM, ADJREAS, ADJTYPE, APPROVBY, AUTH,
BGTNO, BLITEMKEY, BLKEY, BLSTGKEY, BLTYPEKEY, COMMENTS, DLNQDATE, EVENTID,
MASSADJKEY, MODBY, MODDTTM, PAIDSTAT, PAYKEY, PENASMKEY, REFTRANNO,
TRANAMT, TRANBY, TRANDES, TRANDTTM, PENDFLAG, PENDTYPE
FROM @tmp A inner join imsv7.AcctTran C ON (A.AcctKey = C.AcctKey AND A.DueDate = C.TRANDTTM)
/*WHERE A.ACCTKEY = @dwAcctkey
AND A.TRANDTTM <= @tsDueDate;*/

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'S/I AcctTran', @seq, @theDate)

UPDATE DEPTRAN
SET BLKEY = 1, BLITEMKEY = 1
WHERE ACCTKEY IN (Select AcctKey FROM @tmp)
AND BLKEY IN ( Select B.BlKey
FROM @tmp A inner join imsv7.CustBl B
ON (A.AcctKey = B.AcctKey AND A.DueDate = B.DueDate) );

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'Upd DepTran From CustBl', @seq, @theDate)

UPDATE DEPTRAN
SET PAYKEY = 1
WHERE ACCTKEY IN (Select AcctKey FROM @tmp)
AND PAYKEY IN ( Select B.PAYKEY
FROM @tmp A inner join imsv7.ACCTPAY B
ON (A.AcctKey = B.AcctKey AND A.DueDate = B.PAYDTTM) );

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'Upd DepTran From AcctPay', @seq, @theDate)


/* Copy LINE ITEMS RT to the archive table - no lvarchar column */
INSERT INTO ARCHLIRT
(BLITEMKEY, ACCTKEY, BLITRTKEY, BLSTGKEY, ADDBY, ADDDTTM, FULLAMT, PRORATEAMT,
MODBY, MODDTTM, RTBASECHG, RTMAXCHG, RTMINCHG,
RTCHG1, RTCHG2, RTCHG3, RTCHG4, RTCHG5, RTCHG6, RTCHG7, RTCHG8, RTCHG9, RTCHG10,
RTRATE1, RTRATE2, RTRATE3, RTRATE4, RTRATE5, RTRATE6, RTRATE7, RTRATE8, RTRATE9, RTRATE10,
RTUSG1, RTUSG2, RTUSG3, RTUSG4, RTUSG5, RTUSG6, RTUSG7, RTUSG8, RTUSG9, RTUSG10,
RTRATECODE, RTSTDDAYS, RTUNIT, RTUOM, RTUSGDAYS, SRVKEY, SRVUSGDAYS)
SELECT
BLITEMKEY, A.ACCTKEY, BLITRTKEY, BLSTGKEY, ADDBY, ADDDTTM, FULLAMT, PRORATEAMT,
MODBY, MODDTTM, RTBASECHG, RTMAXCHG, RTMINCHG,
RTCHG1, RTCHG2, RTCHG3, RTCHG4, RTCHG5, RTCHG6, RTCHG7, RTCHG8, RTCHG9, RTCHG10,
RTRATE1, RTRATE2, RTRATE3, RTRATE4, RTRATE5, RTRATE6, RTRATE7, RTRATE8, RTRATE9, RTRATE10,
RTUSG1, RTUSG2, RTUSG3, RTUSG4, RTUSG5, RTUSG6, RTUSG7, RTUSG8, RTUSG9, RTUSG10,
RTRATECODE, RTSTDDAYS, RTUNIT, RTUOM, RTUSGDAYS, SRVKEY, SRVUSGDAYS
FROM @tmp A inner join imsv7.BLITEMRT C ON (A.AcctKey = C.AcctKey AND A.DueDate = C.ADDDTTM)
/*WHERE B.ACCTKEY = @dwAcctkey
AND B.ADDDTTM <= @tsDueDate;*/

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'S/I BlItemRT', @seq, @theDate)

/* Copy BBTRAN to the archive table - no lvarchar column */
INSERT INTO ARCHBBTR
(BBTRANKEY, ACCTKEY, SRVKEY, BLSTGKEY, TRANTYPE, TRANDTTM, BLUSG,
BGTBLUSG, BLDAMT, BGTBLDAMT, ADDBY, ADDDTTM, MODBY, MODDTTM)
SELECT
BBTRANKEY, A.ACCTKEY, SRVKEY, BLSTGKEY, TRANTYPE, TRANDTTM, BLUSG,
BGTBLUSG, BLDAMT, BGTBLDAMT, ADDBY, ADDDTTM, MODBY, MODDTTM
FROM @tmp A inner join imsv7.BBTRAN C ON (A.AcctKey = C.AcctKey AND A.DueDate = C.ADDDTTM)
/*WHERE B.ACCTKEY = @dwAcctkey
AND B.ADDDTTM <= @tsDueDate;*/

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'S/I BBTran', @seq, @theDate)

/* */
/* DELETE THE ACTIVE RECORDS */
/* */

/* Delete SERVICE CHARGES that have been archived*/
DELETE FROM ACCTCHG
WHERE ACCTKEY IN (Select AcctKey FROM @tmp)
AND BLKEY IN ( Select B.BlKey
FROM @tmp A inner join imsv7.CustBl B
ON (A.AcctKey = B.AcctKey AND A.DueDate = B.DueDate) );

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'Del AcctChg', @seq, @theDate)

/* Delete TRACKED READINGS that have been archived */
DELETE FROM TRACKRD
WHERE ACCTKEY IN (Select AcctKey FROM @tmp)
AND BLKEY IN ( Select B.BlKey
FROM @tmp A inner join imsv7.CustBl B
ON (A.AcctKey = B.AcctKey AND A.DueDate = B.DueDate) );

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'Del TrackRD', @seq, @theDate)

/* Delete LINE ITEMS that have been archived*/
DELETE FROM BLITEM
WHERE ACCTKEY IN (Select AcctKey FROM @tmp)
AND BLKEY IN ( Select B.BlKey
FROM @tmp A inner join imsv7.CustBl B
ON (A.AcctKey = B.AcctKey AND A.DueDate = B.DueDate) )

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'Del BlItem', @seq, @theDate)

/* Delete PAYMENTS that have been archived*/
DELETE FROM ACCTPAY
WHERE ACCTKEY IN (Select AcctKey FROM @tmp)
AND PAYDTTM IN ( Select B.PayDtTm
From @tmp A inner join imsv7.acctpay B
ON (A.Acctkey = B.Acctkey And B.PayDtTm <= A.DueDate) )

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'Del AcctPay', @seq, @theDate)

/* Delete TRANSACTIONS that have been archived*/
DELETE FROM ACCTTRAN
WHERE ACCTKEY IN (Select AcctKey FROM @tmp)
AND TRANDTTM IN ( Select B.TRANDTTM
From @tmp A inner join imsv7.ACCTTRAN B
ON (A.Acctkey = B.Acctkey And B.TRANDTTM <= A.DueDate) )

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'Del AcctTran', @seq, @theDate)

/* Delete BILL INSERTS */
DELETE FROM BLINSR
WHERE ACCTKEY IN (Select AcctKey FROM @tmp)
AND BLKEY IN ( Select B.BlKey
FROM @tmp A inner join imsv7.CustBl B
ON (A.AcctKey = B.AcctKey AND A.DueDate = B.DueDate) );

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'Del BlInsr', @seq, @theDate)

/* Delete BILLS that have been archived */
/* BILLS MUST BE DELETED LAST - THEY ARE USED IN THE OTHER QUERIES*/
DELETE FROM CUSTBL
WHERE ACCTKEY IN (Select AcctKey FROM @tmp)
AND DUEDATE IN ( Select B.DUEDATE
From @tmp A inner join imsv7.CUSTBL B
ON (A.Acctkey = B.Acctkey And B.DUEDATE <= A.DueDate) )

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'Del CustBl', @seq, @theDate)

/* Delete BLITEMRT that have been archived*/
DELETE FROM BLITEMRT
WHERE ACCTKEY IN (Select AcctKey FROM @tmp)
AND ADDDTTM IN ( Select B.ADDDTTM
From @tmp A inner join imsv7.BLITEMRT B
ON (A.Acctkey = B.Acctkey And B.ADDDTTM <= A.DueDate) )

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'Del BlItemRT', @seq, @theDate)

/* Delete BBTRAN that have been archived */
DELETE FROM BBTRAN
WHERE ACCTKEY IN (Select AcctKey FROM @tmp)
AND ADDDTTM IN ( Select B.ADDDTTM
From @tmp A inner join imsv7.BBTRAN B
ON (A.Acctkey = B.Acctkey And B.ADDDTTM <= A.DueDate) )

Set @theDate = GetDate()
Insert Into imsv7.ArchiveTracking Values (@dwAcctKey, 'Del BBTran', @seq, @theDate)

END;

GO