+ Reply to Thread
Results 1 to 8 of 8

count(*) vs. count(1)

  1. count(*) vs. count(1)

    Let me apologize up front for asking such a basic question.

    Can anyone indicate if there a performance benefit to using
    count(1) over count(*)?
    If so, what is the reasoning behind the performance
    difference and how would this show up (io statistics /
    showplan)?

    I am using ASE 12.5.4.

    Thanks in advance.

  2. Re: count(*) vs. count(1)

    On Dec 4, 10:23 am, Tim Burrington wrote:
    > Let me apologize up front for asking such a basic question.
    >
    > Can anyone indicate if there a performance benefit to using
    > count(1) over count(*)?
    > If so, what is the reasoning behind the performance
    > difference and how would this show up (io statistics /
    > showplan)?
    >
    > I am using ASE 12.5.4.
    >
    > Thanks in advance.


    There is no benefit in using 1 over *. That holds true for EXISTS as
    well. See Gulutzan and Pelzer's book _SQL Performance Tuning_ for
    further discussion.

    --Jeff

  3. Re: count(*) vs. count(1)

    If you want to have a really fast count of the number of
    rows in your table use the following query:
    select rowcnt(doampg) from sysindexes where
    id=object_id("Tb") and indid <2.
    if there are lots of inserts, deletes going on on your
    table, the results obtained by the above query is not
    correct, this is the only draw back, otherwise, the count is
    always in a fraction of second.

    I wonder if anyone else agrees with me and recommends its
    use.

    Tartampion


    > Let me apologize up front for asking such a basic
    > question.
    >
    > Can anyone indicate if there a performance benefit to
    > using count(1) over count(*)?
    > If so, what is the reasoning behind the performance
    > difference and how would this show up (io statistics /
    > showplan)?
    >
    > I am using ASE 12.5.4.
    >
    > Thanks in advance.


  4. Re: count(*) vs. count(1)

    I agree, except that I would recommend running sp_spaceused instead since
    that's less typing.

    HTH,

    Rob V.
    -------------------------------------------------------------
    Rob Verschoor

    Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
    and Replication Server 12.5 / TeamSybase

    Author of Sybase books (order online at www.sypron.nl/shop):
    "Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
    "The Complete Sybase ASE Quick Reference Guide"
    "The Complete Sybase Replication Server Quick Reference Guide"

    mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
    http://www.sypron.nl
    Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
    -------------------------------------------------------------

    wrote in message news:4755bbe3.293.1681692777@sybase.com...
    > If you want to have a really fast count of the number of
    > rows in your table use the following query:
    > select rowcnt(doampg) from sysindexes where
    > id=object_id("Tb") and indid <2.
    > if there are lots of inserts, deletes going on on your
    > table, the results obtained by the above query is not
    > correct, this is the only draw back, otherwise, the count is
    > always in a fraction of second.
    >
    > I wonder if anyone else agrees with me and recommends its
    > use.
    >
    > Tartampion
    >
    >
    >> Let me apologize up front for asking such a basic
    >> question.
    >>
    >> Can anyone indicate if there a performance benefit to
    >> using count(1) over count(*)?
    >> If so, what is the reasoning behind the performance
    >> difference and how would this show up (io statistics /
    >> showplan)?
    >>
    >> I am using ASE 12.5.4.
    >>
    >> Thanks in advance.




  5. Re: count(*) vs. count(1)

    I'd suggest running some test queries with 'set showplan/statistics io on' and see what happens.

    And just for fun try replacing the */1 with a) a column that is a non-leading column of a non-clustered index and b) not
    part of any index ... any differences in performance?

    Tim Burrington wrote:
    > Let me apologize up front for asking such a basic question.
    >
    > Can anyone indicate if there a performance benefit to using
    > count(1) over count(*)?
    > If so, what is the reasoning behind the performance
    > difference and how would this show up (io statistics /
    > showplan)?
    >
    > I am using ASE 12.5.4.
    >
    > Thanks in advance.


  6. Re: count(*) vs. count(1)

    Well, if accuracy isn't a big issue you've also got:

    select rowcnt from systabstats where id = object_id('') and indid < 2

    This should be a tad bit quicker than rowcnt()/sysindexes, especially for larger tables.

    tartampion wrote:
    > If you want to have a really fast count of the number of
    > rows in your table use the following query:
    > select rowcnt(doampg) from sysindexes where
    > id=object_id("Tb") and indid <2.
    > if there are lots of inserts, deletes going on on your
    > table, the results obtained by the above query is not
    > correct, this is the only draw back, otherwise, the count is
    > always in a fraction of second.
    >
    > I wonder if anyone else agrees with me and recommends its
    > use.
    >
    > Tartampion
    >
    >
    >> Let me apologize up front for asking such a basic
    >> question.
    >>
    >> Can anyone indicate if there a performance benefit to
    >> using count(1) over count(*)?
    >> If so, what is the reasoning behind the performance
    >> difference and how would this show up (io statistics /
    >> showplan)?
    >>
    >> I am using ASE 12.5.4.
    >>
    >> Thanks in advance.


  7. Re: count(*) vs. count(1)

    tartampion wrote:
    >
    > If you want to have a really fast count of the number of
    > rows in your table use the following query:
    > select rowcnt(doampg) from sysindexes where
    > id=object_id("Tb") and indid <2.


    Er ... this won't work in ASE 15.

    Its recommended you replace the rowcnt() call with -

    case when ((indid = 0) or (indid = 1 and status & 16 = 16)) then
    row_count(db_id(), id)
    else
    0
    end

    (you may need to join with sysobjects too. I'm not in a
    position to check. Just extracting some code from an
    old article I wrote. See the ASE 15 manuals for further
    details.)

    > if there are lots of inserts, deletes going on on your
    > table, the results obtained by the above query is not
    > correct, this is the only draw back, otherwise, the count is
    > always in a fraction of second.
    >
    > I wonder if anyone else agrees with me and recommends its
    > use.


    There are other ways. If you want a version independent
    method, call a system stored procedure.

    -am 2007

  8. Re: count(*) vs. count(1)

    There is no performance difference between "count(*)" and "count(1)".

    The "*" in this context has nothing to do with a "list" of columns or
    anything, it's just the semantic used for counting records with that system
    function.

    wrote in message
    news:4756be54.1972.1681692777@sybase.com...
    > Thank you all for the responses and suggestions.
    >
    > I can't use the system tables for counts in some of the
    > cases I'm examining as they are in the context of queries
    > with inner joins and search criteria.
    >
    > I ran a quick test as one person suggested with 'set
    > statistics io' and a few dbcc flags. Attached are the
    > results of this test. When I find time I will do a more
    > controlled test involving joins.
    >
    > So far though it looks like there is no appreciable
    > difference between count(*) and count(1).
    >
    > Thanks.
    >
    >
    >> Let me apologize up front for asking such a basic
    >> question.
    >>
    >> Can anyone indicate if there a performance benefit to
    >> using count(1) over count(*)?
    >> If so, what is the reasoning behind the performance
    >> difference and how would this show up (io statistics /
    >> showplan)?
    >>
    >> I am using ASE 12.5.4.
    >>
    >> Thanks in advance.

    >




+ Reply to Thread