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:)
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
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
>>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.tran sType - 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.ch eckDate,
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,@D ROPdate) / 12.0) < 52.0))
Or ((DATEDIFF(d,@DROPdate,@re tireDate) / 365.0) < 2.0)
And ((DATEDIFF(m,@birthDate,@D ROPdate) / 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
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.tran
-- #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.ch
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,@
And ((DATEDIFF(m,@birthDate,@D
Or ((DATEDIFF(d,@DROPdate,@re
And ((DATEDIFF(m,@birthDate,@D
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
-- ' ' + 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,
SUM(ISNULL(distribution, 0)) distribution,
SUM(ISNULL(pensionContrib,
From #tempDROP
Group by
employeeId,
transDate,
irate,
transType
Order by
mbrName,
seq
drop table #tempDROP
drop table #tmpSource
drop table #tmpEmployees
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.
Also, and this is not so minor, one of the indexes for each table should be clustered.
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,
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,
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.