在 SQL Server 中,while 循环需要很长时间
我有一个循环,它检查表 tmp13
中 descript
列的所有名称和日期,并将它们作为单独的行存储在其他表中 (tmp14代码>).问题是while循环执行了很长时间.我不知道如何让它运行得更快.
I have a loop that checks all the names and dates from descript
column from table tmp13
and stores them as individual rows in other table (tmp14
). The problem is that the while loop is executing for a long time. I don't know how to make it run faster.
这是我的代码,检查名称和日期的 descript
列.Descript
是一个文本列,可以有多个名称和日期.我想将这些名称和日期存储在单独的行中.
Here is my code checking the descript
columns for names and date. Descript
is a text column and can have multiple names and dates. I want to store those names and dates in separate rows.
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
SELCET @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 Table_Tasks(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');
OUTPUT
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
推荐答案
While 循环会很慢,因为当您使用过程逻辑在声明式编程语言中进行开发时会发生这种情况.为此,您可以使用 patternSplitCM.您的示例数据存在问题,但这应该可以满足您的需求.
While loops will be slow because that's what happens when you use procedural logic to develop in a declarative programming language. For this you can use patternSplitCM. There are issues with your sample data but this should get you what you need.
SELECT t.p_Num,
t.REQUESTED,
t.date1,
t.BY1,
parsedDate1 = MAX(CASE WHEN s.ItemNumber = 1 THEN s.Item END),
parsedDate2 = MAX(CASE WHEN s.ItemNumber > 1 THEN s.Item END)
FROM #Tmp13 AS t
CROSS
APPLY dbo.patternSplitCM(t.DESCRIPT, '[0-9/]') AS s
WHERE s.[Matched] = 1 AND TRY_CAST(s.item AS DATE) IS NOT NULL
GROUP BY t.p_Num,t.REQUESTED, t.date1,t.BY1
结果:
p_Num REQUESTED date1 BY1 parsedDate1 parsedDate2
----------- ------------ ------------ --------- ------------ ------------
100 John 5/30/2017 James 05/30/2017 05/30/2017
200 John 6/1/2017 Rachael 06/1/2017 06/1/2017
最后,为了获得最佳性能,您希望 p_Num、REQUESTED、DATE1、BY1 列上的索引支持 GROUP BY 子句.索引看起来像:
Lastly, for optimal performance you want an index on the columns p_Num,REQUESTED,DATE1,BY1 to support the GROUP BY clause. The index would look something like:
CREATE UNIQUE NONCLUSTERED INDEX uq_xxx ON #Tmp13(p_Num,REQUESTED,DATE1,BY1)
--INCLUDE (DESCRIPT); -- if you can change this to VARCHAR(8000) or VARCHAR(max)
另请注意,使用并行执行计划可能会运行得更快.为此,您可以使用 TRACEFLAG 8649 或 Adam Machanic 的 make_parallel().
Also note that this will likely run faster with a parallel execution plan. For that you can use TRACEFLAG 8649 or Adam Machanic's make_parallel().
相关文章