query in SP shwoing high no of reads | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

query in SP shwoing high no of reads

I have this query in one of my stored procedures: This particular query shows a high number of reads when the stored procedure is run and I profile it. When I hardcode the values for these variables and run this query form QA I see substatially better performance and lesser reads. SELECT ‘Sub section 2’,a.a_webc_url
FROM pfieldnet_property_table_s a join pfieldnet_property_table_r b
ON a.a_webc_url = b.a_webc_url
WHERE (b.a_expiration_date IS NULL OR b.a_expiration_date >= getdate())
and a_effective_date BETWEEN DATEADD(m,
case when @pfn_allfiles_date_range=’1′ then -1
when @pfn_allfiles_date_range = ‘2’ then -6
when @pfn_allfiles_date_range = ‘3’ then -12 end,
getdate()) AND getdate()
AND i_full_format = ‘smhtml’
AND a.a_webc_url in (SELECT c.a_webc_url
FROM pfieldnet_property_table_r as c,
pfn_check_content_assignment e
WHERE pfn_additional_loc = @location and c.a_webc_url = e.a_webc_url
and territory_cd = @territory_cd)
ORDER BY a_effective_date desc,r_creation_date desc
Is it because the SP cannot think of a good execution plan beacuse of the many variables or could it be something else? Thanx
for more information on execution plan refer this
Madhivanan Failing to plan is Planning to fail
.. and thishttp://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp too. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.