Link to home
Start Free TrialLog in
Avatar of Mark
Mark

asked on

stored procedure takes forever, why?

I have a stored procedures that has been giving me problems for some time. SQL Server 2005 is located on a Windows Office server. When I run this query from an Access .ade program running on a client computer it runs for serveral minutes, sometime 10 or more, and returns nothing. While it's running, the task monitor on the SQL Server host shows sqlservr.exe using 25% of the CPU and 300+K memory utilization.

When I run it using SQL Server Management Studio on the Windows Server it is currently taking around 8 minutes. Normally, this query used to run in about 6 seconds. Every so often users would complain about it not running. I'd run sp_recompile and that would appear to fix things for a while. Now, sp_recompile seems to have no effect.

I need to get this fixed! Help! (although the procedure is pretty long, I've included it below:)
USE [OSH2009]
GO
/****** Object:  StoredProcedure [dbo].[_DROPinterest_sp]    Script Date: 05/02/2009 07:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER procedure [dbo].[_DROPinterest_sp] (@employeeId varchar(12) = null, @asOfDate datetime = null)
AS
BEGIN
 
if @asOfDate is null set @asOfDate = convert(datetime,convert(varchar,getdate() + 1,101))
else set @asOfDate = @asOfDate + 1
set @asOfDate = dateadd(s,-1,@asOfDate)
 
declare @curEmpId  varchar(12),
  @lastEmpId varchar(12),
  @effDate datetime,
  @checkDate datetime,
  @pensionAmount smallmoney,
  @activeAmount smallmoney,
  @distribution smallmoney,
  @balance decimal(14,5),
  @interest smallmoney,
  @iRate decimal(10,7),
  @curIrate decimal(10,7),
  @birthDate datetime,
  @retireDate datetime,
  @DROPdate datetime,
  @laborClass varchar(5),
  @DROPforfeitOverride bit,
  @transType char(1),
  @days integer
 
create table #tempDROP (
  seq int identity,
  employeeId varchar(10),
  mbrName varchar(80),
  transType varchar(30),
  transDate datetime,
  iRate decimal(10,7),
  days int,
  startBal money,
  interest money,
  activeContrib money,
  pensionContrib money,
  distribution money)
 
CREATE INDEX idx1 on #tempDROP (employeeId)
CREATE INDEX idx2 on #tempDROP (transDate)
 
create table #tmpSource (
      transType             varchar(5),
      employeeId            varchar(20),
      birthDate             datetime,
      DROPdate              datetime,
      retireDate            datetime,
      laborClass            varchar(3),
      DROPforfeitOverride   bit,
      checkDate             datetime,
	  activeAmount			smallmoney,
	  pensionAmount			smallmoney,
	  distribution			smallmoney,
      iRate                 decimal(10,7)
)
 
set nocount on
-- _vwPaDROPtransactions.transType - P: Pension contrib., A: Active Contrib., D: Distribution
-- #tmpSource.transType - D: Deposit, W: Withdrawal
insert into #tmpSource select case when t.transType in ('P','A') then 'D' else 'W' end as TransType,
  t.employeeId,max(t.birthDate),max(t.DROPdate), t.retireDate,
  max(t.laborClass),t.DROPforfeitOverride,t.checkDate,
  sum(case when t.transType = 'A' and t.amount is not null then t.amount else 0.00 end) as activeAmount,
  sum(case when t.transType = 'P' and t.amount is not null then t.amount else 0.00 end) as pensionAmount,
  sum(case when t.transType = 'D' and t.amount is not null then -1.0 * t.amount else 0.00 end) as distribution, 0.0 as irate
from _vwPaDROPtransactions t
where (@employeeId is null or t.employeeId = @employeeId)
group by employeeId, t.checkDate, case when t.transType in ('P','A') then 'D' else 'W' end, 
  t.DROPforfeitOverride, t.retireDate
 
insert into #tmpSource select 'I', e.employeeId, e.birthDate, m.DROPdate, m.retireDate, e.laborCLass, e.DROPforfeitOverride,
  r.rateEffStart,0.0,0.0,0.0,r.rate
from tblPaEmpGenInfo e
join tblPaActiveMembers m on m.socialSecurityNo = e.socialSecurityNo
join _vwOHPRSrates r on r.rateType = 'D'
where e.employeeId in (select distinct employeeId from _vwPaDROPtransactions)
  and (@employeeId is null or e.employeeId = @employeeId)
 
insert into #tmpSource select 'D',e.employeeId, e.birthDate, m.DROPdate, m.retireDate, e.laborCLass, e.DROPforfeitOverride,@asOfDate,
  0.0,0.0,0.0,0.0
from tblPaEmpGenInfo e
join tblPaActiveMembers m on m.socialSecurityNo = e.socialSecurityNo
where e.employeeId in (select distinct employeeId from _vwPaDROPtransactions)
  and (@employeeId is null or e.employeeId = @employeeId)
 
DECLARE MyCursor CURSOR READ_ONLY FOR
select * from #tmpSource order by employeeId, checkDate
 
set @lastEmpId = ''
 
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @transType,@curEmpId,@birthDate,@DROPdate,@retireDate,@laborClass,
  @DROPforfeitOverride,@checkDate,@activeAmount,@pensionAmount,@distribution,@iRate
WHILE (@@FETCH_STATUS <> -1)
BEGIN
 
if @checkDate <= @asOfDate
begin
  if @curEmpId <> @lastEmpId
  begin
    set @lastEmpId = @curEmpId
    set @balance = 0.0
    set @effDate = @checkDate
    set @curIrate = @irate
  end
 
  set @days = round(datediff(hh,@effDate,@checkDate) / 24.0,0)
 
  /* ----------------------------------------------------------
     Must be in DROP 3 years if under 52, or 2 years if over 52
     in order to earn interest. No time limit if disabled
         ---------------------------------------------------------- */
 
  if @retireDate is not null and @DROPforfeitOverride = 0 and
      ((((datediff(d,@DROPdate,@retireDate) / 365.0) < 3.0) and ((dateDiff(m,@birthDate,@DROPdate) / 12.0) < 52.0))
        or
      ((datediff(d,@DROPdate,@retireDate) / 365.0) < 2.0) and ((dateDiff(m,@birthDate,@DROPdate) / 12.0) >= 52.0))
            and @laborClass <> 'DIS'
        set @interest = 0.0
  else
    set @interest = @balance * power(1.0 + @curIrate / 365.0,@days) - @balance
 
  if (round(@interest,2) <> 0.0 or round(@pensionAmount + @activeAmount + @distribution,2) <> 0.0)
    insert into #tempDROP select @curEmpId, lastName + ', ' + firstName + case when middleInit is null then ''
                  else ' ' + middleInit + '.' end, case when @transType = 'D' then 'Contribution'
                when @transType = 'W' then 'Distribution'
                when @transType = 'I' then 'Interest Rate Change'
                else '?' end, @checkDate,@curIrate,@days,@balance,@interest,@activeAmount,@pensionAmount,@distribution
                from tblPaEmpGenInfo where employeeId = @curEmpId
 
--print @curEmpId + ' ' + @transType + ' ' + convert(varchar,@checkDate,101) + ' ' + convert(char,@curIrate) +
--  ' ' + convert(varchar,@days) + ' ' + convert(char,@balance) + ' ' + convert(char(10),@interest) +
--  ' ' + convert(char(10),@amount)
 
  set @balance = @balance + @interest + @activeAmount + @pensionAmount + @distribution
  set @effDate = @checkDate
  if @transType = 'I' set @curIrate = @iRate
end
 
  FETCH NEXT FROM MyCursor INTO @transType,@curEmpId,@birthDate,@DROPdate,@retireDate,@laborClass,
    @DROPforfeitOverride,@checkDate,@activeAmount,@pensionAmount,@distribution,@iRate
END
CLOSE MyCursor
DEALLOCATE MyCursor
 
-- NOTE: doing group by because last asOfDate transaction could generate two lines for same date.
 
select max(seq) seq, employeeId, max(mbrName) as mbrName, transType,
  convert(datetime,convert(char,transDate,101)) as transDate, iRate, sum(days) as days,
  min(startBal) as startBal, sum(case when interest is null then 0.0 else interest end)as interest, 
  sum(case when activeContrib is null then 0.0 else activeContrib end) as activeContrib,
  sum(case when pensionContrib is null then 0.0 else pensionContrib end) as pensionContrib, 
  sum(case when distribution is null then 0.0 else distribution end) as distribution,
  sum(case when pensionContrib is null then 0.0 else pensionContrib end + 
    case when activeContrib is null then 0.0 else activeContrib end + 
    case when distribution is null then 0.0 else distribution end) as contrib
from #tempDROP group by employeeId,convert(datetime,convert(char,transDate,101)),irate,transType
order by mbrName,seq
 
drop table #tempDROP
drop table #tmpSource
END

Open in new window

Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

In a word your main problem is CURSOR's.  If you want to have any performance gain you need to lose them.  That is what is killing you.

But unfortunately, it is not enough to just look at a Stored Procedure and know what is going on.  As any DBA can tell you there is far more involved.  For example:
1. Indexes: Are all tables indexed appropriately.  For one I can see that your temp tables are not.
2. Are your tables sufficiently normalized.
3. Even is your hardware adequate.  I can see that you are not running SQL Server on a standalone box which is a red flag.
This does not get rid of your CURSOR (it may be too late for that) but it should prove to be somewhat more performent.  At the very least it should be more legible:
Alter Procedure dbo._DROPinterest_sp
					@employeeId varchar(12) = null, 
					@asOfDate datetime = null
AS
 
DECLARE	@curEmpId  varchar(12),
		@lastEmpId varchar(12),
		@effDate datetime,
		@checkDate datetime,
		@pensionAmount smallmoney,
		@activeAmount smallmoney,
		@distribution smallmoney,
		@balance decimal(14,5),
		@interest smallmoney,
		@iRate decimal(10,7),
		@curIrate decimal(10,7),
		@birthDate datetime,
		@retireDate datetime,
		@DROPdate datetime,
		@laborClass varchar(5),
		@DROPforfeitOverride bit,
		@transType char(1),
		@days integer
 
CREATE TABLE #tempDROP (
		seq int identity,
		employeeId varchar(10),
		mbrName varchar(80),
		transType varchar(30),
		transDate datetime,
		iRate decimal(10,7),
		days int,
		startBal money,
		interest money,
		activeContrib money,
		pensionContrib money,
		distribution money)
 
CREATE INDEX IX_tempDrop1 on #tempDROP (employeeId, transDate, irate, transType)
CREATE INDEX IX_tempDrop2 on #tempDROP (mbrName, seq)
 
CREATE TABLE #tmpSource (
		transType			varchar(5),
		employeeId			varchar(20),
		birthDate			datetime,
		DROPdate			datetime,
		retireDate			datetime,
		laborClass			varchar(3),
		DROPforfeitOverride	bit,
		checkDate			datetime,
		activeAmount		smallmoney,
		pensionAmount		smallmoney,
		distribution		smallmoney,
		iRate				decimal(10,7)
)
 
CREATE INDEX IX_tmpSource on #tmpSource (employeeId, checkDate)
 
 
CREATE TABLE #tmpEmployees (
		employeeId varchar(20))
 
CREATE INDEX IX_tmpEmployee on #tmpEmployees (employeeId)
 
 
SET NOCOUNT ON
 
Select	@asOfDate =
		CASE
			WHEN @asOfDate Is Null THEN DATEADD(day, 0, DATEDIFF(Day, 0, GETDATE()))
			ELSE DATEADD(day, 1, @asOfDate)
		END
 
Set @asOfDate = DATEADD(second, -1, @asOfDate)
 
 
Insert	#tmpEmployees (employeeId)
select	DISTINCT employeeId 
from	_vwPaDROPtransactions
 
-- _vwPaDROPtransactions.transType - P: Pension contrib., A: Active Contrib., D: Distribution
-- #tmpSource.transType - D: Deposit, W: Withdrawal
Insert	#tmpSource (
		transType, 
		employeeId,
		birthDate,
		DROPdate,
		retireDate,
		laborClass,
		DROPforfeitOverride,
		checkDate,
		activeAmount,
		pensionAmount,
		distribution,
		iRate)
Select	CASE 
			WHEN t.transType in ('P','A') then 'D' 
			ELSE 'W' 
		END TransType,
		t.employeeId,
		MAX(t.birthDate),
		MAX(t.DROPdate),
		t.retireDate,
		MAX(t.laborClass),
		t.DROPforfeitOverride,t.checkDate,
		SUM(
		CASE 
			WHEN t.transType = 'A' And t.amount IS NOT NULL THEN t.amount 
			ELSE 0.00 
		END) activeAmount,
		SUM(
		CASE 
			WHEN t.transType = 'P' And t.amount IS NOT NULL THEN t.amount 
			ELSE 0.00 
		END) pensionAmount,
		SUM(
		CASE 
			WHEN t.transType = 'D' And t.amount IS NOT NULL THEN -1.0 * t.amount 
			ELSE 0.00 END) distribution, 
		0.0 irate
From	_vwPaDROPtransactions t
Where	@employeeId is null or t.employeeId = @employeeId
Group by 
		employeeId, 
		t.checkDate, 
		CASE 
			WHEN t.transType in ('P','A') THEN 'D' 
			ELSE 'W' 
		END, 
		t.DROPforfeitOverride, 
		t.retireDate
 
Insert	#tmpSource (
		transType, 
		employeeId,
		birthDate,
		DROPdate,
		retireDate,
		laborClass,
		DROPforfeitOverride,
		checkDate,
		activeAmount,
		pensionAmount,
		distribution,
		iRate)
Select	'I', 
		e.employeeId, 
		e.birthDate, 
		m.DROPdate, 
		m.retireDate, 
		e.laborCLass, 
		e.DROPforfeitOverride,
		r.rateEffStart,
		0.0,
		0.0,
		0.0,
		r.rate
From	tblPaEmpGenInfo e
		Inner Join tblPaActiveMembers m on m.socialSecurityNo = e.socialSecurityNo
		Inner Join _vwOHPRSrates r on r.rateType = 'D'
		Inner Join #tmpEmployees t On e.employeeId = t.employeeId
Where	@employeeId is null or e.employeeId = @employeeId
 
Insert	#tmpSource 
Select	'D',
		e.employeeId, 
		e.birthDate, 
		m.DROPdate, 
		m.retireDate, 
		e.laborCLass, 
		e.DROPforfeitOverride,
		@asOfDate,
		0.0,
		0.0,
		0.0,
		0.0
From	tblPaEmpGenInfo e
		Inner Join tblPaActiveMembers m on m.socialSecurityNo = e.socialSecurityNo
		Inner Join #tmpEmployees t On e.employeeId = t.employeeId
Where	@employeeId is null or e.employeeId = @employeeId
 
DECLARE MyCursor CURSOR READ_ONLY FOR
Select 	transType, 
		employeeId,
		birthDate,
		DROPdate,
		retireDate,
		laborClass,
		DROPforfeitOverride,
		checkDate,
		activeAmount,
		pensionAmount,
		distribution,
		iRate
From	#tmpSource 
Where	@checkDate <= @asOfDate
Order By 
		employeeId, checkDate
 
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @transType, @curEmpId, @birthDate, @DROPdate, @retireDate, @laborClass,
				@DROPforfeitOverride, @checkDate, @activeAmount, @pensionAmount, @distribution, @iRate
 
Set @lastEmpId = ''
 
WHILE (@@FETCH_STATUS <> -1)
	BEGIN
		IF @curEmpId <> @lastEmpId
			BEGIN
				Select	@lastEmpId = @curEmpId,
						@balance = 0.0,
						@effDate = @checkDate,
						@curIrate = @irate
			END
	 
		Set @days = ROUND(DATEDIFF(hour, @effDate, @checkDate) / 24.0,0)
 
		/* ----------------------------------------------------------
		 Must be in DROP 3 years if under 52, or 2 years if over 52
		 in order to earn interest. No time limit if disabled
			 ---------------------------------------------------------- */
	 
		Set @interest =
			CASE
				WHEN @retireDate is not null 
						And @DROPforfeitOverride = 0 
						And	((((DATEDIFF(d,@DROPdate,@retireDate) / 365.0) < 3.0) 
						And ((DATEDIFF(m,@birthDate,@DROPdate) / 12.0) < 52.0))
						Or ((DATEDIFF(d,@DROPdate,@retireDate) / 365.0) < 2.0) 
						And ((DATEDIFF(m,@birthDate,@DROPdate) / 12.0) >= 52.0))
						And @laborClass <> 'DIS' THEN 0.0
				ELSE @balance * POWER(1.0 + @curIrate / 365.0,@days) - @balance
			END
 
		If ROUND(@interest,2) <> 0.0 Or ROUND(@pensionAmount + @activeAmount + @distribution,2) <> 0.0
			BEGIN
				Insert	#tempDROP (
						employeeId,
						mbrName,
						transType,
						transDate,
						iRate,
						days,
						startBal,
						interest,
						activeContrib,
						pensionContrib,
						distribution)
				Select	@curEmpId, 
						lastName + ', ' + firstName + 
									CASE 
										WHEN middleInit IS NULL THEN ''
										ELSE ' ' + middleInit + '.' 
									END, 
						CASE 
							WHEN @transType = 'D' THEN 'Contribution'
							WHEN @transType = 'W' THEN 'Distribution'
							WHEN @transType = 'I' THEN 'Interest Rate Change'
							ELSE '?' 
						END, 
						DATEADD(day, 0, DATEDIFF(day, 0, @checkDate)),
						@curIrate,
						@days,
						@balance,
						@interest,
						@activeAmount,
						@pensionAmount,
						@distribution
				From	tblPaEmpGenInfo 
				Where	employeeId = @curEmpId
			END
 
		--print @curEmpId + ' ' + @transType + ' ' + convert(varchar,@checkDate,101) + ' ' + convert(char,@curIrate) +
		--  ' ' + convert(varchar,@days) + ' ' + convert(char,@balance) + ' ' + convert(char(10),@interest) +
		--  ' ' + convert(char(10),@amount)
 
		Select	@balance = @balance + @interest + @activeAmount + @pensionAmount + @distribution,
				@effDate = @checkDate
 
		IF @transType = 'I' 
			BEGIN
				Set @curIrate = @iRate
			END
 
		FETCH NEXT FROM MyCursor INTO @transType, @curEmpId, @birthDate, @DROPdate, @retireDate, @laborClass,
					@DROPforfeitOverride, @checkDate, @activeAmount, @pensionAmount, @distribution, @iRate
	END
CLOSE MyCursor
DEALLOCATE MyCursor
 
-- NOTE: doing group by because last asOfDate transaction could generate two lines for same date.
 
Select	MAX(seq) seq, 
		employeeId, 
		MAX(mbrName) mbrName, 
		transType,
		transDate, 
		iRate, 
		SUM(days) days,
		MIN(startBal) startBal, 
		SUM(ISNULL(interest, 0)) interest, 
		SUM(ISNULL(activeContrib, 0)) activeContrib,
		SUM(ISNULL(pensionContrib, 0)) pensionContrib,
		SUM(ISNULL(distribution, 0)) distribution,
		SUM(ISNULL(pensionContrib, 0) + ISNULL(activeContrib, 0) + ISNULL(distribution, 0)) contrib
From	#tempDROP 
Group by 
		employeeId,
		transDate,
		irate,
		transType
Order by 
		mbrName,
		seq
 
drop table #tempDROP
drop table #tmpSource
drop table #tmpEmployees

Open in new window

>>At the very least it should be more legible:<<
Well at least in NotePad :)

Let's see if it is any better here:
Alter Procedure dbo._DROPinterest_sp
                              @employeeId varchar(12) = null,
                              @asOfDate datetime = null
AS

DECLARE      @curEmpId  varchar(12),
            @lastEmpId varchar(12),
            @effDate datetime,
            @checkDate datetime,
            @pensionAmount smallmoney,
            @activeAmount smallmoney,
            @distribution smallmoney,
            @balance decimal(14,5),
            @interest smallmoney,
            @iRate decimal(10,7),
            @curIrate decimal(10,7),
            @birthDate datetime,
            @retireDate datetime,
            @DROPdate datetime,
            @laborClass varchar(5),
            @DROPforfeitOverride bit,
            @transType char(1),
            @days integer
 
CREATE TABLE #tempDROP (
            seq int identity,
            employeeId varchar(10),
            mbrName varchar(80),
            transType varchar(30),
            transDate datetime,
            iRate decimal(10,7),
            days int,
            startBal money,
            interest money,
            activeContrib money,
            pensionContrib money,
            distribution money)

CREATE INDEX IX_tempDrop1 on #tempDROP (employeeId, transDate, irate, transType)
CREATE INDEX IX_tempDrop2 on #tempDROP (mbrName, seq)
 
CREATE TABLE #tmpSource (
            transType                  varchar(5),
            employeeId                  varchar(20),
            birthDate                  datetime,
            DROPdate                  datetime,
            retireDate                  datetime,
            laborClass                  varchar(3),
            DROPforfeitOverride      bit,
            checkDate                  datetime,
            activeAmount            smallmoney,
            pensionAmount            smallmoney,
            distribution            smallmoney,
            iRate                        decimal(10,7)
)

CREATE INDEX IX_tmpSource on #tmpSource (employeeId, checkDate)


CREATE TABLE #tmpEmployees (
            employeeId varchar(20))

CREATE INDEX IX_tmpEmployee on #tmpEmployees (employeeId)


SET NOCOUNT ON

Select      @asOfDate =
            CASE
                  WHEN @asOfDate Is Null THEN DATEADD(day, 0, DATEDIFF(Day, 0, GETDATE()))
                  ELSE DATEADD(day, 1, @asOfDate)
            END

Set @asOfDate = DATEADD(second, -1, @asOfDate)


Insert      #tmpEmployees (employeeId)
select      DISTINCT employeeId
from      _vwPaDROPtransactions

-- _vwPaDROPtransactions.transType - P: Pension contrib., A: Active Contrib., D: Distribution
-- #tmpSource.transType - D: Deposit, W: Withdrawal
Insert      #tmpSource (
            transType,
            employeeId,
            birthDate,
            DROPdate,
            retireDate,
            laborClass,
            DROPforfeitOverride,
            checkDate,
            activeAmount,
            pensionAmount,
            distribution,
            iRate)
Select      CASE
                  WHEN t.transType in ('P','A') then 'D'
                  ELSE 'W'
            END TransType,
            t.employeeId,
            MAX(t.birthDate),
            MAX(t.DROPdate),
            t.retireDate,
            MAX(t.laborClass),
            t.DROPforfeitOverride,t.checkDate,
            SUM(
            CASE
                  WHEN t.transType = 'A' And t.amount IS NOT NULL THEN t.amount
                  ELSE 0.00
            END) activeAmount,
            SUM(
            CASE
                  WHEN t.transType = 'P' And t.amount IS NOT NULL THEN t.amount
                  ELSE 0.00
            END) pensionAmount,
            SUM(
            CASE
                  WHEN t.transType = 'D' And t.amount IS NOT NULL THEN -1.0 * t.amount
                  ELSE 0.00 END) distribution,
            0.0 irate
From      _vwPaDROPtransactions t
Where      @employeeId is null or t.employeeId = @employeeId
Group by
            employeeId,
            t.checkDate,
            CASE
                  WHEN t.transType in ('P','A') THEN 'D'
                  ELSE 'W'
            END,
            t.DROPforfeitOverride,
            t.retireDate
 
Insert      #tmpSource (
            transType,
            employeeId,
            birthDate,
            DROPdate,
            retireDate,
            laborClass,
            DROPforfeitOverride,
            checkDate,
            activeAmount,
            pensionAmount,
            distribution,
            iRate)
Select      'I',
            e.employeeId,
            e.birthDate,
            m.DROPdate,
            m.retireDate,
            e.laborCLass,
            e.DROPforfeitOverride,
            r.rateEffStart,
            0.0,
            0.0,
            0.0,
            r.rate
From      tblPaEmpGenInfo e
            Inner Join tblPaActiveMembers m on m.socialSecurityNo = e.socialSecurityNo
            Inner Join _vwOHPRSrates r on r.rateType = 'D'
            Inner Join #tmpEmployees t On e.employeeId = t.employeeId
Where      @employeeId is null or e.employeeId = @employeeId
 
Insert      #tmpSource
Select      'D',
            e.employeeId,
            e.birthDate,
            m.DROPdate,
            m.retireDate,
            e.laborCLass,
            e.DROPforfeitOverride,
            @asOfDate,
            0.0,
            0.0,
            0.0,
            0.0
From      tblPaEmpGenInfo e
            Inner Join tblPaActiveMembers m on m.socialSecurityNo = e.socialSecurityNo
            Inner Join #tmpEmployees t On e.employeeId = t.employeeId
Where      @employeeId is null or e.employeeId = @employeeId
 
DECLARE MyCursor CURSOR READ_ONLY FOR
Select       transType,
            employeeId,
            birthDate,
            DROPdate,
            retireDate,
            laborClass,
            DROPforfeitOverride,
            checkDate,
            activeAmount,
            pensionAmount,
            distribution,
            iRate
From      #tmpSource
Where      @checkDate <= @asOfDate
Order By
            employeeId, checkDate
 
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @transType, @curEmpId, @birthDate, @DROPdate, @retireDate, @laborClass,
                        @DROPforfeitOverride, @checkDate, @activeAmount, @pensionAmount, @distribution, @iRate

Set @lastEmpId = ''

WHILE (@@FETCH_STATUS <> -1)
      BEGIN
            IF @curEmpId <> @lastEmpId
                  BEGIN
                        Select      @lastEmpId = @curEmpId,
                                    @balance = 0.0,
                                    @effDate = @checkDate,
                                    @curIrate = @irate
                  END
      
            Set @days = ROUND(DATEDIFF(hour, @effDate, @checkDate) / 24.0,0)

            /* ----------------------------------------------------------
             Must be in DROP 3 years if under 52, or 2 years if over 52
             in order to earn interest. No time limit if disabled
                   ---------------------------------------------------------- */
      
            Set @interest =
                  CASE
                        WHEN @retireDate is not null
                                    And @DROPforfeitOverride = 0
                                    And      ((((DATEDIFF(d,@DROPdate,@retireDate) / 365.0) < 3.0)
                                    And ((DATEDIFF(m,@birthDate,@DROPdate) / 12.0) < 52.0))
                                    Or ((DATEDIFF(d,@DROPdate,@retireDate) / 365.0) < 2.0)
                                    And ((DATEDIFF(m,@birthDate,@DROPdate) / 12.0) >= 52.0))
                                    And @laborClass <> 'DIS' THEN 0.0
                        ELSE @balance * POWER(1.0 + @curIrate / 365.0,@days) - @balance
                  END

            If ROUND(@interest,2) <> 0.0 Or ROUND(@pensionAmount + @activeAmount + @distribution,2) <> 0.0
                  BEGIN
                        Insert      #tempDROP (
                                    employeeId,
                                    mbrName,
                                    transType,
                                    transDate,
                                    iRate,
                                    days,
                                    startBal,
                                    interest,
                                    activeContrib,
                                    pensionContrib,
                                    distribution)
                        Select      @curEmpId,
                                    lastName + ', ' + firstName +
                                                      CASE
                                                            WHEN middleInit IS NULL THEN ''
                                                            ELSE ' ' + middleInit + '.'
                                                      END,
                                    CASE
                                          WHEN @transType = 'D' THEN 'Contribution'
                                          WHEN @transType = 'W' THEN 'Distribution'
                                          WHEN @transType = 'I' THEN 'Interest Rate Change'
                                          ELSE '?'
                                    END,
                                    DATEADD(day, 0, DATEDIFF(day, 0, @checkDate)),
                                    @curIrate,
                                    @days,
                                    @balance,
                                    @interest,
                                    @activeAmount,
                                    @pensionAmount,
                                    @distribution
                        From      tblPaEmpGenInfo
                        Where      employeeId = @curEmpId
                  END

            --print @curEmpId + ' ' + @transType + ' ' + convert(varchar,@checkDate,101) + ' ' + convert(char,@curIrate) +
            --  ' ' + convert(varchar,@days) + ' ' + convert(char,@balance) + ' ' + convert(char(10),@interest) +
            --  ' ' + convert(char(10),@amount)

            Select      @balance = @balance + @interest + @activeAmount + @pensionAmount + @distribution,
                        @effDate = @checkDate

            IF @transType = 'I'
                  BEGIN
                        Set @curIrate = @iRate
                  END
 
            FETCH NEXT FROM MyCursor INTO @transType, @curEmpId, @birthDate, @DROPdate, @retireDate, @laborClass,
                              @DROPforfeitOverride, @checkDate, @activeAmount, @pensionAmount, @distribution, @iRate
      END
CLOSE MyCursor
DEALLOCATE MyCursor
 
-- NOTE: doing group by because last asOfDate transaction could generate two lines for same date.
 
Select      MAX(seq) seq,
            employeeId,
            MAX(mbrName) mbrName,
            transType,
            transDate,
            iRate,
            SUM(days) days,
            MIN(startBal) startBal,
            SUM(ISNULL(interest, 0)) interest,
            SUM(ISNULL(activeContrib, 0)) activeContrib,
            SUM(ISNULL(pensionContrib, 0)) pensionContrib,
            SUM(ISNULL(distribution, 0)) distribution,
            SUM(ISNULL(pensionContrib, 0) + ISNULL(activeContrib, 0) + ISNULL(distribution, 0)) contrib
From      #tempDROP
Group by
            employeeId,
            transDate,
            irate,
            transType
Order by
            mbrName,
            seq
 
drop table #tempDROP
drop table #tmpSource
drop table #tmpEmployees

Avatar of Mark
Mark

ASKER

OK, I'll play with this this afternoon and let you know wassup. Thanks.
One other very minor detail employeeId is defined sometimes as varchar(10) and other times as varchar(20).  You may want to correct that so that it is consistent.  

Also, and this is not so minor, one of the indexes for each table should be clustered.
Avatar of Mark

ASKER

acperkins, thanks for all this work! Your version 'compiled' w/o error, but didn't actually return any rows. No big. That could hardly be expected w/o access to the actual database. So, I examined each of your elements incorporating them piece-by-piece into my procedure. The big performance killer was on one of my insert 'where' clauses: where e.employeeId in (select distinct employeeId from _vwPaDROPtransactions). Up to that point, the procedure took only 8 seconds to run. When it hit that insert, it ran for minutes! Adding in your suggested #tmpEmployees temp table fixed that. Once I did that the whole thing ran in 10 seconds, versus the 8 minutes it was taking!

I played around with using, or not, your indexes, and also trying them before and after the inserts (on some DBs, indexing after loading the data gives better performance). However, nothing with that seemed to make any noticable difference, nevertheless I retained your suggested indexes. I see that you sought to index on anything that was joined on, ordered by, or grouped by. Probably not a bad idea. I hate to admit my ignorance, but I'm afraid the "clustering" concept is lost on me.

I'm glad the cursor wasn't the bottleneck as this seems like a very appropriate place for a cursor and I'd hate to have to think of an alternative.

Our employeeId's are actually 6 characters, but point taken. I changed them all to varchar(10).

I notice that you preferred CASE clauses to IFs. Any performance reason or is that a style thing?

Other than the indexes and #tmpEmployees table, I didn't notice other differences in your code unless I just missed something. Did I?
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This:
3. I assigned the transDate set to midnight in the look,

Should have read:
3. I assigned the transDate set to midnight in the WHILE loop,

This:
4. I got rid of all the CASE statements in the last query and repaced them with ISNULL,

Shuld have read:
4. I got rid of all the CASE statements in the last query and replaced them with ISNULL,