How do a sql count on an additional table inside a sql stored proc?

Question

I have a stored proc (called sprocGetArticles) which returns a list of articles from the articles table. This stored proc does not have any parameters.

Users can leave comments for each article and I store these comments in the comments table linked by the article id.

Is there any way I can do a comment count for each articleid in the returned list from within the sprocGetArticles stored procedure so I only have to make one call to the database?

My problem is that i need the article id to do the count which I seem unable to declare.

Is this the best approach anyway?

Answer 1

Well, without knowing what you are selecting and your general schema (and assuming you are at least using SQL Server 2005:

WITH CommentCounts AS
(
   SELECT COUNT(*) CommentCount, ac.ArticleID
   FROM Articles a
   INNER JOIN ArticleComments ac
      ON ac.ArticleID = a.ID
   GROUP BY ac.ArticleID
)

SELECT a.*,
       c.CommentCount
FROM Articles a
INNER JOIN CommentCounts c
   ON a.ID = c.ArticleID

This is a Common Table Expression or CTE. You can read more about them here: http://msdn.microsoft.com/en-us/library/ms190766.aspx

Answer 2

SQL allows entire scalar subqueries to be returned as projected columns. Subqueries can be correlated with the parent query. So is easy to count the comments in a subquery that counts the comments for a given article id:

SELECT a.*, (
  SELECT COUNT(*)
  FROM Comments c
  WHERE c.article_id = a.article_id) AS CountComments
  FROM Articles a;

Note that counting the comments each time can be quite expensive, is better to keep the count as an Article property.

Answer 3

The following will work on SQL Server 2005+ or Oracle 9i+:

WITH COMMENT_COUNT AS (
      SELECT ac.article_id
             COUNT(ac.*) 'numComments'
        FROM ARTICLE_COMMENTS ac
    GROUP BY ac.article_id)
SELECT t.description,
       cc.numComments
  FROM ARTICLES t
  JOIN COMMENT_COUNT cc ON cc.article_id = t.article_id

SQL Server call it a Common Table Expression (CTE); Oracle calls it Subquery factoring.

Alternative:

SELECT t.description,
       cc.numComments
  FROM ARTICLES t
  JOIN (SELECT ac.article_id
               COUNT(ac.*) 'numComments'
          FROM ARTICLE_COMMENTS ac
      GROUP BY ac.article_id) cc ON cc.article_id = t.article_id

Performing the subquery in the SELECT statement works, but will perform the worst of all suggestions for the fact it will execute for every row.

Answer 4

Maybe I'm missing something, but what's with all the subqueries and inline views? Why not just do a straightforward left-join, e.g.:

  SELECT a.ArticleId
       , a.ArticleName
       , (other a columns)
       , COUNT(*)
    FROM Articles a
         LEFT JOIN Comments c
                ON c.ArticleId = a.ArticleId
GROUP BY a.ArticleId
       , a.ArticleName
       , (other a columns);

Answer 5

One option no one has mentioned so far would be a computed column on your article table which would count the number of comments. This is in general much faster than actually computing the number of comments everytime around, and if you really need to query that number frequently, it could save you a lot of processing overhead!

In SQL Server 2005 and up, what you could do in this case is create a small stored function to count the number of comments for each article, and then add this as a computed column to your article table. You could then use that as a normal column and trust me - it's a lot quicker than using subqueries all the time!

CREATE FUNCTION dbo.CountComments(@ArticleID INT)
RETURNS INT 
WITH SCHEMABINDING
AS BEGIN
    DECLARE @ArticleCommentCount INT

    SELECT @ArticleCommentCount = COUNT(*)
    FROM dbo.ArticleComments
    WHERE ArticleID = @ArticleID

    RETURN @ArticleCommentCount
END
GO

Add this to your article table as a column:

ALTER TABLE dbo.Articles
    ADD CommentCount AS dbo.CountComments(ArticleID)

and from then on, just use it as a normal column:

SELECT ArticleID, ArticleTitle, ArticlePostDate, CommentCount 
FROM dbo.Articles

To make it even faster, you could add this column as a persisted column to your table, and then it really rocks! :-)

ALTER TABLE dbo.Articles
    ADD CommentCount AS dbo.CountComments(ArticleID) PERSISTED

It's a bit more work upfront, but if you need this often and all the time, it could be well worth the trouble! Also works great for e.g. reading out certain bits of information from a XML column stored in your database table and expose it as a regular INT column or whatever.

Highly recommend! It's a feature often overlooked in SQL Server.

Marc

Answer 6

Regarding the use of computed columns mentioned in the answer, I wanted to confirm the claims that using a computed column would produce better performance (it didn't make sense to me, but I'm no SQL Server guru). The results I got indicated that using a computed column is indeed slower - much slower, than a simple group by or subquery. I ran a test on a SQL Server instance I have on my own PC - here is the methodology and results:

CREATE TABLE smb_header (keycol INTEGER NOT NULL
                        , name1 VARCHAR2(255)
                        , name2 VARCHAR2(255));

INSERT INTO smb_header
  VALUES (1
        , 'This is column 1'
        , 'This is column 2'
         );

INSERT INTO smb_header
   SELECT (SELECT MAX(keycol)
             FROM smb_header
          ) + keycol
        , name1
        , name2
     FROM smb_header;
REM (repeat 20 times to generate ~1 million rows)

ALTER TABLE smb_header ADD PRIMARY KEY (keycol);

CREATE TABLE smb_detail (keycol INTEGER
                        , commentno INTEGER
                        , commenttext VARCHAR2(255));

INSERT INTO smb_detail
   SELECT keycol
        , 1
        , 'A comment that describes this issue'
     FROM smb_header;

ALTER TABLE smb_detail ADD PRIMARY KEY (keycol, commentno);

ALTER TABLE smb_detail ADD FOREIGN KEY (keycol) 
                           REFERENCES smb_header (keycol);

INSERT INTO smb_detail
   SELECT keycol
        , (SELECT MAX(commentno)
             FROM smb_detail sd2
            WHERE sd2.keycol = sd1.keycol
          ) + commentno
        , 'A comment that follows comment number ' 
          + CAST(sd1.commentno AS VARCHAR(32))
     FROM smb_detail sd1
    WHERE MOD(keycol, 31) = 0;

REM repeat 5 times, to create some records that have 64 comments
REM where others have one.

At this point, there will be around 1 million rows in the header, and either 1 or 64 comments for each.

Now I create the function (the same as yours above, only with my column & table names), and the computed column:

alter table dbo.smb_header add CommentCountPersist as dbo.CountComments(keycol)

By the way, PERSISTED will not work for this column, as I suspected in my comments above - it is not possible or too difficult for SQL Server to keep track of which rows need updating if you refer to other tables in your function. Using the PERSISTED keyword produces the error:

Msg 4934, Level 16, State 3, Line 1
Computed column 'CommentCountPersist' in table 'smb_header' cannot be 
persisted because the column does user or system data access.

This makes sense to me - I don't see how SQL Server could determine what rows need updating when other rows change, for any function that could be implemented, without the process of updates being horribly inefficient.

Now, for the tests. I create a temp table #holder to insert the rows into - I want to make sure when my queries run, I process the entire result set, not just the first few rows that would appear in the Mgmt Studio grid control.

  SELECT h.keycol
       , h.name1
       , CommentCount
    INTO #holder
    FROM smb_header h
   WHERE h.keycol < 0

Here are the results of my queries. First, the computed column:

  INSERT
    INTO #holder
  SELECT h.keycol
       , h.name1
       , CommentCount
    FROM smb_header h
   WHERE h.keycol between 5000 and 10000

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 1, logical reads 10160, physical reads 0, 
                   read-ahead  reads 0, lob logical reads 0, 
                   lob physical reads 0, lob read-ahead reads 0.
Table 'smb_header'. Scan count 1, logical reads 44, physical reads 0, 
                    read-ahead reads 0, lob logical reads 0, 
                    lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 265 ms,  elapsed time = 458 ms.

(5001 row(s) affected)
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Now the GROUP BY version, the computed column:

  INSERT
    INTO #holder
  SELECT h.keycol
       , h.name1
       , COUNT(*)
    FROM smb_header h
       , smb_detail d 
   WHERE h.keycol between 5000 and 10000
     AND h.keycol = d.keycol 
GROUP BY h.keycol, h.name1

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 'smb_header'. Scan count 1, logical reads 44, physical reads 0, 
                    read-ahead reads 0, lob logical reads 0, 
                    lob physical reads 0, lob read-ahead reads 0.
Table 'smb_detail'. Scan count 1, logical reads 366, physical reads 0, 
                    read-ahead reads 0, lob logical reads 0, 
                    lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 13 ms.

(5001 row(s) affected)
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Writing the query with the subquery in the SELECT clause as Remus did above yields the same plan & performance as the GROUP BY (which would be expected).

As you can see, the computed column performs version significantly worse. This makes sense to me, as the optimizer is forced to call the function and do the count(*) for every row in the header, instead of using more sophisticated methods of resolving the two sets of data.

It is possible that I'm doing something wrong here. I'd be interested in marc_s contributing his findings.