November 5, 2019 at 2:00 pm
I plan to do some testing on this (if I can find the time) but thought I would post the question to those who know better.
There is a view with 20 fields 17 of those are populated from Scalar-valued Functions, total record count about 800k, select all can take over 2 minutes.
Can / should this be replaced with a table that has 17 computed columns ?
November 5, 2019 at 2:19 pm
first of all, are these functions deterministic? how many times do you use the view ? do you reference any of these functions in the where clause?
personally i've used persisted computed columns for things like stock_value=stock_qty*price type scenarios and it works brilliantly
MVDBA
November 5, 2019 at 6:35 pm
Should this be replaced with a table that has 17 computed columns ?
Yes, if a computed column would work, then that is what you should use rather than a function.
Can this be replaced with a table that has 17 computed columns ?
Unsure. If the columns are computed using only columns from that same row, you should be able to use computed columns. A computed column cannot reference any data in a different row(s).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 5, 2019 at 9:58 pm
I expected it would be a simple answer (foolish, I know).
So here is an example of what I have to work with.
An example of a view where all the functions pull individual answers from an answers table based on parameters provided to the function. (I know...)
CREATE OR ALTER VIEW [dbo].[slv_CaseData]
AS
SELECT
cas.CasMachineID,
cas.CasID,
cas.SGrpMachineID,
cas.SGrpID,
cas.ClientMachineID,
cas.ClientID,
MLT =dbo.ccudf_AnswerTableValue(cas.CasMachineID, cas.CasID, '000-002', 'Code'),
DocType =dbo.ccudf_AnswerTableValue(cas.CasMachineID, cas.CasID, '000-004', 'Disc'),
LoanNbr =dbo.ccudf_AnswerAlpha(cas.CasMachineID, cas.CasID, 'LOAN#')
FROM Case cas
WHERE cas.StsRdy<>4
AND cas.sts NOT IN ('@','&')
GO
I had imagined that the computed columns could be constructed to use the same functions.
I've just completed some prelim. testing and this seems to work.
SELECT TOP 1000
cas.CasMachineID,
cas.CasID,
cas.SGrpMachineID,
cas.SGrpID,
cas.ClientMachineID,
cas.ClientID
INTO dbo.ctest
FROM Case cas
WHERE cas.StsRdy<>4 AND cas.sts NOT IN ('@','&')
GO
SELECT * FROM dbo.ctest
GO
--------------------------------------------------------------
ALTER TABLE dbo.ctest
ADD MLT AS dbo.sludf_AnswerTableValue(CasMachineID, CasID, '000-002', 'Code')
GO
ALTER TABLE dbo.ctest
ADD DocType AS dbo.sludf_AnswerTableValue(CasMachineID, CasID, '000-004', 'Code')
GO
ALTER TABLE dbo.ctest
ADD LoanNbr AS dbo.sludf_AnswerAlpha(CasMachineID, CasID, 'LOAN#')
GO
SELECT * FROM dbo.ctest AS c
GO
November 6, 2019 at 9:04 am
you're still executing the function once per row
you should persist the data
ALTER TABLE dbo.ctest
ADD MLT AS dbo.sludf_AnswerTableValue(CasMachineID, CasID, '000-002', 'Code') PERSISTED
this means it is stored and not recalculated on each read - it recalculates based on when the data is changed
MVDBA
November 6, 2019 at 1:15 pm
Thanks , that's great...
How does it know when data has changed? Does Change Tracking need to be enabled?
November 6, 2019 at 1:33 pm
Thanks , that's great...
How does it know when data has changed? Does Change Tracking need to be enabled?
no need to enable change tracking - think of it more like an invisible trigger that is under the hood. (that's not exactly correct) - you are limited to what you can use though, pretty much you can only use the table you are working on.. I've had occasions where I've had to strip out the barebones of a function and put it in the computed column
here is an example of where we had a function before and replaced it with persisted computed columns
alter table x add
[customer_code_int] AS (CASE WHEN NOT [customer_code] LIKE '%[A-Z]%' AND NOT [customer_code] LIKE '%-%' AND [customer_code]<>'' AND NOT [customer_code] LIKE '%?%' AND NOT ([customer_code]='"' OR [customer_code]='.') AND LEN([customer_code])<=(19) AND NOT [customer_code] LIKE '%/%' AND NOT [customer_code] LIKE '% %' AND [account_code]='mcdonalds' THEN CONVERT([BIGINT],LTRIM(RTRIM([customer_code]))) END) PERSISTED
alter table x add
[IsAddressClean] AS (CASE WHEN CHARINDEX(CHAR((13)),((((([organisation]+[address1])+[address2])+[address3])+[town])+[county])+[postcode])>(0) OR CHARINDEX(CHAR((10)),((((([organisation]+[address1])+[address2])+[address3])+[town])+[county])+[postcode])>(0) THEN (1) ELSE (0) END) PERSISTED NOT NULL
this is where it really works well
MVDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply