Post reply

Difference in time to fetch results between a select * and select count(*)

  • July 21, 2009 at 3:27 am

    #133107

    Both the queries Select * from table_name and Select Count(*) from table_name does a full table scan operation.

    But why does the Select * from table_name take more time to fetch the results ???

    Thanx in Advance !

    Sanz

  • July 21, 2009 at 3:39 am

    #1027485

    Hard to say exactly ,could be multiple factors involved , not least the amount of data sent to the client. post the SQLPlans.


    Clear Sky SQL
    My Blog[/url]

  • July 21, 2009 at 4:04 am

    #1027490

    I'm talking about a general scenario... It can be any table...

    The count(*) result gives results faster than the select * command... Although both these commands use a table scan(I'm not using any indexes)... This is easily visible when you query a huge table....

    Sanz

  • July 21, 2009 at 4:26 am

    #1027501

    with a table with no index, SQL server automatically creates a heap index, so there is at least some information on your table.

    for the count, I'm guessing that although it's a table scan, it doesn't need to gather any pages of data other than the healp index to get the count. but with the select *, it has to gather those same pages, plus all the additional pages where the column data exists, which explains why it might take longer.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 21, 2009 at 6:47 am

    #1027568

    Gail has explained, at length, many of the behaviors of COUNT(*) over at her blog[/url]. In a nutshell, SQL Server is smart enough to figure out which index, table, statistic, has the fewest number of pages that will allow it to arrive at an answer and it uses that. SELECT * flat out has to walk the source, no short-cuts allowed, and then it has to marshal all that into memory, where COUNT does not, and then move it all across the network. It's just more work and therefore it takes longer.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 21, 2009 at 6:59 am

    #1027576

    Thanx a lot guys....

    Sanz

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

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