Improve select query on a physical table in SQL Server

Question

I have table with 184,970 rows and 9 columns; 6 of them are flags with values of 0 or 1 only.

When I run a simple select query

select * 
from table

it takes 17 seconds.

I haven't' created any indexes. Which column should I create an index on, if any? Because I'm retrieving all columns

Do you have any idea how can I improve the performance?

Answer 1

The database really has no choice on how to execute this query:

select * 
from table

It needs to scan all the data and return it. If you need all the data in the table, then nothing is faster than a full table scan.

How can you improve performance? Here are some ideas:

• Select fewer columns. Fewer columns means less data being passed out of the database. That should improve performance.
• Select fewer rows. A where clause means less data and many where clauses can be optimized using indexes.
• Improve the network connectivity between the database and the application.
• Upgrade the hardware or other optimize the server where the database is running.

That said, I would question why you need to dump the entire table into an application. That is not usually how databases are used.

This assumes that the "table" is really a table. If it is a view, then all bets are off. You may have an ability to use indexes or other methods to optimize the code in the view.