7
\$\begingroup\$

I have a stored procedure that looks up an article based on the article's title. But I also need to increment a column in the same table that counts the number of times the article has been viewed.

Trying to be as efficient as possible, I wanted a way to do this without performing multiple lookups. Someone pointed out the newer OUTPUT clause, which I've used below.

I'm just wondering if I'm using it in the most efficient way, if I really made it faster, and if there are any other optimizations that could be employed.

DECLARE @Slug VARCHAR(250) -- Stored procedure argument

-- declare @UpdatedArticle table variable 
DECLARE @UpdatedArticle TABLE
(
 ArtID INT,
 ArtUserID UNIQUEIDENTIFIER,
 ArtSubcategoryID INT,
 ArtTitle VARCHAR(250),
 ArtHtml VARCHAR(MAX),
 ArtDescription VARCHAR(350),
 ArtKeywords VARCHAR(250),
 ArtLicenseID VARCHAR(10),
 ArtViews BIGINT,
 ArtCreated DATETIME2(7),
 ArtUpdated DATETIME2(7)
);

UPDATE Article
 SET ArtViews = ArtViews + 1
OUTPUT
 INSERTED.ArtID,
 INSERTED.ArtUserID,
 inserted.ArtSubcategoryID,
 INSERTED.ArtTitle,
 INSERTED.ArtHtml,
 INSERTED.ArtDescription,
 INSERTED.ArtKeywords,
 INSERTED.ArtLicenseID,
 INSERTED.ArtViews,
 INSERTED.ArtUpdated,
 INSERTED.ArtCreated
INTO @UpdatedArticle
WHERE ArtSlugHash = CHECKSUM(@Slug) AND ArtSlug = @Slug AND ArtApproved = 1

SELECT a.ArtID, a.ArtUserID, a.ArtTitle, a.ArtHtml, a.ArtDescription, a.ArtKeywords, a.ArtLicenseID,
 l.licTitle, a.ArtViews, a.ArtCreated, a.ArtUpdated, s.SubID, s.SubTitle, c.CatID, c.CatTitle,
 sec.SecID, sec.SecTitle, u.UsrDisplayName AS UserName
 FROM @UpdatedArticle a
 INNER JOIN Subcategory s ON a.ArtSubcategoryID = s.SubID
 INNER JOIN Category c ON s.SubCatID = c.CatID
 INNER JOIN [Section] sec ON c.CatSectionID = sec.SecID
 INNER JOIN [User] u ON a.ArtUserID = u.UsrID
 INNER JOIN License l ON a.ArtLicenseID = l.LicID

This is used in an ASP.NET application using SQL Server 2008.

\$\endgroup\$
1
\$\begingroup\$

You could do it all in one step, like this.

CREATE PROCEDURE dbo.IncrementArtViews (@Slug varchar(250)) AS

UPDATE a
SET ArtViews = ArtViews + 1
OUTPUT a.ArtID, a.ArtUserID, a.ArtTitle, a.ArtHtml, a.ArtDescription,
 a.ArtKeywords, a.ArtLicenseID, l.licTitle, a.ArtViews, a.ArtCreated, 
 a.ArtUpdated, s.SubID, s.SubTitle, c.CatID, c.CatTitle, sec.SecID, 
 sec.SecTitle, u.UsrDisplayName AS UserName
FROM dbo.Article a
 INNER JOIN dbo.Subcategory s ON a.ArtSubcategoryID = s.SubID
 INNER JOIN dbo.Category c ON s.SubCatID = c.CatID
 INNER JOIN dbo.[Section] sec ON c.CatSectionID = sec.SecID
 INNER JOIN dbo.[User] u ON a.ArtUserID = u.UsrID
 INNER JOIN dbo.License l ON a.ArtLicenseID = l.LicID
WHERE a.ArtSlugHash = CHECKSUM(@Slug)
 AND a.ArtSlug = @Slug
 AND a.ArtApproved = 1
\$\endgroup\$
  • \$\begingroup\$ I like it. I don't understand it, and will need to study it, but I will use this approach if it works like that. \$\endgroup\$ – Jonathan Wood Feb 26 '11 at 23:37
  • \$\begingroup\$ You have presented an alternative solution, but haven't reviewed the code. Please explain your reasoning (how your solution works and why it is better than the original) so that the author and other readers can learn from your thought process. \$\endgroup\$ – Mast Dec 17 '16 at 18:14
  • \$\begingroup\$ @Mast This answer is from '11. I'd just leave it. \$\endgroup\$ – Simon Forsberg Dec 17 '16 at 18:15
  • \$\begingroup\$ @SimonForsberg Which is why I upvoted it. However, I don't want the answerer to get the wrong idea. New answers should follow new rules. \$\endgroup\$ – Mast Dec 17 '16 at 18:18
1
\$\begingroup\$

Create a view. Update a row in the view, and output the columns.

CREATE VIEW dbo.ArticleView AS
    SELECT a.ArtID, a.ArtUserID, a.ArtTitle, a.ArtHtml, a.ArtDescription,
        a.ArtKeywords, a.ArtLicenseID, l.licTitle, a.ArtViews, a.ArtCreated, 
        a.ArtUpdated, s.SubID, s.SubTitle, c.CatID, c.CatTitle, sec.SecID, 
        sec.SecTitle, u.UsrDisplayName AS [UserName]
    FROM dbo.Article a
        INNER JOIN dbo.Subcategory s ON a.ArtSubcategoryID = s.SubID
        INNER JOIN dbo.Category c ON s.SubCatID = c.CatID
        INNER JOIN dbo.[Section] sec ON c.CatSectionID = sec.SecID
        INNER JOIN dbo.[User] u ON a.ArtUserID = u.UsrID
        INNER JOIN dbo.License l ON a.ArtLicenseID = l.LicID
GO

CREATE PROCEDURE dbo.IncrementArtViews (@Slug varchar(250)) AS
    UPDATE dbo.ArticleView
    SET ArtViews = ArtViews + 1
    OUTPUT ArtID, ArtUserID, ArtTitle, ArtHtml, ArtDescription,
        ArtKeywords, ArtLicenseID, licTitle, ArtViews, ArtCreated, 
        ArtUpdated, SubID, SubTitle, CatID, CatTitle, SecID, 
        SecTitle, [UserName]
    WHERE ArtSlugHash = CHECKSUM(@Slug)
        AND ArtSlug = @Slug
        AND ArtApproved = 1
GO
\$\endgroup\$
0
\$\begingroup\$

I would optimize by not storing meta data along with the data. How does the view count really affect the article itself? Not at all.

It's a different thing... So store it in a different place.

\$\endgroup\$
  • 1
    \$\begingroup\$ I'm not sure I follow. How would it be more efficient if I moved the view count to another table? The existing query needs five joins. Why would it be worth needing another join just to keep that data in a separate table? I'd be curious to how that makes it better. (Aside from the fact that I do consider view counts to be an attribute of the article.) \$\endgroup\$ – Jonathan Wood Jan 30 '11 at 0:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.