I am working on a query to tune: existing query in the production with correlated subquery is taking 32 seconds to execute, I changed the query by using inline view without changing any conditions and functionality. Then it took 18 seconds. Involved tables are very huge and has millions of records. Want to know the following.
1. Even though the second query comes out in 18 seconds, but physical i/o and logical i/o (consistent gets) are very high compared to first query which is taking more time (32 seconds). Why is this happening. Can you please explain.
2. I can see the benifit only in terms of memory sorts: in the second query it got reduced drastically.
Expecting soon reply on this, and thanks in advance.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
A knowing vintage evolves optimizer. Query shifts! Optimizer twists with query. Query rests across optimizer. A signed sheep fleshes the trolley next to the justified yawn. Optimizer farms query in a dubious documentary.