SQL Server 中嵌套循环的更好方法

Question

>我有一个嵌套循环，它检查表tmp13中descript列中的所有名称和日期，并将它们作为单独的行存储在其他表 (tmp14中(。问题是 while 循环执行了很长时间。我不知道如何让它运行得更快。我已经尝试了上一篇文章中的一些建议，但我不是很成功。谁能给出一些建议来解决这个可怕的问题。

这是我检查descript列的名称和日期的代码。Descript是一个文本列，可以有多个名称和日期。我想将这些名称和日期存储在单独的行中。

DECLARE @Id INT
DECLARE @count INT
DECLARE @product_num INT
DECLARE @REQUESTED VARCHAR(50)
DECLARE @FirstDate VARCHAR(255)
DECLARE @RequestedBy VARCHAR(255)
DECLARE @name NVARCHAR(256)
DECLARE @date NVARCHAR(256)
DECLARE @desc NVARCHAR(256)
DECLARE @dateposition INT
DECLARE @nameposition INT
DECLARE @nameend INT
SELECT @count = MAX(id) 
FROM #TMP13
SET @id = 1;
WHILE (@id <= @count)
BEGIN
SELECT @desc = descript FROM #TMP13 WHERE Id = @Id
SELECT @product_num = p_Num FROM #TMP13 WHERE Id = @Id
SELECT @REQUESTED = REQUESTED FROM #TMP13 WHERE Id = @Id
SELECT @FirstDate = DATE1 FROM #TMP13 WHERE Id = @Id
SELECT @RequestedBy = BY1 FROM #TMP13 WHERE Id = @Id

while (patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',@desc) > 0)
begin
set @dateposition = patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9]%',@desc)
set @date = SUBSTRING(@desc,@dateposition,10)
set @nameposition = CHARINDEX('-', @desc)+2
set @nameend = CHARINDEX(' ', @desc, @nameposition)+1
set @name = SUBSTRING(@desc,@nameposition,@nameend-@nameposition)
insert into #TMP14 
values (@Id,@product_num,@REQUESTED, @FirstDate ,@RequestedBY, @date, @name)
set @desc = SUBSTRING(@desc,@nameend,1024)  
end
set @id = @id + 1;
end
select * from #tmp14;

---示例表

CREATE TABLE #Tmp13(
p_Num             INTEGER  NOT NULL PRIMARY KEY 
REQUESTED          varchar(50),
DATE1            VARCHAR(50),   
BY1              VARCHAR(50),
DESCRIPT         TEXT

(;

INSERT INTO #tmp13 (p_Num, REQUESTED, DATE1, BY1, DESCRIPT) 
VALUES (100, 'John', '5/30/2017', 'James', '05/30/2017 12:25am Eastern Standard Time - Mjames reported changes in the pages 05/30/2017 10:35AM JRachael agreed to work on the report and report to James 05/30/2017 10:00 AM James reports errors in page.',NULL);
INSERT INTO #tmp13(WO_NUM, Opendate, ClosedDate, Note) 
VALUES (200, 'John', '6/1/2017', 'Rachael', '06/1/2017 3:20PM Eastern Standard Time - Rsubramaniam reported phone is not functional 06/1/2017 4:00PM Service took the phone and replaced it with new one');

输出：

Id  product_num REQUESTED FirstDate  RequestedBY date name date Name                                                               
1   100          John      5/30/2017  james      5/30/2017 mjames  5/30/2017 jRachael

Answer 1

这是一个选项。 这只是在遵循一致格式的情况下分析列中的多个日期和名称。 您需要进行调整以适应您的解决方案。和。。。

这仅适用于以下内容：

• 日期和名称以一致且可重复的格式存储在字段中："日/月/年时区 - 名称文本日/月/年 时区 - 名称文本 日/月/年 时区 - 名称文本">

我不确定这会给您带来什么样的性能，如果该字段中存储日期和名称的格式发生变化，它将不起作用。 这就是了解格式是否一致和可重复的重要性。

在示例中，我们基本上将阶段拆分为单个单词，然后进行过滤以获得您想要的内容。 根据SQL服务器的版本，我提供了2个不同的选项，用于如何进行拆分。

• SQL Server 版本 2016+，因为它将使用SPLIT_STRING
• 另一个应该回到2012年。 它使用 XML

没有提供太多样本数据，但根据您的评论和回复，我做了一些假设，您可能需要根据您的特定需求进行调整。

1. 我们毕竟是现场日期的出现
2. 以及与日期关联的名称，它紧跟在所述日期之后的"-"之后。

下面是一个示例：

DECLARE @tmp13 TABLE
(
[p_Num] INTEGER NOT NULL
, [DESCRIPT] NVARCHAR(MAX)
PRIMARY KEY([p_Num])
);
DECLARE @tmp13Parse TABLE
(
[Id] INT
, [Position] BIGINT
, [Value] NVARCHAR(500)
unique clustered ([Id], [Position])
);

--insert test data
INSERT INTO @tmp13 (
[p_Num]
, [DESCRIPT]
)
VALUES ( 100
, '05/30/2017 12:25am Eastern Standard Time - Mjames reported changes in the pages 05/30/2017 10:35AM Eastern Standard Time - JRachael agreed to work on the report and report to James 05/30/2017 10:00AM Eastern Standard Time - James reports errors in page.' )
, ( 200
, '05/29/2017 12:25am Central Stanard Time - TSmith reported changes in the pages 05/29/2017 10:35AM Central Stanard Time - JRachael agreed to work on the report and report to James 05/29/2017 10:00AM Central Stanard Time - GregNoName reports errors in page.' )
, ( 300
, '05/28/2017 12:25am Eastern Standard Time - Mjames reported changes in the pages 05/28/2017 10:35AM Eastern Standard Time - JName agreed to work on the report and report to James 05/28/2017 10:00AM Eastern Standard Time - James reports errors in page.' )
, ( 400
, '05/27/2017 12:25am Central Stanard Time - Mjames reported changes in the pages 05/27/2017 10:35AM Central Stanard Time - JRachael agreed to work on the report and report to James 05/27/2017 10:00AM Eastern Standard Time - AnotherName reports errors in page.' )
, ( 500
, '05/26/2017 12:25am Eastern Standard Time - MJohnson reported changes in the pages 05/26/2017 10:35AM Eastern Standard Time - FTestname agreed to work on the report and report to James 05/26/2017 10:00AM Eastern Standard Time - James reports errors in page.' )
, ( 600
, '05/25/2017 12:25am Eastern Standard Time - Mjames reported changes in the pages 05/25/2017 10:35AM Eastern Standard Time - JRachael agreed to work on the report and report to James 05/25/2017 10:00AM Eastern Standard Time - James reports errors in page.' )
, ( 700
, '05/24/2017 12:25am Eastern Standard Time - TTaylor reported changes in the pages 05/24/2017 10:35AM Eastern Standard Time - JRachael agreed to work on the report and report to James 05/24/2017 10:00AM Eastern Standard Time - TMoreTestNames reports errors in page.' );

--Basically what we are doing is loading a table with each individual word making sure we keep which Id it was associated with. 
--along with the Position of where it was in the phrase.
--Two options below depending on SQL Version.
--SQL Version 2016+, we'll use SPLIT_STRING, code is a little more easier
INSERT INTO @tmp13Parse (
[Id]
, [Position]
, [Value]
)
SELECT      [a].[p_Num]
, [b].[Position]
, [b].[Value]
FROM        @tmp13 [a]
CROSS APPLY (
SELECT [Value]
, ROW_NUMBER() OVER ( ORDER BY (
SELECT 1
)
) AS [Position]
FROM   STRING_SPLIT([a].[DESCRIPT], ' ') --this will handle returning a table based on how you split it, in this case a space.
) AS [b];
--Prior to SQL Version 2016 back to 2012, use this option which is using a XML to split the data.
INSERT INTO @tmp13Parse (
[Id]
, [Position]
, [Value]
)
SELECT      [a].[p_Num]
, [ss].[Position]
, [ss].[Value]
FROM        @tmp13 [a]
CROSS APPLY (
SELECT      ROW_NUMBER() OVER ( ORDER BY (SELECT 1)) AS [Position]
, [y].[i].[value]('(./text())[1]', 'nvarchar(max)') AS [Value]
FROM        (
SELECT [x] = CONVERT(XML, '<i>'+ REPLACE([a].[DESCRIPT], ' ', '</i><i>')+ '</i>').[query]('.')
) AS [a]
CROSS APPLY [x].[nodes]('i') AS [y]([i])
) AS [ss];

--After we have split the data we'll now go after the specific values
SELECT      [a].[Id]
, [a].[Value] AS [Date]
, [ccc].[Value] AS [Name]
FROM        @tmp13Parse [a]
--First cross apply - what is the position of '-' after my date field. add 1 since the next value should be the name I'm after.
CROSS APPLY (
SELECT MIN([aa].[Position]) + 1 AS [nameAnchorPosition]
FROM   @tmp13Parse [aa]
WHERE  [aa].[Id] = [a].[Id]
AND [aa].[Value] = '-'
AND [aa].[Position] > [a].[Position]
) AS [bb]
--Second cross apply - Now, based on where I identified '-' to be, plus 1, give me that value.
CROSS APPLY (
SELECT [cc].[Value]
FROM   @tmp13Parse [cc]
WHERE  [cc].[Id] = [a].[Id]
AND [cc].[Position] = [bb].[nameAnchorPosition]
) AS [ccc]
WHERE       TRY_CONVERT(DATE, [a].[Value]) > '1900-01-01';  --will return all those values that are a date as starting point, long with their position.

我在记录集为 54000 的一台服务器上进行了快速测试，使用拆分和解析这两个选项，两者都在 4-10 秒内给了我结果。 您的里程可能会有所不同。