?
Solved

SQL 2008 Stored Procedure running long time.

Posted on 2013-06-27
6
Medium Priority
?
230 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:Galina Besselyanova
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:Galina Besselyanova
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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:Galina Besselyanova
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 1500 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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 SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

762 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