SQL长时间运行的查询/最大化服务器资源，例如内存/ CPU [英] SQL Long running query / maxing out server resource e.g. RAM/CPU

查看：89
本文介绍了SQL长时间运行的查询/最大化服务器资源，例如内存/ CPU的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我最初发布了以下线程 SQL查询-长期运行/采用CPU资源

I originally posted the following thread SQL Query - long running / taking up CPU resource

我的问题是查询的可维护性，已经解决了这一问题（请参见上一个线程，但总之，我使用了很多ISNULL函数

My issue was the SARGABILITY of my query, having addressed that (see the previous thread but in short I was using a lot of ISNULL functions which were bypassing the index scans) I am now having further issues.

我的SQL Server设置如下：

My SQL server settings are as below:

并行度的成本阈值
5

最大并行度
0

我的查询仍然需要2:13运行并导致CPU /内存峰值，我有一个功能强大的服务器，例如64GB RAM，因此资源不是问题。请参阅下面的查询：

My query still takes 2:13 to run and causes CPU / Memory spikes, I have a largely capable server e.g. 64GB RAM so resource is not the issue. See query below:

WITH CTE AS 
(
    SELECT R.Id AS ResultId,
        r.JobId,
        r.CandidateId,
        R.Email,
        CAST(0 AS BIT) AS EmailSent,
        NULL AS EmailSentDate,
        'PICKUP' AS EmailStatus,
        GETDATE() AS CreateDate,
        C.Id AS UserId,
        C.Email AS UserEmail,
        NULL AS Subject
    FROM RESULTS R
    INNER JOIN JOB J ON R.JobId = J.Id
    INNER JOIN Consultant C ON J.UserId = C.Id
    WHERE 
        J.DCApproved = 1
        AND (J.Closed = 0 OR J.Closed IS NULL)
        AND (R.Email <> '' OR R.Email IS NOT NULL)
        AND (R.EmailSent = 0 OR R.EmailSent IS NULL)
        AND R.EmailSentDate IS NULL -- email has not been sent
        AND (R.EmailStatus = '' OR R.EmailStatus IS NULL)
        AND (R.IsEmailSubscribe = 'True' OR R.IsEmailSubscribe IS NULL)
        -- not already been emailed for this job
        AND NOT EXISTS (
            SELECT SMTP.Email
            FROM SMTP_Production SMTP
            WHERE SMTP.JobId = R.JobId AND SMTP.CandidateId = R.CandidateId
        )
        -- not unsubscribed
        AND NOT EXISTS (        
            SELECT u.Id FROM Unsubscribe u
            WHERE (u.EmailAddress = R.Email OR (u.EmailAddress IS NULL AND R.Email IS NULL)) 
        )
        AND NOT EXISTS (
            SELECT SMTP.Id FROM SMTP_Production SMTP
            WHERE SMTP.EmailStatus = 'PICKUP' AND SMTP.CandidateId = R.CandidateId
        )   
        AND C.Id NOT IN (
            -- LIST OF IDS
        )
        AND J.Id NOT IN (
            -- LIST OF IDS
        )
        AND J.ClientId NOT IN 
        (
            -- LIST OF IDS
        )
)

SELECT 
    CTE.ResultId,
    CTE.JobId,
    CTE.CandidateId,
    CTE.Email,
    CTE.EmailSent,
    CTE.EmailSentDate,
    CTE.EmailStatus,
    CTE.CreateDate,
    CTE.UserId,
    CTE.UserEmail,
    NULL
FROM CTE
  INNER JOIN 
    (
        SELECT *, row_number() over(partition by CTE.Email, CTE.CandidateId order by CTE.EmailSentDate desc) as rn
        FROM CTE

    ) DCTE ON CTE.ResultId = DCTE.ResultId AND DCTE.rn = 1

请参见下面的结果表索引，以下内容似乎不正确：

See Indexes for the Results table below, something does not seem right on the below:

/****** Object:  Index [_dta_index_Results_7_2107154552__K35_K2_K3_K34_K36_K8_K33_K1]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [_dta_index_Results_7_2107154552__K35_K2_K3_K34_K36_K8_K33_K1] ON [dbo].[Results]
(
    [EmailSentDate] ASC,
    [JobId] ASC,
    [AryaCandidateId] ASC,
    [EmailSent] ASC,
    [EmailStatus] ASC,
    [Email] ASC,
    [IsEmailSubscribe] ASC,
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [ACI_CMT_APPLICANTS]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [ACI_CMT_APPLICANTS] ON [dbo].[Results]
(
    [Email] ASC
)
INCLUDE (   [Id],
    [AryaCandidateId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [ACI_Job]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [ACI_Job] ON [dbo].[Results]
(
    [AryaCandidateId] ASC,
    [JobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [ACI_Results]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [ACI_Results] ON [dbo].[Results]
(
    [AryaCandidateId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [gen_smtp_auto]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [gen_smtp_auto] ON [dbo].[Results]
(
    [EmailSentDate] ASC,
    [Email] ASC,
    [IsEmailSubscribe] ASC,
    [EmailSent] ASC,
    [EmailStatus] ASC
)
INCLUDE (   [Id],
    [JobId],
    [AryaCandidateId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [Hot]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [Hot] ON [dbo].[Results]
(
    [JobId] ASC,
    [Action] ASC
)
INCLUDE (   [Engaged]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IX_Results]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [IX_Results] ON [dbo].[Results]
(
    [Id] ASC,
    [JobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IX_Results_1]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [IX_Results_1] ON [dbo].[Results]
(
    [Id] ASC,
    [JobId] ASC,
    [AryaCandidateId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [JobMetrics]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [JobMetrics] ON [dbo].[Results]
(
    [JobId] ASC,
    [Source] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [KEY_CAMPAIGN]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [KEY_CAMPAIGN] ON [dbo].[Results]
(
    [ResumeDownloadedDate] ASC,
    [ResumeDownloadStatus] ASC,
    [KeywordCampaignId] ASC,
    [Source] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [MISSING_CREATEDATE]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [MISSING_CREATEDATE] ON [dbo].[Results]
(
    [CreateDate] ASC
)
INCLUDE (   [Id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [MISSING_MOVERSPROB]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [MISSING_MOVERSPROB] ON [dbo].[Results]
(
    [MoversProbability] ASC
)
INCLUDE (   [Id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [MISSING_SORTORDER]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [MISSING_SORTORDER] ON [dbo].[Results]
(
    [SortOrder] ASC
)
INCLUDE (   [Id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [Proto_Resume_Downloa]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [Proto_Resume_Downloa] ON [dbo].[Results]
(
    [JobId] ASC,
    [ResumeDownloadedDate] ASC,
    [ResumeDownloadStatus] ASC,
    [Location] ASC,
    [Source] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [Result_Email]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [Result_Email] ON [dbo].[Results]
(
    [Email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [Result_Email_Send]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [Result_Email_Send] ON [dbo].[Results]
(
    [EmailSentDate] ASC
)
INCLUDE (   [Id],
    [JobId],
    [AryaCandidateId],
    [Email],
    [IsEmailSubscribe],
    [EmailSent],
    [EmailStatus]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [Results_JobId_ACI_Email]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [Results_JobId_ACI_Email] ON [dbo].[Results]
(
    [JobId] ASC
)
INCLUDE (   [Id],
    [AryaCandidateId],
    [Email]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [RESULTS_JOBID_ALL]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [RESULTS_JOBID_ALL] ON [dbo].[Results]
(
    [JobId] ASC
)
INCLUDE (   [Id],
    [AryaCandidateId],
    [CandidateScore],
    [FirstName],
    [LastName],
    [Telephone],
    [Email],
    [AddressLine1],
    [Location],
    [Postcode],
    [Resume],
    [CurrentJob],
    [CurrentCompany],
    [Skills],
    [Experience],
    [Education],
    [AryaUpdateDate],
    [Industry],
    [Source],
    [LinkedIn],
    [Facebook],
    [Twitter],
    [MoversLabel],
    [MoversProbability],
    [SortOrder],
    [CreateDate],
    [ResumeId],
    [IsEmailSubscribe],
    [EmailSent],
    [EmailSentDate],
    [EmailStatus],
    [Registered],
    [HasVoyagerData],
    [Action],
    [Engaged],
    [FormattedCV],
    [CV],
    [DerivedSource],
    [VoyCode],
    [IsEmailEngaged],
    [IsSMSEngaged],
    [KeywordCampaignId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [ResultsGetResultsbyConsultantId]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [ResultsGetResultsbyConsultantId] ON [dbo].[Results]
(
    [JobId] ASC
)
INCLUDE (   [Id],
    [AryaCandidateId],
    [CandidateScore],
    [FirstName],
    [LastName],
    [Telephone],
    [Email],
    [AddressLine1],
    [Location],
    [Postcode],
    [Resume],
    [CurrentJob],
    [CurrentCompany],
    [Skills],
    [Experience],
    [Education],
    [AryaUpdateDate],
    [Industry],
    [Source],
    [LinkedIn],
    [Facebook],
    [Twitter],
    [MoversLabel],
    [MoversProbability],
    [DOB],
    [SortOrder],
    [ResumeDownloaded],
    [ResumeDownloadedDate],
    [ResumeDownloadStatus],
    [CreateDate],
    [ResumeId],
    [IsEmailSubscribe],
    [EmailSent],
    [EmailSentDate],
    [EmailStatus],
    [Action],
    [Engaged],
    [SentToArya],
    [IgnoreEmailSent],
    [IgnoreEmailSentDate],
    [FormattedCV],
    [CV],
    [DerivedSource],
    [IsEmailEngaged],
    [IsSMSEngaged]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [Stats_Results_JOB_ACI_ACTION_ENGAGED]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [Stats_Results_JOB_ACI_ACTION_ENGAGED] ON [dbo].[Results]
(
    [JobId] ASC
)
INCLUDE (   [AryaCandidateId],
    [Action],
    [Engaged]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [Stats_Results_JobId_ACI]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [Stats_Results_JobId_ACI] ON [dbo].[Results]
(
    [JobId] ASC
)
INCLUDE (   [AryaCandidateId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [Stats_Results_JobId_ACI_Action_Engaged]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [Stats_Results_JobId_ACI_Action_Engaged] ON [dbo].[Results]
(
    [JobId] ASC
)
INCLUDE (   [AryaCandidateId],
    [Action],
    [Engaged]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [Stats_Results_JobId_ACI_DERIVED]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [Stats_Results_JobId_ACI_DERIVED] ON [dbo].[Results]
(
    [JobId] ASC
)
INCLUDE (   [AryaCandidateId],
    [DerivedSource]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [Stats_Results_JobId_SOURCE_ACI]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [Stats_Results_JobId_SOURCE_ACI] ON [dbo].[Results]
(
    [JobId] ASC,
    [Source] ASC
)
INCLUDE (   [AryaCandidateId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [Stats_Results_JobId_Source_ACI_V2]    Script Date: 17/10/2018 15:06:18 ******/
CREATE NONCLUSTERED INDEX [Stats_Results_JobId_Source_ACI_V2] ON [dbo].[Results]
(
    [JobId] ASC,
    [Source] ASC
)
INCLUDE (   [AryaCandidateId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Results] ADD  CONSTRAINT [DF_Results_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO


推荐答案

我有一些建议可以减少执行时间：

I have some suggestions for you to reduce the execution time:


  1. 如果电子邮件为空对于主记录，您不需要运行子查询：

因此，代替此语句：

AND NOT EXISTS (        
            SELECT u.Id FROM Unsubscribe u
            WHERE (u.EmailAddress = R.Email OR (u.EmailAddress IS NULL AND R.Email IS NULL)) 
        )

使用以下语句： 

 AND (NOT EXISTS (        
            SELECT u.Id FROM Unsubscribe u
            WHERE u.EmailAddress = R.Email ) 
        ) or  R.Email IS NULL) -- you dont need to check is it is null




  1. 我建议您尽可能减少或减少符号，请尝试使用并集而不是OR。您可以在下面的链接中找到一些示例：

SQL Performance UNION与OR


  1. 据我所知可以使用JOBID过滤SMTP_Production记录，如果可以的话：

代替此语句

 AND NOT EXISTS (
            SELECT SMTP.Id FROM SMTP_Production SMTP
            WHERE SMTP.EmailStatus = 'PICKUP' AND SMTP.CandidateId = R.CandidateId -- can we add SMTP.JobId = R.JobId
        )

您可以在下面使用

 AND NOT EXISTS (
            SELECT SMTP.Id FROM SMTP_Production SMTP
            WHERE SMTP.EmailStatus = 'PICKUP' AND SMTP.CandidateId = R.CandidateId and SMTP.JobId = R.JobId 
        )

查询的最终版本可能是这样的：

final version of the query might be like this:

WITH CTE AS 
(
    SELECT R.Id AS ResultId,
        r.JobId,
        r.CandidateId,
        R.Email,
        CAST(0 AS BIT) AS EmailSent,
        NULL AS EmailSentDate,
        'PICKUP' AS EmailStatus,
        GETDATE() AS CreateDate,
        C.Id AS UserId,
        C.Email AS UserEmail,
        NULL AS Subject
    FROM RESULTS R
    INNER JOIN JOB J ON R.JobId = J.Id
    INNER JOIN Consultant C ON J.UserId = C.Id
    WHERE 
        J.DCApproved = 1
        AND (J.Closed <> 1)
        AND (R.Email <> '' OR R.Email IS NOT NULL)
        AND (R.EmailSent <> 1)
        AND R.EmailSentDate IS NULL -- email has not been sent
        AND (R.EmailStatus = '' OR R.EmailStatus IS NULL)
        AND (R.IsEmailSubscribe <> 'False')
        -- not already been emailed for this job
        AND NOT EXISTS (
            SELECT SMTP.Email
            FROM SMTP_Production SMTP
            WHERE SMTP.JobId = R.JobId AND SMTP.CandidateId = R.CandidateId
        )
        -- not unsubscribed
          AND ((NOT EXISTS (        
            SELECT u.Id FROM Unsubscribe u
            WHERE u.EmailAddress = R.Email ) 
        ) or  R.Email IS NULL) )
         AND NOT EXISTS (
            SELECT SMTP.Id FROM SMTP_Production SMTP
            WHERE SMTP.EmailStatus = 'PICKUP' AND SMTP.CandidateId = R.CandidateId and SMTP.JobId = R.JobId 
        ) 
        AND C.Id NOT IN (
            -- LIST OF IDS
        )
        AND J.Id NOT IN (
            -- LIST OF IDS
        )
        AND J.ClientId NOT IN 
        (
            -- LIST OF IDS
        )
)

SELECT 
    CTE.ResultId,
    CTE.JobId,
    CTE.CandidateId,
    CTE.Email,
    CTE.EmailSent,
    CTE.EmailSentDate,
    CTE.EmailStatus,
    CTE.CreateDate,
    CTE.UserId,
    CTE.UserEmail,
    NULL
FROM CTE
  INNER JOIN 
    (
        SELECT *, row_number() over(partition by CTE.Email, CTE.CandidateId order by CTE.EmailSentDate desc) as rn
        FROM CTE

    ) DCTE ON CTE.ResultId = DCTE.ResultId AND DCTE.rn = 1

这篇关于SQL长时间运行的查询/最大化服务器资源，例如内存/ CPU的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆