SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Houston
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I Need Help with @@rowcount

    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?

    Code:
    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

  2. #2
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Houston
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    never mind

    I solved my problem with ADO. I made the variables output parameters and then accessed them via ADO.
    Code:
    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

  3. #3
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    an easier and much more efficient method would be to use COUNT() to get the totals

    example:

    SELECT @total=COUNT(*) FROM orders

  4. #4
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Houston
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    crowdozer-

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

  5. #5
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Houston
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    i never said i knew what i was doing

    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.

  7. #7
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    select count(*) from visitors
    
    select * from visitors
    select @@rowcount
    provided these results:

    Code:
    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
    Last edited by crowdozer; May 17, 2002 at 10:34.

  8. #8
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Houston
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nice job, crowdozer. I will re-do my SQL statement and thanks for the help.

  9. #9
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  10. #10
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Houston
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •