Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Computed Columns


Computed Columns

Author
Message
Rose Bud
Rose Bud
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 1062
forjonathanwilson (7/21/2010)
A calculated field is not going to be A+B, because thats just a stupid test case. More likely a computed field will be connected to a UDF and be gathering data from elsewhere.


Not in my experience! Computed columns are often used in data warehousing for mundane things like FullName and Age, two examples mentioned by others in this discussion.
JohnG69
JohnG69
Mr or Mrs. 500
Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)

Group: General Forum Members
Points: 569 Visits: 452
We use them here for Total Calculations using UDF functions.
But since we are developping the application, we are beeing very careful that they are only used when there aren't too many columns returned...

For example, When calling an invoice.

Otherwise we use views for reporting purposes.

Not sure if this is recommended or not. But this is how it is done here and now they are starting to use LINQ... Not familiar with it. I might try to understand it and create a new question?!?!
JohnG69
JohnG69
Mr or Mrs. 500
Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)

Group: General Forum Members
Points: 569 Visits: 452
oops... meant number of rows returned...
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2954 Visits: 2629
Good one....got it wrong....I have to do some more reading.
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3396 Visits: 1552
jghali (7/21/2010)
We use them here for Total Calculations using UDF functions.
But since we are developping the application, we are beeing very careful that they are only used when there aren't too many columns returned...



I think it's more likely to be the UDFs (assuming they are scalar) that are the problem with many rows, since UDFs are calculated for each row, effectively making the query incur a cursor-like performance penalty. This is a problem I frequently run into when investigating slow queries on large numbers of rows.

You can reduce ore eliminate the negative effect either by persisting and indexing the computed column (assuming the UDF is deterministic) or by using a different method. Using a view won't help if the view contains the same scalar UDF as the computed column.

Duncan
ziangij
ziangij
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2910 Visits: 374
agree completely, we use computed columns for getting the full name from firstname, lastname combination...

wware (7/21/2010)
forjonathanwilson (7/21/2010)
A calculated field is not going to be A+B, because thats just a stupid test case. More likely a computed field will be connected to a UDF and be gathering data from elsewhere.


Not in my experience! Computed columns are often used in data warehousing for mundane things like FullName and Age, two examples mentioned by others in this discussion.

SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21091 Visits: 18259
Thanks for the question. I learned something today!!



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Trey Staker
Trey Staker
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1200 Visits: 2788
Nice question, made me think and I learned. I didn't know about the part of using udf's.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
How many check boxes are too many? w00t

"The engine tweaks to be performant."

I ticked that.

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8329 Visits: 11580
jghali (7/21/2010)
Sorry about the ambiguous answer.

What I was trying to get to is...

Retrieving data from a normal column is much faster then having to calculate the value every time a computed field is called.
Can you imagine, if you are calling 100 000 rows including a computed column? It would definitely take more resources specially if the computed column comes from other tables via UDFs...

Hi jghali,

First, thanks for contributing a question, in spite of the harsh comments so often targeted at QotD contributers. Please submit more in the future!

On your (rhetoric?) question: "Can you imagine, if you are calling 100 000 rows including a computed column? It would definitely take more resources (...)" (and I omitted the last part if this quote on purpose) - I have to disagree. SQL Server performance is almost always I/O bound; the CPU spends enormous amounts of time waiting for the next data to be available. Since a computed column is not stored, it takes less disk space, reducing I/O. Here is a very unlogical and exaggerated example:
CREATE TABLE Demo
(KeyCol int NOT NULL PRIMARY KEY,
SmallString varchar(10) NOT NULL,
Repetitions smallint NOT NULL,
LongString AS REPLICATE(SmallString, Repetitions));


If the LongString column is computed and not persisted, rows take approximately 20-30 bytes (I don't have the exact numbers, as I'm on holiday); a single data page (8K) will store approximately 300 rows. The 100,000 rows you mention will be on less than 350 data pages.
However, if LongString is a persisted computed column or a regular column, then the amount of bytes per row depends on the length of SmallString and the value of Repetitions. If we assume an average length of 5 bytes for SmallString and an average value of 200 Repetitions, the average length of LongString will be 1,000 bytes; the average row length then is 1,020-1,030 bytes, so we can squeeze only 7 or 8 rows in a data page. The same 100,000 rows now take over 13,000 data pages! This will take much longer for SQL Server to read and process.


The last part of your post, the part I previously omitted from my quote, is "specially if the computed column comes from other tables via UDFs...". This is indeed true. SQL Server can't optimize this very well; it will execute the UDF 100,000 times, so if the UDF has to read from another table, that read will be repeated 100,000 times. Extremely bad for performance. This is but one of the many reasons why using a UDF in a computed column is not recommended.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search