Query Result Slows Down If i Use Case Statement.

[nasirpinnacle]

I wrote a Function who returns around 800000 records in 17-20 Seconds but if i use 7 differernt simple case Statements its time increases to 03 min and 10 seconds.. Can somebody help me.

Table Structure:
Create Table Attendence (dated DateTime,
Location_ID VarChar(3),
fs_time VarChar(6),
employee_id VarChar(32),
Present SmallInt,
time_in VarChar(8),
TimeIN_AMPM VarChar(2),
time_out VarChar(8),
TimeOut_AMPM VarChar(2),
LunchTime_Out VarChar(8),
LunchTime_In VarChar(8),
over_time VarChar(8),
FullHalf TinyInt,
TypeOfLeave VarChar(12),
LCReason VarChar(100),
BTEarlyOutgoing Reason VarChar(100),
BTLCReason VarChar(100),
EarlyOutgoingRe ason VarChar(100),
ManuallyTemperr ed TinyInt,
DontCalculateOT FromTime TinyInt,

IsGazetted TinyInt,
HToM_TimeIn SmallInt,
HToM_TimeOut SmallInt,
HToM_OverTime SmallInt,
ManualOverTime Varchar(8),
HToM_ManualOver Time SmallInt,
LeaveForm_TypeO fLeave Varchar(12),
LeaveForm_FullH alf TinyInt,
CPLGivenOn DateTime,
MarkedSandwich TinyInt,
CurrentDeptt Varchar(12),
CurrentDesig Varchar(3),
CurrentShift Varchar(3),
ShortLeave_WOP TinyInt Default 0,
ShortLeave_Leav e TinyInt Default 0,
ShortLeave_Type OfLeave Varchar(12),
Constraint pk_Attendence Primary Key (Dated, fs_time, Employee_id),
Constraint fk_Attendence_E mployeeDetails Foreign Key (Employee_id) References EmployeeDetails (id),
Constraint fk_Attendence_C Deptt Foreign Key (CurrentDeptt) References DefDepartments (ID),
Constraint fk_Attendence_C Desig Foreign Key (CurrentDesig) References DefDesignations (ID),
Constraint fk_Attendence_C Shift Foreign Key (CurrentShift) References DefShifts (ID)
);
=========Functi on============= ======
Alter Function f_LeaveRecords (@EmpID Varchar(32), @FromDate VarChar(11), @ToDate VarChar(11),
@iDeptt Varchar(12), @iDesig Varchar(12))
Returns Table
as
Return (
Select T.Dated,

T.Time_In TimeIn,

T.Time_Out TimeOut,

IsNull(T.Manual lyTemperred,0) ManuallyTemperr ed,
ED.ID Employee_ID,
Case When ED.Daily=1 then 0 When ED.Monthly=1 Then 1 When ED.Contract=1 Then 2
When ED.PieceRate=1 Then 3 End TypeOfEmployee,

Case When IsNull(T.IsGaze tted,0)<>0 and IsNull(IsNull(T .LeaveForm_Type OfLeave,T.TypeO fLeave),'')='' Then 'GZ'
When IsNull(IsNull(T .LeaveForm_Type OfLeave,T.TypeO fLeave),'')='' and Present=0 and (IsNull(IsNull( T.LeaveForm_Ful lHalf,T.FullHal f),0)<>0 or ((IsNull(Time_I n,' : ')=' : ' or IsNull(Time_In, '')=''))) Then 'Absent'
Else IsNull(IsNull(T .LeaveForm_Type OfLeave,T.TypeO fLeave),'') End TypeOfLeave,

Case When ((DatePart(dw, T.Dated)=PD1.Da yNo or DatePart(dw, T.Dated)=PD2.Da yNo or IsNull(T.IsGaze tted,0)=1) and T.Present=0 and IsNull(IsNull(T .LeaveForm_Type OfLeave,T.TypeO fLeave),'')='') Then 1
When IsNull(T.Presen t, 0) <>0 Then
Case When IsNull(IsNull(T .LeaveForm_Full Half,T.FullHalf ),0)=0 Then 1 Else 0.5 End
When T.Time_IN<>' : ' and T.Time_IN<>'' and (T.Time_Out='' or T.Time_Out=' : ') Then 0
Else 0 End DayPresent,

Case When IsNull(IsNull(T .LeaveForm_Full Half,T.FullHalf ),0)=0 Then 0 Else 1 End HalfLeave,

Case When IsNull(IsNull(T .LeaveForm_Full Half,T.FullHalf ),0)<>0 Then 0.5
When IsNull(IsNull(T .LeaveForm_Type OfLeave,T.TypeO fLeave),'') In ('Absent', 'LWP', 'L.W.Pay', 'L.W/O.Pay') and T.Present=0 and ((IsNull(T.Time _In, ' : ')=' : ' or T.Time_In='') and (IsNull(T.Time_ Out, ' : ')=' : ' or T.Time_Out='')) Then 1
When IsNull(IsNull(T .LeaveForm_Type OfLeave,T.TypeO fLeave),'')<>'' and IsNull(IsNull(T .LeaveForm_Type OfLeave,T.TypeO fLeave),'')<>'R est' and IsNull(T.IsGaze tted,0)=0 Then 1 Else 0 End+
((IsNull(T.Shor tLeave_WOP,0)+I sNull(T.ShortLe ave_Leave,0))*I sNull(GS.ShortL eaveEqualToDedu ctionOfLeave,0) ) LeavesCounted,

T.CurrentDeptt Deptt_ID, T.CurrentDesig Desig_ID, T.CurrentShift Shift_ID
From Attendence T
Left Join EmployeeDetails ED on T.Employee_ID=E D.ID
-- Left Join period on period.From_DT= Period.From_DT
Left Join PrintDays PD1 on ED.RestDay=PD1. ID
Left Join PrintDays PD2 on ED.RestDayOther =PD2.ID
-- Left Join DefDepartments DD on DD.ID=T.Current Deptt
-- Left Join DefDesignations DG on DG.ID=T.Current Desig
-- Left Join DefShifts DS on DS.ID=T.Current Shift
Left Join GlobalSettings GS On 1=1
Where (@EmpID='' or T.Employee_ID=@ EmpID)
and (@FromDate='' or T.Dated Between @FromDate and @ToDate)
)
=============== =Simple Query Which Takes 19 Seconds=====
Select * from f_LeaveRecords ('', '01-Jul-2001', '30-Jun-2010', '', '') T
=============== =Other Query Which Takes More than 3 Minutes=======
Select *,
Case When T.TypeOfLeave=' Casual' Then T.LeavesCounted Else 0 End Casual,
Case When T.TypeOfLeave=' Annual' Then T.LeavesCounted Else 0 End Annual,
Case When T.TypeOfLeave=' Sick' Then T.LeavesCounted Else 0 End Sick,
Case When T.TypeOfLeave In ('LWP', 'L.W.Pay', 'L.W/O.Pay') Then T.LeavesCounted Else 0 End LWP,
Case When T.TypeOfLeave=' Absent' Then T.LeavesCounted Else 0 End Absent,
Case When T.TypeOfLeave=' GZ' Then T.LeavesCounted Else 0 End GZ,
Case When T.TypeOfLeave=' Rest' Then T.LeavesCounted Else 0 End Rest from f_LeaveRecords ('', '01-Jul-2001', '30-Jun-2010', '', '') T