Results 1 to 10 of 10

Thread: Faster with SELECT * or SELECT Field1, etc.?[Resolved]

  1. Feb 25th, 2004, 10:27 AM #1
    ober0330
    ober0330 is offline

    Thread Starter
    Frenzied Member ober0330's Avatar
    Join Date
    Dec 2001
    Location
    OH, USA
    Posts
    1,945

    Faster with SELECT * or SELECT Field1, etc.?[Resolved]

    I'm wondering if is faster to select all the fields from a db and just not use the ones you don't want, or to pick out the specific fields you're going to use.

    I have a table with ~20 items. I print a report with about 10 of them. Is it faster to do a SELECT * or to do SELECT Field1, Field2, etc (just the fields I want)?

    Edit: Also, this is for several hundred records and not necessarily an ADO/DAO question... I'm actually using MS SQL Server and PHP. I'm wondering more about the theory than the actual working of it.
    Last edited by ober0330; Feb 25th, 2004 at 10:37 AM.
    format your code!! - [vbcode] [/vbcode]

    ANSWERS CAN BE FOUND HERE!!

    my personal company
    Reply With Quote Reply With Quote
  2. Feb 25th, 2004, 10:30 AM #2
    crptcblade
    crptcblade is offline
    The Devil crptcblade's Avatar
    Join Date
    Aug 2000
    Location
    Quetzalshacatenango
    Posts
    9,091
    The less data you make it retrieve, the faster it should be.

    You should only bring back what you will need anyway.
    Laugh, and the world laughs with you. Cry, and you just water down your vodka.


    Take credit, not responsibility
    Reply With Quote Reply With Quote
  3. Feb 25th, 2004, 10:31 AM #3
    randem
    randem is offline
    Banned randem's Avatar
    Join Date
    Oct 2002
    Location
    Maui, Hawaii
    Posts
    11,385
    ober5861

    It is always faster to only transfer the data that you actually need. Time needs to be taken to retrieve the data and format it to send it to you. Fields that you do not need will take time, Not to mention memory.
    Reply With Quote Reply With Quote
  4. Feb 25th, 2004, 10:32 AM #4
    ober0330
    ober0330 is offline

    Thread Starter
    Frenzied Member ober0330's Avatar
    Join Date
    Dec 2001
    Location
    OH, USA
    Posts
    1,945
    But they way I'm thinking, if it has to go and decide which ones to grab, then that takes processor time. I have to think that it would take less processing to just grab them all!?
    format your code!! - [vbcode] [/vbcode]

    ANSWERS CAN BE FOUND HERE!!

    my personal company
    Reply With Quote Reply With Quote
  6. Feb 25th, 2004, 10:32 AM #5
    ober0330
    ober0330 is offline

    Thread Starter
    Frenzied Member ober0330's Avatar
    Join Date
    Dec 2001
    Location
    OH, USA
    Posts
    1,945
    And like I said, I'm only throwing out a handful of fields.
    format your code!! - [vbcode] [/vbcode]

    ANSWERS CAN BE FOUND HERE!!

    my personal company
    Reply With Quote Reply With Quote
  7. Feb 25th, 2004, 10:34 AM #6
    si_the_geek
    si_the_geek is offline
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,974
    I have noticed a speed difference in the past between using "Select *" and "Select Field1, Field2, ...Field9" (all fields), even though the data is exactly the same in both cases.

    I'm guessing that when you send "select *" to the server it needs to convert this to individual fields, and the local software (ADO/DAO/ODBC etc) needs to do the same to recieve the data.

    Quite why it would make such a difference I dont know, but in my experience it is definitely faster to name all of the fields!
    (Microsoft MVP from July 2007 to June 2017) . . . . . . . . . . Hitchhiker's Guide to Getting Help at VBForums

    Database Development FAQs/Tutorials (updated May 2011) (includes fixing common DB related errors, and [VB.Net] ADO.Net Tutorial, and [Classic VB] ADO tutorial /further steps)
    other useful DB bits: . Connection strings (alternative copy).•. MDAC/Jet/ACE downloads .•. SQL Server downloads .

    Classic VB FAQs (updated Oct 2010) (includes fixing common VB errors)
    some of my Classic VB bits: . Tutorial: How to automate Excel from VB6 (or VB5/VBA) .•. SQL 'Select' statement formatter/checker .•. Convert colour number to colour name .•. FlexGrid: fill from recordset .•. FlexGrid: AutoSize columns .•. DB Reserved Words checker .
    Reply With Quote Reply With Quote
  8. Feb 25th, 2004, 10:35 AM #7
    ober0330
    ober0330 is offline

    Thread Starter
    Frenzied Member ober0330's Avatar
    Join Date
    Dec 2001
    Location
    OH, USA
    Posts
    1,945
    Alright... thanks for the input fellas!

    format your code!! - [vbcode] [/vbcode]

    ANSWERS CAN BE FOUND HERE!!

    my personal company
    Reply With Quote Reply With Quote
  9. Feb 25th, 2004, 10:37 AM #8
    ober0330
    ober0330 is offline

    Thread Starter
    Frenzied Member ober0330's Avatar
    Join Date
    Dec 2001
    Location
    OH, USA
    Posts
    1,945
    No, it won't be a stored procedure because the WHERE in the query will change.
    format your code!! - [vbcode] [/vbcode]

    ANSWERS CAN BE FOUND HERE!!

    my personal company
    Reply With Quote Reply With Quote
  11. Feb 25th, 2004, 10:45 AM #9
    randem
    randem is offline
    Banned randem's Avatar
    Join Date
    Oct 2002
    Location
    Maui, Hawaii
    Posts
    11,385
    ober5861


    Think of it like this... Suppose it takes 1 second to get one fields information and you have 100 fields, This would take 100 seconds.

    If you only want one field, they can not be the same.
    Last edited by randem; Feb 25th, 2004 at 11:30 AM.
    Reply With Quote Reply With Quote
  12. Feb 25th, 2004, 10:57 AM #10
    ober0330
    ober0330 is offline

    Thread Starter
    Frenzied Member ober0330's Avatar
    Join Date
    Dec 2001
    Location
    OH, USA
    Posts
    1,945
    Yeah. I got it. Thanks.
    format your code!! - [vbcode] [/vbcode]

    ANSWERS CAN BE FOUND HERE!!

    my personal company
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules


Click Here to Expand Forum to Full Width