我有一个SQL Server TSQL问题。我的存储过程正在运行非常缓慢。关于使用集合操作改进这些查询的任何建议将不胜感激。
请记住,我无法控制数据的存储方式。
我有两张表格,格式如下:
-
人员ID,姓名,DeletedFlag,ScheduleTotal,WorkedTotal,DeptNum,DeptName,这是一个临时表其中预先填充了用户选择的人员以及用户可以访问.
-
个人ID,记录类型,年份,HexMap
对于每个人,将有两个或零个特定的记录年一个记录日程时间,一个记录工作时间。六角图是一个字符串(每两个字符表示计划或工作的总数时间)。我可以长达732个字符(每天一对),总是从1月1日开始。我写了一个函数来分割字符串,所以我没有对此感到担忧。
想要的结果
使用合计更新的第一个表ScheduleTotal和WorkedTotal字段基于用户选择的日期从第二表中选择。日期范围可以在一个日历年内或跨多个日历年。
我能做到,但我觉得我走的太多了。以下是我目前正在做。建议赞赏:
A。TEMP结果表1:对于用户的选定的日期范围(While循环):在临时表中插入上面表1中列出的人员的personid、记录类型和hexmap。
WHILE (@YearCount <= @YearEnd)
....
INSERT INTO #Tmp_HexMap (personid, hexMap, recordtype)
SELECT T1.personid, H.HexMap, H.recordtype
FROM [MyDatabase_' + CONVERT(Varchar(5),@DatabaseNum) + '].[dbo].[hextable] H
INNER JOIN #Tmp_Persons T1 ON T1.Personid = H.PersonID
WHERE Year = ' + CONVERT(Varchar(5),@YearCount)
B。温度结果表2:每年仍处于While循环中。我为每个人逐一计数(内部While循环)并将我的十进制结果插入另一个临时表十六进制拆分器函数--它返回一个带有小数的表变量后果我添加人员ID、计划或工作日期和计划以及工作时间。
WHILE (@PersonID IS NOT NULL)
....
INSERT INTO #Tmp_scheduledtime (personid, scheddate, scheduledworkedtime)
SELECT @personid as personid,
DATEADD(dd,(Row_Number() OVER(Order by (Select NULL))+(@CurrentStartInt-1)-1),@YearStartDate) AS SchedDate,
workedtime FROM Report_Maint.dbo.workedtimesplit(@HexMap,@CurrentStartInt,@WorkedTimeDateINT)
C。温度结果表3&4:在while循环之外,我将总数插入临时表:
INSERT INTO #Tmp_Grouped1 (personid, ScheduledTotal, WorkedTotal)
SELECT personid, ISNULL(SUM(scheduledworkedtime),-1.00) From #Tmp_scheduledtime
GROUP BY personid
D。最后,我更新了原始表格:
'UPDATE #Tmp_Persons ' +
'SET HoursSched = ScheduledWorkedTotal ' +
'From #Tmp_Grouped1 TG ' +
'WHERE TG.Personid = #Tmp_Persons.Personid ' +
' AND #Tmp_persons.PeriodBegin >= ' + '''' + CONVERT(nvarchar(24),@DateBegin,121) + '''' + ' ' +
'AND #Tmp_persons.PeriodEnd <= ' + '''' + CONVERT(nvarchar(24),@DateEnd,121) + ''''
你有没有想过把这些变成一个或两个查询,而不是通过while循环?感谢您的帮助!
标记
我没有从您的示例中获得足够具体的数据,但通常情况下,您可以使用JOIN执行基于集合的UPDATE来完成这种行为,而不需要循环和快速性能。让我举一个简单的例子来演示。
CREATE TABLE Products
(ProductID int
,Name varchar(30)
,Price money)
CREATE TABLE NewPrices
(ProductID int
,Price money)
为了帮助可视化,请先在它们之间写一个连接,并排查看新旧价格:
SELECT p.ProductID, p.Name, p.Price as OriginalPrice, n.Price as NewPrice
FROM Products as p
JOIN NewPrices as n on p.ProductID = n.ProductID
好的,足够简单的JOIN,你可以看到并排的价格。现在只需将SELECT更改为UPDATE,但保留FROM和JOIN,如下所示:
UPDATE p
SET Price = n.Price
FROM Products as p
JOIN NewPrices as n on p.ProductID = n.ProductID
这将匹配ProductID上表中的每个记录,并用n表中匹配行(即NewPrices)的值更新p表(即Products)中的值。
基本上,在类C语言中,FROM语句与FOR语句相同,SELECT语句与PRINT语句类似。因此FROM&JOIN执行WHILE语句的循环,而不是执行SELECT,而是更新两个表的JOIN的每一行。
一旦掌握了这一点,就可以看看SQL Server中的MERGE语句,并探索它的魔力