October 28, 2009 at 12:25 am
Hi there
This simple query below kills our server performance bcos this logic is used in lot of our SPs
if there is a better way pls help.
@sectionID <=0
OR a.section_id = @sectionID
this above code does parallelism but if i comment 1 line it dosent
--@sectionID <=0 OR
a.section_id = @sectionID
and also if there is a better way of writing this logic pls help
SELECT a.[id]
FROM articles a WITH (NOLOCK)
WHERE a.active_status = 1 AND a.approved_status = 1 AND a.site_id = @siteid
AND (
@sectionID <=0
OR a.section_id = @sectionID
)
AND (
@subsectionID <=0
OR a.subsection_id in(
SELECT [id]
FROM sbk_subsection sub_sst WITH (NOLOCK)
WHERE sub_sst.section_id = @sectionid
AND
(
sub_sst.subsection_name = @subsectionName
OR sub_sst.subsection_name LIKE @subsectionName + '[_]%'
)
)
)
AND (getdate() >= a.publish_date OR a.publish_date IS NULL)
AND (getdate() <= a.end_date OR a.end_date IS NULL)
ORDER BY a.article_date DESC, title
there are 494569 records in articles table
Cheers
October 28, 2009 at 3:42 am
I suggest you to read this article on how to post performace problems
http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
With a quick look I see a couple of potential problems:
1) NOLOCK is bad, it could return inconsistent data, due to page splits and dirty reads
2) The "OR" condition you are commenting always leads to a index scan instead of a seek: try to rewrite it
Regards
Gianluca
-- Gianluca Sartori
October 28, 2009 at 3:53 am
Gail Shaw has written a fine article on this sort of query, which addresses just the sort of issues you are seeing.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
October 28, 2009 at 3:57 am
If you want a more in-depth reference on the subject, you could also read Erland Somarskog's blog:
http://www.sommarskog.se/dyn-search-2005.html
-- Gianluca Sartori
October 28, 2009 at 4:21 am
wow that was great article thats exactly what i was looking for
thankx guys
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply