Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2013
    Posts
    13

    Question Unanswered: Select all VS Select requiered columns - Resources usage question

    Hello, i was wondering if it's worth to specify the columns in a SELECT statement instead of selecting all when you need about 80% of them.

    Will selecting only the required columns use less resources even considering it will have to look for 20 column names instead of bringing all the 25 columns from a table?

    Thanks for any advice you could provide on this..
    Gaston

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I have always been a firm believer in identifying each field you need and requesting that field to be returned. If there is only one row the difference may not be substantial but 100 bytes extra times say 1000 rows because 100k more of data transferred.

    Also if you take the data and then start sorting it, the database server will attempt to sort in memory and if there is insufficient memory will write to disk. Taking all data will mean that all the data gets stored in memory which means fewer records are stored and sorted in memory potentially causing performance issues with your queries.

    I have also seen many times when people write INSERT statements with SELECT * FROM... Doing this could results in data corruption issues especially if the underlying table in the select has added new fields or has changed the order of the fields.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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