My function takes too long to run. I want to:
A. Find out exactly why it is taking so long
B. Optimize the function or execution plan.
The function: NOTE: Several separate functions are called in each 'THEN' clause.
ALTER FUNCTION [History].[getPrimaryID_Asset_Asset](@table varchar(32), @schema varchar(32), @column_name varchar(max), @column_value varchar(max))
RETURNS varchar(max)
AS
BEGIN
DECLARE @ID varchar(max) = @schema + '.' + @table + '.' + @column_name
IF SUBSTRING(@Schema, 1, 1) != '[' SET @Schema = QUOTENAME(@Schema)
IF SUBSTRING(@Schema, 1, 1) != '[' SET @Table = QUOTENAME(@Table)
RETURN
CASE
WHEN @column_value IS NULL THEN NULL
WHEN @ID = '[Asset].[Asset].AssetTypeAutoID' THEN [Enum].getAssetType(CONVERT(int, @column_value))
WHEN @ID = '[Asset].[Asset].AssetStatusAutoID' THEN [Enum].getAssetStatus(CONVERT(int, @column_value))
WHEN @ID = '[Asset].[Asset].ModelNumberAutoID' THEN [ModelNumber].getModelNumberID(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].ProjectAutoID' THEN [Project].getProjectName(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].CabinetAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].SystemAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].PowerSupplyAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].PlantModelAutoID' THEN [Location].getPlantModelID(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].ColumnAutoID' THEN [Location].getColumnID(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].DeviceTypeAutoID' THEN [Typical].getDeviceTypeID(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].RackAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].PLCAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].CalBoxAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].GasTypeAutoID' THEN [Enum].getGasType(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].RedundantAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].VertexAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].PRMAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].NACAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].ConduitAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].GasZoneTypeAutoID' THEN [Enum].getGasZoneType(Convert(int, @Column_Value))
WHEN @ID = '[Asset].[Asset].ProcurementPlantModelAutoID' THEN [Location].getPlantModelID(Convert(int, @Column_Value))
ELSE @column_value
END
END
I tried looking at the execution plan to see what steps were taking so long. Originally, I was running this function within an update trigger that was updating 600+ rows of a table called Asset.Asset. It was taking about 55 seconds to run. I pulled the execution plan for a simple use of my function from select History.getPrimaryID_Asset_Asset('Asset','Asset', 'ProcurementPlantModelAutoID', '1000')
to illustrate where my problem is.
When I look at the Execution Plan, I see A clustered index seek for every function that is in a 'THEN' statement. Even the ones that should never get used. I cannot seem to include the full execution plan details on here so I will show 2 screen shots illustrating what I mean. ExecutionPlanStart
I do not fully understand what I am looking at, but it appears that the trailing branches in the execution plan all are related to the functions used in THEN statement and each of them take time. Is there a way to optimize my function to take less time or avoid the execution plans evaluation of functions in 'THEN' statements that are not used?
1条答案
按热度按时间4nkexdtk1#
Use multiple IFstatements instead of single complex CASEexpression, to run exactly one simple query for each function invocation.