参考广告印象进行大规模 SQL 插入的最佳实践是什么？
我正在开发一个需要能够跟踪广告展示次数的网站。我的环境是带有 IIS 的 ASP.Net，使用 SQL Server DMBS 和可能的 Memcached，因此不会有太多的数据库访问。我还必须考虑可扩展性，因为我希望这个应用程序成为全球现象（祈祷并努力工作）！情况如下：
- 我的客户将为 Y 次广告展示支付 X 金额。
- 这些广告展示（目前仅包含文字广告）将显示在特定页面上。
- 该页面由 Memcached 提供，减少了到数据库的次数
- 当广告展示时，需要在数据库的展示次数中添加一个“+1”勾号
所以困境是这样的：我需要能够添加每个广告印象计数器的“+1”刻度线，但我无法在每次加载广告时运行该 SQL 语句。我需要以某种方式将“+1”印象数存储在会话中（或其他地方），然后每 X 分钟、几小时或一天运行一批。
请记住，可扩展性是这里的一个重要因素。如果你们有任何建议，我们将不胜感激。
I am working on a site where I will need to be able to track ad impressions. My environment is ASP.Net with IIS using a SQL Server DMBS and potentially Memcached so that there are not as many trips to the database. I must also think about scalability as I am hoping that this application becoming a global phenom (keeping my fingers crossed and working my ass off)! So here is the situation:
- My Customers will pay X amount for Y Ad impressions
- These ad impressions (right now, only text ads) will then be shown on a specific page.
- The page is served from Memcached, lessening the trips to the DB
- When the ad is shown, there needs to be a "+1" tick added to the impression count for the database
So the dilemma is this: I need to be able to add that "+1" tick mark to each ad impression counter BUT I cannot run that SQL statement every time that ad is loaded. I need to somehow store that "+1" impression count in the session (or elsewhere) and then run a batch every X minutes, hours, or day.
Please keep in mind that scalability is a huge factor here. Any advice that you all have would be greatly appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我见过一些项目通过在每个 Web 场服务器上部署 SQL Server Express 版本并依靠 Service Broker 将跟踪审核传送到中央服务器来处理此问题。由于场中的每台服务器都会更新本地 SQL 实例，因此它们可以扩展到天空。 Service Broker 确保近乎实时的可靠交付。我见过网络场在很长一段时间内 24x7 平均每秒处理 300-400 个请求，并且 Service Broker 的排队特性能够在数小时内吸收每秒 5000-7500 次点击的峰值并在合理的时间内恢复，不会造成审计损失，并且积压工作仍处于控制之中。
如果您确实希望扩大规模并成为下一个 MySpace，那么您应该学习他们是如何做的它，基于队列的异步、解耦处理就是游戏的名称。
I've seen projects deal with this by deploying SQL Server Express edition on each web farm server and relying on Service Broker to deliver the track audit to the central server. Because each server in the farm updates a local SQL instance, they can scale as high as the sky. Service Broker assures the near-real-time reliable delivery. I've seen web farms handle 300-400 requests per second on average, 24x7 over long time, and with the queued nature of Service Broker being able to absorb spikes of 5000-7500 hits per second for hours at end, and recover in reasonable time, without audit loss and with the backlog staying under control.
If you really expect to scale and become the next MySpace, then you should learn from how they do it, and queue based asynchronous, decoupled processing is the name of the game.
您可以做的是将计数增加到不太永久的存储中，并定期（每 1 分钟、5 分钟、每小时......）同步到更可靠的数据库中。
如果您的临时商店出现故障，您会在短时间内失去点击次数；这样做的最终结果是，在极少数发生故障的情况下，付费广告的人会获得一些免费的印象。
您可以向 memcache 发送“以原子方式将该值增加 +1”命令。您还可以执行一些操作，例如每次显示广告时向平面文件写入一行，并让您的“每 5 分钟”同步作业轮换日志，然后对文件中刚刚轮换的所有行进行计数。
Something you can do is increment the counts into a less permanent store, and periodically (every 1 minute, 5 minutes, hour..) sync into your more reliable database.
If your temporary store goes down, you lose the hit counts for some short period of time; the net effect of this is that in a rare event of a malfunction, the people paying for ads get some free impressions.
You can send a "atomically increment this value by +1" command to memcache. You could also do something like write a line to a flat file every time an ad is displayed, and have your "every 5 minute" sync job rotate the log, then go through counting all the lines in the file that was just rotated out.