Faster way compare changes between SQL sub-queries?

user_6554099 0
Viewed 76

I need to check for the change in the color of cats between two dates.

I have the query below:

WITH
cats_prior AS (SELECT IDTAG,Color FROM  CATS WHERE APPOINTMENT = '06/30/2019'),
cats_now AS (SELECT IDTAG,Color FROM  CATS WHERE APPOINTMENT = '08/31/2019')

SELECT cats_prior.IDTAG, cats_prior.Color,cats_now.Color
FROM cats_prior
JOIN cats_now on cats_prior.IDTAG = cats_now.IDTAG
WHERE cats_prior.Color != cats_now.Color

It works but it takes 11 minutes and there are around 15 million cats in that table.

Is there another way to do this? or a way to make this faster?

This is SQL Server.

sql-server sql database
2 Answers

I would try aggregation with a HAVING clause:

SELECT IDTAG
FROM  CATS
WHERE APPOINTMENT IN ('2019-08-31', '2019-06-30')
GROUP BY IDTAG
HAVING MIN(COLOR) <> MAX(COLOR);

An index on CATS(APPOINTMENT, IDTAG, COLOR) would help. This index might also speed up your version of the query.

user_11440350

Just another way to try the same thing:

select distinct cp.IDTAG
from CATS cp
inner join CATS cn
   on cp.IDTAG = cn.IDTAG
   and cp.color <> cn.color
where cp.APPOINTMENT = '06/30/2019'
   and cn.APPOINTMENT = '08/31/2019'

You can check performance against your data.

user_120554790
Related