higher cpu execution time

Question

Post reply

higher cpu execution time

IT researcher

SSCertifiable

Points: 7567
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 ...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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: 5940 More actions · Answer 2

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

Lowell SSC Guru Points: 323504 More actions · Answer 3

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 · Answer 4

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]