CODEWITHSUNDEEP
sqlsql-serverazure
mrcode
mrcode

Reputation: 505

Optimize sql with unions

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:

enter image description here enter image description here

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

Upvotes: 0

Views: 66

Answers (2)

Brian Pressler
Brian Pressler

Reputation: 6713

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.

Upvotes: 1

Russ
Russ

Reputation: 4163

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.

Upvotes: 2

Related Questions