Can this SQL be made more efficient and combined into one select?

Question

I have the 4 SQL queries below that I combine into one result set at the end.

I am guessing that since I am operating on the same tables in each of the SQL sections, there is a cool way to get my results out of one unified SQL query.

Currently I am doing it like this:

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 
into #tmp_Attachments_Sent_To_smc
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)
ORDER BY [date] DESC;
--select * from #tmp_Attachments_Sent_To_smc

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 
into #tmp_Attachments_Sent_from_smc
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)
ORDER BY [date] DESC;


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 
into #tmp_Sent_to_smc
from Message M
where MessageSourceId=2
GROUP BY  CONVERT(DATE, crdate)
ORDER BY [date] DESC;


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 
into #tmp_Sent_from_smc
from Message M
where MessageSourceId=1
GROUP BY  CONVERT(DATE, crdate)
ORDER BY [date] DESC;


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
join #tmp_Attachments_Sent_from_smc B on A.date = b.date
join #tmp_Sent_from_smc C on A.date = C.date
join #tmp_Sent_to_smc D on A.date = D.date


drop table #tmp_Attachments_Sent_To_smc
drop table #tmp_Attachments_Sent_from_smc
drop table #tmp_Sent_from_smc
drop table #tmp_Sent_to_smc

I guess if you're after prettying up, there are two answers for that. If you're after efficient, there's another. Can't have both!

孔夫子 · Accepted Answer · 2012-11-30 04:53:25Z

Notes

Because you are INNER JOIN-ing A and B in the final select, I have pre-filtered the data at source to eliminate the non-matches early (remove where a date doesn't have both RecipientTypeIds). This is used twice.
billinkc is right. Using a LEFT function on a varchar column is not SARGable, so use LIKE instead. Being SARG-able means the criteria can be used in a Search ARGument into an index
Using the PIVOTing pattern, both from/to can be resolved in a single pass through the data. This technique is used twice.
Creating indexes on DATE on the temporary tables will help the query if you are crunching say 1000 unique dates (3 years of data)
The CAST(.. to decimal) needs a specifier. Otherwise, it is equivalent to decimal(18,0). (which could still be useful for avoiding integer division)

A single query

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

The original query batch:

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

I used this DDL and DML for testing:

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

nice solution, and the bullet points are most helpful! thanks
+1 and I also strongly agree with your comments elsewhere that the original temporary table solution was probably 'better' in the sense that it is more optimizer-friendly and particularly more likely to produce more efficient/stable execution plans as things change over time.

billinkc · Answer 2 · 2012-11-29 18:38:06Z

As a first pass, I'd see whether you can eliminate the temporary tables. I like CTE's but they don't always provide better performance.

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

Otherwise, what is probably hurting your performance are the conversions in your WHEREs and GROUP BY (CONVERT(date, M.crdate) CAST(AD.Size AS decimal(10, 2). Those are likely resulting in table scans which hurts you. You could also look at persisted computed columns if you can't modify the existing data types as a means to store those computed items.

I'm staring at the LEFT(mr.EmailAddress, 4) and I think that'll also force a table scan but I think it can be fixed with a mr.EmailAddress LIKE 'smc+%' That should at least be SARGAble.

At that point, you should be able to look at your query plans and determine whether an index could help.

Also, prefix your tables with the appropriate schema. I presume it's dbo but as I understand it, that will help with plan cache reuse.

im getting and error on line ", tmp_Sent_to_smc AS" but trying each of the selects individually works fine.
I had missed a closing parenthesis on tmp_Attachments_Sent_from_smc. Also added a semi-colon before the statement as CTE's require the previous statement to be properly terminated and I find that it is a rare case that they are.
Eliminating temporary tables is a BAD idea. The temporary tables can be indexed and clustered, whereas with CTEs you are at the mercy of the SQL Server.
@RichardTheKiwi I don't think it's possible or fair to empirically say that either using or eliminating temp tables is always a bad idea. This is like saying always or never.
@AaronBertrand I apologize. Restated: For this question, it is most likely a bad idea to convert to CTE.

asked	5 months ago
viewed	156 times
active	5 months ago

Can this SQL be made more efficient and combined into one select?

2 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged sql-server sql or ask your own question.

Community Bulletin

Can this SQL be made more efficient and combined into one select?

2 Answers

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-server sql or ask your own question.

Community Bulletin

Related