Why does query without WHERE clause take longer if table doesn't have index?

Question

I ran a Select * from TABLE from two identical tables, the only difference was that one didn't have indexes.

The table that didn't have any indexes took 7 minutes and the one with indexes took 5 minutes.

The tables had 29000 rows each.

Is that normal? Does this also happen in other database servers?

Have you tried it multiple times? Are there any variations in the results when you repeat the measurements? — Branko Dimitrijevic, Jun 10 '13 at 22:36
Is perhaps one table heap-based and the other clustered? Could you show us the exact DDL? — Branko Dimitrijevic, Jun 10 '13 at 22:48
Also, is 100% of the columns covered by the indices in the 2nd table? — Sebas, Jun 10 '13 at 23:48
2nd guess: if you have a real life table with a history, been built for 2 years long... And just for the sake of the exercise you decide to create a 2nd one using `CTAS`, then no, the tables are not exactly identical. Don't forget data fragmentation. — Sebas, Jun 10 '13 at 23:49
7 minutes for what exactly? Seems very slow for only 29K rows. How long does `select count(*)` take? — Tim, Jun 11 '13 at 00:43
For example, I can select * from a non-indexed table with 128K rows and have the resultset returned in 2 seconds on my PC running SQL Server 2008R2. — Tim, Jun 11 '13 at 00:54
Voting to close as you haven't responded to any of the questions in the comments asking for more information. — Martin Smith, Nov 19 '13 at 17:16

score 0 · Answer 1 · answered Jun 10 '13 at 23:41

With so little information it's a bit of wild guesses. A few idea that may explain the difference:

If your table does not even have a clustered index (usually the primary key), it's stored as a heap. Clustered index pages have links to the next page, hence they allow a fast sequential traversal. Heap pages don't have such links and the engine has to constantly refer back to the Index Allocation Map (IAM) pages. This fact can explain that a full scan of a heap may be slower than the full scan of a clustered index.
You are not querying exactly the same tables, so some external factors may come into play. How were the table filled? Maybe they don't have the same fragmentation level. A fragmented table is slower to read.
Are you really doing a "SELECT * FROM..." or was it a shortcut for the question? Because if you did select only a subset of the columns, they may have been found in a covering index, which would obviously be faster to read than the full table data.

1 Answers1