Parallelism SP tuning

• sqlcool

  SSC Eights!

  Points: 967

  More actions

  October 28, 2009 at 12:25 am

  #385659

  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

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  October 28, 2009 at 3:42 am

  #1071601

  I suggest you to read this article on how to post performace problems

  http://www.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

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  October 28, 2009 at 3:53 am

  #1071605

  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/

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  October 28, 2009 at 3:57 am

  #1071611

  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

• sqlcool

  SSC Eights!

  Points: 967

  More actions

  October 28, 2009 at 4:21 am

  #1071616

  wow that was great article thats exactly what i was looking for

  thankx guys