增加的数据库字段 [英] Incremented DB Field

问题描述

假设我在网站上有一篇文章，我想跟踪这篇文章的浏览量.在文章表中，有 PK ID - int、名称 - nvarchar(50) 和 ViewCount - int.每次查看页面时，我都会增加 ViewCount 字段.我担心更新字段时发生冲突.我可以通过如下事务在 sproc 中运行它:

Let's say that I have an article on a website, and I want to track the number of views to the article. In the Articles table, there's the PK ID - int, Name - nvarchar(50), and ViewCount - int. Every time the the page is viewed, I increment the ViewCount field. I'm worried about collisions when updating the field. I can run it in a sproc with a transaction like:

CREATE PROCEDURE IncrementView
(
    @ArticleID int
)
as

BEGIN TRANSACTION

UPDATE Article set ViewCount = ViewCount + 1 where ID = @ArticleID

IF @@ERROR <> 0
BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Error Incrementing', 16, 1)
    RETURN
END

COMMIT

我担心我最终会在此模型中不计算 PageViews.另一种可能的解决方案是日志类型的模型，我实际记录文章的浏览次数，并使用函数和视图的组合来获取有关文章浏览次数的数据.

My fear is that I'm going to end up with PageViews not being counted in this model. The other possible solution is a log type of model where I actually log views to the articles and use a combination of a function and view to grab data about number of views to an article.

推荐答案

可能更好的模型是在应用程序的某个地方每小时缓存一次查看次数，然后以批处理方式更新它们.

Probably a better model is to cache the number of views hourly in the app somewhere, and then update them in a batch-style process.

--

为了详细说明，一个简单的模型可能是:

To to elaborate more, a simple model for you may be:

1. 每个页面加载，对于给定页面，增加一个静态哈希图.同样在每次加载时，检查自上次更新"以来是否已经过去了足够的时间，如果是，则执行更新.

1. Each page load, for the given page, increment a static hashmap. Also on each load, check if enough time has elapsed since 'Last Update', and if so, perform an update.

技巧一点，将基值放在 asp.net 缓存中 (http://msdn.microsoft.com/en-us/library/aa478965.aspx)，当超时时，[实现链接中描述的缓存删除处理程序] 进行更新.将超时设置为一小时.

Be tricky, and put the base value in the asp.net cache (http://msdn.microsoft.com/en-us/library/aa478965.aspx) and, when it times out, [implement the cache removal handler as described in the link] do the update. Set the timeout for an hour.

在这两种模型中，您都将拥有页面到计数的静态地图；您将在每个视图中更新它，并且您还将使用它 - 以及缓存的数据库数量 - 来获取当前的实时"计数.

In both models, you'll have the static map of pages to counts; you'll update this each view, and you'll also use this - and the cached db amount - to get the current 'live' count.

这篇关于增加的数据库字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！