mercredi 10 avril 2019

Conditional statement to determine inner join or left outer join?

I have a large millions+ rows of record that in a table that I want to do an inner join when a parameter is provided, if not do a filter based search. Long term solution is to split up the logic, but I need some assistance in the short term.

I'm trying to do something like below:

params:
@movie_ids int_table (optional passing in a table of only ints here) 
@filter_name
@filter_genre
@filter_actor_name

DECLARE @id_count = sets movie id count here or 0; 

SELECT 
      m.Id
      m.Name
      m.ShortName
      m.Genre
      m.ImageUrl
FROM Movies m WITH(NOLOCK)
IF movie_ids <> 0 
  BEGIN
    INNER JOIN @movie_ids mids
      ON mids.id = m.Id
  END
ELSE 
  LEFT OUTER JOIN code_genres cg
    ON m.GenreId = cg.Id
  INNER JOIN Actors a
    ON a.name LIKE '%@filter_actor_name%'
END
WHERE m.Name LIKE '%filter_name%'
OR m.ShortName LIKE '%filter_name%'

Sorry I couldn't provide the actual stored procedure since I'm not the original writer. The problem in short term I'm trying to figure out is to find out if I can do this conditional joins.

Currently it has a LEFT OUTER JOIN with a where clause that suppose to do the conditional, but the amount of data it is returning before the filter occurs is too much - too slow. Something like here

Let me know if I can provide more details.

(Long terms solution is done - split up the logic from sql, but I need something for now)

Aucun commentaire:

Enregistrer un commentaire