Results 1 to 7 of 7

Thread: Question 'bout efficiency in SELECT-statement.

  1. #1
    Join Date
    Oct 2002
    Posts
    4

    Question 'bout efficiency in SELECT-statement.

    Is it more efficient to request all attributes from a table than to pick some of them out.

    I mean. If I need all attributes except one. Is it then to prefer to use "SELECT *" instead of list all attributes except the one not needed?

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    select column1, column2, column3 from tablename

  3. #3
    Join Date
    Oct 2002
    Posts
    4
    I think you missed my point. I know the syntax perfectly. I'm trying to find out how I can achieve a selection in an as time-efficient way as possible.

    Is it a time-demanding issue to pick out only some of the attributes from a table instead of ask for the complete table?

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    From a application point of view....

    Thats was my answer. Use Column names all the time. Do not use Select *.
    Always query what you need.

    If you are using query analyser for adhoc queries, then you can choose whatever you like.

    If you have 100 columns and you like to retrieve only 90 columns and has billion rows, then get all the columns from syscolumns and remove all the Un- necessary columns and query the table for particular condition.

    If you have 100 columns and you like to retrieve only 90 columns and has 100 rows, just do a select *

    It totally depends on what you like to trade.

  5. #5
    Join Date
    Oct 2002
    Posts
    4
    Thank you.

    I will start selecting specific columns, but as my tables grow I will test the efficiency, and perhaps change the syntax!

  6. #6
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    SELECT * is easy to write but it pays to specify column name in the long run. For example, you code your application based on the number of columns you expect from SELECT *, now after few months if you add or drop a column from the table, you will have to re-scan all your application code to see if you have used that table and fix the code.

  7. #7
    Join Date
    Sep 2002
    Posts
    169
    SQL might decide to use an index only IF all of columns from the table that you specify (in the select list, joining conditions, where clause) are in the index.

    If you specify "SELECT *", then it is unlikely that such an index would be useful.

    As a general rule, you should ask for the minimum number of rows and columns that will satisfy your requirements. This means that the potential work that SQL needs to do is kept to a minimum, the work required to move the data from SQL to your application is kept to a minimum and your application only needs to handle the minimum amount of data. Doing this gives you a better chance of writing an application that will scale nicely.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •