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().

這篇關于在 SQL Server 中，while 循環需要很長時間的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！