这个SQL能不能再高效一点，合并成一个select呢？

作为第一步，我会看看您是否可以消除临时表。我喜欢CTE的，但它们并不总是提供更好的性能。

;WITH tmp_Attachments_Sent_To_smc AS
(
    -- Find all the emails to smc+
    -- that had attachments 
    -- and something else
    SELECT
        [date] = CONVERT(date, M.crdate)
    ,   [tally sent to smc w/ attachment] = COUNT(*)
    ,   [total MB size] = SUM(CAST(AD.Size AS decimal(10, 2))) / 1024 / 1024
    FROM
        AttachmentDetail AD
        INNER JOIN 
            MessageAttachment MA
            ON AD.AttachmentId = MA.AttachmentId
        INNER JOIN 
            MessageRecipient MR
            ON MA.MessageId = MR.MessageId
        INNER JOIN 
            [Message] M
            ON MR.MessageId = M.id
    WHERE
        AD.isinline <> 1
        AND MR.RecipientTypeId = 2
        AND LEFT(mr.EmailAddress, 4) = 'smc+'
    GROUP BY
        CONVERT(date, M.crdate)
)
, tmp_Attachments_Sent_from_smc AS
(
    SELECT
        [date] = CONVERT(date, M.crdate)
    ,   [tally sent from smc w/ attachment] = COUNT(*)
    ,   [total MB size] = SUM(CAST(AD.Size AS decimal(10, 2))) / 1024 / 1024
    FROM
        AttachmentDetail AD
        INNER JOIN 
            MessageAttachment MA
            ON AD.AttachmentId = MA.AttachmentId
        INNER JOIN 
            MessageRecipient MR
            ON MA.MessageId = MR.MessageId
        INNER JOIN 
            [Message] M
            ON MR.MessageId = M.id
    WHERE
        AD.isinline <> 1
        AND MR.RecipientTypeId = 1
        AND LEFT(mr.EmailAddress, 4) = 'smc+'
    GROUP BY
        CONVERT(date, M.crdate)
)
, tmp_Sent_to_smc AS
(
    SELECT
        [date] = CONVERT(date, M.crdate)
    ,   [grand total sent to smc] = COUNT(*)
    ,   [total MB size sent to smc] = SUM(CAST(Size AS decimal)) / 1024 / 1024
    FROM
        [Message] M
    WHERE
        MessageSourceId = 2
    GROUP BY
        CONVERT(date, crdate)
)
, tmp_Sent_from_smc AS
(
    SELECT
        [date] = CONVERT(date, M.crdate)
    ,   [grand total sent from smc] = COUNT(*)
    ,   [total MB size sent from smc] = SUM(CAST(Size AS decimal)) / 1024 / 1024
    FROM
        [Message] M
    WHERE
        MessageSourceId = 1
    GROUP BY
        CONVERT(date, crdate)
)
SELECT
    A.*
,   B.[tally sent from smc w/ attachment]
,   B.[total MB size]
,   C.[grand total sent from smc]
,   C.[total MB size sent from smc]
,   D.[grand total sent to smc]
,   D.[total MB size sent to smc]
FROM
    tmp_Attachments_Sent_To_smc A
    INNER JOIN 
        tmp_Attachments_Sent_from_smc B
        ON A.date = b.date
    INNER JOIN 
        tmp_Sent_from_smc C
        ON A.date = C.date
    INNER JOIN 
        tmp_Sent_to_smc D
        ON A.date = D.date;

否则，可能会损害您的性能的是 WHERE 和 GROUP BY ( 中的转换CONVERT(date, M.crdate) CAST(AD.Size AS decimal(10, 2)。这些可能会导致对您造成伤害的表扫描。如果您不能将现有数据类型修改为意味着存储那些计算的项目。

我正在盯着LEFT(mr.EmailAddress, 4)看，我认为这也会强制进行表扫描，但我认为它可以通过mr.EmailAddress LIKE 'smc+%'至少应该是 SARGAble 来修复。

到那时，您应该能够查看您的查询计划并确定索引是否有帮助。

此外，为您的表添加适当的模式前缀。我认为它是 dbo，但据我了解，这将有助于计划缓存重用。

笔记

1. 因为您在最终选择中是 INNER JOIN-ing A 和 B，所以我在源中预先过滤了数据以尽早消除不匹配项（删除日期没有两个 RecipientTypeIds 的地方）。这是使用了两次。
2. billinkc是正确的。在 varchar 列上使用 LEFT 函数不是SARGable，因此请改用 LIKE 。支持 SARG 意味着可以在搜索参数中将条件用于索引
3. 使用 PIVOTing 模式，from/to 都可以通过一次数据传递来解决。这种技术被使用了两次。
4. DATE如果您正在处理 1000 个唯一日期（3 年的数据），则在临时表上创建索引将有助于查询
5. CAST(.. to decimal)需要一个说明符。否则，它等同于 decimal(18,0)。（这对于避免整数除法仍然有用）

单个查询

select   A.*,
         C.[grand total sent from smc] ,
         C.[total MB size sent from smc],
         C.[grand total sent to smc] ,
         C.[total MB size sent to smc]
from (
    select 
    [date] = CONVERT(DATE, M.crdate), 
    [tally sent to smc w/ attachment] = SUM(case MR.RecipientTypeId when 2 then 1 else 0 end),
    [tally sent from smc w/ attachment] = SUM(case MR.RecipientTypeId when 1 then 1 else 0 end),
    [total MB size to] =  sum(cast(case MR.RecipientTypeId when 2 then AD.Size else 0 end as decimal (10,2)) )/1024/1024,
    [total MB size from] =  sum(cast(case MR.RecipientTypeId when 1 then AD.Size else 0 end as decimal (10,2)) )/1024/1024
    from AttachmentDetail AD
    inner join MessageAttachment MA on AD.AttachmentId = MA.AttachmentId
    inner join MessageRecipient MR on MA.MessageId = MR.MessageId 
    inner join Message M on MR.MessageId = M.id
    where AD.isinline <>1
        and MR.RecipientTypeId in(1,2)
        and mr.EmailAddress LIKE 'smc+%'
    GROUP BY  CONVERT(DATE, M.crdate)
    HAVING COUNT(DISTINCT MR.RecipientTypeId) = 2
    ) A
join (
    select 
    [date] = CONVERT(DATE, M.crdate), 
    [grand total sent to smc] = SUM(case MessageSourceId when 2 then 1 else 0 end),
    [grand total sent from smc] = SUM(case MessageSourceId when 1 then 1 else 0 end),
    [total MB size sent to smc] =  sum(cast(case MessageSourceId when 2 then Size else 0 end as decimal(10,2) ) )/1024/1024 ,
    [total MB size sent from smc] =  sum(cast(case MessageSourceId when 1 then Size else 0 end as decimal(10,2) ) )/1024/1024 
    from Message M
    where MessageSourceId in (1,2)
    GROUP BY  CONVERT(DATE, crdate)
    HAVING COUNT(DISTINCT MessageSourceId) = 2 -- note #1
    ) C on A.[date] = C.[date]
GO

原始查询批次：

create table #tmp_Attachments_Sent_smc
(
    [date] DATE primary key clustered, -- indexed as well for final query
    [tally sent to smc w/ attachment] bigint,
    [tally sent from smc w/ attachment] bigint,
    [total MB size to] decimal(10,2),
    [total MB size from] decimal(10,2)
)
GO
insert #tmp_Attachments_Sent_smc
select 
    [date] = CONVERT(DATE, M.crdate), 
    [tally sent to smc w/ attachment] = count(case MR.RecipientTypeId when 2 then 0 end),
    [tally sent from smc w/ attachment] = count(case MR.RecipientTypeId when 1 then 0 end),
    [total MB size to] =  sum(cast(case MR.RecipientTypeId when 2 then AD.Size end as decimal (10,2)) )/1024/1024,
    [total MB size from] =  sum(cast(case MR.RecipientTypeId when 1 then AD.Size end as decimal (10,2)) )/1024/1024 
  from AttachmentDetail AD
  join MessageAttachment MA on AD.AttachmentId = MA.AttachmentId
  join MessageRecipient MR on MA.MessageId = MR.MessageId 
  join Message M on MR.MessageId = M.id
 where AD.isinline <>1
   and MR.RecipientTypeId in(1,2)
   and mr.EmailAddress LIKE 'smc+%' -- note #2
 GROUP BY CONVERT(DATE, M.crdate)
HAVING COUNT(DISTINCT MR.RecipientTypeId) = 2 -- note #1
-- ORDER BY [date] DESC; -- no point to ordering in an insert statement
GO

create table #tmp_Sent_smc
(
    [date] DATE primary key clustered, -- indexed as well for final query
    [grand total sent to smc] bigint,
    [grand total sent from smc] bigint,
    [total MB size sent to smc] decimal(10,2),
    [total MB size sent from smc] decimal(10,2)
)
GO
insert #tmp_Sent_smc
select 
    [date] = CONVERT(DATE, M.crdate), 
    [grand total sent to smc] = count(case MessageSourceId when 2 then 1 end),
    [grand total sent from smc] = count(case MessageSourceId when 1 then 1 end),
    [total MB size sent to smc] =  sum(cast(case MessageSourceId when 2 then Size end as decimal(10,2) ) )/1024/1024 ,
    [total MB size sent from smc] =  sum(cast(case MessageSourceId when 1 then Size end as decimal(10,2) ) )/1024/1024 
  from Message M
 where MessageSourceId in (1,2)
 GROUP BY  CONVERT(DATE, crdate)
HAVING COUNT(DISTINCT MessageSourceId) = 2 -- note #1
--ORDER BY [date] DESC;

select A.*,
       C.[grand total sent from smc] ,
       C.[total MB size sent from smc],
       C.[grand total sent to smc] ,
       C.[total MB size sent to smc]
  from #tmp_Attachments_Sent_smc A
  join #tmp_Sent_smc C on A.date = C.date

我使用这个 DDL 和 DML 进行测试：

USE TEMPDB;
if object_id('dbo.VNewID') is not null drop view dbo.VNewID;
if object_id('dbo.Rnd') is not null drop function dbo.Rnd;
if object_id('AttachmentDetail') is not null drop table AttachmentDetail;
if object_id('MessageAttachment') is not null drop table MessageAttachment;
if object_id('Message') is not null drop table Message;
if object_id('MessageRecipient') is not null drop table MessageRecipient;
GO
-- this view supports the next function
create view VNewID as select NewID() N
GO
-- this function generates a random number within a range, for the random data in the insert statements below
create function dbo.Rnd(@max int) returns int as begin return (SELECT ABS(CAST(CAST(N AS VARBINARY) AS INT)) from VNewID) % (@max+1) end
GO
-- create tables with some sample date
create table AttachmentDetail (AttachmentId int primary key clustered, isinline bit, size int);
insert AttachmentDetail select number, dbo.Rnd(2), number from master..spt_values where type='p' and number % 3 = 1;
insert AttachmentDetail select AttachmentId+10000, isinline, size from AttachmentDetail;
insert AttachmentDetail select AttachmentId+20000, isinline, size from AttachmentDetail;
create table MessageAttachment (AttachmentId int, MessageId int, primary key clustered (AttachmentId, MessageId));
insert MessageAttachment select number, number from master..spt_values where type='p' and number % 3 = 1;
insert MessageAttachment select AttachmentId+10000, MessageId +10000 from MessageAttachment;
insert MessageAttachment select AttachmentId+20000, MessageId +20000 from MessageAttachment;
create table Message (Id int primary key clustered, crdate datetime, size int, MessageSourceId tinyint);
insert Message select number, dateadd(hh, -number, getdate()), number, dbo.Rnd(4) from master..spt_values where type='p';
insert Message select Id+10000, dateadd(d,-365,crdate), size, MessageSourceId from Message;
insert Message select Id+20000, dateadd(d,-730,crdate), size, MessageSourceId from Message;
create index ix_message_1 on Message(MessageSourceId) include(id, crdate);
create table MessageRecipient (MessageId int, RecipientTypeId tinyint, EmailAddress varchar(100));
insert MessageRecipient select number, dbo.Rnd(2) + 1, case dbo.Rnd(2) when 0 then 'smc+' else 'other' end + right(number,10) 
    from master..spt_values where type='p';
insert MessageRecipient select MessageId+10000, RecipientTypeId, EmailAddress from MessageRecipient;
insert MessageRecipient select MessageId+20000, RecipientTypeId, EmailAddress from MessageRecipient;
create index ix_MessageRecipient_1 on MessageRecipient(EmailAddress);
GO