I Need Help with @@rowcount

[writhe]

I'm trying to write a stored procedure that can gather some database statistics. I'm running SQL Server 2000 and am trying to take advantage of @@rowcount, which I've read is better on performance than COUNT(). Below, you'll see I've used several SELECT statements to fill three variables with the @@rowcount function. My problem is that I only want to return one recordset ... the one containing "TodaysOrders," "UnprocessedOrders," "TotalOrders" so I can access them in an ASP page. Is it possible to drop the prior SELECT statements or somehow create output variables?

CREATE PROCEDURE dbo.sp_CountRecords

AS
	DECLARE @today INT, @unprocessed INT, @total INT

	SELECT * FROM orders WHERE TransTime > CONVERT(DATETIME, LEFT(CONVERT(VARCHAR, GETDATE(), 120), 10))
	SET @today  = @@rowcount

	SELECT * FROM orders WHERE TransactionID in (SELECT TransactionID FROM orderstatus WHERE Status = 0)
	SET @unprocessed = @@rowcount

	SELECT * FROM orders
	SET @total = @@rowcount

	SELECT @today AS TodaysOrders, @unprocessed AS UnprocessedOrders, @total AS TotalOrders

[writhe]

I solved my problem with ADO. I made the variables output parameters and then accessed them via ADO.

CREATE PROCEDURE dbo.sp_CountRecords
	@today INT OUTPUT,
	@unprocessed INT OUTPUT,
	@total INT OUTPUT
AS
	SELECT * FROM orders WHERE TransTime > CONVERT(DATETIME, LEFT(CONVERT(VARCHAR, GETDATE(), 120), 10))
	SET @today  = @@rowcount

	SELECT * FROM orders WHERE TransactionID in (SELECT TransactionID FROM orderstatus WHERE Status = 0)
	SET @unprocessed = @@rowcount

	SELECT * FROM orders
	SET @total = @@rowcount

	SELECT @today AS TodaysOrders, @unprocessed AS UnprocessedOrders, @total AS TotalOrders

[JimBolla]

an easier and much more efficient method would be to use COUNT() to get the totals

example:

SELECT @total=COUNT(*) FROM orders

[writhe]

crowdozer-

I've read that is a bigger performance drain than using @@rowcount, especially if the table is large.

[JimBolla]

i find that hard to believe. in your code, its actually generating a whole recordset before getting the count on it. which are also returned to your calling application as a recordset (or 3 in your example) i don't see how this could possibly me more efficient then simply getting the count. but if you would point me to your sources, feel free to educate me otherwise.

[writhe]

crowdozer-

Well, I'm no pro at this. Maybe I should change the SELECT statements to select a single column, rather than all of them and isn't there a statement that tells SQL to not create a recordset?

Anyway, this is an excerpt from Beginning SQL Programming, Wrox Press (p. 371):

Performance Tip: The COUNT function, as well as other Aggregate functions, is not particularly easy on performance. If we want to get the count of records returned by a query with SQL Server, we can access the @@ROWCOUNT variable, which always contains the number of rows affected by the last query of the connection.

Sorry, it's not more detailed but that's all I've got for you.

[JimBolla]

hmmm. i may be mistaken but the way interpret that quote is that its more efficient to use @@rowcount if you've already done the select statement (because you had to) than to do a Count(*) after you've already done the select statement. a simple test using sql tracing should show this.

select count(*) from visitors

select * from visitors
select @@rowcount

provided these results:

select count(*) from visitors     	SQL:StmtCompleted	0	0	30
select * from visitors 	SQL:StmtCompleted	19	47	33
select @@rowcount    	SQL:StmtCompleted	0	0	0

i ran this on several different sizes of data and got similar results. basically, select @@rowset IS faster than select count(*), but select count(*) is WAY faster than select(*)


ps: columns are: statemet, type, duration, writes, reads

[writhe]

Nice job, crowdozer. I will re-do my SQL statement and thanks for the help.

[MattR]

Remember that, yes, @@rowcount is faster, because there is an additional step to compute the aggregate count( * ). However remember that SELECT * @@rowcount is highly inefficient, it locks the whole table, etc.. It may work here in isolation but I would suspect in a multi-user environment with those queries being run often will cause major problems.

Do you have indexes on the cols in the WHERE clauses? That could help.

[writhe]

Originally posted by MattR
Do you have indexes on the cols in the WHERE clauses? That could help.

I'm not sure I follow you here. I've got ID columns in each table.

For what it's worth, I've changed the procedure and used crowdozer's suggestion. Here it is:

CREATE PROCEDURE dbo.sp_CountRecords
	@today INT OUTPUT,
	@unprocessed INT OUTPUT,
	@total INT OUTPUT
AS
	SELECT @today = COUNT(*) FROM orders WHERE TransTime > CONVERT(DATETIME, LEFT(CONVERT(VARCHAR, GETDATE(), 120), 10))

	SELECT @unprocessed = COUNT(*) FROM orders WHERE TransactionID in (SELECT TransactionID FROM orderstatus WHERE Status = 0)

	SELECT @total = COUNT(*) FROM orders