What is faster? Creating calculated field within table or creating calculated fields within query?
-
Hi,
what is faster?
Best,
Jacek - jaryszek - Monday, March 12, 2018 5:44 AM
It takes longer and is more intrusive to create a calculated column in a table than it is to create the calculation in a query. Is that what you mean?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Yes,
exactly.
thank you!
Jacek
- jaryszek - Monday, March 12, 2018 5:44 AM
It depends on many factors, the data, type of calculation etc. What exactly are you trying to do?
😎 -
I am trying to do data standarization from Excel.
In Excel i have multiple columns where i am summing fields.
Best,
Jacek -
How often is the data modified (INSERT, UPDATE, DELETE) compared to how often is the data queried?
- jaryszek - Monday, March 12, 2018 6:57 AM
Can you elaborate a bit further please?
😎
You need to spell out the problem as you were explaining it to a child since we cannot see your data nor do we know the business requirements. - jaryszek - Monday, March 12, 2018 5:44 AM
It depends on the data and query cost. Normally if you alter a table for a calculative field and select the same field , It'll be somewhat faster. But If you create a CTE within your SQL query for a calculative field , then the optimizer need to calculate and spend some time there (within SQL query) and execute it.
You can take the call based on the above information. Hope it helps !
-
Hi Guys,
thank you for help and support!
Data is only needed to pull out to Excel as data source for calculations.There will be not a lot of query, more inserting and updating data.
Best,
Jacek -
Hi Guys,
my relationships look like below:
And now within ServerDetailedInfoTable i should have several calculations fields. Like changing bytes for GB,
if conditionals and so on.I am wondering the better way to do it is add calculated fields within view and view pull out to Excel or within this tables i should add calculated columns...
There will be inserting and updating mainly or deleting.Best,
Jacek - jaryszek - Tuesday, March 13, 2018 2:58 AM
Given that you expect more inserting and updating data than querying, it's probably better to do the calculations at query time, so in a query, view, or maybe even in the Excel spreadsheet if that is the primary consumer of this data. If the calculation is done within Excel to change bytes to GB for example, then it would be done on the user's computer instead of in the database server.
If this was a datamart, data warehouse, or some other reporting database then it would make more sense to do the calculation at the time of data insert or update.
-
One thing I would add ... It seems that for now the data is only being supplied to an Excel workbook , to which then calculations are done. In the future if other users or another department will need to do the same then better to centralise the calculations at the server so that two entities do not have different logic to try to arrive at the same number. And the information can be fed to other reporting tools as well. It also keeps the knowledge at the server on how to do the calculation rather than in one person's head / Excel sheet. Just a suggestion, just that if this is going to be a repeated process and numbers are critical then this may be the way to go.
----------------------------------------------------
-
Without doing a deep read on the other responses, if a calculation is deterministic, then adding a PERSISTED computed column on a table will cause the answer to be calculated during the insert and persisted through the life of the row, which means if the affecting values in the row don't change, the calculated column won't either. That can make things extremely fast if you're already reading from the table anyway. The column can also be indexed, which means it can also be a part of a "covering index", which can make things a whole lot faster on top of it all.
Think of it as a single "materialized column" much like you'd find in an "Indexed View" ("materialized view" for some).
There are caveats to just about everything but, done correctly, PERSISTED computed columns can be a big help to read performance.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Tuesday, March 13, 2018 12:35 PM
I also find they help a lot when the base table does not have a column in the right format , such as dates stored as strings.
----------------------------------------------------
- MMartin1 - Tuesday, March 13, 2018 8:43 PM
Agreed. They have tons of uses. Combining DATE and TIME columns. Returning a date with no time. Pre-Concatenated names. Normalized searchable/joinable addresses. Pre-lookups. Luhn 10 and other checksums. Tons of other uses.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply