SQL Server Performance Forum – Threads Archive

making select * from tbl query fast

How should I optimize my db so that my ”select * from tbl’query will be fast.some of my
‘select * from table are taking 2 sec to execute.

You should rather optimize your query. IMO, there is no place for a SELECT * in production code. Be specific on the columns you want to retrieve. —
Frank
http://www.insidesql.de

Frank, A question comes up: Is any advantage or disadvantage of having index in this case? In other words, if the table has indexes does it make the results of SELECT * faster or slower? CanadaDBA

If the index is referencing the minimum columns and mostly queries columns then the SQL will use the cache to get the results quicker. As Frank said its better to optimize the query or check the execution plan in QA in order to get more information on query execution. I say if the query is used frequently then having an index is advantage and if there aren’t many rows say a couple of thousands, then I wouldn’t worry for an index. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

A SELECT * doesn’t have anything to do with an index per se (Ignore convering indexes now). Methinks they offer advantages when you have a WHERE clause [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] The main disadvantage of SELECT * is that you generate superfluous network traffic by sending data from the server to the client that is not asked for. I know, that SELECT * is a convienent way to ensure that you always catch all columns. However, once you’ve finished your schema, it shouldn’t get updated very often, and so you can be explicite in your column list. – Frank http://www.insidesql.de</a>

You may have conditions in your where clause covered by an index, but because of columns that are not part of index you will have additional bookmark lookup. That’s bad in case you don’t really need values of these extra columns.

]]>