问题描述
我想在存储过程中动态填充日期时间列.下面是我目前使用的查询,但会降低查询性能.
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.