Hi all,

Suppose, In a query, I need to fetch all the fields of a table. I am using SQL server 2000. Then which of the following query is better ?

1) SELECT * FROM <TABLE_NAME> WHERE <CONDITION>

2) SELECT <field1>, <field2>, <field3>, ....., <fieldn> FROM <TABLE_NAME> WHERE <CONDITION>

I believe that there is a performance hit with the first one because the database must build a list of all columns before doing the actual query. On the other hand if you modify your table by adding new columns 'at the end of the row' then you may well not have to worry about re-writing the query.

--

Joe

But using the first method, if the table is modified and columns are not added at the end you will have to modify every consumer of the query. You do remember every single client application that uses that query, don't you? ;) Well, modifying the table and using the first method breaks every one of them.

Or, if you get some anal DBA that likes his columns all nice and neat and someday rearranges them in a "more appropriate" order, all your apps are broken, too.

I much prefer to explicitly state which columns are returned in the query, that way there are no questions about what columns are returned, and in what order. Performance isn't the issue, readability and more stability in the face of change is, IMO.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thanks for the responses.

Performance is of ultimate interest to me. So I will explicitly write all the fields. The problem is that the particular table happens to have 56 fields and I need to access all of them. I am showing all the details of a particular entity through a web page (btw, I am using asp 3.0).

Change of fields / addition of fields will not be done in haste. So I think, the method of explicitly writing all the fields is the suitable one.

Evenif I am using select *, I will not use index based references in my asp code. i.e. some thing like rstEntity(0), rstEntity(1) etc. will not be written.

If you don't want the hassle of typing 56 field names (and let's face it - who does) you can let Query Analyzer do it for you (if you have SQL 2000). Right-click the table in the Object Browser and choose "Script object to new window as > select" and hey presto all the fields are there - no risk of typos :D

hth
Phil

First off, I doubt seriously you could measure an appreciable difference in performance between the two methods.

Performance impacts are much more likely to be obtained by carefully constructing the query and insuring that proper indexes exist on the appropriate columns and that all columns are the appropriate datatype (and length).

I'm curious, if performance is your concern, what will you use in place of "index based references" to the recordset's field objects? Index based references are much faster than access via a field's name property ...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Aha ! a nice method.

Thanks a lot Phil. It saves my one hour at the least.

Hello Jeff,

Thanks for giving me the information that index based access is faster. Normally, I don't use that. Now I can start using it in queries where fields are explicitly stated.

But, I think it is not good to use index based access if I am using Select *. This will prevent it from breaking when I insert new fields (need not be always at the end - especially in long tables. I prefer to have a logical grouping of fields in design window).

No.

Using an index has virtually nothing to do with the fields in the SELECT statement (well, it sort of does if you are using a covering index, but's that's another story).

An index is meant to improve the performance of JOIN operations or selections in WHERE clauses.

If you execute a query such as:
what you are asking is "Give me all the rows where the indicated condition is true". Since you want all the rows where the condition is true, you'll need to look at every single row in the table in order to determine if each row meets the condition or not. If you have a zillion rows in your table, that will take a while.

On the other hand, if you have an index on the column(s) involved in the selection, the query processor can take advantage of the fact that the index allows a lookup by value, so it can use the index to greatly reduce the amount of information the processor has to look at in order to determine your resultset.

Note that the more specific the index information is, the more useful the index. If you try to select based on a column value where the value can only have a very few possible values, (a column indicating a person's ************, for instance), an index is not likely to do you much good. In fact the query processor may very well opt to ignore the index in that case and scan the table directly, figuring that the index isn't worth the effort since too many rows are likely to be returned and the overhead of processing the index isn't worth it.

You also need to evaluate the ratio of updates to reads in your application with relation to the number of indexes you'll create. If you insert/update/delete a row, then all the indexes on the table will have to be maintained and that is overhead. Whether the tradeoff of reduced update performance versus improved SELECT performance is worth it is something only you can tell.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Hello Jeff,

Sorry for confusing you. I am already using database index on tables.

I am using ASP as scripting language. While accessing records, I am using a stored procedure + recordset combination. In the recordset, I am accessing a field as below.

rstEmployee("firstname")

rstEmployee("lastname")

etc. etc.

I thought if I access them like

rstEmployee(3)

rstEmployee(4)

etc. , then it would become faster. But this is incorrect , is n't it ?