Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Computed Columns Expand / Collapse
Author
Message
Posted Wednesday, July 21, 2010 12:04 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 8:20 AM
Points: 1,490, Visits: 1,060
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.
Post #956610
Posted Wednesday, July 21, 2010 12:12 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:16 AM
Points: 434, Visits: 202
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?!?!

Post #956620
Posted Wednesday, July 21, 2010 12:13 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:16 AM
Points: 434, Visits: 202
oops... meant number of rows returned...
Post #956623
Posted Wednesday, July 21, 2010 12:36 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:24 AM
Points: 2,818, Visits: 2,567
Good one....got it wrong....I have to do some more reading.
Post #956656
Posted Wednesday, July 21, 2010 3:30 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:31 AM
Points: 3,352, Visits: 1,490
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
Post #956801
Posted Thursday, July 22, 2010 1:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 2, 2014 11:03 PM
Points: 2,132, Visits: 371
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.
Post #956962
Posted Thursday, July 22, 2010 8:37 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 18,064, Visits: 16,099
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
Post #957233
Posted Thursday, July 22, 2010 9:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 11:00 AM
Points: 1,142, Visits: 2,693
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
Post #957281
Posted Thursday, July 22, 2010 1:09 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:18 PM
Points: 9,928, Visits: 11,206
How many check boxes are too many?

"The engine tweaks to be performant."

I ticked that.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #957498
Posted Thursday, July 22, 2010 3:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:54 PM
Points: 6,130, Visits: 8,394
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
Post #957616
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse