提高 CASE 選擇性能

我有以下查詢，在少於 100K 行的數據集上執行最多需要 2 秒。

我的 SQL 非常生鏽，但這看起來比它應該做的工作要多得多。任何人都可以提供一些關於在哪裡尋找加快速度的指示嗎？

SELECT
   a.AddressID,
   IsPrincipal = CASE
   WHEN EXISTS(SELECT TOP 1 1 FROM dbo.Setting s WHERE s.SettingValue = a.AddressID AND s.SettingDefinitionID = 3 AND s.ProfileID = 1)
               THEN 1
               ELSE 0
           END,
   IsPickUp = CASE
               WHEN EXISTS(SELECT TOP 1 1 FROM dbo.AddressRole ar WHERE ar.AddressID = a.AddressID AND ar.[AddressRoleTypeID] = 2)
               THEN 1
               ELSE 0
           END,
   IsSender = CASE
               WHEN EXISTS(SELECT TOP 1 1 FROM dbo.AddressRole ar WHERE ar.AddressID = a.AddressID AND ar.[AddressRoleTypeID] = 3)
               THEN 1
               ELSE 0
           END,
           IsDelivery = CASE
               WHEN EXISTS(SELECT TOP 1 1 FROM dbo.AddressRole ar WHERE ar.AddressID = a.AddressID AND ar.[AddressRoleTypeID] = 4)
               THEN 1
               ELSE 0
           END,
   IsReceiver = CASE
               WHEN EXISTS(SELECT TOP 1 1 FROM dbo.AddressRole ar WHERE ar.AddressID = a.AddressID AND ar.[AddressRoleTypeID] = 5)
               THEN 1
               ELSE 0
           END
       FROM dbo.[Address] AS a
       WHERE a.MFTID = '12345'

AddressRole您可以像這樣加入和Setting表，而不是對每一行進行這樣的查找：

SELECT
   a.AddressID,
   IsPrincipal = Max(iif(s.SettingValue Is Not Null, 1, 0)),
   IsPickUp = Max(iif(ar.AddressRoleTypeID = 2, 1, 0)),
   IsSender = Max(iif(ar.AddressRoleTypeID = 3, 1, 0)),
   IsDelivery = Max(iif(ar.AddressRoleTypeID = 4, 1, 0)),
   IsReceiver = Max(iif(ar.AddressRoleTypeID = 5, 1, 0))
 FROM dbo.[Address] AS a
 Left Join dbo.Setting As s
   On a.AddressID = s.SettingValue
   And s.SettingDefinitionID = 3
   And s.ProfileID = 1
 Left Join dbo.AddressRole As ar
   On a.AddressID = ar.AddressID
 WHERE a.MFTID = '12345'
 Group By a.AddressID;

這應該會減少執行的連接次數，並且不會更改結果集的基數。

我添加了MAX以便我可以做 a ，因為我完全期望表格中的每一行GROUP BY可能有不止一行。這樣，我們將基數保持為與表相同，但我們可以查看其中的任何行是否包含我們正在尋找的值。AddressRole``Address``Address``AddressRole``AddressRoleTypeID

如果原始查詢的子查詢沒有返回單行，則不需要MAXand 。GROUP BY``TOP

引用自：https://dba.stackexchange.com/questions/154420