SQL SP runs Slow -

hi using sp takes 7 minutes in server has 7336 recrds , 6seconds in server has 3500 records.

can me know why happening?

thanks, -divya

the sp:

select worksheet_id person per inner join person emplee on emplee.person_id = per.person_id , dbo.fn_checkrptsecurity(emplee.person_id, @p_sec_account_id) > 0

  left join search_assignment_vw person_asgn    on person_asgn.assignment_id =                       dbo.fn_getrptassignmentid(emplee.person_id)   left join lookup emplee_asgn_stat  on emplee_asgn_stat.type_ = 'assignmment_status_code'  , emplee_asgn_stat.code = person_asgn.asgn_stat_code  inner join  (select w1.assignment_id, w1.worksheet_id, w1.effective_date, w1.approved_by, w3.created_by     worksheet_payroll_vw w1      inner join worksheet w3      on w3.worksheet_id = w1.worksheet_id     w1.effective_date = case                               when @p_most_recent_only = 'y'                               (select max(w2.effective_date)                                    worksheet_payroll_vw w2                                    w1.assignment_id = w2.assignment_id                                     , (isnull(@p_wks_effective_date,w2.effective_date) =w2.effective_date))                               else isnull(@p_wks_effective_date,w1.effective_date)                              end         )  person_wks  on person_wks.assignment_id = dbo.fn_getrptassignmentid(emplee.person_id)  inner join     (select assignment_id, value    assignment_history ah       field_name ='home payroll group'     , effective_date =  (select max(effective_date)                           assignment_history                            assignment_id = ah.assignment_id                            ,   effective_date <=getdate()                            ,   field_name = 'home payroll group')      )home_payroll      on home_payroll.assignment_id = dbo.fn_getrptassignmentid(emplee.person_id)          (@p_selected_person_only = 'n' or emplee.person_id = @p_person_id)      ,     (@p_asgn_stat_code null or person_asgn.asgn_stat_code = substring(@p_asgn_stat_code,1,1)      or person_asgn.asgn_stat_code = substring(@p_asgn_stat_code,2,1))      ,     (@p_policy_id null or person_asgn.program_code = @p_policy_id)      ,     (@p_home_country_id null or person_asgn.homecountryid=@p_home_country_id)       ,     (@p_home_city_id null or person_asgn.homecityid=@p_home_city_id )      ,     (@p_home_company_id null or person_asgn.homebusinessid=@p_home_company_id )      ,     (@p_home_division_id null or person_asgn.homecomponentid=@p_home_division_id )      ,     (@p_host_country_id null or person_asgn.hostcountryid=@p_host_country_id )      ,     (@p_host_city_id null or person_asgn.hostcityid=@p_host_city_id )      ,     (@p_host_company_id null or person_asgn.hostbusinessid=@p_host_company_id )      ,     (@p_host_division_id null or person_asgn.hostcomponentid=@p_host_division_id )      ,     (@p_created_by null or person_wks.created_by=@p_created_by )      ,     (@p_approved_by null or person_wks.approved_by=@p_approved_by )      ,     (@p_payroll_code null or home_payroll.value=@p_payroll_code ) 

order per.last_name asc, per.first_name asc, person_wks.effective_date desc

---

the function in 5th line 1 running slow. rest of part running in 4secs

---

the function: begin declare @v_asgn_count int, @v_result int

select @v_asgn_count = count(assignment_id) --to find out if employee has assignment assignment expatriate_person_id = @p_person_id ,       asgn_stat_code in ('pd','a','i')  if(@v_asgn_count > 0)   --yes assignment, check against security_assignment_vw begin     select @v_result = count(assignment_id)     security_assignment_vw     sec_account_id = @p_sec_account_id ,           assignment_id in (select assignment_id                 assignment                 expatriate_person_id = @p_person_id ,                       asgn_stat_code in ('pd','a','i')) end else    --no assignment, check against security_person_vw begin     select @v_result = count(person_id)     security_person_vw      sec_account_id = @p_sec_account_id ,           person_id = @p_person_id end  return @v_result 

end

do schemas match exactly... in particular check missing indexes.