I have a table with [1 million data ,11 columns]. My select query [select * from tbl] seems to be taking 2 minutes to complete.
Server is SQL Server Express 2005. Table has a clustered index [primary key], no other indexes.
If I create a copy of the table using select * into table1 from tbl, then the same select query on the new table takes maximum 10 seconds. I couldn't find any differences in table structure
What might causing the original table slow?
How are you running the query? Is is from an SQL tool connected remotely? If so part of the problem could be related to the cost of shipping the million rows of data to your client (and the client subsequently dealing with that data). It is also possible that the select * into table1 from tbl query is being cleverly optimised by the database. Given that the query is creating an exact copy of the table, the database might be able to do a more efficient operation to replicate the data then through its general SQL mechanism. You can test this by comparing a more complicated query, e.g. use a subset of the rows and add some constraints in the where clause.
What might causing the original table slow?
Nothing. It is not slow.
"select * into table from table" - the data never leaves the server.
"select * from table" - the data is sent to the client who has to do processing. it also means you send the data over SOME sort of network interface (even it local) - all that is overhead.
reading 11 million rows simply takes time to process.