Optimize sql with unions

Question

I try to insert data in a temp table but i cant get it to work.

I try to select 1000 items from each select.

INSERT INTO #Data(user_id, created_at)
SELECT TOP(1000) u.id, c.created_at
FROM comment as c
inner join outfit as o on o.id = c.outfit_id and o.deleted = 0 and o.user_id = 645 AND c.deleted = 0
inner join user as u on u.id = c.user_id and u.is_active = 1

UNION

SELECT TOP(1000) u.id, l.created_at
FROM like as l
inner join outfit as o on o.id = l.outfit_id and o.deleted = 0 and o.user_id = 645
inner join user as u on u.id = l.user_id and u.is_active = 1

UNION 

SELECT TOP(1000) u.id, f.created_at
FROM friend_user as f
inner join user as u on u.id = f.user_id and u.is_active = 1
where f.friend_id = 645 AND f.approved = 1

UNION

SELECT TOP(1000) u.id, c.created_at
  FROM comment_tagged_user AS T
  INNER JOIN comment as c ON c.id = T.comment_id and T.user_id = 645 AND c.deleted = 0
  inner join outfit as o on o.id = c.outfit_id and o.deleted = 0
  inner join user as u on u.id = c.user_id and u.is_active = 1


ORDER BY o.created_at DESC

Now i try to select total 1000 rows of the same sql above. (I remove the TOP 1000 on each SELECT)

INSERT INTO #Data(user_id, created_at)
SELECT TOP 1000 * FROM ( 
   SELECT  u.id, c.created_at
   FROM comment as c
   inner join outfit as o on o.id = c.outfit_id and o.deleted = 0 and o.user_id = 645 AND c.deleted = 0
   inner join user as u on u.id = c.user_id and u.is_active = 1

   UNION

   SELECT u.id, l.created_at
   FROM like_like as l
   inner join outfit as o on o.id = l.outfit_id and o.deleted = 0 and o.user_id = 645
   inner join user as u on u.id = l.user_id and u.is_active = 1

   UNION 

   SELECT u.id, f.created_at
   FROM friend_user as f
   inner join user as u on u.id = f.user_id and u.is_active = 1
   where f.friend_id = 645 AND f.approved = 1

   UNION

   SELECT u.id, c.created_at
   FROM comment_tagged_user AS T
   INNER JOIN comment as c ON c.id = T.comment_id and T.user_id = 645 AND c.deleted = 0
   inner join outfit as o on o.id = c.outfit_id and o.deleted = 0
   inner join user as u on u.id = c.user_id and u.is_active = 1

) AS Data ORDER BY created_at DESC

But the result is not the same. This is what i get:

Left image is from the first sql. The second image shows the correct result. But, the second SQL takes 7,8s, the first SQL takes only 0.7s.

So, what am i doing wrong in the first sql? Should i not see the same result in the beginning of the list? I use Azure Sql

Compare "Optimize sql with unions" and the statement "I can't get it to work". Which one is it? Does it work or not? — spender
– spender, Commented Aug 17, 2015 at 14:54
Are you sure you want UNION and not UNION ALL? (Do you want to remove duplicates or not?) UNION ALL is much faster. — jarlh
– jarlh, Commented Aug 17, 2015 at 14:55
spender, the first one is that i want to return the same result as the 2:nd. As u can se on the image, they not return the same result. jarlh, yes, i want them to return duplicates. — mrcode
– mrcode, Commented Aug 17, 2015 at 14:57
The code is not similar at all. The second inserts 1,000 rows. The first inserts up to 4,000 rows. — Gordon Linoff
– Gordon Linoff, Commented Aug 17, 2015 at 14:57
Gordon Linoff, Yes, but should not the first 10 rows be the same? I sort on created_at — mrcode
– mrcode, Commented Aug 17, 2015 at 14:59

Russ · Accepted Answer · 2015-08-17 15:34:07Z

2

With 4000 rows, you are sorting on 4000 rows, after ONLY the first 1000 of each of the 4 tables have been retrieved, so on the 1st query, the top 1000 rows of each table, with NO sorting, are returned, then the sorting is performed on that 4000 row result. On the second query, all resulting rows are returned, and then sorting is performed, to return only the top 1000 rows.

This explains both the difference in output AND the difference in performance.

answered Aug 17, 2015 at 15:34

Russ

4,19324 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brian Pressler · Accepted Answer · 2015-08-17 18:16:58Z

1

In your first query, each part of the union is not ordered. If you want them to be ordered you have to put them in sub-queries like this:

INSERT INTO #Data (user_id, created_at)
select top 1000 * 
from
(
    (
    SELECT TOP(1000) u.id, c.created_at
    FROM comment as c
    inner join outfit as o on o.id = c.outfit_id and o.deleted = 0 and o.user_id = 645 AND c.deleted = 0
    inner join user as u on u.id = c.user_id and u.is_active = 1
    order by created_at DESC
    )
    UNION
    (
    SELECT TOP(1000) u.id, l.created_at
    FROM like as l
    inner join outfit as o on o.id = l.outfit_id and o.deleted = 0 and o.user_id = 645
    inner join user as u on u.id = l.user_id and u.is_active = 1
    order by created_at DESC
    )
    UNION 
    (
    SELECT TOP(1000) u.id, f.created_at
    FROM friend_user as f
    inner join user as u on u.id = f.user_id and u.is_active = 1
    where f.friend_id = 645 AND f.approved = 1
    order by created_at DESC
    )
    UNION
    (
    SELECT TOP(1000) u.id, c.created_at
      FROM comment_tagged_user AS T
      INNER JOIN comment as c ON c.id = T.comment_id and T.user_id = 645 AND c.deleted = 0
      inner join outfit as o on o.id = c.outfit_id and o.deleted = 0
      inner join user as u on u.id = c.user_id and u.is_active = 1
    order by created_at DESC
    )
) Data
ORDER BY created_at DESC

This query should return equivalent results as your second query.

edited Aug 17, 2015 at 18:16

answered Aug 17, 2015 at 16:09

Brian Pressler

6,7232 gold badges21 silver badges42 bronze badges

1 Comment

mrcode Over a year ago

Perfect thanx. It works perfect when i removed, q1, q2, q3, q4 (after each query)

Collectives™ on Stack Overflow

Optimize sql with unions

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related