December 30, 2013 at 4:56 am
Hello,
Can you please help and let me know why the next select with @start_date and @end_date doesn't work, i meant the select keep running for ever unless i stop it?
Beforehand thank you
declare @start_date datetime
declare @end_date datetime
set @start_date='2013-12-30 00:00:01.000'
set @end_date='2013-12-30 23:59:59.000'
SELECT si.site_name, si.site_addr1, si.site_addr2, si.city_name, si.zip_code, si.county_name, ai.alarminc_no,sy.cs_no,st.descr,ai.alarm_local_date,a.agency_name, ai.agency_ref_id, ai.alarm_zone_id,
ai.dispatch_local_date, ev.descr as name, u.descr as tipo
FROM alarm_incident ai INNER JOIN
system sy ON ai.system_no = sy.system_no INNER JOIN
site si ON sy.site_no = si.site_no INNER JOIN
agency a ON ai.agency_no = a.agency_no INNER JOIN
udf u ON si.udf1 = u.udf_id INNER JOIN
event_history eh ON ai.alarminc_no = eh.alarminc_no INNER JOIN
event ev ON eh.event_id = ev.event_id INNER JOIN
service_type st ON st.servtype_id=ai.servtype_id
WHERE (ai.full_clear_opactdisp_id='DP') AND (ai.alarm_local_date between @start_date and @end_date) AND (eh.full_clear_flag = 'Y')
Same select with date not like a variable works fine and quickly
SELECT si.site_name, si.site_addr1, si.site_addr2, si.city_name, si.zip_code, si.county_name, ai.alarminc_no,sy.cs_no,st.descr,ai.alarm_local_date,a.agency_name, ai.agency_ref_id, ai.alarm_zone_id,
ai.dispatch_local_date, ev.descr, udf.descr as tipo
FROM alarm_incident ai INNER JOIN
system sy ON ai.system_no = sy.system_no INNER JOIN
site si ON sy.site_no = si.site_no INNER JOIN
agency a ON ai.agency_no = a.agency_no INNER JOIN
udf ON si.udf1 = udf.udf_id INNER JOIN
event_history eh ON ai.alarminc_no = eh.alarminc_no INNER JOIN
event ev ON eh.event_id = ev.event_id INNER JOIN
service_type st ON st.servtype_id=ai.servtype_id
WHERE (ai.full_clear_opactdisp_id='DP') AND (ai.alarm_local_date between '2013-12-30 00:00:01.000' and '2013-12-30 23:59:59.000') AND (eh.full_clear_flag = 'Y')
December 30, 2013 at 5:12 am
Parameter sniffing, or more accurately lack thereof. http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
Try this, and note the slight changes I made to the dates and date comparison as well, as the original version could miss rows with the datetime very close to midnight
DECLARE @start_date DATETIME
DECLARE @end_date DATETIME
SET @start_date = '2013-12-30 00:00:00'
SET @end_date = '2013-12-31 00:00:00'
SELECT si.site_name ,
si.site_addr1 ,
si.site_addr2 ,
si.city_name ,
si.zip_code ,
si.county_name ,
ai.alarminc_no ,
sy.cs_no ,
st.descr ,
ai.alarm_local_date ,
a.agency_name ,
ai.agency_ref_id ,
ai.alarm_zone_id ,
ai.dispatch_local_date ,
ev.descr AS name ,
u.descr AS tipo
FROM alarm_incident ai
INNER JOIN system sy ON ai.system_no = sy.system_no
INNER JOIN site si ON sy.site_no = si.site_no
INNER JOIN agency a ON ai.agency_no = a.agency_no
INNER JOIN udf u ON si.udf1 = u.udf_id
INNER JOIN event_history eh ON ai.alarminc_no = eh.alarminc_no
INNER JOIN event ev ON eh.event_id = ev.event_id
INNER JOIN service_type st ON st.servtype_id = ai.servtype_id
WHERE ( ai.full_clear_opactdisp_id = 'DP' )
AND ( ai.alarm_local_date >= @start_date AND ai.alarm_local_date < @end_date )
AND ( eh.full_clear_flag = 'Y' )
OPTION (RECOMPILE)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2013 at 5:40 am
Gail,
Thanks a lot for your reply and solution, it works fine now with OPTION (RECOMPILE)
Have a happy next new year 2014 🙂
Regards
Claudio
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply