Efficient/optimized query for my query using multiple UNIONS with JOIN

Question

Can someone please have a look into query and suggest any improvement or optimized query for the same so that query runs faster .

So basically, I have 2 table Survey and SurveyInvite.

Sample data for Table Survey

CREATE TABLE dbo.Survey
(
  createdate   date, 
  emailinvite  char(4),
  phoneinvite  char(4),
  smsinvite    char(4),
  surveyid     int
);

INSERT dbo.Survey VALUES
('20220201','12ab','12bc', null ,1),
('20220210','23be','45hg','45tr',2),
('20220220','65hg', null ,'89kj',3);

Sample data for Table SurveyInvite

CREATE TABLE dbo.SurveyInvite
(
  sentdate date,
  id       char(4)
);

INSERT dbo.SurveyInvite VALUES
('20220201','12ab'),
('20220205','12bc'),
('20220210','23be'),
('20220214','45hg'),
('20220218','45tr'),
('20220220','65hg'),
('20220224','89kj');

The output should be

Type	sentdate	inviteid	surveyid
Email	2022-02-01	12ab	1
Email	2022-02-10	23be	2
Email	2022-02-20	65hg	3
Phone	2022-02-05	12bc	1
Phone	2022-02-14	45hg	2
SMS	2022-02-18	45tr	2
SMS	2022-02-24	89kj	3

So basically, I have to get sentdate from SurveyInvite table against each type(email,phone,sms).

Survey table should be unpivoted on email,phone and sms to transform column into rows.

Here's my query

SELECT 'Email' as Type,esi.sentdate,emailinvite as inviteid,s.surveyid
FROM Survey s
INNER JOIN SurveyInvite esi on s.emailinvite=esi.id

UNION 

SELECT 'SMS' as Type,ssi.sentdate,smsinvite as inviteid,s.surveyid
FROM Survey s
INNER JOIN SurveyInvite ssi on s.smsinvite=ssi.id

UNION 

SELECT 'Phone' as Type,psi.sentdate,phoneinvite as inviteid,s.surveyid
FROM Survey s
INNER JOIN SurveyInvite psi on s.phoneinvite=psi.id

Please suggest other way to write query if that makes query faster. I am still trying using UNPIVOT,left join,CTE to avoid using UNION.

Sample setup here

`UNION ALL` can be faster than `UNION`, because `UNION` does the necessary work to deduplicate the result set. Other than that, `UNION ALL` is the correct way to generate your result set. It's possible that indexes might help. To help you with that we need to see your table definitions, your index definitions, and the output of EXPLAIN. Please [read this](https://stackoverflow.com/tags/query-optimization/info), then [edit] your question. — O. Jones, Feb 24 '22 at 13:35

Charlieface · Answer 1 · 2022-02-24T13:51:50.350

You don't need to query the tables three times, you can just unpivot. The easiest way to do this is with a CROSS APPLY (VALUES

SELECT
  v.Type,
  ssi.sentdate,
  v.inviteid,
  s.surveyid
FROM Survey s
CROSS APPLY (VALUES
  ('Email', s.emailinvite),
  ('Phone', s.phoneinvite),
  ('SMS',   s.smsinvite)
) v (Type, inviteid)
INNER JOIN SurveyInvite ssi on v.inviteid = ssi.id;

I suggest you consider normalizing your database in the first place by storing the data unpivoted in a separate table.

Aaron Bertrand · Answer 2 · 2022-02-24T14:00:46.343

1

Another way (again the key is to only read either table once instead of three times):

SELECT i.sentdate, 
       [Type] = REPLACE(u.Types, 'invite', ''),
       inviteid = u.id,
       u.surveyid
FROM dbo.Survey AS s
UNPIVOT (Id FOR Types IN 
  (emailinvite, phoneinvite, smsinvite)) AS u
INNER JOIN dbo.SurveyInvite AS i ON u.Id = i.id;

As you can see from the db<>fiddle, this eliminates 4 of the 6 table scans and also an expensive distinct sort.

edited Feb 24 '22 at 14:00

answered Feb 24 '22 at 13:51

Aaron Bertrand

272,866
37
466
490

Thanks very much for quick response and guidance . I am very clear now how to optimize my original query. Much Appreciated everyone ! – sarika1810 Feb 24 '22 at 14:59

score 0 · Answer 3 · answered Feb 24 '22 at 13:52

I assume that you have set the primary and foreign keys correctly. It might also beneficial to have indexes on the foreign keys. See: Should every SQL Server foreign key have a matching index?.

As always with these performance questions. Only benchmarking different variants can tell you which one is the fastest. The same query can perform very differently with a different set of data.

One possibility is to use joins and base the query on SurveyInvite:

SELECT
    I.sentdate,
    CASE WHEN SE.id IS NOT NULL THEN 'Email'
         WHEN SP.id IS NOT NULL THEN 'Phone'
         ELSE 'SMS'
    END AS Type,
    I.id AS inviteid,
    CASE WHEN SE.id IS NOT NULL THEN SE.surveyid
         WHEN SP.id IS NOT NULL THEN SP.surveyid
         ELSE SS.surveyid
    END AS surveyid
FROM
    SurveyInvite I
    LEFT JOIN Survey SE
        ON I.emailinvite = SE.id
    LEFT JOIN Survey SP
        ON I.phoneinvite = SP.id
    LEFT JOIN Survey SS
        ON I.smsinvite = SS.id

Efficient/optimized query for my query using multiple UNIONS with JOIN

3 Answers3