Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

SQL 2008 Stored Procedure running long time.

Hello,

I have a SP in sql 2008 which runs for 4 hours. It never run for that long before. The SP is doing a lot of inserts and updates. I am not sure where to start looking for the solution. We just had an upgrade from sql 2005 to sql 2008.
0
Galina Besselyanova
Asked:
Galina Besselyanova
1 Solution
 
DultonCommented:
If this is a new development.... check you indexes. They may need rebuilt or reorganized depending on the level of fragmentation.
0
 
Galina BesselyanovaSenior Software Developer/EngineerAuthor Commented:
Thank you. I will try this tonight.
0
 
didnthaveanameCommented:
Just to build on what Dulton said, you may also want to: update the underlying statistics and/or recompile the stored proc (with sp_recompile: http://msdn.microsoft.com/en-us/library/ms181647(v=sql.100).aspx
0
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.

 
EvilPostItCommented:
I would recommend a full index rebuild if possible on all user databases when upgrading between SQL versions.

If you like you could post the execution plan to be debugged.
0
 
Galina BesselyanovaSenior Software Developer/EngineerAuthor Commented:
Index rebuild didn't help.
Thanks

Here is the code:
USE [imisprod]
GO
/****** Object:  StoredProcedure [dbo].[ApplyPay]    Script Date: 06/28/2013 09:13:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






ALTER PROCEDURE [dbo].[ApplyPay] WITH RECOMPILE
      AS

DECLARE @TraNum int
DECLARE @ActivityNum int

BEGIN TRANSACTION

--Lock the entire counter table for the entire transactions
Select * from counter  with (holdlock, tablock)

--SELECT "BEFORE WAIT"
--Test locking , wait 5 minutes
--WAITFOR DELAY '000:05:00'
--SELECT "AFTER WAIT"


SELECT @TraNum = (SELECT Counter.LAST_VALUE FROM Counter WHERE Counter.COUNTER_NAME='Trans') + 1
SELECT @ActivityNum = (SELECT Counter.LAST_VALUE FROM Counter WHERE Counter.COUNTER_NAME='Activity') + 1

/************************************************/
/*  Do a lot of date math here                  */
/************************************************/

DECLARE @BatchNum varchar(12)
DECLARE @Other_Bat varchar(12)
DECLARE @PerYear varchar(4)
DECLARE @Period varchar(2)
DECLARE @DString datetime

IF (DATEPART(month,getdate())) < 5
    BEGIN
            SELECT @PerYear = CONVERT(char,DATEPART(year,getdate()))
            SELECT @Period = CONVERT(varchar(2), (DATEPART(month, getdate())+8))
      END
ELSE
      BEGIN
            SELECT @PerYear = CONVERT(char,(DATEPART(year,getdate())+1))
            SELECT @Period = CONVERT(varchar(2), (DATEPART(month, getdate())-4))
      END

IF LEN(@Period) = 1
      SELECT @Period = '0' + @Period

SELECT @DString = getdate()


/************************************************/
/* Declare all for the upcoming cursor            */
/* assignment.                                           */
/************************************************/


            
DECLARE @ID_Crs varchar(10)
DECLARE @Eff_Dt datetime
DECLARE @InvRef int
DECLARE @RetRef int
DECLARE @Amt_Crs money
DECLARE @Cur_Amt money
DECLARE @LeftOver money
DECLARE @x int
DECLARE @y int
DECLARE @z int
DECLARE @Cnt_Inv int
DECLARE @TraRef int
DECLARE @Other_Trans datetime
DECLARE @Annual varchar(255)
DECLARE @AnnualRead varchar(255)
DECLARE @PdThru datetime
DECLARE @ChkNum varchar(10)
DECLARE @Orig_Pay int
DECLARE @OldDate datetime
DECLARE @strDate varchar(12)
DECLARE @Inv_Date datetime
DECLARE @Flagger int
DECLARE @PostVal int
DECLARE @Eff_Thru datetime
DECLARE @Eff_Num int

/************************************************/
/* Build your cursor here                        */
/************************************************/

DECLARE InvCount INSENSITIVE CURSOR FOR
SELECT   BT_ID,
         REFERENCE_NUM,
         BALANCE
         FROM InvoiceTemp

OPEN InvCount
FETCH NEXT FROM InvCount INTO @ID_Crs, @InvRef, @Amt_Crs
SELECT @y = @TraNum
SELECT @x = 1
SELECT @z = 99
SELECT @RetRef = 0
SELECT @LeftOver = 0

/************************************************/
/* Beginning of cursor loop                        */
/************************************************/

WHILE (@@fetch_status <> -1)
BEGIN
      IF (@@fetch_status <> -2)
      BEGIN

/************************************************/
/* Trans construction begins here.  First we    */
/* to establish how many                          */
/************************************************/

      SELECT @Cnt_Inv = (SELECT COUNT(*) FROM Invoice
      WHERE BT_ID = @ID_Crs AND BALANCE > 0 AND
      ORG_CODE = 'company' AND (AR_ACCOUNT = '1-1-000-00-0000-1310' OR
       AR_ACCOUNT = '1-1-000-00-0000-2700'))      

    IF @Cnt_Inv > 0
/************************************************/
/* If there is 1 or more matches, then            */
/************************************************/

      BEGIN
      
      While @Amt_Crs < 0
/************************************************/
/* While there is still money left in the       */
/* Advanced Receipt file...                             */
/************************************************/
      BEGIN

/************************************************/
/* ...all hell breaks loose                        */
/************************************************/

      SELECT @Flagger = 0      

/************************************************/
/* Find the amount from the earliest invoice      */
/* with an open balance      (effective date)            */
/************************************************/
      SELECT @Cur_Amt = (SELECT BALANCE FROM INVOICE WHERE EFFECTIVE_DATE IN
            (SELECT Min(EFFECTIVE_DATE)
            FROM Invoice
            WHERE BT_ID = @ID_Crs AND BALANCE > 0 AND
            ORG_CODE = 'company' AND (AR_ACCOUNT = '1-1-000-00-0000-1310' OR
              AR_ACCOUNT = '1-1-000-00-0000-2700'))
            AND BT_ID = @ID_Crs AND BALANCE > 0 AND
            ORG_CODE = 'company' AND (AR_ACCOUNT = '1-1-000-00-0000-1310' OR
              AR_ACCOUNT = '1-1-000-00-0000-2700')
            GROUP BY BALANCE)
            
      IF @Cur_Amt is Null SELECT @Cur_Amt = 0
      SELECT @Cur_Amt = (@Cur_Amt * -1)
      
/************************************************/
/* Find the Reference Number from the earliest  */
/* Invoice with an open balance                  */
/************************************************/
      SELECT @RetRef = (SELECT top 1 REFERENCE_NUM FROM INVOICE WHERE EFFECTIVE_DATE IN
            (SELECT Min(EFFECTIVE_DATE)
            FROM Invoice
            WHERE BT_ID = @ID_Crs AND BALANCE > 0 AND
            ORG_CODE = 'company' AND (AR_ACCOUNT = '1-1-000-00-0000-1310' OR              AR_ACCOUNT = '1-1-000-00-0000-2700'))
            AND BT_ID = @ID_Crs AND BALANCE > 0 AND
            ORG_CODE = 'company' AND (AR_ACCOUNT = '1-1-000-00-0000-1310' OR
              AR_ACCOUNT = '1-1-000-00-0000-2700')
            GROUP BY REFERENCE_NUM
            order by REFERENCE_NUM desc)

/************************************************/
/* Find the effective date from invoice             *//************************************************/

/*      SELECT @Eff_Dt = (SELECT EFFECTIVE_DATE FROM Trans
                                   WHERE INVOICE_REFERENCE_NUM = @RetRef
                                AND TRANSACTION_TYPE = 'DIST')
*/
      SELECT @Eff_Dt = (SELECT EFFECTIVE_DATE FROM Invoice
                               WHERE REFERENCE_NUM = @RetRef)
     
/************************************************/
/* Find the Batch Number from invoice             */
/************************************************/
/*      SELECT @Other_Bat = (SELECT BATCH_NUM FROM Trans
                                 WHERE INVOICE_REFERENCE_NUM = @RetRef
                                  AND TRANSACTION_TYPE = 'DIST')
*/
      SELECT @Other_Bat = (SELECT BATCH_NUM FROM Invoice
                               WHERE REFERENCE_NUM = @RetRef)

/************************************************/
/* Find the tranaction date from invoice             */
/************************************************/
/*      SELECT @Other_Trans = (SELECT TRANSACTION_DATE FROM Trans
                                 WHERE INVOICE_REFERENCE_NUM = @RetRef
                                  AND TRANSACTION_TYPE = 'DIST')
*/
      SELECT @Other_Trans = (SELECT INVOICE_DATE FROM Invoice
                               WHERE REFERENCE_NUM = @RetRef)

/************************************************/
/* Find the product code from invoice             */
/* (used to compute whether invoice is annual       */
/* or semi-annual)                              */
/************************************************/
      SELECT @AnnualRead = (SELECT TOP 1 PRODUCT_CODE FROM Trans WHERE
                                      INVOICE_REFERENCE_NUM = @RetRef
                                AND TRANSACTION_TYPE = 'DIST'
                                ORDER BY INVOICE_REFERENCE_NUM DESC)
      
/************************************************/
/* Find the months paid date from invoice             */
/* Used to plug in values in the Trans file)      */
/************************************************/
      /*SELECT @Eff_Num = (SELECT MONTHS_PAID FROM Trans WHERE
                                      INVOICE_REFERENCE_NUM = @RetRef
                                AND TRANSACTION_TYPE = 'DIST')*/

/************************************************/
/* Compute Annual or Semi-Annual                   */
/************************************************/
      SELECT @Annual = CASE WHEN patindex('Semi%',@AnnualRead) > 0 then 'SEMI-ANNUAl'
                                             WHEN patindex('Annual%',@AnnualRead) > 0 then 'ANNUAL'                          
                                      ELSE ' '
                                      END
                                     
/************************************************/
/* Find the Paid_thru date for the Trans file       */
/************************************************/
      SELECT @PdThru = CASE WHEN @Annual = 'SEMI-ANNUAL' THEN DATEADD(month,6,@Eff_Dt)
                                          WHEN @Annual = 'ANNUAL' THEN DATEADD(year,1,@Eff_Dt)
                                      ELSE GETDATE()
                                      END
      SELECT @PdThru = DATEADD(Day,-1,@PdThru)
                                                               

      IF @Cur_Amt = 0
         BEGIN
                  SELECT @LeftOver = @Amt_Crs
                     SELECT @Amt_Crs = 0
                  GOTO Finished
         END
      
      SELECT @Amt_Crs
      SELECT @Cur_Amt


/************************************************/
/* If there is still money left in the cursor...*/
/************************************************/
      
      IF @Amt_Crs < @Cur_Amt
      BEGIN
/************************************************/
/* Assign a horde of variables                  */
/************************************************/      SELECT @Orig_Pay = (SELECT ORIGINATING_TRANS_NUM FROM Invoice WHERE REFERENCE_NUM = @InvRef)
      SELECT @Inv_Date = (SELECT INVOICE_DATE FROM Invoice WHERE REFERENCE_NUM = @InvRef)
      SELECT @ChkNum = (SELECT CHECK_NUMBER FROM Trans WHERE TRANS_NUMBER = @Orig_Pay AND TRANSACTION_TYPE = 'PAY')
/************************************************/
/* If the Invoice with an open charge has a      */
/* greater date than the open credit...            */
/*                                    */
/*************************************************/
      IF @Eff_Dt > @Inv_Date
       BEGIN
              SELECT @OldDate = @Other_Trans
            SELECT @BatchNum = @Other_Bat
            SELECT @PostVal = 2
        END
      ELSE
      BEGIN
            /*  4/27 make all PostVal = 2 */
            /*SELECT @OldDate = (SELECT TRANSACTION_DATE FROM Trans WHERE TRANS_NUMBER = @Orig_Pay AND TRANSACTION_TYPE = 'PAY') */
            /*SELECT @BatchNum = (SELECT BATCH_NUM FROM Trans WHERE TRANS_NUMBER = @Orig_Pay AND TRANSACTION_TYPE = 'PAY') */
            /*SELECT @PostVal = 5 */

            SELECT @OldDate = (SELECT TRANSACTION_DATE FROM Trans WHERE TRANS_NUMBER = @Orig_Pay AND TRANSACTION_TYPE = 'PAY')
            SELECT @BatchNum = (SELECT BATCH_NUM FROM Trans WHERE TRANS_NUMBER = @Orig_Pay AND TRANSACTION_TYPE = 'PAY')
            SELECT @PostVal = 2
      END
      SELECT @strDate = CONVERT(char(12), @OldDate,1)
      SELECT @Flagger = 0
      
/************************************************/
/* Build the Trans file...                        */
/************************************************/
      INSERT Trans(TRANS_NUMBER,
                 LINE_NUMBER,
                 BATCH_NUM,
                 OWNER_ORG_CODE,
                 SOURCE_SYSTEM,
                   JOURNAL_TYPE,
                 TRANSACTION_TYPE,
                 TRANSACTION_DATE,
                 BT_ID,
                 ST_ID,
                 INVOICE_REFERENCE_NUM,
                 DESCRIPTION,
                 PRODUCT_CODE,
                 EFFECTIVE_DATE,
                 FISCAL_PERIOD,
                 AMOUNT,
                 PSEUDO_ACCOUNT,
                 GL_ACCT_ORG_CODE,
                 GL_ACCOUNT,
                 INVOICE_CHARGES,
                 INVOICE_CREDITS,
                 QUANTITY,
                 DATE_ENTERED,
                 ACTIVITY_SEQN,
                 POSTED,
                 PROD_TYPE,
                 ACTIVITY_TYPE,
                 ENTERED_BY,
                 INVOICE_ADJUSTMENTS,
                 INVOICE_LINE_NUM)            VALUES (@y,
                  @x,      
                  @BatchNum,
                  'company',
                  'AR',
                  'PAY',
                  'AR',
                  @strDate,
                  @ID_Crs,
                  @ID_Crs,
                  @RetRef,
                  'Applied Credit',
                  '',
                  @Eff_Dt,
                  CONVERT(int,(@PerYear + @Period)),
                  @Cur_Amt,
                  'company-AR-AR',
                  'company',
                  '1-1-000-00-0000-1310',
                  0,
                  @Cur_Amt,
                  1,
                  @DString,
                  0,
                  2,
                  '',
                  '',
                  'ADMIN',
                  0,
                  @x)

      SELECT @x = @x + 1

      INSERT Trans(TRANS_NUMBER,
                 LINE_NUMBER,
                 BATCH_NUM,
                 OWNER_ORG_CODE,
                 SOURCE_SYSTEM,
                   JOURNAL_TYPE,
                 TRANSACTION_TYPE,
                 TRANSACTION_DATE,
                 BT_ID,
                 ST_ID,
                 INVOICE_REFERENCE_NUM,
                 DESCRIPTION,
                 PRODUCT_CODE,
                 EFFECTIVE_DATE,
                 FISCAL_PERIOD,
                 AMOUNT,
                 PSEUDO_ACCOUNT,
                 GL_ACCT_ORG_CODE,
                 GL_ACCOUNT,
                 INVOICE_CHARGES,
                 INVOICE_CREDITS,
                 QUANTITY,
                 DATE_ENTERED,
                 ACTIVITY_SEQN,
                 POSTED,
                 PROD_TYPE,
                 ACTIVITY_TYPE,
                 ENTERED_BY,
                 INVOICE_ADJUSTMENTS,
                 INVOICE_LINE_NUM)
            VALUES (@y,
                  @x,      
                  @BatchNum,
                  'company',
                  'AR',
                  'PAY',
                  'AR',                  @strDate,
                  @ID_Crs,
                  @ID_Crs,
                  @InvRef,
                  '',
                  '',
                  @Eff_Dt,
                  CONVERT(int,(@PerYear + @Period)),
                  (@Cur_Amt * -1),
                  'company-AR-AR',
                  'company',
                  '1-1-000-00-0000-2700',
                  0,
                  0,
                  0,
                  @DString,
                  0,
                  @PostVal,
                  '',
                  '',
                  'ADMIN',
                  0,
                  0)
            
/************************************************/
/* Update the affected invoices...                  */
/************************************************/

    UPDATE Invoice SET BALANCE = (BALANCE + @Cur_Amt), CREDITS = (CREDITS + ABS(@Cur_Amt)) WHERE REFERENCE_NUM = @RetRef
    UPDATE Invoice SET BALANCE = (BALANCE + ABS(@Cur_Amt)), CHARGES = (CHARGES + ABS(@Cur_Amt)) WHERE REFERENCE_NUM = @InvRef

      --NimbleUser update to fix issue with paying credited dues online
      UPDATE Invoice_Lines SET BALANCE = (BALANCE + @Cur_Amt), CREDITS = (CREDITS + ABS(@Cur_Amt)) WHERE REFERENCE_NUM = @RetRef AND LINE_NUM = 1

      SELECT @Eff_Thru = DATEADD(month,@Eff_Num,@PdThru)      
      INSERT Activity (SEQN, ID, ACTIVITY_TYPE, TRANSACTION_DATE, EFFECTIVE_DATE,
                        AMOUNT, UF_1, PRODUCT_CODE, THRU_DATE, UF_4)
                     VALUES(@ActivityNum, @ID_Crs, 'DUES', @DString, @Eff_Dt,
                     ABS(@Cur_Amt), @ChkNum, @Annual, @Eff_Thru, @InvRef)         
      SELECT @ActivityNum = @ActivityNum + 1
      SELECT @x = 1
      SELECT @y = @y + 1
      SELECT @Amt_Crs = @Amt_Crs - @Cur_Amt
      END
/* End IF Loop, start ELSE  */
/************************************************/
/* In other words, if we've applied all of the       */
/* "bank", the open credit amount is equal to       */
/* or less than the current open invoice            */
/************************************************/
      
      ELSE
      
      BEGIN
      SELECT @Flagger = 0
      IF @Amt_Crs >= @Cur_Amt SELECT @Flagger = 1
/************************************************/
/* Assign a horde of variables                  */
/************************************************/
      SELECT @Orig_Pay = (SELECT ORIGINATING_TRANS_NUM FROM Invoice WHERE REFERENCE_NUM = @InvRef)
      SELECT @Inv_Date = (SELECT INVOICE_DATE FROM Invoice WHERE REFERENCE_NUM = @InvRef)
      SELECT @ChkNum = (SELECT CHECK_NUMBER FROM Trans WHERE TRANS_NUMBER = @Orig_Pay AND TRANSACTION_TYPE = 'PAY')
/************************************************/
/* If the Invoice with an open charge has a      */
/* greater date than the open credit...            */
/************************************************/
      IF @Eff_Dt > @Inv_Date
       BEGIN
              SELECT @OldDate = @Other_Trans
            SELECT @BatchNum = @Other_Bat
            SELECT @PostVal = 2
        END
      ELSE
      BEGIN
            /*  4/27/00  make all PostVal =2 */
            /*SELECT @OldDate = (SELECT TRANSACTION_DATE FROM Trans WHERE TRANS_NUMBER = @Orig_Pay AND TRANSACTION_TYPE = 'PAY') */
            /*SELECT @BatchNum = (SELECT BATCH_NUM FROM Trans WHERE TRANS_NUMBER = @Orig_Pay AND TRANSACTION_TYPE = 'PAY') */
            /*SELECT @PostVal = 5 */

            SELECT @OldDate = (SELECT TRANSACTION_DATE FROM Trans WHERE TRANS_NUMBER = @Orig_Pay AND TRANSACTION_TYPE = 'PAY')
            SELECT @BatchNum = (SELECT BATCH_NUM FROM Trans WHERE TRANS_NUMBER = @Orig_Pay AND TRANSACTION_TYPE = 'PAY')
            SELECT @PostVal = 2
      END
      
      SELECT @strDate = CONVERT(char(12), @OldDate,1)

/************************************************/
/* Build the Trans file...                        */
/************************************************/
      INSERT Trans(TRANS_NUMBER,
                 LINE_NUMBER,
                 BATCH_NUM,
                 OWNER_ORG_CODE,
                 SOURCE_SYSTEM,
                   JOURNAL_TYPE,
                 TRANSACTION_TYPE,
                 TRANSACTION_DATE,
                 BT_ID,
                 ST_ID,
                 INVOICE_REFERENCE_NUM,
                 DESCRIPTION,
                 PRODUCT_CODE,
                 EFFECTIVE_DATE,
                 FISCAL_PERIOD,
                 AMOUNT,
                 PSEUDO_ACCOUNT,
                 GL_ACCT_ORG_CODE,
                 GL_ACCOUNT,
                 INVOICE_CHARGES,
                 INVOICE_CREDITS,
                 QUANTITY,
                 DATE_ENTERED,
                 ACTIVITY_SEQN,
                 POSTED,
                 PROD_TYPE,
                 ACTIVITY_TYPE,
                 ENTERED_BY,
                 INVOICE_ADJUSTMENTS,
                 INVOICE_LINE_NUM)            VALUES (@y,
                  @x,      
                  @BatchNum,
                  'company',
                  'AR',
                  'PAY',
                  'AR',
                  @strDate,
                  @ID_Crs,
                  @ID_Crs,
                  @RetRef,
                  'Applied Credit',
                  '',
                  @Eff_Dt,
                  CONVERT(int,(@PerYear + @Period)),
                  @Amt_Crs,
                  'company-AR-AR',
                  'company',
                  '1-1-000-00-0000-1310',
                  0,
                  @Amt_Crs,
                  1,
                  @DString,
                  0,
                  2,
                  '',
                  '',
                  'ADMIN',
                  0,
                  @x)

      SELECT @x = @x + 1

      INSERT Trans(TRANS_NUMBER,
                 LINE_NUMBER,
                 BATCH_NUM,
                 OWNER_ORG_CODE,
                 SOURCE_SYSTEM,
                   JOURNAL_TYPE,
                 TRANSACTION_TYPE,
                 TRANSACTION_DATE,
                 BT_ID,
                 ST_ID,
                 INVOICE_REFERENCE_NUM,
                 DESCRIPTION,
                 PRODUCT_CODE,
                 EFFECTIVE_DATE,
                 FISCAL_PERIOD,                 AMOUNT,
                 PSEUDO_ACCOUNT,
                 GL_ACCT_ORG_CODE,                 GL_ACCOUNT,
                 INVOICE_CHARGES,
                 INVOICE_CREDITS,                 QUANTITY,
                 DATE_ENTERED,
                 ACTIVITY_SEQN,
                 POSTED,
                 PROD_TYPE,
                 ACTIVITY_TYPE,                 ENTERED_BY,
                 INVOICE_ADJUSTMENTS,
                 INVOICE_LINE_NUM)
            VALUES (@y,
                  @x,      
                  @BatchNum,
                  'company',
                  'AR',                  'PAY',
                  'AR',
                  @strDate,
                  @ID_Crs,
                  @ID_Crs,
                  @InvRef,
                  '',
                  '',
                  @Eff_Dt,
                  CONVERT(int,(@PerYear + @Period)),
                  (@Amt_Crs * -1),
                  'company-AR-AR',                  'company',
                  '1-1-000-00-0000-2700',
                  0,
                  0,
                  0,
                  @DString,
                  0,
                  @PostVal,
                  '',
                  '',
                  'ADMIN',
                  0,
                  0)
            
/************************************************/
/* Update the affected invoices...                  */
/************************************************/
      UPDATE Invoice SET BALANCE = (BALANCE + @Amt_Crs), CREDITS = (CREDITS + ABS(@Amt_Crs)) WHERE REFERENCE_NUM = @RetRef
      UPDATE Invoice SET BALANCE = (BALANCE + ABS(@Amt_Crs)), CHARGES = (CHARGES + ABS(@Amt_Crs)) WHERE REFERENCE_NUM = @InvRef

      --NimbleUser update to fix issue with paying credited dues online
      UPDATE Invoice_Lines SET BALANCE = (BALANCE + @Amt_Crs), CREDITS = (CREDITS + ABS(@Amt_Crs)) WHERE REFERENCE_NUM = @RetRef AND LINE_NUM = 1

      SELECT @Eff_Thru = DATEADD(month,@Eff_Num,@PdThru)      
      INSERT Activity (SEQN, ID, ACTIVITY_TYPE, TRANSACTION_DATE, EFFECTIVE_DATE,
                        AMOUNT, UF_1, PRODUCT_CODE, THRU_DATE, UF_4)
                     VALUES(@ActivityNum, @ID_Crs, 'DUES', @DString, @Eff_Dt,
                     ABS(@Amt_Crs), @ChkNum, @Annual, @Eff_Thru, @InvRef)         
      SELECT @ActivityNum = @ActivityNum + 1

      SELECT @x = 1
      SELECT @y = @y + 1
      SELECT @Amt_Crs = 0
      
      END
/* End Else Loop      */

Finished:



    END
/* End While Loop      */


IF @PostVal = 5
BEGIN
      Update Trans Set POSTED = 5 from Trans, Invoice
      where trans.GL_ACCOUNT = '1-1-000-00-0000-2700'
      and Trans.TRANS_NUMBER = @Orig_Pay
      and Invoice.INVOICE_DATE = @Inv_Date
      and Trans.TRANS_NUMBER=Invoice.ORIGINATING_TRANS_NUM
END
      
IF @LeftOver < 0
      BEGIN
            Update Trans Set POSTED = 2 from Trans
              where Trans.GL_Account = '1-1-000-00-0000-2700'
               and Trans.INVOICE_REFERENCE_NUM = @InvRef
            and Trans.BT_ID = @ID_Crs
            SELECT @LeftOver = 0
      END

UPDATE Name SET PAID_THRU = @PdThru WHERE ID = @ID_Crs
      
      END            
/*  Ending the RetRef condition */
      
      END            
/* Ending fetch status -2      */
      
      FETCH NEXT FROM InvCount INTO @ID_Crs, @InvRef, @Amt_Crs

END
/* Ending fetch status -1      */

CLOSE InvCount
DEALLOCATE InvCount


UPDATE Counter
   SET LAST_VALUE = @y
      WHERE Counter.COUNTER_NAME='Trans'

UPDATE Counter
   SET LAST_VALUE = @ActivityNum
         WHERE Counter.COUNTER_NAME='Activity'


--commit all the inserts/updates and release all the locks. Let others use the counter now
COMMIT TRANSACTION
0
 
didnthaveanameCommented:
Can you also post the execution plan?  As EvilPostIt alluded to, it would be most helpful to use that in identifying problematic areas/places where index support could be useful.  Also, unless the inputs passed to the stored procedure are not typical, I wouldn't use the with recompile clause in the stored procedure defintion.  One of the benefits of a stored procedure is execution plan re-use (there can be significant overhead, depending on the size and complexity of the query, with compiling execution plans and selecting the best plan).
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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