Indianrock (2/14/2016)
Well I certainly hope sql server central.com doesn't lose any of this as it will take a while to go through. 🙂 We do have extensive index maintenance including update statistics with full scan run for a few hours every night and longer on Saturdays. I'll get back on this later todaycreation complete temp table in prod, old query form with no TOP, no new indexes and no order by 18 minutes in prod
-- Production create top 1 temp table old query form with no order by 73 seconds ( new indexes on Early payoff do not exist )
new query form:
top 20k in 10 minutes in prod without new indexes including order by
10 minutes for all 5 million in prod without new indexes including order by
if populating a temp table with all address IDs that are confirmed for deletion then the ORDER BY is absolutely wasted overhead
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato