i have created a sp, that is taking too much time (more than 35 sec, is it more?).
SELECT top 11 ROW_NUMBER() OVER (ORDER BY hjs_job_details.date_posted desc, KEY_TBL.RANK desc) AS Row, job_id, (case when len(job_title)>59 then (substring(job_title,0,59)+'...') else job_title end ) as title, company_name as Source_Org, source_company_name as Org, jobdescription_url, dbo.job_posted_on(hjs_job_details.date_posted) as date_posted, (short_desc+'...') as description, street, city, hjs_state.state_abbr as state, hjs_job_details.zipcode, company_url, hjs_job_details.state as job_state,hjs_job_details.crawledby FROM hjs_job_details left outer join hjs_adv_source_type ha on hjs_job_details.source_type_id=ha.source_type_id left outer join hjs_latlong on hjs_job_details.zipcode = hjs_latlong.zipcode left outer join hjs_state on (hjs_job_details.state=hjs_state.state_abbr) left outer join hjs_service_campaign on (hjs_job_details.campaign_id = hjs_service_campaign.camp_id) INNER JOIN FREETEXTTABLE(hjs_job_details,(Job_title,short_desc,company_name),'nurse') as KEY_TBL ON hjs_job_details.job_id = KEY_TBL.[KEY] where ( (1=1) and (dbo.udfn_Distance(29.6804,-82.3457,latitude,longitude) <= 50 ) and ( ( hjs_job_details.campaign_id is null ) or (hjs_service_campaign.status <> 1) or ( dbo.CheckSponsordJobDailyBudget(job_id)=0))) and((convert(datetime,convert(nvarchar,expiry_date,101))>=convert(datetime,convert(nvarchar,getdate(),101)) OR expiry_date IS NULL) and (hjs_job_details.status=1 or hjs_job_details.status is null))
the line (dbo.udfn_Distance(29.6804,-82.3457,latitude,longitude) <= 50 ) is taking too much time when we search in 70000 records.it is for finding distance between two lat/longs, i have checked function udfn_Distance, it is working fine.it is only taking time when it takes lat/longs for records.
actually after calling this sp that 6 more sps are calling , so my page loading gets very slow, it take more than 5 mins to load full page with radius search.
i want to make it fast.how can i do that.
Please help me.