July 21, 2010 at 12:04 pm
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.
July 21, 2010 at 12:12 pm
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?!?!
July 21, 2010 at 12:13 pm
oops... meant number of rows returned...
July 21, 2010 at 12:36 pm
Good one....got it wrong....I have to do some more reading.
July 21, 2010 at 3:30 pm
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
July 22, 2010 at 1:10 am
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.
July 22, 2010 at 8:37 am
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[/url]
Learn Extended Events
July 22, 2010 at 9:32 am
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
July 22, 2010 at 1:09 pm
How many check boxes are too many? :w00t:
"The engine tweaks to be performant."
I ticked that.
Paul
July 22, 2010 at 3:32 pm
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.
July 23, 2010 at 7:33 am
Very well said Hugo.
That's pretty much what I was trying to say although I wasn't thinking of Space (since today, disk space is pretty cheap)... your explanation is excellent.
Thank you for clarifying.
JGhali
July 23, 2010 at 11:40 am
I guess now would be a good time to point to a great post by PaulWhiteNz about UDFs and SCHEMABINDING which I can not find right now. My read performance is quite good while write sucks as I am on my iPhone;)
Paul could you quote yourself please?
Here is the link to a discussion on SSC: http://www.sqlservercentral.com/Forums/Topic737008-360-1.aspx#bm738974
Hrvoje Piasevoli
July 23, 2010 at 5:50 pm
hrvoje.piasevoli (7/23/2010)
I guess now would be a good time to point to a great post by PaulWhiteNz about UDFs and SCHEMABINDING which I can not find right now. My read performance is quite good while write sucks as I am on my iPhone;)Paul could you quote yourself please?
Will this do?
http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/596424.aspx
July 26, 2010 at 3:06 am
I hate "me too" replies, but I also failed on the "Will definitely use more resources" option, because it is just not true. Consider the alternative of a standard column populated by a trigger. A trigger will update the column on every insert and every relevant update; whereas the computed column will only be evaluated when it is selected. If you never include the column in a select statement then how can it use more resources? And yes I know that you wouldn't create a computed column that you don't intend to use, but a seldom-accessed computed column is still going to use fewer resources than a trigger.
I didn't choose "The engine tweaks to be performant." as I have no idea what it means!
Final whinge, it's a shame that these multiple-check-box question give the marks on an all-or-nothing basis; choosing 4 out of 5 correct options scores the same as choosing no correct options but every incorrect one 😀
August 16, 2010 at 6:12 am
Nice question, and as everyone else pointed out, the computed columns doesn't need to user more resources. Hugo gave an excellent example on how it could use less resources.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply