Loading ...
Sorry, an error occurred while loading the content.

RE: [mugh-sqlcon] Getting Count(*) from 1 table where data is more

Expand Messages
  • Satya
    I know the below explanation doesn t fulfill Teucer s questions but still wanted to share with you all - - In my no work & all TP days, i did some research to
    Message 1 of 4 , May 20, 2004
    • 0 Attachment
          I know the below explanation doesn't fulfill Teucer's questions but still wanted to share with you all -
      - In my no work & all TP days, i did some research to find out the different ways to know the Total rowcount of a particular table.
      Let me show u by using a sample table called Report_details -
      1) obviously one can use for less no. of records -
          select count(*) from report_details --1523037
      2) you can even use this but gives approx.
          sp_spaceused 'report_details' --rows = 1523005
      3) although it lacks some confirmation, the sysindexes table keeps an internal counter on row count, so if were looking for a plain row count, without conditions,
      this may be a good approach: use
         select rows from sysobjects,sysindexes  --rows = 1523005
         where sysobjects.name='report_details' and sysobjects.id=sysindexes.id and indid=1
      instead of
         select count(*) from ...

      if you need to rounds up the row count of every user table on a database: Use
      select sysobjects.name, max(rows) from sysobjects,sysindexes
      where sysobjects.id=sysindexes.id
      and sysobjects.xtype='U' group by sysobjects.name
       
      (NOTE: As said by Mitra also, If you compare this with plain select count(*) where results are not always accurate, mainly due to the fact that statistics may not be updated as expected)
      Lets keep this thread alive till we find a definate reply to it
       
      -Satya, v-satch

      Teucer <ansari@...> wrote:

      That�s interesting..

       

      I have something which are basically inferences based upon the mail thread. Please don�t shoot me for I am mostly in the dark wrt sql <g>

       

      1. If SysIndexes table is not gonna return me the exact row count in a table given the relevant info due to high density and things like that as pointed out by Mitra then whats the appropriate way to get exact count without using count(*)
      2. now do we have any other sys table which can gather me the exact count. I thought something like this for effectively using indexes there must be some information which is available for the sql engine to pick up total records available in a given table are we taking a look at them or is it something which is not present.

       

      -Itana/Teucer-

       


      From: Viswamitra Chevendra (Wipro Ltd.) [mailto:v-vische@...]
      Sent: Wednesday, May 19, 2004 1:02 AM
      To: sqlcon@yahoogroups.com
      Subject: RE: [mugh-sqlcon] Getting Count(*) from 1 table where data is more

       

      One thing to keep in mind.  �sysindexes� table does not get reflected online.  If the table has high density of inserts(or for that matter deletes), then it is likely that the sysindexes tables does not have the exact figure.

       

      Generally this can be used when one requires the approx number (rather than exact count)

       

      Thanks & Regards,

       

      Mitra

       


      From: Ram Kishore CH (Wipro Ltd.) [mailto:v-ramkch@...]
      Sent: Tuesday, May 18, 2004 6:38 PM
      To: sqlcon@yahoogroups.com
      Subject: [mugh-sqlcon] Getting Count(*) from 1 table where data is more

       

       

      Tip of the day

       

      GET COUNT(*) FROM 1 TABLE


      IN a scenario where the user would like to retrieve the Count(*) from a table without any condition, in that case this query will work

      select count(*) from

      'Suppose product is the table,
      if the product table is light then its ok but if it contains a huge data, then this query can be used.

      select max(rowcnt) from sysindexes where id in (select id from sysobjects where name='product')

      What will happen in this case, it will reduce the no. of rows to fetch from database. What happens is whenever a row is updated in the database, the 'rowcnt' field is also updated in the sysindexes table.

       

      Regards,

       

      RamKishore Charugundla,

      Microsoft India Community Star | MCSD,

      Wipro Technologies,

      Hyderabad,

      Ph : (040) - 5547 - 4226

      http://www.mugh.net

       

       



      SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net

      http://www.mugh.net/sql




      SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net

      http://www.mugh.net/sql






      SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net

      http://www.mugh.net/sql




      Do you Yahoo!?
      Yahoo! Domains - Claim yours for only $14.70/year

    • Vish .
      well IMO sysindexes is the most appropriate system table for obtaining the record count but as explained by satya and viswamithra they get out a sync so one
      Message 2 of 4 , May 21, 2004
      • 0 Attachment
        well IMO sysindexes is the most appropriate system table for obtaining the
        record count but as explained by satya and viswamithra they get out a sync
        so one approach that could be used is to update the stats.

        using sp_spaceused 'table' @updatestats='true'
        or run
        dbcc updatestats with row_count

        This would correct the value in the sysindex table but may sometimes cause
        recompliation of proc
        http://support.microsoft.com/default.aspx?scid=kb;EN-US;308737

        however i would recommend using count(*) if you are very keen on accurate
        count else you know what to do .

        regards

        Viswanath




        >From: Satya <hisatya@...>
        >Reply-To: sqlcon@yahoogroups.com
        >To: sqlcon@yahoogroups.com
        >Subject: RE: [mugh-sqlcon] Getting Count(*) from 1 table where data is more
        >Date: Thu, 20 May 2004 14:52:45 -0700 (PDT)
        >
        > I know the below explanation doesn't fulfill Teucer's questions but
        >still wanted to share with you all -
        >- In my no work & all TP days, i did some research to find out the
        >different ways to know the Total rowcount of a particular table.
        >Let me show u by using a sample table called Report_details -
        >1) obviously one can use for less no. of records -
        > select count(*) from report_details --1523037
        >2) you can even use this but gives approx.
        > sp_spaceused 'report_details' --rows = 1523005
        >3) although it lacks some confirmation, the sysindexes table keeps an
        >internal counter on row count, so if were looking for a plain row count,
        >without conditions,
        >this may be a good approach: use
        > select rows from sysobjects,sysindexes --rows = 1523005
        > where sysobjects.name='report_details' and sysobjects.id=sysindexes.id
        >and indid=1
        >instead of
        > select count(*) from ...
        >
        >if you need to rounds up the row count of every user table on a database:
        >Use
        >select sysobjects.name, max(rows) from sysobjects,sysindexes
        >where sysobjects.id=sysindexes.id
        >and sysobjects.xtype='U' group by sysobjects.name
        >
        >(NOTE: As said by Mitra also, If you compare this with plain select
        >count(*) where results are not always accurate, mainly due to the fact that
        >statistics may not be updated as expected)
        >
        >Lets keep this thread alive till we find a definate reply to it
        >
        >-Satya, v-satch
        >
        >Teucer <ansari@...> wrote:
        >v\:* {behavior:url(#default#VML);}o\:* {behavior:url(#default#VML);}w\:*
        >{behavior:url(#default#VML);}.shape
        >{behavior:url(#default#VML);}st1\:*{behavior:url(#default#ieooui) }
        >That�s interesting..
        >
        >
        >
        >I have something which are basically inferences based upon the mail thread.
        >Please don�t shoot me for I am mostly in the dark wrt sql <g>
        >
        >
        >
        > If SysIndexes table is not gonna return me the exact row count in a
        >table given the relevant info due to high density and things like that as
        >pointed out by Mitra then whats the appropriate way to get exact count
        >without using count(*)
        > now do we have any other sys table which can gather me the exact count.
        >I thought something like this for effectively using indexes there must be
        >some information which is available for the sql engine to pick up total
        >records available in a given table are we taking a look at them or is it
        >something which is not present.
        >
        >
        >
        >-Itana/Teucer-
        >
        >
        >
        >---------------------------------
        >
        >
        >From: Viswamitra Chevendra (Wipro Ltd.) [mailto:v-vische@...]
        >Sent: Wednesday, May 19, 2004 1:02 AM
        >To: sqlcon@yahoogroups.com
        >Subject: RE: [mugh-sqlcon] Getting Count(*) from 1 table where data is more
        >
        >
        >
        >
        >One thing to keep in mind. �sysindexes� table does not get reflected
        >online. If the table has high density of inserts(or for that matter
        >deletes), then it is likely that the sysindexes tables does not have the
        >exact figure.
        >
        >
        >
        >Generally this can be used when one requires the approx number (rather than
        >exact count)
        >
        >
        >
        >Thanks & Regards,
        >
        >
        >
        >Mitra
        >
        >
        >
        >---------------------------------
        >
        >
        >From: Ram Kishore CH (Wipro Ltd.) [mailto:v-ramkch@...]
        >Sent: Tuesday, May 18, 2004 6:38 PM
        >To: sqlcon@yahoogroups.com
        >Subject: [mugh-sqlcon] Getting Count(*) from 1 table where data is more
        >
        >
        >
        >
        >
        >
        >Tip of the day
        >
        >
        >
        >GET COUNT(*) FROM 1 TABLE
        >
        >
        >IN a scenario where the user would like to retrieve the Count(*) from a
        >table without any condition, in that case this query will work
        >
        >select count(*) from
        >
        >'Suppose product is the table,
        >if the product table is light then its ok but if it contains a huge data,
        >then this query can be used.
        >
        >select max(rowcnt) from sysindexes where id in (select id from sysobjects
        >where name='product')
        >
        >What will happen in this case, it will reduce the no. of rows to fetch from
        >database. What happens is whenever a row is updated in the database, the
        >'rowcnt' field is also updated in the sysindexes table.
        >
        >
        >
        >Regards,
        >
        >
        >
        >RamKishore Charugundla,
        >
        >Microsoft India Community Star | MCSD,
        >
        >Wipro Technologies,
        >
        >Hyderabad,
        >
        >Ph : (040) - 5547 - 4226
        >
        >http://www.mugh.net
        >
        >
        >
        >
        >
        >
        >
        >SqlCon is a special interests group run under the aegis of MUGH -
        >http://www.mugh.net
        >
        >http://www.mugh.net/sql
        >
        >
        >
        >
        >
        >
        >SqlCon is a special interests group run under the aegis of MUGH -
        >http://www.mugh.net
        >
        >http://www.mugh.net/sql
        >
        >
        >
        >
        >
        >
        >
        >
        >SqlCon is a special interests group run under the aegis of MUGH -
        >http://www.mugh.net
        >
        >http://www.mugh.net/sql
        >
        >
        >
        >Yahoo! Groups SponsorADVERTISEMENT
        >
        >
        >---------------------------------
        >Yahoo! Groups Links
        >
        > To visit your group on the web, go to:
        >http://groups.yahoo.com/group/sqlcon/
        >
        > To unsubscribe from this group, send an email to:
        >sqlcon-unsubscribe@yahoogroups.com
        >
        > Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
        >
        >
        >
        >---------------------------------
        >Do you Yahoo!?
        >Yahoo! Domains - Claim yours for only $14.70/year

        _________________________________________________________________
        Stop worrying about overloading your inbox - get MSN Hotmail Extra Storage!
        http://join.msn.click-url.com/go/onm00200362ave/direct/01/
      Your message has been successfully submitted and would be delivered to recipients shortly.