higher cpu execution time

• IT researcher

  SSCertifiable

  Points: 7616

  More actions

  June 19, 2013 at 12:38 am

  #294874

  I have a very simple query to select all columns from a table if a particular column does not a character...but its cpu time is very high

  total_cpu_time: 2953125

  total_execution_count: 7

  number_of_statements:1

  what could be the probable reason for this ...

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 19, 2013 at 12:59 am

  #1625294

  No idea. Not enough information.

  Please post query, table definition and index definitions,

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Sean Smith

  SSCertifiable

  Points: 6023

  More actions

  August 6, 2013 at 10:03 am

  #1639006

  Can you share the table definition, query, and number of unfiltered rows in the table?

• Lowell

  SSC Guru

  Points: 323522

  More actions

  August 6, 2013 at 11:00 am

  #1639034

  well in general, "particular column does not a character" would mean a NOT LIKE '%X%' pr PATINDEX =0, right?, so that would requre a table scan of all rows, so i'm no suprised it has a big impact; every value has to be evaluated.

  that would not be Sargable, so it cannot use an index.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Erland Sommarskog

  SSC-Insane

  Points: 24038

  More actions

  August 6, 2013 at 4:07 pm

  #1639161

  Lowell (8/6/2013)

  ---

  well in general, "particular column does not a character" would mean a NOT LIKE '%X%' pr PATINDEX =0, right?, so that would requre a table scan of all rows, so i'm no suprised it has a big impact; every value has to be evaluated.

  And furthermore, it requires a full scan of every column value, which is very expensive if the full rules of Unicode must be applied. Which they must, unless any of the below are true:

  1) You have a binary collation.

  2) The column is varchar and you have an SQL collation.

  [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]