Why is "SELECT *" about 10 times faster than "SELECT (ALL Columns)"

  • Dear all,

    I have noticed that  a "SELECT * ..."  is about 10 time faster than "SELECT Column1, Column2, ... LastColumn  FROM Table WHERE ID = (number)"

    70 ms <-> 650 ms

    The Queryplans are equal and so are the costs - in both cases it is a grouped index scan with a Cost of 0,003

    The Table has about 130k Rows and the grouped index is on the column "ID" (INT) and all together there are 34 columns.

    The Table uses 53 MB dataspace and 250 MB indexspace

    I also found out that the Server response time is low (70 ms) when I SELECT up to 31 columns (no difference what columns that are) - SELECT more than 31 columns and the response time goes up back to 650 ms

    Selecting an other table on the DB with the same amount of rows but more columns shows no difference between "SELECT * " and "SELECT (ALL Columns) "

    Does anybody have an advice what else i could check to find out why the reponse times differ?

    Thanks in advance for any advice or explanation

    Greeting Frank

  • That's interesting. I assume you've done this multiple times, with the same (approximate) result?


  • Hi Phil,

    thats correct, yesterday and today, and every day about 15 times for each query

  • You said grouped index... Does that mean clustered index? Are there other indexes?

    What is the compatibilbity level?

    How does the execution plan compare between the two?

    Can you share w/ https://www.brentozar.com/pastetheplan/?

  • Phil Parkin wrote:

    That's interesting. I assume you've done this multiple times, with the same (approximate) result?

    That's one of the ways we made a Partitioned View a whole lot faster.  Instead of listing all of the column names for each UNION ALL partition, we used SELECT *.  I don't remember the exact number of columns but I was my first "too many columns on a poorly designed table" shock thought.

    The thing that appears to be happening is the optimizer knows ahead of time that it doesn't have to wade through a bunch of named columns and that it needs to simply return "whole rows".  I don't know if it actually does it that way but it sure seems like that's that trick.

    As you know, this is NOT a trick that works everywhere, especially in an ORM where the data for all columns for the returned rows would be returned instead of just the rows needed.  Because of that old experience, I'm OK with the use of SELECT * when all of the columns or maybe all but a couple of columns are needed.  Partitioned views are definitely one of those spots.  So is making columns of tables, etc.  It's like the old WITH (NOLOCK) thing... normally, a really bad thing to do but, as with everything else, "It Depends".

    And, no, I haven't done any recent testing but this thread might drag a readily deployable test out of me.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi ratbak,

    * yes i meant clustered index.

    * yes, there are 5 more indexes

    * we are running SQL 2014 and compatability Level is 140

    * and YES the 2 execution plans are absoulute identical

    unfortunately i cannot give a complete execution plan but this is what i can say:

    the plan is very simple, plaese see the attached photo

    ATTENTION: today at the office a colleague and me made a weired discovery

    We both use SSMS (same version) to make our checks (queryplan and clientstatistics) - 20.2.30.0

    and we are both conected in the same WIFI and VPN

    BUT his queries are both the same (quick) - no difference between "SELECT * ... "  and "SELECT Columns ..."

    Later I will check which language his SSMS is - mine is german - and will also have more colleagues check this out - but because the fact that many colleagues are in homeoffice its quite difficult to make compareable tests

    Attachments:
    You must be logged in to view attached files.
  • doing a select * in SSMS will also be impacted by network speed between workstation and server, workstation processor (and available cpu %), memory, disk speed, and SSMS settings.

    if you really wanna measure the difference in speed between the * vs columns, do a insert into a temp table. preferably within a SP so other SSMS settings don't affect the temp table insert.

     

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply