Which is better to use IN clause or inner join with variable table

When we are using the IN clause, we experienced that it is taking too much time.

Read about IN clause in SQL server

 https://bageshkumarbagi-msbi.blogspot.com/2019/10/in-clause-in-sql.html

For better performance, we need to avoid the IN clause. In place of in clause, we can use variable table.

Let’s see the demo.

 I have a table DimProduct_test in this we have approx. 4 million records.

     

Now we need to get the product details where ProductAlternateKey in

('BA-8327','BC-M005'

,'BK-M18B-44','BK-M18B-52'

,'BK-M38S-42','BK-M68B-38'

,'BK-M68B-42','BK-M68B-46'

,'BK-M68S-38','BK-M82B-42')

and Color in ('Black','Blue','Grey','Multi','Red','Silver'

,'Silver/Black','White','Yellow')

Using IN clause

   

For the 77.5k records, it is taking approx. 7 seconds.

Now I am using variable table

  

It is taking approx. 4 seconds.

This is simple sql script. If we are working with high volume of data then we will see the execution time differences on both.