SQL Query performance options

 

if Select SQL take lots of time, try below options

1. Inner HASH JOIN
2. Query hint - Option (recompile) – running more than 20 minutes, cancelled query
3. Cross Apply/Outer Apply
   (Optimize the query to use cross apply/outer apply instead of subquery)  – completed in 4 min
4. Use Temp table with index
5. Create update statement to replace sub query
6. Query hint - FORCE_LEGACY_CARDINALITY_ESTIMATION
   Use this hint in select statement
   option (use hint('FORCE_LEGACY_CARDINALITY_ESTIMATION')) 

SELECT *

FROM Schema.Table

WHERE Condition = 'Value'

OPTION (USE HINT ('FORCE_DEFAULT_CARDINALITY_ESTIMATION'));