需要一些帮助来优化存储过程

Question

我有一个存储过程，它正在构建动态 sql 查询，然后通过 exec(@sql) 运行它。

存储过程正在连接大约 12 个表。事实上，它的运行速度相对较快。但后来我需要添加一个额外的字段。为此，我创建了一个标量函数，如下所示：

SELECT @weight = @weight +COUNT(*) FROM dbo.UserPDMedication WHERE UserID = @userid
SELECT @weight = @weight +COUNT(*) FROM dbo.[User] WHERE UserID = @userid AND HoehnYarhID IS NOT null
SELECT @weight = @weight +COUNT(*) FROM dbo.[User] WHERE UserID = @userid AND DateOfBirth IS NOT NULL
SELECT @weight = @weight +COUNT(*) FROM dbo.[User] WHERE UserID = @userid AND GenderID IS NOT NULL
SELECT @weight = @weight +COUNT(*) FROM dbo.[User] WHERE UserID = @userid AND DateDiagnosed IS NOT null

它基本上只是一个根据用户填写的问题数量返回 int 的函数。因此，对于存储过程中的每个用户，都会调用此函数。存储过程如下所示：（

    SELECT DISTINCT u.UserID, u.Healthy, u.DateOfBirth, u.City, st.StateCode AS State, u.GenderID, g.Gender, u.Latitude, u.Longitude, u.PDConditionID, u.Zip, u.Distance,
    (SELECT TOP 1 EmailID FROM Messages m WHERE TrialID = ' + @trialID + ' AND ToUserID = u.userid AND LocationID = ' + @locationID + ') AS MessageID, dbo.UserWeightedValue(u.UserID) as wt
FROM [User] u
    INNER JOIN aspnet_UsersInRoles uir ON u.AspnetUserID = uir.UserId
    INNER JOIN aspnet_Roles r ON uir.RoleId = r.RoleId
    FULL JOIN UserHealthCondition uhc ON u.UserID = uhc.UserID
    FULL JOIN UserMotorSymptom ums ON u.UserID = ums.UserID
    FULL JOIN UserNonMotorSymptom unms ON u.UserID = unms.UserID
    FULL JOIN UserPDMedication updm ON u.UserID = updm.UserID
    FULL JOIN UserPDTreatment updt ON u.UserID = updt.UserID
    FULL JOIN UserSupplement us ON u.UserID = us.UserID
    FULL JOIN UserPDGeneticMarker updgm ON u.UserID = updgm.UserID
    FULL JOIN UserFamilyMember ufm ON u.UserID = ufm.UserID
    FULL JOIN State st ON u.StateID = st.ID
    FULL JOIN Gender g ON u.GenderID = g.ID
WHERE u.UserID IS NOT NULL

我删除了一些块以尝试保持简短）。该 get 在存储过程中作为动态字符串执行。关于如何优化它以加快速度有什么建议吗？

谢谢

编辑：我使用这里的建议组合来完成这项工作。尽管我将多个 select 语句合并为 2 个语句，但我保持了函数原样。然后，我采用原始存储过程并将 select 更改为 select into ##temp。然后我针对该临时表运行我的函数。执行时间下降至 3-4 秒。我想我必须承认这个问题，因为正是他的明确指出让我走上了正确的道路。但谢谢大家。

Answer 1

DISTINCT 绝对会像聚合一样导致性能下降。你真的需要它吗？通常，当您看到 DISTINCT 时，这表明数据或结构问题被消除重复项的能力所掩盖，而结构本应自行消除重复项。

之后，我希望将其移动为 JOIN，而不是 SELECT 列表中的相关查询。这并不是一定会成功，但优化器通常能够更好地将其纳入计划中。

根据您所呈现内容的复杂性，我还会查看执行计划。首先要检查，您是否进行了全面优化或是否超时。如果超时，那么您正在处理的是最佳猜测，而不是经过完全计算的“足够好”计划。如果是这样，您需要考虑简化此查询。如果你有一个足够好的计划，看看其中的瓶颈在哪里。

Answer 2

如果UserID是User表的主键，那么就不需要对用户填写的问题进行一次SELECT，您可以将其包装在一个 SELECT 中：

SELECT @weight = @weight + COUNT(HoehnYarhID) + COUNT(DateOfBirth) + COUNT(GenderID) + COUNT(DateDiagnosed)
FROM dbo.[User] 
WHERE UserID = @userid 

Answer 3

将标量值函数转换为内联表值函数。

• 标量函数、内联、和性能