Solved

SQL 2008 Stored Procedure running long time.

Posted on 2013-06-27
6
212 Views
Last Modified: 2013-10-28
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
Comment
Question by:kqureshi321
6 Comments
 
LVL 6

Expert Comment

by:Dulton
ID: 39282425
If this is a new development.... check you indexes. They may need rebuilt or reorganized depending on the level of fragmentation.
0
 

Author Comment

by:kqureshi321
ID: 39282468
Thank you. I will try this tonight.
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39282551
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39283661
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
 

Author Comment

by:kqureshi321
ID: 39293301
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
 
LVL 8

Accepted Solution

by:
didnthaveaname earned 500 total points
ID: 39293501
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now