Esse SQL pode ser mais eficiente e combinado em um select?

Como primeira passagem, veria se você pode eliminar as tabelas temporárias. Eu gosto de CTE , mas nem sempre eles oferecem melhor desempenho.

;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;

Caso contrário, o que provavelmente está prejudicando seu desempenho são as conversões em seus WHEREs e GROUP BY ( CONVERT(date, M.crdate) CAST(AD.Size AS decimal(10, 2). Essas provavelmente estão resultando em varreduras de tabela que prejudicam você. Você também pode examinar as colunas computadas persistentes se não puder modificar os tipos de dados existentes como um significa armazenar esses itens calculados.

Estou olhando para o LEFT(mr.EmailAddress, 4)e acho que isso também forçará uma verificação da tabela, mas acho que pode ser corrigido com um mr.EmailAddress LIKE 'smc+%'Isso deve ser pelo menos SARGable.

Nesse ponto, você deve ser capaz de examinar seus planos de consulta e determinar se um índice pode ajudar.

Além disso, prefixe suas tabelas com o esquema apropriado. Presumo que seja dbo, mas pelo que entendi, isso ajudará no plano de reutilização do cache.

Notas

1. Como você está INNER JOIN-ing A e B na seleção final, pré-filtrei os dados na origem para eliminar as não correspondências antecipadamente (remover onde uma data não possui ambos os RecipientTypeIds). Isso é usado duas vezes.
2. billinkcestá certo. Usar uma função LEFT em uma coluna varchar não é SARGable , portanto, use LIKE. Ser compatível com SARG significa que os critérios podem ser usados ​​em um ARGument de pesquisa em um índice
3. Usando o padrão PIVOTing, ambos de/para podem ser resolvidos em uma única passagem pelos dados. Esta técnica é usada duas vezes.
4. A criação de índices nas DATEtabelas temporárias ajudará na consulta se você estiver analisando, digamos, 1.000 datas exclusivas (3 anos de dados)
5. O CAST(.. to decimal)precisa de um especificador. Caso contrário, é equivalente a decimal(18,0). (que ainda pode ser útil para evitar a divisão inteira)

Uma única consulta

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

O lote de consulta original:

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

Eu usei este DDL e DML para testar:

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