在 Microsoft SQL Server 2019 如果有太多的 if-else 在 Scalar Function 中，執行該 Function 會跑很久?

前言

最近同事詢問一個很奇怪的問題，他們有一個 Scalar Function 在 MSSQL 2019 (15.0.2000.5)上面執行，
透過 SSMS 直接執行該 Function ，結果一直跑不出來。
直接跑 Function 裡面的 Script，卻一下子就跑出來。
一樣的程式，在另一台 Server (15.0.4153.1)上也一下子就跑出來了。
到底是怎麼一回事呢?

研究

SQL 的 Scalar Function 大約長的如下的程式碼(if else if ….大約 19 個)，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
CREATE FUNCTION [dbo].[ufn_get_user_info3](
 @strUserName NVARCHAR(256),
 @strKIND NVARCHAR(50) = 'USER_NAME'
)
RETURNS NVARCHAR(4000)
AS
Begin
	 DECLARE @strRTN_VAL NVARCHAR(4000) = '';    --回傳值
	 if @strKIND = 'UserName'
	 begin
		SELECT @strRTN_VAL = UserName
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'NormalizedUserName'
	 begin
		SELECT @strRTN_VAL = NormalizedUserName
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'Name'
	 begin
		SELECT @strRTN_VAL = [Name]
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'Surname'
	 begin
		SELECT @strRTN_VAL = [Surname]
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'Email'
	 begin
		SELECT @strRTN_VAL = [Email]
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'NormalizedEmail'
	 begin
		SELECT @strRTN_VAL = [NormalizedEmail]
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'PasswordHash'
	 begin
		SELECT @strRTN_VAL = [PasswordHash]
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'PhoneNumber'
	 begin
		SELECT @strRTN_VAL = [PhoneNumber]
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'SecurityStamp'
	 begin
		SELECT @strRTN_VAL = [SecurityStamp]
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'ExtraProperties'
	 begin
		SELECT @strRTN_VAL =[ExtraProperties]
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'ConcurrencyStamp'
	 begin
		SELECT @strRTN_VAL =[ConcurrencyStamp]
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'CreationTime'
	 begin
		SELECT @strRTN_VAL = convert(nvarchar(4000),[CreationTime], 120)
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'LastModificationTime'
	 begin
		SELECT @strRTN_VAL = convert(nvarchar(4000),[LastModificationTime] , 120)
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'DeletionTime'
	 begin
		SELECT @strRTN_VAL = convert(nvarchar(4000),[DeletionTime] , 120)
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'Id'
	 begin
		SELECT @strRTN_VAL = cast(Id as varchar(200))
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'TenantId'
	 begin
		SELECT @strRTN_VAL = cast([TenantId] as varchar(200))
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'CreatorId'
	 begin
		SELECT @strRTN_VAL = cast([CreatorId] as varchar(200))
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'LastModifierId'
	 begin
		SELECT @strRTN_VAL = cast([LastModifierId] as varchar(200))
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 else if @strKIND = 'DeleterId'
	 begin
		SELECT @strRTN_VAL = cast([DeleterId] as varchar(200))
          FROM [AbpUsers] WITH(NOLOCK)
		  WHERE UserName = @strUserName;
	 end;
	 return @strRTN_VAL;
END

直接在 SSMS 下 Select [dbo].[ufn_get_user_info3]('admin', 'NormalizedEmail') 就沒有回應了。

原本想說會不會是使用了太多 CPU 的原因，
所以在原本的 Script 後加上 OPTION (MAXDOP 1) ，
還是一樣的狀況。在測試環境上，約跑了 1 分 24 秒 資料才出來。

預估的執行計劃如下，

但刪掉一些 if-else 資料就可以很順利的跑出來。
完全沒想法，感覺 SQL 都到了 2019 應該不會有這種 問題呀~~~
…
…
…
結果後來同事將 MSSQL 2019 更新到新的版本，執行就沒問題了。
目前測試在 SQL Server 2019 版本 15.0.4153.1 上跑是沒問題的。

下次要懷疑自已前，先懷疑軟體是否有更新到新的版本。

測試的資料如下，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
CREATE TABLE [dbo].[AbpUsers](
	[Id] [uniqueidentifier] NOT NULL,
	[TenantId] [uniqueidentifier] NULL,
	[UserName] [nvarchar](256) NOT NULL,
	[NormalizedUserName] [nvarchar](256) NOT NULL,
	[Name] [nvarchar](64) NULL,
	[Surname] [nvarchar](64) NULL,
	[Email] [nvarchar](256) NOT NULL,
	[NormalizedEmail] [nvarchar](256) NOT NULL,
	[EmailConfirmed] [bit] NOT NULL,
	[PasswordHash] [nvarchar](256) NULL,
	[SecurityStamp] [nvarchar](256) NOT NULL,
	[IsExternal] [bit] NOT NULL,
	[PhoneNumber] [nvarchar](16) NULL,
	[PhoneNumberConfirmed] [bit] NOT NULL,
	[IsActive] [bit] NOT NULL,
	[TwoFactorEnabled] [bit] NOT NULL,
	[LockoutEnd] [datetimeoffset](7) NULL,
	[LockoutEnabled] [bit] NOT NULL,
	[AccessFailedCount] [int] NOT NULL,
	[ExtraProperties] [nvarchar](max) NULL,
	[ConcurrencyStamp] [nvarchar](40) NULL,
	[CreationTime] [datetime2](7) NOT NULL,
	[CreatorId] [uniqueidentifier] NULL,
	[LastModificationTime] [datetime2](7) NULL,
	[LastModifierId] [uniqueidentifier] NULL,
	[IsDeleted] [bit] NOT NULL,
	[DeleterId] [uniqueidentifier] NULL,
	[DeletionTime] [datetime2](7) NULL,
 CONSTRAINT [PK_AbpUsers] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[AbpUsers] ([Id], [TenantId], [UserName], [NormalizedUserName], [Name], [Surname], [Email], [NormalizedEmail], [EmailConfirmed], [PasswordHash], [SecurityStamp], [IsExternal], [PhoneNumber], [PhoneNumberConfirmed], [IsActive], [TwoFactorEnabled], [LockoutEnd], [LockoutEnabled], [AccessFailedCount], [ExtraProperties], [ConcurrencyStamp], [CreationTime], [CreatorId], [LastModificationTime], [LastModifierId], [IsDeleted], [DeleterId], [DeletionTime]) VALUES (N'eaeeb5db-5209-0e71-b58c-3a0380591d83', NULL, N'admin', N'ADMIN', N'admin', N'', N'rainmaker_ho@gss.com.tw', N'RAINMAKER_HO@GSS.COM.TW', 0, N'AQAAAAEAACcQAAAAEBtitT9fC5a4FAueYPe3RUKq9/lq9mLTQuax0+7vgydsV05Xtf4lzjq+4C9LAfChPA==', N'GXPCPZASRPVU7LUMWXR4OESDEFZDUCS4', 0, N'', 0, 1, 0, NULL, 1, 0, N'{}', N'c353047ea876484bba0901b79ccf4533', CAST(N'2022-04-28T11:17:16.9722713' AS DateTime2), NULL, CAST(N'2022-04-28T16:29:42.7502832' AS DateTime2), N'eaeeb5db-5209-0e71-b58c-3a0380591d83', 0, NULL, NULL)
GO
ALTER TABLE [dbo].[AbpUsers] ADD  DEFAULT (CONVERT([bit],(0))) FOR [EmailConfirmed]
GO
ALTER TABLE [dbo].[AbpUsers] ADD  DEFAULT (CONVERT([bit],(0))) FOR [IsExternal]
GO
ALTER TABLE [dbo].[AbpUsers] ADD  DEFAULT (CONVERT([bit],(0))) FOR [PhoneNumberConfirmed]
GO
ALTER TABLE [dbo].[AbpUsers] ADD  DEFAULT (CONVERT([bit],(0))) FOR [TwoFactorEnabled]
GO
ALTER TABLE [dbo].[AbpUsers] ADD  DEFAULT (CONVERT([bit],(0))) FOR [LockoutEnabled]
GO
ALTER TABLE [dbo].[AbpUsers] ADD  DEFAULT ((0)) FOR [AccessFailedCount]
GO
ALTER TABLE [dbo].[AbpUsers] ADD  DEFAULT (CONVERT([bit],(0))) FOR [IsDeleted]
GO

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

大家好，我是亂馬客 Rainmaker，目前任職於叡揚資訊，大家也都叫我 RM 。 平常負責開發公司各產品線的加值、共用的軟體，例如 Azure, OpenAI, Chatbot, Database ... 同時 Support 公司各種技術及資安修改問題。 Blog中的內容也是個人的看法， 如果有需要討論的地方， 可以留言，或是 mail 給我 :) rainmaker_ho@gss.com.tw