SQL Query is taking too much time

[ashu2409]

Hi,

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.

Thanks,
Ashish Pandey

[sknake]

Are your tables indexed properly and are the transactions completing and closing? Have you ran the query in the SQL Query analyzer with statistics enabled? Is your web application using parameterized SQL or are you building the queries dynamically? How many rows are in the underlying table that udfn_Distance checks?

[ashu2409]

Hi,

Thanks for reply,

Yes i have created the index on columns of one main table hjs_job_details.this is the main job table which has millions records.
should i have to create the index on more tables?

i have also checked the query in query analyzer (no idea about statistics enabled ?).it is taking the time there too.

web application is using parameterized SQL.

function udfn_Distance checks over 200000 rows.