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

Question

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

存储的proc连接了大约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。因此，对于存储的proc中的每个用户，都会调用此函数。存储的proc如下所示：

    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在存储的proc中作为动态字符串执行。关于如何优化它来加快速度，有什么建议吗？

谢谢

编辑:我在这里使用了一些建议来解决这个问题。尽管我将多个select语句组合成两个语句，但我仍然保持函数的原样，然后将原始存储的proc改为select，并将select改为##temp。然后我在临时表上运行我的函数。执行时间下降到3-4秒。我想我必须对这个问题给予肯定，因为是他指出的独特之处让我走上了正确的道路。但还是要感谢大家。

Answer 1

DISTINCT绝对会对性能造成影响，就像聚合一样。你真的需要它吗？通常，当您看到DISTINCT时，它表明数据或结构问题正在被消除重复项的能力所掩盖，即结构应该自行消除。

在此之后，我将不再在选择列表中使用相关查询，而是将其作为连接进行移动。这并不一定会成功，但通常优化器更能将其融入到计划中。

基于你所展示的内容的复杂性，我也会看看执行计划。首先要检查的是，你是否做了全面的优化或者超时了。如果它超时了，那么您正在处理的是一个最佳猜测，而不是一个经过充分计算的“足够好”的计划。如果是这样的话，您需要考虑简化这个查询。如果你有一个足够好的计划，看看瓶颈在哪里。

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

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

• Scalar functions, inlining, and performance