Matching similar entities based on many to many relationship

Viewed 74

I have two entities in my database that are connected with a many to many relationship. I was wondering what would be the best way to list which entities have the most similarities based on it?

I tried doing a count(*) with intersect, but the query takes too long to run on every entry in my database (there are about 20k records). When running the query I wrote, CPU usage jumps to 100% and the database has locking issues.

Here is some code showing what I've tried:

My tables look something along these lines:

/* 20k records */
create table Movie(
   Id INT PRIMARY KEY,
   Title varchar(255)
);

/* 200-300 records */
create table Tags(
   Id INT PRIMARY KEY,
   Desc varchar(255)
);

/* 200,000-300,000 records */
create table TagMovies(
    Movie_Id INT,
    Tag_Id INT,
    PRIMARY KEY (Movie_Id, Tag_Id),
    FOREIGN KEY (Movie_Id) REFERENCES Movie(Id),
    FOREIGN KEY (Tag_Id) REFERENCES Tags(Id),
);

(This works, but it is terribly slow) This is the query that I wrote to try and list them: Usually I also filter with top 1 & add a where clause to get a specific set of related data.

SELECT 
    bk.Id,
    rh.Id
FROM
    Movies bk
    CROSS APPLY (
        SELECT TOP 15
           b.Id,
           /* Tags Score */
           (
            SELECT COUNT(*) FROM (
                SELECT x.Tag_Id FROM TagMovies x WHERE x.Movie_Id = bk.Id
                INTERSECT
                SELECT x.Tag_Id FROM TagMovies x WHERE x.Movie_Id = b.Id
                ) Q1
           )
           as Amount
        FROM 
            Movies b 
        WHERE 
            b.Id <> bk.Id
        ORDER BY Amount DESC
    ) rh

Explanation: Movies have tags and the user can get try to find movies similar to the one that they selected based on other movies that have similar tags.

2 Answers
Related