June 19, 2013 at 12:38 am
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 ...
June 19, 2013 at 12:59 am
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
August 6, 2013 at 10:03 am
Can you share the table definition, query, and number of unfiltered rows in the table?
August 6, 2013 at 11:00 am
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
August 6, 2013 at 4:07 pm
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]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy