Hi all,
I have a query which has 4 with clause CTE's.
Now I am joining these CTE's but the interesting part is, query works fast in a lower environment and takes time in production.
Just to add more information, stats are gathered on the tables in both the environments, the table structure is also same with the Indexes and PK, and the number of rows are almost similar (maybe 1k rows more in Production)
When trying to enhance the performance I was suggested to use CARDINALITY hint and see if the plan changes and query executes faster. I honestly saw no change as the query keeps running in prod and doesn't yield the result. I wanted to understand the actual usage of this hint and does it even improve performance. As per my readings, it is advised not to use hints in Production.
Please don't mind as I'm not able to share the actual query.