特定键值的慢SQL SELECT

Question

为什么这两个查询的时间会有这么大的差异？

此查询大约需要51秒才能完成：

SELECT TOP 1000 * 
FROM [PHONER].[dbo].[V_PhonerSubjects]
WHERE ProjectID = 137;

但是，此查询大约需要1秒才能完成：

SELECT TOP 1000 * 
FROM [PHONER].[dbo].[V_PhonerSubjects]
WHERE ProjectID = 107;

注意:唯一的区别是键的值不同。

两者的记录总数都超过了1000条。Project 107有26000条记录，project 137有4,500条记录。

ProjectID是一个带索引的主键。

我注意到，对于第二个查询，在1秒内完成并将所有行发送到结果窗口。第一个查询在大约3秒后开始发送行，并在大约51秒后完成。

这是视图V_PhonerSubjects的SQL

SELECT        
   dbo.PhonerEmner.PhonerEmID AS SubjectID, dbo.PhonerEmner.FK_ProID AS ProjectID, 
   dbo.PhonerProjekt.PhonerTitel AS ProjectName, dbo.Medlemsdata.Vennenr AS FriendNo, 
   dbo.Medlemsdata.OpretteDato AS CreatedDate, dbo.Medlemsdata.OpretteID AS CreatedID, 
   dbo.Bruger.Intialer AS CreatedBy, dbo.Medlemsdata.ÆndretDato AS ChangedDate, 
   dbo.Medlemsdata.ÆndretID AS ChangedID,  
   Bruger_3.Intialer AS ChangedBy, 
   ISNULL(dbo.Medlemsdata.Organisation, N'') + N' ' + ISNULL(dbo.Medlemsdata.Fornavn, N'') + N' ' + ISNULL(dbo.Medlemsdata.Efternavn, N'') AS Name, 
   dbo.MedlemsAdresse.AdrID AS AddressID, dbo.MedlemsAdresse.Adresse AS Address1, 
   dbo.MedlemsAdresse.Adresse2 AS Address2, dbo.MedlemsAdresse.Postnr AS ZIP, 
   dbo.Postnumre.[By] AS City, dbo.Medlemsdata.CPRnr AS CPRno, 
   dbo.Medlemsdata.Køn AS Gender, dbo.Medlemsdata.Telefon AS Phone01, 
   dbo.Medlemsdata.TlfNote1 AS Phone02Type, dbo.Medlemsdata.Tlf1 AS Phone02, 
   dbo.Medlemsdata.TlfNote2 AS Phone03Type, dbo.Medlemsdata.Tlf2 AS Phone03, 
   dbo.Medlemsdata.TlfNote3 AS Phone04Type, 
   dbo.Medlemsdata.Tlf3 AS Phone04, dbo.Medlemsdata.TlfSMS AS PhoneMobile, 
   dbo.Medlemsdata.[E-mail] AS Email, dbo.Medlemsdata.SPFelt1 AS SPField01,
   dbo.Medlemsdata.SPFelt2 AS SPField02, dbo.Medlemsdata.SPFelt3 AS SPField03, 
   dbo.Medlemsdata.SPFelt4 AS SPField04, dbo.Medlemsdata.SPFelt5 AS SPField05, 
   dbo.Medlemsdata.SPFelt6 AS SPField06, dbo.Medlemsdata.SPFelt7 AS SPField07, 
   dbo.Medlemsdata.SPFelt8 AS SPField08, dbo.Medlemsdata.SPFelt9 AS SPField09, 
   dbo.Medlemsdata.SPFelt10 AS SPField10, dbo.Medlemsdata.SPFelt11 AS SPField11, 
   dbo.Medlemsdata.SPFelt12 AS SPField12, dbo.Medlemsdata.SPFelt13 AS SPField13, 
   dbo.Medlemsdata.SPFelt14 AS SPField14, dbo.PhonerEmner.SidsteKontakt AS LastContact, 
   dbo.PhonerEmner.AntalKontakt AS ContactTimes, dbo.PhonerEmner.KontaktDage AS ContactDays, 
   dbo.PhonerEmner.KontaktEfter AS ContactAfter, 
   dbo.PhonerEmner.PhonerIgen AS ContactAfterPhonerID, 
   Bruger_1.Navn AS ContactAfterPhonerName, dbo.PhonerEmner.PhonerNote, 
   dbo.PhonerEmner.Stemning AS Mood, dbo.PhonerEmner.Status, 
   dbo.PhonerEmner.PhonerAft AS LastPhonerID, Bruger_2.Navn AS LastPhonerName, 
   dbo.PhonerEmner.SlutNote AS EndNote, dbo.PhonerEmner.SlutDato AS EndDate, 
   dbo.PhonerImport.PhonerImportID AS ImportID, dbo.PhonerImportData.Status AS ImportStatus, 
   dbo.PhonerImport.ImportFileName, dbo.PhonerImport.ImportTime, 
   dbo.PhonerProjekt.SvarerIkkeTid
FROM            
   dbo.Bruger AS Bruger_1 
RIGHT OUTER JOIN
   dbo.PhonerProjekt 
RIGHT OUTER JOIN
   dbo.PhonerEmner ON dbo.PhonerProjekt.PhonerProID = dbo.PhonerEmner.FK_ProID 
LEFT OUTER JOIN
   dbo.PhonerImportData ON dbo.PhonerEmner.PhonerEmID = dbo.PhonerImportData.FK_PhonerEmID 
LEFT OUTER JOIN
   dbo.Bruger AS Bruger_2 ON dbo.PhonerEmner.PhonerAft = Bruger_2.BrugerID ON Bruger_1.BrugerID = dbo.PhonerEmner.PhonerIgen 
LEFT OUTER JOIN
   dbo.Bruger 
RIGHT OUTER JOIN
   dbo.Bruger AS Bruger_3 
RIGHT OUTER JOIN
   dbo.Medlemsdata ON Bruger_3.BrugerID = dbo.Medlemsdata.ÆndretID ON dbo.Bruger.BrugerID = dbo.Medlemsdata.OpretteID ON dbo.PhonerEmner.FK_Vennenr = dbo.Medlemsdata.Vennenr 
LEFT OUTER JOIN
   dbo.Postnumre 
RIGHT OUTER JOIN
   dbo.MedlemsAdresse ON dbo.Postnumre.Postnummer = dbo.MedlemsAdresse.Postnr ON dbo.Medlemsdata.FK_AdrID = dbo.MedlemsAdresse.AdrID 
LEFT OUTER JOIN
   dbo.PhonerImport ON dbo.PhonerImportData.FK_PhonerImportID = dbo.PhonerImport.PhonerImportID

项目107的客户端统计数据：

Client Execution Time   14:04:24        
Query Profile Statistics            
  Number of INSERT, DELETE and UPDATE statements    0       0.0000
  Rows affected by INSERT, DELETE, or UPDATE statements 0       0.0000
  Number of SELECT statements   2       2.0000
  Rows returned by SELECT statements    1001        1001.0000
  Number of transactions    0       0.0000
Network Statistics          
  Number of server roundtrips   3       3.0000
  TDS packets sent from client  3       3.0000
  TDS packets received from server  241     241.0000
  Bytes sent from client    340     340.0000
  Bytes received from server    976874      976874.0000
Time Statistics         
  Client processing time    95      95.0000
  Total execution time  391     391.0000
  Wait time on server replies   296     296.0000

项目137的客户端统计数据：

Client Execution Time   13:58:28        
Query Profile Statistics            
  Number of INSERT, DELETE and UPDATE statements    0       0.0000
  Rows affected by INSERT, DELETE, or UPDATE statements 0       0.0000
  Number of SELECT statements   2       2.0000
  Rows returned by SELECT statements    1001        1001.0000
  Number of transactions    0       0.0000
Network Statistics          
  Number of server roundtrips   3       3.0000
  TDS packets sent from client  3       3.0000
  TDS packets received from server  217     217.0000
  Bytes sent from client    340     340.0000
  Bytes received from server    877700      877700.0000
Time Statistics         
  Client processing time    129596      129596.0000
  Total execution time  130297      130297.0000
  Wait time on server replies   701     701.0000

Answer 1

左连接和右连接的复杂混乱，以及查询非常不相关的可读性很可能是问题的一部分。我可能完全错了，并接受这一点。但是，我根据从每个表到下一个表的所有层次连接条件重构了您的查询...每个表的"JOIN"ed和"ON“条件直接作为关联vs混合。在这里，您可以按层次查看表之间的关系，以获取必要的详细信息。我还使用了别名，以提高原始文章的可读性。

因此，看起来您已经将一些phone项目加入到了PhonerEmner表中。基于这些元素，您可以尝试提取尽可能多的查找辅助数据。如果它存在，很好，获取它，但是如果在子级相关的表中找不到这样的记录，不要停止。这样，它们都是左连接。如果希望在任何给定表中都需要记录，只需将左连接更改为内连接，并且/或者添加WHERE子句以确保找到给定的alias.ID列。

话虽如此，我还是要确保这些表有索引，以便通过..适当地优化连接。

table            index
PhonerEmner      (FK_ProID, PhonerIgen, PhonerEmID, PhonerAft, FK_Vennenr
PhonerProjekt    (PhonerProID)
Bruger           (BrugerID
PhonerImportData (FK_PhonerEmID, FK_PhonerImportID
PhonerImport     (PhonerImportID
Medlemsdata      (Vennenr, OpretteID, ÆndretID, FK_AdrID )
MedlemsAdresse   (AdrID, Postnr)
Postnumre        (Postnummer)

并提出了这个修改后的查询，您可以将其创建为新的视图进行测试。

SELECT
      PE.PhonerEmID AS SubjectID, 
      PE.FK_ProID AS ProjectID, 
      PP.PhonerTitel AS ProjectName, 
      MED.Vennenr AS FriendNo, 
      MED.OpretteDato AS CreatedDate, 
      MED.OpretteID AS CreatedID, 
      B.Intialer AS CreatedBy, 
      MED.ÆndretDato AS ChangedDate, 
      MED.ÆndretID AS ChangedID,  
      B3.Intialer AS ChangedBy, 
      ISNULL(MED.Organisation, N'') 
         + N' ' + ISNULL(MED.Fornavn, N'') 
         + N' ' + ISNULL(MED.Efternavn, N'') AS Name, 
      ADR.AdrID AS AddressID, 
      ADR.Adresse AS Address1, 
      ADR.Adresse2 AS Address2, 
      ADR.Postnr AS ZIP, 
      PST.[By] AS City, 
      MED.CPRnr AS CPRno, 
      MED.Køn AS Gender, 
      MED.Telefon AS Phone01, 
      MED.TlfNote1 AS Phone02Type, 
      MED.Tlf1 AS Phone02, 
      MED.TlfNote2 AS Phone03Type, 
      MED.Tlf2 AS Phone03, 
      MED.TlfNote3 AS Phone04Type, 
      MED.Tlf3 AS Phone04, 
      MED.TlfSMS AS PhoneMobile, 
      MED.[E-mail] AS Email, 
      MED.SPFelt1 AS SPField01,
      MED.SPFelt2 AS SPField02, 
      MED.SPFelt3 AS SPField03, 
      MED.SPFelt4 AS SPField04, 
      MED.SPFelt5 AS SPField05, 
      MED.SPFelt6 AS SPField06, 
      MED.SPFelt7 AS SPField07, 
      MED.SPFelt8 AS SPField08, 
      MED.SPFelt9 AS SPField09, 
      MED.SPFelt10 AS SPField10, 
      MED.SPFelt11 AS SPField11, 
      MED.SPFelt12 AS SPField12, 
      MED.SPFelt13 AS SPField13, 
      MED.SPFelt14 AS SPField14, 
      PE.SidsteKontakt AS LastContact, 
      PE.AntalKontakt AS ContactTimes, 
      PE.KontaktDage AS ContactDays, 
      PE.KontaktEfter AS ContactAfter, 
      PE.PhonerIgen AS ContactAfterPhonerID, 
      B1.Navn AS ContactAfterPhonerName, 
      PE.PhonerNote, 
      PE.Stemning AS Mood, 
      PE.Status, 
      PE.PhonerAft AS LastPhonerID, 
      B2.Navn AS LastPhonerName, 
      PE.SlutNote AS EndNote, 
      PE.SlutDato AS EndDate, 
      PI.PhonerImportID AS ImportID, 
      PID.Status AS ImportStatus, 
      PI.ImportFileName, PI.ImportTime, 
      PP.SvarerIkkeTid
   FROM
      dbo.PhonerEmner PE
         LEFT JOIN dbo.PhonerProjekt PP
            ON PE.FK_ProID = PP.PhonerProID 
         LEFT JOIN dbo.Bruger B1 
            ON PE.PhonerIgen = B1.BrugerID
         LEFT JOIN dbo.PhonerImportData PID 
            ON PE.PhonerEmID = PID.FK_PhonerEmID
            LEFT JOIN dbo.PhonerImport PI 
               ON PID.FK_PhonerImportID = PI.PhonerImportID
         LEFT JOIN dbo.Bruger B2 
            ON PE.PhonerAft = B2.BrugerID 
         LEFT JOIN dbo.Medlemsdata MED 
            ON PE.FK_Vennenr = MED.Vennenr 
            LEFT JOIN dbo.Bruger B
               ON MED.OpretteID = B.BrugerID
            LEFT JOIN dbo.Bruger B3 
               ON MED.ÆndretID = B3.BrugerID
            LEFT JOIN dbo.MedlemsAdresse ADR 
               ON MED.FK_AdrID = ADR.AdrID 
               LEFT JOIN dbo.Postnumre PST
                  ON ADR.Postnr = PST.Postnummer

Answer 2

考虑到运行速度较慢的行数越少，我会怀疑数据内容，而不是模式。

如果一个数据集在大多数外部连接上找到匹配，而另一个数据集没有，这可能解释了运行时的一些差异。连接时，未命中只会ping索引，但命中会导致表读取。

Answer 3

我找到问题了。

我更改了连接。对我来说，不管我是以一种方式还是以另一种方式做到这一点，这都无关紧要，但显然它做到了。

而不是将PhonerEmner加入到PhonerEmID = FK_PhonerEmID上的PhonerImportData。我在Vennenr = FK_Vennenr上加入了MedlemsData to PhonerImportData。这一定与PhonerEmID不是主键而只是被索引有关。