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
http://www.sql-server-performance.com/query_execution_plan_analysis.asp
Madhivanan Failing to plan is Planning to fail

.. and thishttp://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp too. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>