由于DATEDIFF函式，查詢性能變慢

我正在撰寫一個 SQL 查詢，這給了我一個緩慢的性能。由于 DATEDIFF 功能，它沒有給我任何郵件結果。請幫助我重新制作此查詢，以便我的輸出結果更快。我將把查詢放在下面

SELECT DISTINCT isnull(hrr.SourceEmailID,'')
      ,''
      ,''
      ,hrr.RID
      ,hrr.ResID
      ,hrr.ReqID
      ,'Interview Rejected To Employee'
      ,(
       SELECT TOP 1
         RID
       FROM HCM_TEMPLATE_LIBRARY WITH (NOLOCK)
       WHERE Title = 'Interview Reject Mail To Employee (Applicant Source- EGES)'
       )
      ,GETUTCDATE()
      ,hrr.CreatedUserID
      ,0
     FROM hc_resume_bank hrb WITH (NOLOCK)
     INNER JOIN hc_req_resume hrr WITH (NOLOCK)
         ON hrr.resid = HRB.rid
     INNER JOIN HC_REQ_RESUME_STAGE_STATUS hrrss WITH (NOLOCK) ON hrrss.ReqResID = hrr.RID
     INNER JOIN HCM_RESUME_SOURCE hrs WITH (NOLOCK) ON hrs.RID = hrr.SourceID
     INNER JOIN HC_REQ_RES_INTERVIEW_STAGES hrris ON hrris.ReqResId = hrr.RID
     WHERE hrrss.stageid = 4 
      AND hrrss.statusid = 9 
      AND hrr.SourceID = 4 
      AND isnull(hrb.SourceEmailId, '') <> ''
      AND isnull(hrr.SourceEmailId, '') <> ''
      and hrr.AddedType=10 
      AND Datediff(MI, dateadd(mi, 330, hrrss.StatusDate), DATEADD(mi, 330, GETUTCDATE())) <=5

uj5u.com熱心網友回復：

假設您已經確定 datediff 是性能不佳的根本原因，我建議更改此設定：

Datediff(MI, dateadd(mi, 330, hrrss.StatusDate), DATEADD(mi, 330, GETUTCDATE())) <=5

對此：

hrrss.StatusDate >= DATEADD(MI, -5, GETDATE())

這假設 StatusDate 中的日期與服務器的時區相同。

uj5u.com熱心網友回復：

DISTINCT在查詢中幾乎總是一個寫得不好的查詢的指標，作者連接了很多表，構建了一個巨大的中間結果，因此它們必須歸結為它的實際大小DISTINCT。這是一項昂貴的操作。它似乎適用于您的查詢。如果您只是想確保 hc_req_resume.resid 在 hc_resume_bank 中有一個帶有 sourceemailid 的條目，則使用EXISTSorIN進行此查找，而不是連接。

您的查詢帶有適當的查找子句：

SELECT 
  ISNULL(hrr.sourceemailid,'')
  ,''
  ,''
  ,hrr.rid
  ,hrr.resid
  ,hrr.reqid
  ,'Interview Rejected To Employee'
  ,(
     SELECT TOP 1
       rid
     FROM hcm_template_library
     WHERE title = 'Interview Reject Mail To Employee (Applicant Source- EGES)'
   )
  ,GETUTCDATE()
  ,hrr.createduserid
  ,0
FROM hc_req_resume hrr
WHERE hrr.sourceid = 4 
AND hrr.addedtype = 10 
AND hrr.resid IN 
(
  SELECT hrb.rid 
  FROM hc_resume_bank hrb 
  WHERE hrb.sourceemailid <> ''
)
AND hrr.rid IN 
(
  SELECT hrrss.reqresid 
  FROM hc_req_resume_stage_status hrrss 
  WHERE hrrss.stageid = 4 
  AND hrrss.statusid = 9 
  AND hrrss.statusdate >= DATEADD(MI, -5, GETUTCDATE())
)
AND hrr.sourceid IN (SELECT hrs.rid FROM hcm_resume_source hrs)
AND hrr.rid IN (SELECT hrris.reqresid FROM hc_req_res_interview_stages);

列的命名在這里并不容易。為什么該列有時稱為 rid 有時稱為 reqresid？然后我看到了一個擺脫與一個殘留物相結合的東西。這只是同一事物的另一個名稱嗎？或者說擺脫有兩種含義？而被稱為 ID 的表實際上指的是什么？是否有一個名為 r 或 reqres 或 res 的表？好像不是這樣，但是為什么表的ID和表的名字不一樣，讀者一定猜到是什么了？我們甚至無法做出太多猜測，如果有可能擺脫不在 hc_req_res_interview_stages 中有一個匹配項，或者對于一個 sourceid 在 hcm_resume_source 中沒有一個匹配項。通常您對 ID 有一個外鍵約束，因此 ID 為空（如果允許）或者它在父表中確實有匹配項。查找將我們毫無意義。它在您的查詢中嗎？或者這些表不是父表，而是其他子表參考同一個父表？

洗掉任何不需要的查找。hcm_resume_source 和 hc_req_res_interview_stages 中的查找可能是這樣的候選人，但我不知道。

最后，您需要適當的索引。對于 hc_req_resume 這可能類似于

create index idx1 on hc_req_resume (sourceid, addedtype, rid, resid);

那么你可能想要：

create index idx2 on hc_resume_bank (rid) where sourceemailid <> '';
create index idx3 on hc_req_resume_stage_status (stageid, statusid, statusdate, reqresid);

索引中列的順序應根據它們的選擇性進行調整。

uj5u.com熱心網友回復：

Salmon A 有一個很好的答案，我想進一步擴展。

類似于為什么 Salman A 建議您將函式移到 where 子句的右側hrrss.StatusDate，這同樣適用于SourceEmailId，因為將函式放在左側可以防止在這些列上使用索引。

但是，ISNULL()解決起來有點棘手，并且有幾種可能的方法可以解決它。

1. 考慮該列是否真的應該允許 NULLS，并且如果將列更改為不允許 NULLS 是一種選擇。然后你的 where 子句看起來像這樣。

AND hrb.SourceEmailId <> ''
AND hrr.SourceEmailId <> ''

2. SourceEmailId 也有可能永遠是以太，將有一個有效值，或者為 NULL。這將是首選，因為在值未知的情況下應使用 NULL。在這種情況下，您不應該檢查<> ''. 只需檢查該電子郵件IS NOT NULL。

AND hrb.SourceEmailId IS NOT NULL
AND hrr.SourceEmailId IS NOT NULL

3. 如果選項 1 和 2 不是選項，則考慮一個UNION結果集。在這種情況下，您將撰寫一個查詢 forhrb.SourceEmailId <> ''和UNIONthat 到第二個查詢 for 的結果中hrb.SourceEmailId IS NOT NULL。由于您SourceEmailId對兩個不同的表進行了檢查，因此可能意味著四個查詢。但是，不要被更多查詢的事實所困擾，這會以某種方式意味著它會變慢。如果所有 4 個查詢都經過適當調整，并且每個查詢都在 100 毫秒內運行，那么這比一個在 5 分鐘內運行的組合查詢要好。

可以在以下鏈接中找到有關問題的更多詳細資訊以及使用 ISNULL() 的可能解決方法。

• isnull-around-the-predicate-and-sargability
• 在僅使用文字值的 WHERE 子句中替換 ISNULL() 有哪些不同的方法？

一旦應用了這些更改，您將擁有一個可以實際使用這些列上的索引的查詢。那時，我將開始審查您的執行計劃和索引，并可能考慮洗掉DISTINCT. 但是，只要WHERE您的查詢中有幾個子句在每次執行時都會強制SCAN執行，那么現在執行這些操作不會產生太大的好處。

uj5u.com熱心網友回復：

你在未來搜索一個結果，這是正確的嗎？-編輯：我意識到它只是您正在尋找的最后 5 分鐘，因此在這種情況下，您不妨洗掉左側的功能，看看這是否會阻止索引掃描。

關于性能緩慢。您的查詢（僅關注此處的 datediff）不能以這種方式進行搜索。SQL server 需要首先計算表中所有行中的列，總是會導致表掃描。洗掉左側的功能。

解決此問題的一種方法是，首先以可搜索的方式從主表中獲取結果，將其放入一個臨時表，然后將臨時表用于函式并使用其 id 回傳主表以獲取結果。請參見下面的示例。

IF OBJECT_ID('tempdb..#MyTableName') IS NOT NULL
    DROP TABLE #MyTableName

CREATE TABLE #MyTableName
(
    PK INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
    ID INT,
    StatusDate DATETIME
)

INSERT INTO #MyTableName (ID,StatusDate ) 
SELECT
ID,StatusDate 
FROM dbo.basetable p
WHERE p.StatusDate >  GETUTCDATE() --narrow your date criteria as much as needed
GO


SELECT P.* FROM #MyTableName T 
JOIN dbo.basetable P
ON P.Id = T.ID
WHERE Datediff(MI, dateadd(mi, 330, T.StatusDate), DATEADD(mi, 330, GETUTCDATE())) <= 5
OPTION (RECOMPILE)
;

如果你可以在你的日期列上創建一個非聚集索引，看看它帶來了什么。以您撰寫它的方式，它總是會掃描表，但至少它有一個索引。以可追溯的方式，該索引也將幫助很多人。

標籤：sql sql服务器

上一篇：根據RevisionId獲取歷史值

下一篇：如果列值為a，A，A，A，a，A，a，A，a，a，a，A，如何撰寫SQL查詢以列印a，A