Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pithhelmet
Posting Yak Master
183 Posts |
 Posted - 2011-10-07 : 17:13:05
|
| Hi Everyone,We are having a discussion on calculated fields, and one side of the group says they slow down the queries, the other says the fields are calculated once, then the data is populated in that table and it is done.Here is one of the calculated fields...(((isnull([LastName]+', ','')+isnull([Suffix]+', ',''))+isnull([FirstName],''))+isnull(' '+[MiddleName],''))and another one...(CONVERT([int],case when dateadd(year,datediff(year,[dateofbirth],getdate()),[dateofbirth])>getdate() then datediff(year,[dateofbirth],getdate())-(1) else datediff(year,[dateofbirth],getdate()) end,(0)))So the question to the masses is....does a calculated field impose a performance hit when a query on the table includes one or more of these calculated fields?thank youtake caretony |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-10-07 : 17:23:29
|
| You have an option to "persist" the value in the table or not. So it comes down to speed vs. storage space.EDIT:If it is persisted then it is also eligible for indexing and/or partitioning.Be One with the OptimizerTG |
 |
|
|
jassi.singh
Posting Yak Master
122 Posts |
Posted - 2011-10-08 : 09:40:52
|
| Hi,persisted-computed-columns Boost performance. It is one of the Top 10 Hidden Gems in SQL Server 2005. Following link explain in more great detailPlease refer: http://technet.microsoft.com/en-us/library/cc917696.aspxPlease mark answer as accepted if it helped you.Thanks,Jassi Singh |
|
|
|
pithhelmet
Posting Yak Master
183 Posts |
Posted - 2011-10-10 : 12:09:52
|
| Update - after reading the comments above, i did a little more research on the table structure to see if maybe i missed something.... but i did not, the calculated columns are not persistant. No, these columns are not PCC.so, the question remains, do non-PCC computed columns impose a performance hit on the database?my reaction after researching on this is they do impose a slight performance hit on the engine.A followup question is this....Would a simple trigger that updates the formally, computed column, values be a better choice??thanks |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-10-10 : 15:28:15
|
| >> do non-PCC computed columns impose a performance hit on the database?Of course yes - as I said before "speed vs. storage space". However, if you are just returning a page of data at a time then you probably won't notice it. if you're returning millions of rows then it could be significant. Best thing for you is to test the difference yourself from a query window. You can use "set statistics time" to compare a select which includes your column vs. one that does not.>>Would a simple trigger that updates the formally, computed column, values be a better choice??IMO no. That would home-grown equivalent to altering the column to PERSISTED.Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Calculated field performance hit