Substring takes 100%CPU

• sundar329

  Say Hey Kid

  Points: 680

  More actions

  July 21, 2013 at 11:43 am

  #296651

  SELECT

  CASE WHEN substring(column_name, 1, 2) = '44' THEN column_name ELSE '44' + column_name

  END 'column_name', min(col2) 'date'

  FROM DBNAME..TABLENAME(nolock)

  WHERE col3 = '1' and col4 not in('447404000130','7404000130')

  group by CASE WHEN substring(column_name, 1, 2) = '44' THEN column_name ELSE '44' + column_name END

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 21, 2013 at 12:06 pm

  #1634320

  Give this a try:

  SELECT

  CASE WHEN column_name like '44%' THEN column_name ELSE '44' + column_name END 'column_name',

  min(col2) 'date'

  FROM

  DBNAME..TABLENAME --(nolock) << NOLOCK is a bad idea, sure others will explain

  WHERE

  col3 = '1' AND

  col4 not in('447404000130','7404000130')

  group by

  CASE WHEN column_name like '44%' THEN column_name ELSE '44' + column_name END

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  July 21, 2013 at 10:12 pm

  #1634339

  You can also refer this link posted by Gail to another forum:

  http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

  _______________________________________________________________
  To get quick answer follow this link:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/