Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generic COUNT(*) Expand / Collapse
Author
Message
Posted Wednesday, October 20, 2010 12:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 3, 2012 10:05 PM
Points: 3, Visits: 40
Comments posted to this topic are about the item Generic COUNT(*)
Post #1007486
Posted Wednesday, October 20, 2010 2:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:25 PM
Points: 55, Visits: 217
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]
(
@TableName sysname = null
)
returns bigint
as
begin

-- Example:
-- select dbo.fn_UTL_GetRowCount ('tblAccounts')
-- select top 10 name, dbo.fn_UTL_GetRowCount (name) from sys.tables

declare @out bigint
select @out = i.rowcnt
from sys.sysindexes i
where id = object_id(@TableName)
and (i.indid = 0 or i.indid = 1)
return @out

end
Post #1007526
Posted Wednesday, October 20, 2010 4:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 2,658, Visits: 4,733
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]
(
@TableName sysname = null
)
returns bigint
as
begin

-- Example:
-- select dbo.fn_UTL_GetRowCount ('tblAccounts')
-- select top 10 name, dbo.fn_UTL_GetRowCount (name) from sys.tables

declare @out bigint
select @out = i.rowcnt
from sys.sysindexes i
where id = 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/
Post #1007595
Posted Thursday, October 21, 2010 4:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 4:21 PM
Points: 41, Visits: 71
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.
Post #1008886
Posted Friday, October 22, 2010 5:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 3, 2012 10:05 PM
Points: 3, Visits: 40
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.

Post #1009106
Posted Friday, October 22, 2010 6:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 4:21 PM
Points: 41, Visits: 71
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.
Post #1009126
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse