Return Data and Update Row without Multiple Lookups?

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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.