問題描述

我想在存儲過程中動態填充日期時間列.下面是我目前使用的查詢，但會降低查詢性能.

I want to populate a datetime column on the fly within a stored procedure. below is the query that I currently have that does same but slows down query performance.

CREATE TABLE #TaxVal
(
    ID         INT
    , PaidDate DATETIME
    , CustID   INT
    , CompID   INT 
)

INSERT INTO #TaxVal(ID, PaidDate, CustID, CompID)
VALUES(01, '20150201',12, 100)
    , (03,'20150301', 18,101)
    , (10,'20150401',19,22)
    , (17,'20150401',02,11)
    , (11,'20150411',18,201)
    , (78,'20150421',18,299)
    , (133,'20150407',18,101)

--  SELECT * FROM #TaxVal

DECLARE @StartDate DATETIME = '20150101'
    , @EndDate     DATETIME = '20150501'

DECLARE @Tab TABLE 
(
    CompID    INT
    , DateField DATETIME
)

DECLARE @T INT
SET @T = 0
WHILE @EndDate >= @StartDate + @T 
BEGIN
    INSERT INTO @Tab 
    SELECT CompID
         , @StartDate + @T AS DateField
    FROM #TaxVal
    WHERE CustID = 18
        AND CompID = 101
    ORDER BY DateField DESC

    SET @T = @T + 1
END

SELECT DISTINCT * FROM @Tab 

DROP TABLE #TaxVal

編寫此查詢以獲得更好性能的最佳方法是什么?

Which is the best way to write this query for better performance?

推薦答案

改變這個:

DECLARE @T INT
SET @T = 0
WHILE @EndDate >= @StartDate + @T 
BEGIN
    INSERT INTO @Tab 
SELECT CompID
     , @StartDate + @T AS DateField
FROM #TaxVal
WHERE CustID = 18
    AND CompID = 101
ORDER BY DateField DESC

SET @T = @T + 1
END

為此:

;with cte as(
select cast('20150101' as date) as d
union all
select dateadd(dd, 1, d) as d from cte where d < '20150501'
)
INSERT INTO @Tab
SELECT CompID, d
FROM #TaxVal 
cross join cte
WHERE CustID = 18 AND CompID = 101
Option(maxrecursion 0)

這是獲取范圍內所有日期的遞歸公用表表達式.然后你做一個 cross join 并插入.請注意，插入時設置順序是沒有意義的.

Here is recursive common table expression to get all dates in range. Then you do a cross join and insert. Notice that there is no sense to order set while inserting.

這篇關于填充日期時間列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！