Wrox Blog - Stored Procedure Update

norman001 · February 11th, 2009, 03:43 PM

Hi,

I am trying to add how many comments already in the comments table for each blog post so I could add this number just beside [Show Comments]..[ number of comments ..ex. 3 comments]. I know I have to update sprocBlogEntrySelectList, sprocBlogEntrySelectListByCategory & sprocBlogEntrySelectListByDate stored procedures. Following is the 1st procedure. I spent long time without success trying various SQL statements in SQL query analyzer trying to join BlogEntry and comments tables so I could get blogs along with a count(id) from comments table for each blog. Any help would be appreciated.

=========================================
CREATE PROCEDURE sprocBlogEntrySelectList
AS
SELECT TOP 15
BlogEntry.Id,
BlogEntry.Title,
BlogEntry.Body,
BlogEntry.DatePublished,
BlogEntry.PostedBy

FROM
BlogEntry
ORDER BY
BlogEntry.DatePublished DESC
GO
==================================

Imar · February 11th, 2009, 04:40 PM

Hi there,

Why count them in the stored procedure? The sproc returns them all, so you can simply check the Count property of the Rows collection of the DataSet...

Alternatively, look into OUTPUT parameters. You can create a parameter of type OUTPUT, fill it with a SELECT COUNT(*) and assign it to the parameter. Then in the code that executes this procedure, set up a SqlParamater with an Output type and after you execute the sproc get the Value property of the parameter.

Hope this helps,

Imar
Sorry, I misread your post. I thought you wanted to count the actual blogs.

Old Pedant's solution should roughly do the trick....

Old Pedant · February 11th, 2009, 04:50 PM

Well, I for one don't have that book, so it would help you get a wider audience if you posted the schema for the tables.

For now, I'm assuming that you have a BlogComments table that looks like this:

Code:

Table: BlogComments
    id INTEGER REFERENCES BlogEntry(id), -- this is your foreign key 
    comment NTEXT

Other fields being pretty much irrelevant.

So then:

CREATE PROCEDURE sprocBlogEntrySelectList
AS
SELECT TOP 15 BE.Id, BE.Title, BE.Body, BE.DatePublished, BE.PostedBy, COUNT(BC.id) AS commentsCount
FROM BlogEntry AS BE LEFT JOIN BlogComments AS BC ON BE.id = BC.id
GROUP BY BE.Id, BE.Title, BE.Body, BE.DatePublished, BE.PostedBy
ORDER BY BE.DatePublished DESC

You must use a LEFT JOIN because there might be NO comments yet on a given blog entry and so an INNER JOIN would then give you nothing at all for that BlogEntry record. You must use COUNT( ) of some field in the BlogComments table so that indeed you will get a zero count when there are no comments.

Old Pedant · February 11th, 2009, 04:54 PM

Quote:

Originally Posted by Imar

Why count them in the stored procedure? The sproc returns them all

Huh???

The SP isn't returning *ANY* of the COMMENTS. It is only returning the actual blog ENTRIES.

The poster wrote "I am trying to add how many comments already in the comments table for each blog post ..."

But you wrote the book, so I guess you know what tables are involved.

Imar · February 11th, 2009, 04:57 PM

No, you're right. I misread the question. I changed my message just before I got yours... ;-)

Imar

norman001 · February 11th, 2009, 05:32 PM

Hi Imar,

Wow! Beautiful! I love that SQL statement.. works perfect..

But now I am wondering I have to update 3 stored procedures for this.
The LEFT JOIN statement may eat the database performance..is it not?
What about the following solution...

1. Add another column to BlogEntry table: CommentCount, default 0
2 Every time a user enters a comment to comments table, increment the comment count column in BlogEntry table
3. Then in the existing blogentry data set, CommentCount will be automatically available..

Thanks!

Old Pedant · February 11th, 2009, 06:23 PM

Are you going to be using one of these three SPs on a site that is so busy that you'll be executing them thousands of times an hour?

If so, then yes, you could consider using a DB trigger to bump the count in the blog table.

But honest, a simple LEFT JOIN like this will be virtually unnoticeable in a typical blog site, where you get maybe 10s of hits per hour, not 1000s.

I'll bet you have dozens and dozens of things in that blog site that you could improve that would save you a lot more CPU time than you would save by avoiding this tiny little LEFT JOIN. The very nature of all too many ASP.NET web controls--that has them posting back to the server for dubious reasons--probably eats up more than this query (even unindexed...see below) ever could.

NOTE: This assumes that the BlogComments.id field is indexed. Getting the count of the number of occurrences of a given value in the index of a table is enormously efficient. If it's not indexed, and if the query then has to scan all records, it won't be so pretty.

Maybe the best advice I could offer from my personal experience: Don't pre-optimize. Write your code so it works. THEN do performance measurement and find the bottlenecks. Now, don't go out of your way to avoid optimizing, of course. But "don't sweat the small stuff" until you discover it isn't really that small.

norman001 · February 11th, 2009, 08:06 PM

Hi, Imar:

Yes, I agree with your advice but it could depend on the specific scenario.
The site I am working on is a video streaming site where users can upload and watch videos. Right now there are perf issues already. On top of that I am attaching this blog functionality.Therefore I am trying to be vigilant on any perf issues regardless of how small it is. I also think paying attention to perf ( and security) at the conceptual stage will avoid headaches later..I am posting here what I did to get the count of comments..

Step1. Added another column to the BlogEntry table: CommentCount;int;default 0
Step2. Updated all 3 select blog procedures to select CommentCount
Step3. Created following 2 procedures and added into the user control
===========================
Public Function Get_CommentCount(ByVal blogEntryId As Integer) As String
Return SqlHelper.ExecuteScalar(_constring, CommandType.Text, "Select CommentCount from BlogEntry where id= @blogEntryId", New SqlParameter("@blogEntryId", blogEntryId)).ToString()
End Function

Public Function Increment_CommentCount(ByVal blogEntryId As Integer) As Integer
Dim current_comments = Me.Get_CommentCount(blogEntryId)
current_comments = current_comments + 1
SqlHelper.ExecuteNonQuery(_constring, CommandType.Text, "Update BlogEntry set commentcount= @comments where id= @blogEntryId", New SqlParameter("@blogEntryId", blogEntryId), New SqlParameter("@comments", current_comments))
Return current_comments
End Function
========================
Step4. Added folowing line to btnAddComment_Click event procedure
GetBlogData.Increment_CommentCount(Convert.ToInt32 (ViewState("BlogEntryCommentId")))

Step5. Added a simple label to BlogEntries.aspx and put label.text as '<%#Eval("commentcount")%>'

Old Pedant · February 11th, 2009, 08:20 PM

Ummm...I'm *NOT* Imar. He might be insulted to be confused with my ugly old face. http://www.juncojunction.com/images/th_bill.jpg

Anyway, if you are going to go with the added field in BlogEntry, then you probably really should use a TRIGGER in that database, such that adding a BlogComment will automatically increment the field in BlogEntry.

Alternative: If you are using a Stored Procedure to add new comments (and if you aren't, WHY aren't you??? if you are that worried about performance, you should) just do the incrementing in that SP. Many SQL purists would urge you to do that, instead of using a trigger.

I *REALLY* think you are making things worse, doing it the way you are.

It is SURELY a heavier load on the server to make two separate SQL queries, one to get the blogs and one to get the count of comments, than to just get the count as part of getting the blogs. General rule: Use as few trips between client (ASP.NET) and server (SQL Server) as possible. (Same rule SHOULD be applied to browser as client and ASP.NET as server, but if you are still using mostly standard ASP.NET controls, chances are you are throwing away a lot of performance on this aspect.)

Old Pedant · February 11th, 2009, 08:32 PM

formatting messed up...deleted and tried again