scary long and slow stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

scary long and slow stored procedure

Author

Topic

thomaswoof67
Starting Member

3 Posts

Posted - 2007-03-02 : 12:22:51

I'm still learning the ins and outs of SQL. I have a stored procedure that is 628 lines. It creates a temporary table recursively. It is a table of employee hierarchy with the id of the top of the hierarchy passed into the stored procedure. It's selected out of the employee table. This is not a problem. It runs quickly and returns. The rest of the code is the problem. I need to select all employees out of that temporary table who have overdue tasks. This is based on joins with a JobPlan table and an Evaluation table. It takes 20+ seconds to run. I've put indexes in the db on all the fields the selects use, and the run time is unchanged. The largest maximum number of records in the temporary table with the hierarchy is only 341, so it's not a volume issue.

The stored procedure is a series of selects and unions. I don't know what I need to do to optimize this. I find it hard to believe that this is the best execution time I can get. As I understand it, I can't use a view because it's all based on a recursively built table.

Thanks in advance for any assistance. I've approached this problem from a number of different angles, in my C# code, manipulating the datagrid it's displayed in, etc. Nothing works satisfactorily. In fact, the same logic in the C# code, building a table in memory, takes about the same amount of time to execute. That struck me as strange; as I understand it, SQL stored procedures are supposed to be much faster than app code.

I hate to use this much space, but I here's the code (hope the formatting comes out okay):

NOTE: I can't get it to include the indents... either with tabs or spaces. Could someone tell me how to do that, or I'll email the formatted code to them if they want. Drat.

ALTER PROCEDURE dbo.spGetOverdueEmployeesAllInOne

(
@petsID int
)

AS

SET NOCOUNT ON

--local temp tables are only visible in current session of current user, so can have multiple sessions creating same-fullnamed temp tables, do not need a unique fullname

if exists (
select *
from
tempdb.dbo.sysobjects o
where
o.xtype in ('U') and
o.id = object_id( N'tempdb..#PETSEMPLOYEEBRANCHTEMP' )
)
drop table #PETSEMPLOYEEBRANCHTEMP

CREATE TABLE #PETSEMPLOYEEBRANCHTEMP (
[petsID] [int] IDENTITY (1, 1) NOT NULL,
[fullname] [varchar] (50),
[Supervisor] [int]
) ON [PRIMARY]

create index idx_petsID on #PETSEMPLOYEEBRANCHTEMP(petsID)
create index idx_supervisor on #PETSEMPLOYEEBRANCHTEMP(supervisor)

exec spEmployeeBranchRecursiveLoop @petsID

----Incomplete job plans
------exists
--------first job plan

Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
where (select count(*)
from evaluation ev
inner join jobplan jpinner on jpinner.jobplanid = ev.jobplanid
where jpinner.petsuserid = pebt.petsID
and ev.EvaluationTypeID = 1) = 0
and jp.JobPlanCompletedDate is null
and GetDate() > DateAdd(day, 14, jp.JobPlanDueDate)
and pebt.supervisor is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

--------not first job plan
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
where (select count(*)
from evaluation ev
inner join JobPlan jpinner on jpinner.jobplanid = ev.jobplanid
where jpinner.petsuserid = pebt.petsID
and ev.EvaluationTypeID = 1) >= 1
and jp.JobPlanCompletedDate is null
and GetDate() > jp.JobPlanDueDate
and supervisor is not null --still don't know why it's returning nulls without this
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

------doesn't exist
--------first job plan
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
where (select count(*)
from jobplan jp
where jp.PetsUserID = pebt.petsID
and jp.jobplanhistoricalrecord is null) = 0
and GetDate() > DateAdd(day, 14, (Select pu.PetsUserAppointedDate
from PetsUser pu
where pu.PetsUserID = pebt.petsID))
and pebt.supervisor is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

--------not first job plan
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
where (select count(*)
from evaluation ev
inner join (select top 1 jpinner.jobplanid, jpinner.jobplanduedate
from JobPlan jpinner
where jpinner.petsuserid = pebt.petsID
order by jpinner.jobplanid desc)jpouter on ev.jobplanid = jpouter.jobplanid
and ev.EvaluationTypeID = 1
and ev.EvaluationCompletedDate is not null)
= 1
and supervisor is not null --still don't know why it's returning nulls without this
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

--first year
----one interim, incomplete (3 month)
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
inner join petsuser pu on pu.petsuserid = jp.petsuserid
--inner join evaluation ev on ev.jobplanid = jp.jobplanid
--one non-historical eval exists
where (select count (*)
from evaluation ev1
inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
where jp1.petsuserid = jp.petsuserid
and jp1.jobplanhistoricalrecord is null) = 1
--one non-historical incomplete
and (select count(*)
from evaluation ev2
inner join jobplan jp1 on jp1.jobplanid = ev2.jobplanid
where jp1.petsuserid = jp.petsuserid
and jp1.jobplanhistoricalrecord is null
and ev2.evaluationcompleteddate is null) = 0
--less than a year since appointment
and DateAdd(year, 1, pu.petsuserappointeddate) >= jp.jobplanduedate
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(month, 3, (select top 1 jp5.jobplanduedate
from jobplan jp5
where jp5.petsuserid = pebt.petsID
order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

------nine month, 1 interim (incomplete) since prob
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
inner join petsuser pu on pu.petsuserid = jp.petsuserid
--inner join evaluation ev on ev.jobplanid = jp.jobplanid
--one non-historical probationary
where (select count (*)
from evaluation ev1
inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
where jp1.petsuserid = jp.petsuserid
and jp1.jobplanhistoricalrecord is null
and ev1.evaluationtypeid = 2) = 1
--1 interim after probationary
and (select count (*) --get count of interims
from evaluation ev2
inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
where jp2.petsuserid = pu.petsuserid
and jp2.jobplanhistoricalrecord is null
and ev2.evaluationtypeid = 3
--where creation date is greater than probationary completion date
and ev2.evaluationcreationdate >
(select top 1 ev4.evaluationcreationdate
from evaluation ev4
inner join jobplan jp4 on jp4.jobplanid = ev4.jobplanid
where jp4.petsuserid = pu.petsuserid
and jp4.jobplanhistoricalrecord is null
and ev4.evaluationtypeid = 2
order by ev4.evaluationcreationdate desc)) = 1
--1 incomplete interim after probationary
and (select count(*)
from evaluation ev2
inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
where jp2.petsuserid = pu.petsuserid
and jp2.jobplanhistoricalrecord is null
and ev2.evaluationtypeid = 3
--where creation date is greater than probationary completion date
and ev2.evaluationcreationdate >
(select top 1 ev4.evaluationcreationdate
from evaluation ev4
inner join jobplan jp4 on jp4.jobplanid = ev4.jobplanid
where jp4.petsuserid = pu.petsuserid
and jp4.jobplanhistoricalrecord is null
and ev4.evaluationtypeid = 2
order by ev4.evaluationcreationdate desc)
and ev2.evaluationcompleteddate is null) = 0
--less than a year since appointment
and DateAdd(year, 1, pu.petsuserappointeddate) >= jp.jobplanduedate
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(month, 8, (select top 1 jp5.jobplanduedate
from jobplan jp5
where jp5.petsuserid = pebt.petsID
order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

--semi-yearly eval exists overdue
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
--inner join petsuser pu on pu.petsuserid = jp.petsuserid
--at least one non-historical annual
where (select count (*)
from evaluation ev1
inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
where jp1.petsuserid = jp.petsuserid
and jp1.jobplanhistoricalrecord is null
and ev1.evaluationtypeid = 1) > 0
--1 interim after latest jobplan due date
and (select count (*) --get count of interims
from evaluation ev2
inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
where jp2.petsuserid = pebt.petsID
and jp2.jobplanhistoricalrecord is null
and ev2.evaluationtypeid = 3
--where creation date is greater than probationary completion date
and ev2.evaluationcreationdate >
(select top 1 jp4.jobplanduedate
from jobplan jp4
where jp4.petsuserid = pebt.petsID
and jp4.jobplanhistoricalrecord is null
order by jp4.jobplanduedate desc)) = 1
--last evaluation is incomplete

and (select count(*)
from evaluation ev5
inner join jobplan jp5 on jp5.jobplanid = ev5.jobplanid
where jp5.petsuserid = pebt.petsID
and jp5.jobplanhistoricalrecord is null
and ev5.evaluationcompleteddate is null) = 0
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(month, 6, (select top 1 jp5.jobplanduedate
from jobplan jp5
where jp5.petsuserid = pebt.petsID
order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

--interim eval, not exist
----first year
-------zero evals
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
inner join petsuser pu on pu.petsuserid = jp.petsuserid
--one non-historical probationary
where (select count (*)
from evaluation ev1
inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
where jp1.petsuserid = pebt.petsID
and jp1.jobplanhistoricalrecord is null) = 0
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
--less than a year since appointment
and DateAdd(year, 1, pu.petsuserappointeddate) >= jp.jobplanduedate
--over 3 months past latest jp due date
and DateAdd(month, 3, (select top 1 jp5.jobplanduedate
from jobplan jp5
where jp5.petsuserid = pebt.petsID
order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

------nine month, 1 interim (incomplete) since prob
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
inner join petsuser pu on pu.petsuserid = jp.petsuserid
--inner join evaluation ev on ev.jobplanid = jp.jobplanid
--one non-historical probationary
where (select count (*)
from evaluation ev1
inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
where jp1.petsuserid = jp.petsuserid
and jp1.jobplanhistoricalrecord is null
and ev1.evaluationtypeid = 2) = 1
--0 interim after probationary
and (select count (*) --get count of interims
from evaluation ev2
inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
where jp2.petsuserid = pu.petsuserid
and jp2.jobplanhistoricalrecord is null
and ev2.evaluationtypeid = 3
--where creation date is greater than last jobplan due date
and ev2.evaluationcreationdate >
(select top 1 ev4.evaluationcreationdate
from evaluation ev4
inner join jobplan jp4 on jp4.jobplanid = ev4.jobplanid
where jp4.petsuserid = pu.petsuserid
and jp4.jobplanhistoricalrecord is null
and ev4.evaluationtypeid = 2
order by ev4.evaluationcreationdate desc)) = 0
--less than a year since appointment
and DateAdd(year, 1, pu.petsuserappointeddate) >= jp.jobplanduedate
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(month, 9, (select top 1 jp5.jobplanduedate
from jobplan jp5
where jp5.petsuserid = pebt.petsID
order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

----semi-yearly eval not exist overdue
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
--inner join petsuser pu on pu.petsuserid = jp.petsuserid
--at least one non-historical annual
where (select count (*)
from evaluation ev1
inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
where jp1.petsuserid = jp.petsuserid
and jp1.jobplanhistoricalrecord is null
and ev1.evaluationtypeid = 1) > 0
--0 interim after latest jobplan due date
and (select count (*) --get count of interims
from evaluation ev2
inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
where jp2.petsuserid = pebt.petsID
and jp2.jobplanhistoricalrecord is null
and ev2.evaluationtypeid = 3
--where creation date is greater than last jobplan due date
and ev2.evaluationcreationdate >
(select top 1 jp4.jobplanduedate
from jobplan jp4
where jp4.petsuserid = pebt.petsID
and jp4.jobplanhistoricalrecord is null
order by jp4.jobplanduedate desc)) = 0
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(month, 6, (select top 1 jp5.jobplanduedate
from jobplan jp5
where jp5.petsuserid = pebt.petsID
order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

----probationary eval exist overdue
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
inner join petsuser pu on pu.petsuserid = jp.petsuserid
where (select count (*)
from evaluation ev1
inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
where jp1.petsuserid = jp.petsuserid
and jp1.jobplanhistoricalrecord is null
and ev1.evaluationcompleteddate is null
and ev1.evaluationtypeid = 2) = 1
--less than a year since appointment
and DateAdd(year, 1, pu.petsuserappointeddate) >= jp.jobplanduedate
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(month, 6, (select top 1 jp5.jobplanduedate
from jobplan jp5
where jp5.petsuserid = pebt.petsID
order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

----probationary eval not exist overdue
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
inner join petsuser pu on pu.petsuserid = jp.petsuserid
--no probationary
where (select count (*)
from evaluation ev1
inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
where jp1.petsuserid = jp.petsuserid
and jp1.jobplanhistoricalrecord is null
and ev1.evaluationtypeid = 2) = 0
--no incomplete evals
and (select count (*)
from evaluation ev1
inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
where jp1.petsuserid = jp.petsuserid
and jp1.jobplanhistoricalrecord is null
and ev1.evaluationcompleteddate is null
and ev1.evaluationtypeid = 3) = 0
--at least one interim eval
and (select count (*)
from evaluation ev1
inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
where jp1.petsuserid = jp.petsuserid
and jp1.jobplanhistoricalrecord is null
and ev1.evaluationtypeid = 3) > 0
--less than a year since appointment
and DateAdd(year, 1, pu.petsuserappointeddate) >= jp.jobplanduedate
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(month, 6, (select top 1 jp5.jobplanduedate
from jobplan jp5
where jp5.petsuserid = pebt.petsID
order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

--annual exists
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
where (select count (*) --one incomplete annual (non-historical
from evaluation ev1
inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
where jp1.petsuserid = jp.petsuserid
and jp1.jobplanhistoricalrecord is null
and ev1.evaluationcompleteddate is null
and ev1.evaluationtypeid = 1) = 1
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(year, 1, (select top 1 jp5.jobplanduedate
from jobplan jp5
where jp5.petsuserid = pebt.petsID
order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

--annual not exist overdue
----first year
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
inner join petsuser pu on pu.petsuserid = jp.petsuserid
--no annual
where (select count (*)
from evaluation ev1
inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
where jp1.petsuserid = jp.petsuserid
and jp1.jobplanhistoricalrecord is null
and ev1.evaluationtypeid =1) = 0
--one non-historical probationary
and (select count (*)
from evaluation ev1
inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
where jp1.petsuserid = jp.petsuserid
and jp1.jobplanhistoricalrecord is null
and ev1.evaluationtypeid = 2) = 1
--1 interim after probationary
and (select count (*) --get count of interims
from evaluation ev2
inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
where jp2.petsuserid = pu.petsuserid
and jp2.jobplanhistoricalrecord is null
and ev2.evaluationtypeid = 3
--where creation date is greater than probationary completion date
and ev2.evaluationcreationdate >
(select top 1 ev4.evaluationcreationdate
from evaluation ev4
inner join jobplan jp4 on jp4.jobplanid = ev4.jobplanid
where jp4.petsuserid = pu.petsuserid
and jp4.jobplanhistoricalrecord is null
and ev4.evaluationtypeid = 2
order by ev4.evaluationcreationdate desc)) = 1
--last interim after probationary is complete
and (select count(*)
from evaluation ev2
inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
where jp2.petsuserid = pu.petsuserid
and jp2.jobplanhistoricalrecord is null
and ev2.evaluationtypeid = 3
--where creation date is greater than probationary completion date
and ev2.evaluationcreationdate >
(select top 1 ev4.evaluationcreationdate
from evaluation ev4
inner join jobplan jp4 on jp4.jobplanid = ev4.jobplanid
where jp4.petsuserid = pu.petsuserid
and jp4.jobplanhistoricalrecord is null
and ev4.evaluationtypeid = 2
order by ev4.evaluationcreationdate desc)
and ev2.evaluationcompleteddate is null) = 0
--less than a year since appointment
and DateAdd(year, 1, pu.petsuserappointeddate) >= jp.jobplanduedate
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(year, 1, (select top 1 jp5.jobplanduedate
from jobplan jp5
where jp5.petsuserid = pebt.petsID
order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

--annual not exist overdue
----not first year
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
--at least one annual
where (select count (*)
from evaluation ev1
inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
where jp1.petsuserid = pebt.petsID
and jp1.jobplanhistoricalrecord is null
and ev1.evaluationtypeid = 1) >= 1
--at least 1 interim after latest jobplan due date
and (select count (*) --get count of interims
from evaluation ev2
inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
where jp2.petsuserid = pebt.petsID
and jp2.jobplanhistoricalrecord is null
and ev2.evaluationtypeid = 3
--where creation date is greater than latest jobplan due date
and ev2.evaluationcreationdate >
(select top 1 jp4.jobplanduedate
from jobplan jp4
where jp4.petsuserid = pebt.petsID
and jp4.jobplanhistoricalrecord is null
order by jp4.jobplanduedate desc)) > 0
--no incomplete interims after last jp due date
and (select count (*)
from evaluation ev2
inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
where jp2.petsuserid = pebt.petsID
and jp2.jobplanhistoricalrecord is null
and ev2.evaluationtypeid = 3
and ev2.evaluationcompleteddate is null
--where creation date is greater than latest jobplan due date
and ev2.evaluationcreationdate >
(select top 1 jp4.jobplanduedate
from jobplan jp4
where jp4.petsuserid = pebt.petsID
and jp4.jobplanhistoricalrecord is null
order by jp4.jobplanduedate desc)) = 0
--no annual after last interim
and (select count (*)
from evaluation ev5
inner join jobplan jp5 on jp5.jobplanid = ev5.jobplanid
where jp5.petsuserid = pebt.petsID
and jp5.jobplanhistoricalrecord is null
and ev5.evaluationtypeid = 1
--where creation date is greater than last interim date
and ev5.evaluationcreationdate >
(select top 1 ev6.evaluationcreationdate
from evaluation ev6
inner join jobplan jp6 on jp6.jobplanid = ev6.jobplanid
where jp6.petsuserid = pebt.petsID
and jp6.jobplanhistoricalrecord is null
and ev6.evaluationtypeid = 3
order by ev6.evaluationcreationdate desc)) = 0
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(year, 1, (select top 1 jp5.jobplanduedate
from jobplan jp5
where jp5.petsuserid = pebt.petsID
order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

drop table #PETSEMPLOYEEBRANCHTEMP
RETURN

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-02 : 12:39:49

Use code tags to retain your code formatting.

Put this around your code minus the spaces:

[ c o d e ]

Code goes here

[ / c o d e]

Tara Kizer

thomaswoof67
Starting Member

3 Posts

Posted - 2007-03-02 : 14:09:12

Hi Tara;

Weird, I tried it multiple times before, and it didn't work. I must've mis-typed repeatedly, 'cause now it does work. Bugger. =)

Thanks!


ALTER PROCEDURE dbo.spGetOverdueEmployeesAllInOne

	(
		@petsID int
	)

AS


SET NOCOUNT ON



--local temp tables are only visible in current session of current user, so can have multiple sessions creating same-fullnamed temp tables, do not need a unique fullname


if exists (
	select  *
	from
		tempdb.dbo.sysobjects o
	where
		o.xtype in ('U')	and
		o.id = object_id( N'tempdb..#PETSEMPLOYEEBRANCHTEMP' )
	)
drop table #PETSEMPLOYEEBRANCHTEMP

CREATE TABLE #PETSEMPLOYEEBRANCHTEMP (
[petsID] [int] IDENTITY (1, 1) NOT NULL,
[fullname] [varchar] (50),
[Supervisor] [int]
) ON [PRIMARY]

create index idx_petsID on #PETSEMPLOYEEBRANCHTEMP(petsID)
create index idx_supervisor on #PETSEMPLOYEEBRANCHTEMP(supervisor)

exec spEmployeeBranchRecursiveLoop @petsID

/*
if exists (
	select  *
	from
		tempdb.dbo.sysobjects o
	where
		o.xtype in ('U')	and
		o.id = object_id( N'tempdb..#ACTIONEMPLOYEES' )
	)
drop table #OVERDUEEMPLOYEES

CREATE TABLE #OVERDUEEMPLOYEES (
[petsID] [int],
[fullname] [varchar] (50),
[Supervisor] [int]
) ON [PRIMARY]

Insert into #OVERDUEEMPLOYEES
exec spGetOverdueJobPlans  @petsID

insert into #OVERDUEEMPLOYEES
exec spGetOverdueEvals @petsID

select *
from #OVERDUEEMPLOYEES
order by fullname

drop table #OVERDUEEMPLOYEES
*/


----Incomplete job plans
------exists
--------first job plan

 
Select pebt.petsID, pebt.fullname, pebt.supervisor 
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
where  (select count(*)
	from evaluation ev
	inner join jobplan jpinner on jpinner.jobplanid = ev.jobplanid
	where jpinner.petsuserid = pebt.petsID
	and ev.EvaluationTypeID = 1) = 0
and jp.JobPlanCompletedDate is null
and GetDate() > DateAdd(day, 14, jp.JobPlanDueDate)
and pebt.supervisor is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union
 
--------not first job plan
Select pebt.petsID, pebt.fullname, pebt.supervisor 
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
where  (select count(*)
	from evaluation ev
	inner join JobPlan jpinner on jpinner.jobplanid = ev.jobplanid
	where jpinner.petsuserid = pebt.petsID
	and ev.EvaluationTypeID = 1) >= 1
and jp.JobPlanCompletedDate is null
and GetDate() > jp.JobPlanDueDate
and supervisor is not null --still don't know why it's returning nulls without this
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union


------doesn't exist
--------first job plan
Select pebt.petsID, pebt.fullname, pebt.supervisor 
from #PETSEMPLOYEEBRANCHTEMP pebt
where (select count(*)
	from jobplan jp
	where jp.PetsUserID = pebt.petsID
	and jp.jobplanhistoricalrecord is null) = 0
and	GetDate() > DateAdd(day, 14, (Select pu.PetsUserAppointedDate
			from PetsUser pu
			where pu.PetsUserID = pebt.petsID))
and 	pebt.supervisor is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

--------not first job plan
Select pebt.petsID, pebt.fullname, pebt.supervisor 
from #PETSEMPLOYEEBRANCHTEMP pebt
where  (select count(*)
	from evaluation ev
	inner join (select top 1 jpinner.jobplanid, jpinner.jobplanduedate
		from JobPlan jpinner
		where jpinner.petsuserid = pebt.petsID
		order by jpinner.jobplanid desc)jpouter on ev.jobplanid = jpouter.jobplanid
	and ev.EvaluationTypeID = 1
	and ev.EvaluationCompletedDate is not null)
 = 1
and supervisor is not null --still don't know why it's returning nulls without this
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union


--first year
----one interim, incomplete (3 month)
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
inner join petsuser pu on pu.petsuserid = jp.petsuserid
--inner join evaluation ev on ev.jobplanid = jp.jobplanid
--one non-historical eval exists
where (select count (*)
	from evaluation ev1
	inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
		where jp1.petsuserid = jp.petsuserid
		and jp1.jobplanhistoricalrecord is null) = 1 
--one non-historical incomplete
and (select count(*)
	from evaluation ev2
	inner join jobplan jp1 on jp1.jobplanid = ev2.jobplanid
	where jp1.petsuserid = jp.petsuserid
	and jp1.jobplanhistoricalrecord is null
	and ev2.evaluationcompleteddate is null) = 0
--less than a year since appointment
and DateAdd(year, 1, pu.petsuserappointeddate) >= jp.jobplanduedate 
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(month, 3, (select top 1 jp5.jobplanduedate
						from jobplan jp5
						where jp5.petsuserid = pebt.petsID
						order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

------nine month, 1 interim (incomplete) since prob
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
inner join petsuser pu on pu.petsuserid = jp.petsuserid
--inner join evaluation ev on ev.jobplanid = jp.jobplanid
--one non-historical probationary
where (select count (*)
	from evaluation ev1
	inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
		where jp1.petsuserid = jp.petsuserid
		and jp1.jobplanhistoricalrecord is null
		and ev1.evaluationtypeid = 2) = 1 
--1 interim after probationary
and (select count (*) --get count of interims
	from evaluation ev2
	inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
	where jp2.petsuserid = pu.petsuserid
	and jp2.jobplanhistoricalrecord is null
	and ev2.evaluationtypeid = 3
	--where creation date is greater than probationary completion date
	and ev2.evaluationcreationdate > 
		(select top 1 ev4.evaluationcreationdate
		from evaluation ev4
		inner join jobplan jp4 on jp4.jobplanid = ev4.jobplanid
		where jp4.petsuserid = pu.petsuserid
		and jp4.jobplanhistoricalrecord is null
		and ev4.evaluationtypeid = 2
		order by ev4.evaluationcreationdate desc)) = 1   
--1 incomplete interim after probationary
and (select count(*)
	from evaluation ev2
	inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
	where jp2.petsuserid = pu.petsuserid
	and jp2.jobplanhistoricalrecord is null
	and ev2.evaluationtypeid = 3
	--where creation date is greater than probationary completion date
	and ev2.evaluationcreationdate > 
		(select top 1 ev4.evaluationcreationdate
		from evaluation ev4
		inner join jobplan jp4 on jp4.jobplanid = ev4.jobplanid
		where jp4.petsuserid = pu.petsuserid
		and jp4.jobplanhistoricalrecord is null
		and ev4.evaluationtypeid = 2
		order by ev4.evaluationcreationdate desc)
	and ev2.evaluationcompleteddate is null) = 0
--less than a year since appointment
and DateAdd(year, 1, pu.petsuserappointeddate) >= jp.jobplanduedate 
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(month, 8, (select top 1 jp5.jobplanduedate
						from jobplan jp5
						where jp5.petsuserid = pebt.petsID
						order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

--semi-yearly eval exists overdue
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
--inner join petsuser pu on pu.petsuserid = jp.petsuserid
--at least one non-historical annual
where (select count (*)
	from evaluation ev1
	inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
		where jp1.petsuserid = jp.petsuserid
		and jp1.jobplanhistoricalrecord is null
		and ev1.evaluationtypeid = 1) > 0 
--1 interim after latest jobplan due date
and (select count (*) --get count of interims
	from evaluation ev2
	inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
	where jp2.petsuserid = pebt.petsID
	and jp2.jobplanhistoricalrecord is null
	and ev2.evaluationtypeid = 3
	--where creation date is greater than probationary completion date
	and ev2.evaluationcreationdate > 
		(select top 1 jp4.jobplanduedate
		from  jobplan jp4
		where jp4.petsuserid = pebt.petsID
		and jp4.jobplanhistoricalrecord is null
		order by jp4.jobplanduedate desc)) = 1   
--last evaluation is incomplete

and (select count(*)
	from evaluation ev5
	inner join jobplan jp5 on jp5.jobplanid = ev5.jobplanid
	where jp5.petsuserid = pebt.petsID
	and jp5.jobplanhistoricalrecord is null
	and ev5.evaluationcompleteddate is null) = 0
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(month, 6, (select top 1 jp5.jobplanduedate
						from jobplan jp5
						where jp5.petsuserid = pebt.petsID
						order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

--interim eval, not exist
----first year
-------zero evals
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
inner join petsuser pu on pu.petsuserid = jp.petsuserid
--one non-historical probationary
where (select count (*)
	from evaluation ev1
	inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
		where jp1.petsuserid = pebt.petsID
		and jp1.jobplanhistoricalrecord is null) = 0 
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
--less than a year since appointment
and DateAdd(year, 1, pu.petsuserappointeddate) >= jp.jobplanduedate 
--over 3 months past latest jp due date
and DateAdd(month, 3, (select top 1 jp5.jobplanduedate
						from jobplan jp5
						where jp5.petsuserid = pebt.petsID
						order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

------nine month, 1 interim (incomplete) since prob
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
inner join petsuser pu on pu.petsuserid = jp.petsuserid
--inner join evaluation ev on ev.jobplanid = jp.jobplanid
--one non-historical probationary
where (select count (*)
	from evaluation ev1
	inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
		where jp1.petsuserid = jp.petsuserid
		and jp1.jobplanhistoricalrecord is null
		and ev1.evaluationtypeid = 2) = 1 
--0 interim after probationary
and (select count (*) --get count of interims
	from evaluation ev2
	inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
	where jp2.petsuserid = pu.petsuserid
	and jp2.jobplanhistoricalrecord is null
	and ev2.evaluationtypeid = 3
	--where creation date is greater than last jobplan due date
	and ev2.evaluationcreationdate > 
		(select top 1 ev4.evaluationcreationdate
		from evaluation ev4
		inner join jobplan jp4 on jp4.jobplanid = ev4.jobplanid
		where jp4.petsuserid = pu.petsuserid
		and jp4.jobplanhistoricalrecord is null
		and ev4.evaluationtypeid = 2
		order by ev4.evaluationcreationdate desc)) = 0  
--less than a year since appointment
and DateAdd(year, 1, pu.petsuserappointeddate) >= jp.jobplanduedate 
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(month, 9, (select top 1 jp5.jobplanduedate
						from jobplan jp5
						where jp5.petsuserid = pebt.petsID
						order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

----semi-yearly eval not exist overdue
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
--inner join petsuser pu on pu.petsuserid = jp.petsuserid
--at least one non-historical annual
where (select count (*)
	from evaluation ev1
	inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
		where jp1.petsuserid = jp.petsuserid
		and jp1.jobplanhistoricalrecord is null
		and ev1.evaluationtypeid = 1) > 0 
--0 interim after latest jobplan due date
and (select count (*) --get count of interims
	from evaluation ev2
	inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
	where jp2.petsuserid = pebt.petsID
	and jp2.jobplanhistoricalrecord is null
	and ev2.evaluationtypeid = 3
	--where creation date is greater than last jobplan due date
	and ev2.evaluationcreationdate > 
		(select top 1 jp4.jobplanduedate
		from  jobplan jp4
		where jp4.petsuserid = pebt.petsID
		and jp4.jobplanhistoricalrecord is null
		order by jp4.jobplanduedate desc)) = 0   
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(month, 6, (select top 1 jp5.jobplanduedate
						from jobplan jp5
						where jp5.petsuserid = pebt.petsID
						order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

----probationary eval exist overdue
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
inner join petsuser pu on pu.petsuserid = jp.petsuserid
where (select count (*)
	from evaluation ev1
	inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
		where jp1.petsuserid = jp.petsuserid
		and jp1.jobplanhistoricalrecord is null
		and ev1.evaluationcompleteddate is null
		and ev1.evaluationtypeid = 2) = 1 
--less than a year since appointment
and DateAdd(year, 1, pu.petsuserappointeddate) >= jp.jobplanduedate 
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(month, 6, (select top 1 jp5.jobplanduedate
						from jobplan jp5
						where jp5.petsuserid = pebt.petsID
						order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

----probationary eval not exist overdue
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
inner join petsuser pu on pu.petsuserid = jp.petsuserid
--no probationary
where (select count (*)
	from evaluation ev1
	inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
		where jp1.petsuserid = jp.petsuserid
		and jp1.jobplanhistoricalrecord is null
		and ev1.evaluationtypeid = 2) = 0 
--no incomplete evals
and (select count (*)
	from evaluation ev1
	inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
		where jp1.petsuserid = jp.petsuserid
		and jp1.jobplanhistoricalrecord is null
		and ev1.evaluationcompleteddate is null
		and ev1.evaluationtypeid = 3) = 0 
--at least one interim eval
and (select count (*)
	from evaluation ev1
	inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
		where jp1.petsuserid = jp.petsuserid
		and jp1.jobplanhistoricalrecord is null
		and ev1.evaluationtypeid = 3) > 0 
--less than a year since appointment
and DateAdd(year, 1, pu.petsuserappointeddate) >= jp.jobplanduedate 
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(month, 6, (select top 1 jp5.jobplanduedate
						from jobplan jp5
						where jp5.petsuserid = pebt.petsID
						order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

--annual exists
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
where (select count (*) --one incomplete annual (non-historical
	from evaluation ev1
	inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
		where jp1.petsuserid = jp.petsuserid
		and jp1.jobplanhistoricalrecord is null
		and ev1.evaluationcompleteddate is null
		and ev1.evaluationtypeid = 1) = 1 
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(year, 1, (select top 1 jp5.jobplanduedate
						from jobplan jp5
						where jp5.petsuserid = pebt.petsID
						order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

--annual not exist overdue
----first year
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
inner join petsuser pu on pu.petsuserid = jp.petsuserid
--no annual
where (select count (*)
	from evaluation ev1
	inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
		where jp1.petsuserid = jp.petsuserid
		and jp1.jobplanhistoricalrecord is null
		and ev1.evaluationtypeid =1) = 0 
--one non-historical probationary
and (select count (*)
	from evaluation ev1
	inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
		where jp1.petsuserid = jp.petsuserid
		and jp1.jobplanhistoricalrecord is null
		and ev1.evaluationtypeid = 2) = 1 
--1 interim after probationary
and (select count (*) --get count of interims
	from evaluation ev2
	inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
	where jp2.petsuserid = pu.petsuserid
	and jp2.jobplanhistoricalrecord is null
	and ev2.evaluationtypeid = 3
	--where creation date is greater than probationary completion date
	and ev2.evaluationcreationdate > 
		(select top 1 ev4.evaluationcreationdate
		from evaluation ev4
		inner join jobplan jp4 on jp4.jobplanid = ev4.jobplanid
		where jp4.petsuserid = pu.petsuserid
		and jp4.jobplanhistoricalrecord is null
		and ev4.evaluationtypeid = 2
		order by ev4.evaluationcreationdate desc)) = 1   
--last interim after probationary is complete
and (select count(*)
	from evaluation ev2
	inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
	where jp2.petsuserid = pu.petsuserid
	and jp2.jobplanhistoricalrecord is null
	and ev2.evaluationtypeid = 3
	--where creation date is greater than probationary completion date
	and ev2.evaluationcreationdate > 
		(select top 1 ev4.evaluationcreationdate
		from evaluation ev4
		inner join jobplan jp4 on jp4.jobplanid = ev4.jobplanid
		where jp4.petsuserid = pu.petsuserid
		and jp4.jobplanhistoricalrecord is null
		and ev4.evaluationtypeid = 2
		order by ev4.evaluationcreationdate desc)
	and ev2.evaluationcompleteddate is null) = 0
--less than a year since appointment
and DateAdd(year, 1, pu.petsuserappointeddate) >= jp.jobplanduedate 
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(year, 1, (select top 1 jp5.jobplanduedate
						from jobplan jp5
						where jp5.petsuserid = pebt.petsID
						order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor

union

--annual not exist overdue
----not first year
Select pebt.petsID, pebt.fullname, pebt.supervisor
from #PETSEMPLOYEEBRANCHTEMP pebt
inner join JobPlan jp on jp.PetsUserID = pebt.petsID
--at least one annual
where (select count (*)
	from evaluation ev1
	inner join jobplan jp1 on jp1.jobplanid = ev1.jobplanid
		where jp1.petsuserid = pebt.petsID
		and jp1.jobplanhistoricalrecord is null
		and ev1.evaluationtypeid = 1) >= 1 
--at least 1 interim after latest jobplan due date
and (select count (*) --get count of interims
	from evaluation ev2
	inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
	where jp2.petsuserid = pebt.petsID
	and jp2.jobplanhistoricalrecord is null
	and ev2.evaluationtypeid = 3
	--where creation date is greater than latest jobplan due date
	and ev2.evaluationcreationdate > 
		(select top 1 jp4.jobplanduedate
		from jobplan jp4
		where jp4.petsuserid = pebt.petsID
		and jp4.jobplanhistoricalrecord is null
		order by jp4.jobplanduedate desc)) > 0   
--no incomplete interims after last jp due date
and (select count (*)
	from evaluation ev2
	inner join jobplan jp2 on jp2.jobplanid = ev2.jobplanid
	where jp2.petsuserid = pebt.petsID
	and jp2.jobplanhistoricalrecord is null
	and ev2.evaluationtypeid = 3
	and ev2.evaluationcompleteddate is null
	--where creation date is greater than latest jobplan due date
	and ev2.evaluationcreationdate > 
		(select top 1 jp4.jobplanduedate
		from jobplan jp4 
		where jp4.petsuserid = pebt.petsID
		and jp4.jobplanhistoricalrecord is null
		order by jp4.jobplanduedate desc)) = 0   
--no annual after last interim
and (select count (*)
	from evaluation ev5
	inner join jobplan jp5 on jp5.jobplanid = ev5.jobplanid
	where jp5.petsuserid = pebt.petsID
	and jp5.jobplanhistoricalrecord is null
	and ev5.evaluationtypeid = 1
	--where creation date is greater than last interim date
	and ev5.evaluationcreationdate > 
		(select top 1 ev6.evaluationcreationdate
		from evaluation ev6
		inner join jobplan jp6 on jp6.jobplanid = ev6.jobplanid
		where jp6.petsuserid = pebt.petsID
		and jp6.jobplanhistoricalrecord is null
		and ev6.evaluationtypeid = 3
		order by ev6.evaluationcreationdate desc)) = 0
--last jp complete
and jp.jobplancompleteddate is not null
--not historical record
and jp.jobplanhistoricalrecord is null
and DateAdd(year, 1, (select top 1 jp5.jobplanduedate
						from jobplan jp5
						where jp5.petsuserid = pebt.petsID
						order by jp5.jobplanduedate desc)) < GetDate()
and pebt.fullname is not null
--group by pebt.petsID, pebt.fullname, pebt.supervisor



drop table #PETSEMPLOYEEBRANCHTEMP
	RETURN

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 14:50:41

Have you tried changing UNION to UNION ALL?

Peter Larsson
Helsingborg, Sweden

thomaswoof67
Starting Member

3 Posts

Posted - 2007-03-02 : 17:35:15

Peter;

Excellent! Got my execute time to down around 4 seconds! Which is reasonable for a web app, this web app at least.

Thank you so much. I'm going to look up the difference between UNION and UNION ALL and see exactly what it does and when I should use it.

Thanks!
Thomas

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 18:00:28

FIRST SELECT. Substitute what you have to this

select		pebt.petsID,
		pebt.fullname,
		pebt.supervisor 
from		#PETSEMPLOYEEBRANCHTEMP as pebt
inner join	JobPlan as jp on jp.PetsUserID = pebt.petsID
left join	evaluation as ev on ev.jobplanid = jp.jobplanid and ev.EvaluationTypeID = 1
where		ev.jobplanid is null
		and jp.JobPlanCompletedDate is null
		and dateadd(day, -14, GetDate()) > jp.JobPlanDueDate
		and pebt.supervisor is not null

See if it produces the same records and see the difference in time to get them.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 18:29:58

First three selects can be substituted with these

select		pebt.petsID,
		pebt.fullname,
		pebt.supervisor 
from		#PETSEMPLOYEEBRANCHTEMP as pebt
inner join	JobPlan as jp on jp.PetsUserID = pebt.petsID
left join	evaluation as ev on ev.jobplanid = jp.jobplanid and ev.EvaluationTypeID = 1
where		ev.jobplanid is null
		and jp.JobPlanCompletedDate is null
		and dateadd(day, -14, GetDate()) > jp.JobPlanDueDate
		and pebt.supervisor is not null

union all

Select		pebt.petsID,
		pebt.fullname,
		pebt.supervisor 
from		#PETSEMPLOYEEBRANCHTEMP as pebt
inner join	JobPlan as jp on jp.PetsUserID = pebt.petsID
inner join	evaluation as ev on ev.jobplanid = jp.jobplanid
where		ev.EvaluationTypeID = 1
		and jp.JobPlanCompletedDate is null
		and GetDate() > jp.JobPlanDueDate
		and pebt.supervisor is not null 

union all

Select		pebt.petsID,
		pebt.fullname,
		pebt.supervisor 
from		#PETSEMPLOYEEBRANCHTEMP as pebt
inner join	PetsUser as pu on pu.PetsUserID = pebt.petsID
left join	jobplan as jp on jp.PetsUserID = pebt.petsID and jp.jobplanhistoricalrecord is null
where		jp.petsuserid is null
		and dateadd(day, -14, GetDate()) > pu.PetsUserAppointedDate
		and pebt.supervisor is not null

As you can see, almost all of your code ca be replaces with must faster ones with higher performance.
Maybe, if you could provide sample data for the three tables involved, we can come up with some better query than you have now.
I have to say that is one beast of a badly written query...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources