每个用户使用SqlCacheDependency是个好主意吗？

I'm thinking of caching permissions for every user on our application server. Is it a good idea to use a SqlCacheDependency for every user?

我正在考虑为我们的应用程序服务器上的每个用户缓存权限。为每个用户使用SqlCacheDependency是一个好主意吗？

The query would look like this

查询看起来像这样

SELECT PermissionId, PermissionName From Permissions Where UserId = @UserId

That way I know if any of those records change then to purge my cache for that user.

这样我知道是否有任何记录改变，以清除该用户的缓存。

2 个解决方案

#1

If you read how Query Notifications work you'll see why createing many dependency requests with a single query template is good practice. For a web app, which is implied by the fact that you use SqlCacheDependency and not SqlDependency, what you plan to do should be OK. If you use Linq2Sql you can also try LinqToCache:

如果您阅读查询通知的工作原理，您将了解为什么使用单个查询模板创建许多依赖关系请求是一种很好的做法。对于Web应用程序，您使用SqlCacheDependency而非SqlDependency这一事实暗示，您打算做的事情应该没问题。如果您使用Linq2Sql，您还可以尝试LinqToCache：

var queryUsers = from u in repository.Users 
        where u.UserId = currentUserId 
        select u;
var user= queryUsers .AsCached("Users:" + currentUserId.ToString());

For a fat client app it would not be OK. Not because of the query per-se, but because SqlDependency in general is problematic with a large number of clients connected (it blocks a worker thread per app domain connected):

对于胖客户端应用程序，它不会没问题。不是因为查询本身，而是因为SqlDependency一般是有问题的，连接了大量客户端（它阻止每个应用程序域连接的工作线程）：

SqlDependency was designed to be used in ASP.NET or middle-tier services where there is a relatively small number of servers having dependencies active against the database. It was not designed for use in client applications, where hundreds or thousands of client computers would have SqlDependency objects set up for a single database server.

SqlDependency旨在用于ASP.NET或中间层服务，其中存在相对较少数量的服务器，这些服务器具有对数据库的活动依赖性。它不是设计用于客户端应用程序，其中数百或数千台客户端计算机将为单个数据库服务器设置SqlDependency对象。

Updated

更新

Here is the same test as @usr did in his post. Full c# code:

这是与@usr在他的帖子中所做的相同的测试。完整的c＃代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DependencyMassTest.Properties;
using System.Threading.Tasks;
using System.Threading;

namespace DependencyMassTest
{
    class Program
    {
        static volatile int goal = 50000;
        static volatile int running = 0;
        static volatile int notified = 0;
        static int workers = 50;
        static SqlConnectionStringBuilder scsb;
        static AutoResetEvent done = new AutoResetEvent(false);

        static void Main(string[] args)
        {
            scsb = new SqlConnectionStringBuilder(Settings.Default.ConnString);
            scsb.AsynchronousProcessing = true;
            scsb.Pooling = true;

            try
            {
                SqlDependency.Start(scsb.ConnectionString);

                using (var conn = new SqlConnection(scsb.ConnectionString))
                {
                    conn.Open();

                    using (SqlCommand cmd = new SqlCommand(@"
if object_id('SqlDependencyTest') is not null
    drop table SqlDependencyTest

create table SqlDependencyTest (
    ID int not null identity,
    SomeValue nvarchar(400),
    primary key(ID)
)
", conn))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }

                for (int i = 0; i < workers; ++i)
                {
                    Task.Factory.StartNew(
                        () =>
                        {
                            RunTask();
                        });
                }
                done.WaitOne();
                Console.WriteLine("All dependencies subscribed. Waiting...");
                Console.ReadKey();
            }
            catch (Exception e)
            {
                Console.Error.WriteLine(e);
            }
            finally
            {
                SqlDependency.Stop(scsb.ConnectionString);
            }
        }

        static void RunTask()
        {
            Random rand = new Random();
            SqlConnection conn = new SqlConnection(scsb.ConnectionString);
            conn.Open();

            SqlCommand cmd = new SqlCommand(
@"select SomeValue
    from dbo.SqlDependencyTest
    where ID = @id", conn);
            cmd.Parameters.AddWithValue("@id", rand.Next(50000));

            SqlDependency dep = new SqlDependency(cmd);
            dep.OnChange += new OnChangeEventHandler((ob, qnArgs) =>
            {
                Console.WriteLine("Notified {3}: Info:{0}, Source:{1}, Type:{2}", qnArgs.Info, qnArgs.Source, qnArgs.Type, Interlocked.Increment(ref notified));
            });

            cmd.BeginExecuteReader(
                (ar) =>
                {
                    try
                    {
                        int crt = Interlocked.Increment(ref running);
                        if (crt % 1000 == 0)
                        {
                            Console.WriteLine("{0} running...", crt);
                        }
                        using (SqlDataReader rdr = cmd.EndExecuteReader(ar))
                        {
                            while (rdr.Read())
                            {
                            }
                        }
                    }
                    catch (Exception e)
                    {
                        Console.Error.WriteLine(e.Message);
                    }
                    finally
                    {
                        conn.Close();
                        int left = Interlocked.Decrement(ref goal);

                        if (0 == left)
                        {
                            done.Set();
                        }
                        else if (left > 0)
                        {
                            RunTask();
                        }
                    }
                }, null);

        }

    }
}

After 50k subscriptions are set up (takes about 5 min), here are the stats io of a single insert:

在设置了50k订阅（大约需要5分钟）之后，以下是单个插入的统计信息：

set statistics time on
insert into Test..SqlDependencyTest (SomeValue) values ('Foo');

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 16 ms.

Inserting 1000 rows takes about 7 seconds, which includes firing several hundred notifications. CPU utilization is about 11%. All this is on my T420s ThinkPad.

插入1000行大约需要7秒，其中包括发送数百个通知。 CPU利用率约为11％。这一切都在我的T420s ThinkPad上。

set nocount on;
go

begin transaction
go
insert into Test..SqlDependencyTest (SomeValue) values ('Foo');
go 1000

commit
go

#2

the documentation says:

文件说：

SqlDependency was designed to be used in ASP.NET or middle-tier services where there is a relatively small number of servers having dependencies active against the database. It was not designed for use in client applications, where hundreds or thousands of client computers would have SqlDependency objects set up for a single database server.

SqlDependency旨在用于ASP.NET或中间层服务，其中存在相对较少数量的服务器，这些服务器具有对数据库的活动依赖性。它不是设计用于客户端应用程序，其中数百或数千台客户端计算机将为单个数据库服务器设置SqlDependency对象。

It tells us not to open thousands of cache dependencies. That is likely to cause resource problems on the SQL Server.

它告诉我们不要打开数千个缓存依赖项。这可能会导致SQL Server上的资源问题。

There are a few alternatives:

有几种选择：

1. Have a dependency per table
2. 每个表都有一个依赖项
3. Have 100 dependencies per table, one for every percent of rows. This should be an acceptable number for SQL Server yet you only need to invalidate 1% of the cache.
4. 每个表有100个依赖项，每个行的百分比一个。这应该是SQL Server可接受的数字，但您只需要使1％的缓存无效。
5. Have a trigger output the ID of all changes rows into a logging table. Create a dependency on that table and read the IDs. This will tell you exactly which rows have changed.
6. 让触发器将所有更改行的ID输出到日志记录表中。在该表上创建依赖项并读取ID。这将告诉您确切哪些行已更改。

In order to find out if SqlDependency is suitable for mass usage I did a benchmark:

为了找出SqlDependency是否适合大规模使用，我做了一个基准测试：

        static void SqlDependencyMassTest()
        {
            var connectionString = "Data Source=(local); Initial Catalog=Test; Integrated Security=true;";
            using (var dependencyConnection = new SqlConnection(connectionString))
            {
                dependencyConnection.EnsureIsOpen();

                dependencyConnection.ExecuteNonQuery(@"
if object_id('SqlDependencyTest') is not null
    drop table SqlDependencyTest

create table SqlDependencyTest (
    ID int not null identity,
    SomeValue nvarchar(400),
    primary key(ID)
)

--ALTER DATABASE Test SET ENABLE_BROKER with rollback immediate
");

                SqlDependency.Start(connectionString);

                for (int i = 0; i < 1000 * 1000; i++)
                {
                    using (var sqlCommand = new SqlCommand("select ID from dbo.SqlDependencyTest where ID = @id", dependencyConnection))
                    {
                        sqlCommand.AddCommandParameters(new { id = StaticRandom.ThreadLocal.GetInt32() });
                        CreateSqlDependency(sqlCommand, args =>
                            {
                            });
                    }

                    if (i % 1000 == 0)
                        Console.WriteLine(i);
                }
            }
        }

You can see the amount of dependencies created scroll through the console. It gets slow very quickly. I did not do a formal measurement because it was not necessary to prove the point.

您可以在控制台中看到创建的依赖项数量。它很快变慢。我没有做正式的测量，因为没有必要证明这一点。

Also, the execution plan for a simple insert into the table shows 99% of the cost being associated with maintaining the 50k dependencies.

此外，简单插入表中的执行计划显示99％的成本与维护50k依赖关系相关联。

Conclusion: Does not work at all for production use. After 30min I have 55k dependencies created. Machine at 100% CPU all the time.

结论：根本不适用于生产用途。 30分钟后，我创建了55k依赖项。机器始终处于100％CPU状态。