Q:

How to speed up stored procedure in SQL Server 2008 R2

I have complex relation and big data in my SQL Server 2008 R2 database. 
My problem is to long (33 Seconds) with 83 data  when execute. Can you help me to optimized this stored procedure? This query use to calculate attendance, late, overtime employee in date range and finally insert every calculate to table to show recapitulation
USE [DbHumanResourceDevelopment]
GO
/****** Object:  StoredProcedure [dbo].[InputRekapitulasiLooping]    Script Date: 04/17/2015 08:31:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[InputRekapitulasiLooping]
(@PersonalCalendardateAwal date , @PersonalCalendardateAkhir date )
as 
delete EmployeeRekapitulasi
Declare @TotalWorkingDay int
declare @totalholiday int
declare @workingday int
declare @doattandance int
declare @absence int
declare @s1 int 
declare @s2 int
declare @s3 int 
declare @s4 int 
declare @s5 int 
declare @s6 int 
declare @s7 int 
declare @s8 int 
declare @s9 int 
declare @s10 int 
declare @s11 int 
declare @s12 int 
declare @s13 int 
declare @jlhtelat int
declare @jlhcptpulang int
declare @jumrow int
declare @FingerPrintID int

select @jumrow = 20
while @jumrow > 0
begin
select @FingerPrintID = FingerPrintID from
(Select ROW_NUMBER() over (order by FingerPrintID) as Row,
FingerPrintID from tblkaryawan) as FingerPrintID where Row = @jumrow
select @jumrow = @jumrow - 1
select @workingday = ( select COUNT(Hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan ='' and PersonalCalendarStatus = 0  and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir
)
select @doattandance = ( select COUNT(hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan ='' and JamMasuk <> '' and PersonalCalendarStatus = 0 and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir
)
select @absence = (( select COUNT(Hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan ='' and PersonalCalendarStatus = 0 and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir
) - ( select COUNT(hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan ='' and JamMasuk <> '' and PersonalCalendarStatus = 0 and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir
))
select @TotalWorkingDay=( select COUNT(Hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and PersonalCalendarStatus = 0  and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir
)
select @totalholiday=( select COUNT(Catatan) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan <>''  and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir
)
select @s1=(select COUNT(hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan <>'' and PersonalCalendarStatus = 45  and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir)
select @s2=(select COUNT(hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan <>'' and PersonalCalendarStatus = 46  and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir)
select @s3=(select COUNT(hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan <>'' and PersonalCalendarStatus = 47  and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir)
select @s4=(select COUNT(hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan <>'' and PersonalCalendarStatus = 48  and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir)
select @s5=(select COUNT(hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan <>'' and PersonalCalendarStatus = 49  and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir)
select @s6=(select COUNT(hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan <>'' and PersonalCalendarStatus = 50  and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir)
select @s7=(select COUNT(hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan <>'' and PersonalCalendarStatus = 51  and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir)

select @s8=(select COUNT(hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan <>'' and PersonalCalendarStatus = 52  and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir)
select @s9=(select COUNT(hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan <>'' and PersonalCalendarStatus = 53  and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir) 
select @s10=(select COUNT(hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan <>'' and PersonalCalendarStatus = 54  and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir)
select @s11=(select COUNT(hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan <>'' and PersonalCalendarStatus = 55  and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir)
 select @s12=(select COUNT(hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan <>'' and PersonalCalendarStatus = 56  and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir)
select @s13=(select COUNT(hari) from ViewLaporanKaryawanFix where Hari <> 'Sun' and Catatan <>'' and PersonalCalendarStatus = 57  and  FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir)
select @jlhtelat = (select COUNT(status) from ViewLaporanKaryawanFix  where Status = 'telat' and FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir)
select @jlhcptpulang = (select COUNT(StatusPulang) from ViewLaporanKaryawanFix  where StatusPulang = 'Pulang Awal' and FingerPrintID = @FingerPrintID and PersonalCalendardate between @PersonalCalendardateAwal and @PersonalCalendardateAkhir)

insert into EmployeeRekapitulasi values (@FingerPrintID,@TotalWorkingDay,@totalholiday,@workingday,@doattandance,@absence,
@s1,@s2,@s3,@s4,@s5,@s6,@s7,@s8,@s9,@s10,@s11,@s12,@s13,@jlhtelat,@jlhcptpulang,0,0,0,0,0,convert(varchar(11),@PersonalCalendardateAwal )+ ' s/d ' + convert(varchar(11),@PersonalCalendardateAkhir))

end

Please help me to optimize this stored procedure

A:

If I understand correctly, you like to insert the first 20 FingerPrintId and the calculated data for each. You're doing in RBAR fashion. You need do this in a set-based manner. You can actually do this using a single SELECT statement.
ALTER proc [dbo].[InputRekapitulasiLooping](
    @PersonalCalendardateAwal DATE,
    @PersonalCalendardateAkhir DATE
)
AS

DELETE FROM EmployeeRekapitulasi

--INSERT INTO EmployeeRekapitulasi
SELECT
    v.FingerPrintId,
    COUNT(CASE WHEN v.Hari <> 'Sun' AND v.PersonalCalendarStatus = 0  THEN v.Hari END),
    COUNT(CASE WHEN v.Hari <> 'Sun' AND v.Catatan <> '' THEN v.Hari END),
    COUNT(CASE WHEN v.Hari <> 'Sun' AND v.Catatan = '' AND v.PersonalCalendarStatus = 0 THEN v.Hari END),
    COUNT(CASE WHEN v.Hari <> 'Sun' and v.Catatan = '' AND v.PersonalCalendarStatus = 0 AND v.JamMasuk <> '' THEN v.Hari END),  
    COUNT(CASE WHEN v.Hari <> 'Sun' AND v.Catatan = '' AND v.PersonalCalendarStatus = 0 THEN v.Hari END) -
        COUNT(CASE WHEN v.Hari <> 'Sun' AND v.Catatan = '' AND v.PersonalCalendarStatus = 0 AND v.JamMasuk <> '' THEN v.Hari END),
    COUNT(CASE WHEN v.Hari <> 'Sun' AND v.Catatan <> '' AND v.PersonalCalendarStatus = 45 THEN v.Hari END),
    COUNT(CASE WHEN v.Hari <> 'Sun' AND v.Catatan <> '' AND v.PersonalCalendarStatus = 46 THEN v.Hari END),
    COUNT(CASE WHEN v.Hari <> 'Sun' AND v.Catatan <> '' AND v.PersonalCalendarStatus = 47 THEN v.Hari END),
    COUNT(CASE WHEN v.Hari <> 'Sun' AND v.Catatan <> '' AND v.PersonalCalendarStatus = 48 THEN v.Hari END),
    COUNT(CASE WHEN v.Hari <> 'Sun' AND v.Catatan <> '' AND v.PersonalCalendarStatus = 49 THEN v.Hari END),
    COUNT(CASE WHEN v.Hari <> 'Sun' AND v.Catatan <> '' AND v.PersonalCalendarStatus = 50 THEN v.Hari END),
    COUNT(CASE WHEN v.Hari <> 'Sun' AND v.Catatan <> '' AND v.PersonalCalendarStatus = 51 THEN v.Hari END),
    COUNT(CASE WHEN v.Hari <> 'Sun' AND v.Catatan <> '' AND v.PersonalCalendarStatus = 52 THEN v.Hari END),
    COUNT(CASE WHEN v.Hari <> 'Sun' AND v.Catatan <> '' AND v.PersonalCalendarStatus = 53 THEN v.Hari END),
    COUNT(CASE WHEN v.Hari <> 'Sun' AND v.Catatan <> '' AND v.PersonalCalendarStatus = 54 THEN v.Hari END),
    COUNT(CASE WHEN v.Hari <> 'Sun' AND v.Catatan <> '' AND v.PersonalCalendarStatus = 55 THEN v.Hari END),
    COUNT(CASE WHEN v.Hari <> 'Sun' AND v.Catatan <> '' AND v.PersonalCalendarStatus = 56 THEN v.Hari END),
    COUNT(CASE WHEN v.Hari <> 'Sun' AND v.Catatan <> '' AND v.PersonalCalendarStatus = 57 THEN v.Hari END),
    COUNT(CASE WHEN v.status = 'telat' THEN v.status END),
    COUNT(CASE WHEN v.StatusPulang = 'Pulang Awal' THEN v.StatusPulang END),
    0,
    0,
    0,
    0,
    0,
    CONVERT(VARCHAR(11), @PersonalCalendardateAwal) + ' s/d ' + CONVERT(VARCHAR(11), @PersonalCalendardateAkhir)
FROM ViewLaporanKaryawanFix v
INNER JOIN( 
        SELECT TOP 20 FingerPrintId
        FROM tblkaryawan
        ORDER BY FingerPrintId  
)t On t.FingerPrintId = v.FingerPrintId
WHERE
    v.PersonalCalendardate BETWEEN @PersonalCalendardateAwal AND @PersonalCalendardateAkhir

Notes

You may want to enclose this in a single transaction.

