使用函数获取枚举状态时的SQL性能问题及优化咨询

你的问题核心是表值函数导致查询性能下降，主要原因是多语句表值函数无法被SQL Server优化器充分展开，导致执行计划低效。以下是几种更优的实现方式：

1. 改用内联表值函数 #

如果你的fn_GetOpenOrderState是多语句表值函数，改成内联版本——内联函数会被优化器当作视图一样展开，和主查询合并执行，性能接近直接写IN子句。

创建内联函数的代码：

CREATE FUNCTION [dbo].[fn_GetOpenOrderState]()
RETURNS TABLE
AS
RETURN (
    SELECT 'OnBoardModify' AS State
    UNION ALL
    SELECT 'registered'
    UNION ALL
    SELECT 'OnCanceling'
)

查询时改用JOIN（比IN更易被优化器处理）：

SELECT o.*
FROM dbo.Order_MOT o
JOIN [dbo].[fn_GetOpenOrderState]() s 
    ON o.OMSOrderState = s.State

2. 使用静态查找表（最推荐） #

创建专门的表存储进行中状态，既能保证性能，又能最方便地维护状态列表：

步骤1：创建查找表 #

CREATE TABLE dbo.OpenOrderStates (
    State VARCHAR(50) PRIMARY KEY -- 加主键保证唯一性，同时提升连接性能
)

步骤2：初始化数据 #

INSERT INTO dbo.OpenOrderStates 
VALUES ('OnBoardModify'), ('registered'), ('OnCanceling')

步骤3：查询时JOIN该表 #

SELECT o.*
FROM dbo.Order_MOT o
JOIN dbo.OpenOrderStates s 
    ON o.OMSOrderState = s.State

后续要修改进行中状态，只需更新这个表的数据，完全不用修改业务查询代码，兼顾性能和可维护性。

3. 用视图代替表值函数 #

如果只是需要返回固定的状态列表，视图也是一个选项，优化器同样能很好地处理它：

创建视图 #

CREATE VIEW dbo.vw_OpenOrderStates
AS
SELECT 'OnBoardModify' AS State
UNION ALL
SELECT 'registered'
UNION ALL
SELECT 'OnCanceling'

查询方式 #

-- 用IN子句
SELECT o.*
FROM dbo.Order_MOT o
WHERE o.OMSOrderState IN (SELECT State FROM dbo.vw_OpenOrderStates)

-- 或用JOIN（更优）
SELECT o.*
FROM dbo.Order_MOT o
JOIN dbo.vw_OpenOrderStates s 
    ON o.OMSOrderState = s.State

原问题的性能根源 #

从执行计划来看，你的原表值函数被优化器当作“黑盒”，无法准确估算返回的行数，导致选择了低效的执行策略（比如全表扫描或不合适的连接方式）。而直接写IN子句时，优化器明确知道要匹配的具体值，能更好地利用OMSOrderState上的索引。

内容的提问来源于stack exchange，提问作者javad rabbani