Any ideas on why this query would run so slow?

Question

1. Query one--index seek

   SELECT P.Date_Send_Ins,C.* 
   FROM DATABASE1..TABLE1 P  WITH (FORCESEEK) JOIN DATABASE2..TABLE2 C   ON P.COLUMN1  = C.COLUMN1  AND P.COLUMN2 =    C.COLUMN2 
   WHERE    LEFT(C.COLUMN3 ,7) LIKE 'YYYY/MM' and LEFT(P.COLUMN3 ,2) = 'YS'
   

2. Query two

   SELECT P.Date_Send_Ins,C.*
   FROM DATABASE1..TABLE1 P   JOIN DATABASE2..TABLE2 C   ON P.COLUMN1  = C.COLUMN1  AND P.COLUMN2 = C.COLUMN2 
   WHERE    LEFT(C.COLUMN3 ,7) LIKE 'YYYY/MM' and LEFT(P.COLUMN3 ,2) = 'YS'
   

3. Query three--index seek

   SELECT P.Date_Send_Ins,C.* 
   FROM DATABASE1..TABLE1 P   JOIN DATABASE2..TABLE2 C   ON P.COLUMN1  = C.COLUMN1  AND P.COLUMN2 = C.COLUMN2 
   WHERE    LEFT(C.COLUMN3 ,7) LIKE 'YYYY/MM' and  P.Date_Send_Ins LIKE 'YS%'
   

Query one and three are fast in sql server but query two is too slow and results do not appear .

In another server all of them are fast ,i run sp_updatestats, but it's not better .

How to solve this problem?

Answer 1

Avoid using functions on any table columns in the where clause. Almost always a performance issue. E.g don't use LEFT()

SELECT P.Date_Send_Ins,C.* 
FROM DATABASE1..TABLE1 P  
JOIN DATABASE2..TABLE2 C   ON P.COLUMN1  = C.COLUMN1  AND P.COLUMN2 = C.COLUMN2 
WHERE  C.COLUMN3 LIKE 'YYYY/MM%' 
and P.COLUMN3 = 'YS%'