Running a particular SQL statement in Toad or any other tool takes a less than 10 seconds where as running the same query in Optimizer takes over a minute. Why is there a difference in execution in Optimizer and other products and why is Optimizer is slower making it difficult to analyze and compare alternates?
SQL Optimizer runs below commands before the query statement in order to collect statistics from SQL Server:
SET Statistics IO ON
SET Statistics Time ON
These commands are required by SQL Optimizer and can reduce the time of execution in Optimizer.
To get a faster execution time in Optimizer, use the Down Arrow icon next to Auto Optimize and Execute icons and select Execute First Record option.
