Generic COUNT(*)

  • Comments posted to this topic are about the item Generic COUNT(*)

  • Hi,

    Assuming that you use the auto statistics create & open and have all the statistics updated well, i think that it will be better to read from the statistics and not performing a real counting of the rows which is a litle bit physically i would say.

    See my code and try it for huge tables and see the difference in time and I/O's.

    ALTER function [dbo].[fn_UTL_GetRowCount]

    (

    @TableNamesysname = null

    )

    returns bigint

    as

    begin

    -- Example:

    -- select dbo.fn_UTL_GetRowCount ('tblAccounts')

    -- select top 10 name, dbo.fn_UTL_GetRowCount (name) fromsys.tables

    declare@outbigint

    select@out = i.rowcnt

    fromsys.sysindexes i

    whereid = object_id(@TableName)

    and(i.indid = 0 or i.indid = 1)

    return@out

    end

  • Victor Shahar (10/20/2010)


    Hi,

    Assuming that you use the auto statistics create & open and have all the statistics updated well, i think that it will be better to read from the statistics and not performing a real counting of the rows which is a litle bit physically i would say.

    See my code and try it for huge tables and see the difference in time and I/O's.

    ALTER function [dbo].[fn_UTL_GetRowCount]

    (

    @TableNamesysname = null

    )

    returns bigint

    as

    begin

    -- Example:

    -- select dbo.fn_UTL_GetRowCount ('tblAccounts')

    -- select top 10 name, dbo.fn_UTL_GetRowCount (name) fromsys.tables

    declare@outbigint

    select@out = i.rowcnt

    fromsys.sysindexes i

    whereid = object_id(@TableName)

    and(i.indid = 0 or i.indid = 1)

    return@out

    end

    The sys.sysindexes System Table has been included as a view for backward compatibility. It might be removed in a future version of Microsoft SQL Server. Check the link below

    http://msdn.microsoft.com/en-us/library/ms190283.aspx

    So it is advisable to use the sys.dm_db_partition_stats System Table and the query would be similar

    DECLARE @strTableName VARCHAR(100)

    SET @strTableName = 'mstEmployees'

    SELECT SUM(row_count)

    FROM sys.dm_db_partition_stats

    WHERE object_name( object_id ) = @strTableName

    AND index_id IN ( 0, 1 )

    You can also have a look at the discussion that followed the QOTD for 19th October 2010

    http://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Is the point of this approach simply to turn an ad-hoc SQL command into a stored procedure? If so, I really don't see the benefit of an ad-hoc dynamic query implemented in a proc over an ad-hoc query in the data tier. That is, why not just build the COUNT query dynamically in the data tier for the relevant table?

    This approach just seems like like a convoluted way to do something that adds complexity with no obvious benefit.

  • thanks all for the comments.

    the use of the stored procedure 4 this solution might seem not so obvious but i do personally prefer sp instead of adhoc launched from data access since its easier to maintain; so i believe it's not abt right or wrong or overcomplicate things but about preferences.

  • stevanuz (10/22/2010)


    thanks all for the comments.

    the use of the stored procedure 4 this solution might seem not so obvious but i do personally prefer sp instead of ad-hoc launched from data access since its easier to maintain; so i believe it's not about right or wrong or over-complicate things but about preferences.

    I think this may be an example of taking a good idea (encapsulating logic in a stored proc) and applying it without consideration of whether it is appropriate in the specific circumstance. If you want to encapsulate the logic, I'd suggest creating a centralized count method in your code that builds the SQL. You take on a lot of baggage using dynamic SQL that could create unintended side effects (for example permission and scope issues) with no benefit on the other end of that trade-off.

  • Thanks for the script.

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

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