CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Alex Gordon
Alex Gordon

Reputation: 60892

creating a copy of a table without indexes

  1. I selected from a view myview that was based on another view and a table mytable with no indexes, the query ran for 40 minutes
  2. I added 5 indexes to mytable and reran myview and the query ran for 3 minutes

  3. I made a copy of mytable like this:

    select * into mytableNEW from mytable

  4. Please notice that no indexes were copied.
  5. I reran myview (not forgetting to point to the new table), and it took 3 minutes!!

question: Why did my query initially take 40 minutes without indexes, but after creating indexes on the table and then copying the data (without indexes) to another table, the query sped up very significantly??

Upvotes: 1

Views: 596

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

My guess (which is too long for a comment) is a suboptimal query plan for the view.

The query plan for a view is based on when the view is first run, not on the current run. Over time, the table changed, the environment changed, but the view continued to use the original query plan. I suspect that it was doing nested loop joins, and the execution engine is smart enough to dynamically find and use the indexes, even without recompiling the query.

When you repointed the view to the new table, the view was recompiled for the current environment, so the execution plan was optimal.

This is a guess. A difference of 40 minutes due to cache misses seems very large, unless the data is really, really big.

Upvotes: 1

Related Questions