<i id='leRJj'><tr id='leRJj'><dt id='leRJj'><q id='leRJj'><span id='leRJj'><b id='leRJj'><form id='leRJj'><ins id='leRJj'></ins><ul id='leRJj'></ul><sub id='leRJj'></sub></form><legend id='leRJj'></legend><bdo id='leRJj'><pre id='leRJj'><center id='leRJj'></center></pre></bdo></b><th id='leRJj'></th></span></q></dt></tr></i><div id='leRJj'><tfoot id='leRJj'></tfoot><dl id='leRJj'><fieldset id='leRJj'></fieldset></dl></div>
  • <tfoot id='leRJj'></tfoot>
      • <bdo id='leRJj'></bdo><ul id='leRJj'></ul>

      1. <small id='leRJj'></small><noframes id='leRJj'>

        <legend id='leRJj'><style id='leRJj'><dir id='leRJj'><q id='leRJj'></q></dir></style></legend>

        SQL Server 计数很慢

        SQL Server Count is slow(SQL Server 计数很慢)

        <i id='fPwTX'><tr id='fPwTX'><dt id='fPwTX'><q id='fPwTX'><span id='fPwTX'><b id='fPwTX'><form id='fPwTX'><ins id='fPwTX'></ins><ul id='fPwTX'></ul><sub id='fPwTX'></sub></form><legend id='fPwTX'></legend><bdo id='fPwTX'><pre id='fPwTX'><center id='fPwTX'></center></pre></bdo></b><th id='fPwTX'></th></span></q></dt></tr></i><div id='fPwTX'><tfoot id='fPwTX'></tfoot><dl id='fPwTX'><fieldset id='fPwTX'></fieldset></dl></div>

        <small id='fPwTX'></small><noframes id='fPwTX'>

        <legend id='fPwTX'><style id='fPwTX'><dir id='fPwTX'><q id='fPwTX'></q></dir></style></legend>
              <bdo id='fPwTX'></bdo><ul id='fPwTX'></ul>

                  <tfoot id='fPwTX'></tfoot>

                    <tbody id='fPwTX'></tbody>
                1. 本文介绍了SQL Server 计数很慢的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  计算数据量大的表可能会很慢,有时需要几分钟;它也可能在繁忙的服务器上产生死锁.我想显示真实值,NOLOCK 不是一个选项.

                  Counting tables with large amount of data may be very slow, sometimes it takes minutes; it also may generate deadlock on a busy server. I want to display real values, NOLOCK is not an option.

                  我使用的服务器是 SQL Server 2005 或 2008 Standard 或 Enterprise - 如果重要的话.我可以想象 SQL Server 维护每个表的计数,如果没有 WHERE 子句,我可以很快得到那个数字,对吗?

                  The servers I use is SQL Server 2005 or 2008 Standard or Enterprise - if it matters. I can imagine that SQL Server maintains the counts for every table and if there is no WHERE clause I could get that number pretty quickly, right?

                  例如:

                  SELECT COUNT(*) FROM myTable
                  

                  应该立即返回正确的值.我需要依靠统计数据来更新吗?

                  should immediately return with the correct value. Do I need to rely on statistics to be updated?

                  推荐答案

                  非常接近(忽略任何进行中的事务)将是:

                  Very close approximate (ignoring any in-flight transactions) would be:

                  SELECT SUM(p.rows) FROM sys.partitions AS p
                    INNER JOIN sys.tables AS t
                    ON p.[object_id] = t.[object_id]
                    INNER JOIN sys.schemas AS s
                    ON s.[schema_id] = t.[schema_id]
                    WHERE t.name = N'myTable'
                    AND s.name = N'dbo'
                    AND p.index_id IN (0,1);
                  

                  这将比 COUNT(*) 返回快得多,并且如果您的表变化得足够快,那么它的准确度并没有降低 - 如果您的表在您开始 COUNT 之间发生了变化(并且锁被占用)以及何时返回(当锁被释放并且所有等待的写事务现在都被允许写入表时),它是否更有价值?我不这么认为.

                  This will return much, much quicker than COUNT(*), and if your table is changing quickly enough, it's not really any less accurate - if your table has changed between when you started your COUNT (and locks were taken) and when it was returned (when locks were released and all the waiting write transactions were now allowed to write to the table), is it that much more valuable? I don't think so.

                  如果您有要计算的表的某个子集(例如,WHERE some_column IS NULL),您可以在该列上创建一个过滤索引,并以一种方式构建 where 子句或其他,取决于它是例外还是规则(因此在较小的集合上创建过滤索引).所以这两个索引之一:

                  If you have some subset of the table you want to count (say, WHERE some_column IS NULL), you could create a filtered index on that column, and structure the where clause one way or the other, depending on whether it was the exception or the rule (so create the filtered index on the smaller set). So one of these two indexes:

                  CREATE INDEX IAmTheException ON dbo.table(some_column)
                    WHERE some_column IS NULL;
                  
                  CREATE INDEX IAmTheRule ON dbo.table(some_column)
                    WHERE some_column IS NOT NULL;
                  

                  然后您可以使用类似的方式获得计数:

                  Then you could get the count in a similar way using:

                  SELECT SUM(p.rows) FROM sys.partitions AS p
                    INNER JOIN sys.tables AS t
                    ON p.[object_id] = t.[object_id]
                    INNER JOIN sys.schemas AS s
                    ON s.[schema_id] = t.[schema_id]
                    INNER JOIN sys.indexes AS i
                    ON p.index_id = i.index_id
                    WHERE t.name = N'myTable'
                    AND s.name = N'dbo'
                    AND i.name = N'IAmTheException' -- or N'IAmTheRule'
                    AND p.index_id IN (0,1);
                  

                  如果你想知道相反的情况,你只需从上面的第一个查询中减去.

                  And if you want to know the opposite, you just subtract from the first query above.

                  这篇关于SQL Server 计数很慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  Building a comma separated list?(建立一个逗号分隔的列表?)
                  Errors in SQL Server while importing CSV file despite varchar(MAX) being used for each column(尽管每列都使用了 varchar(MAX),但在导入 CSV 文件时 SQL Server 中出现错误)
                  How can I import an Excel file into SQL Server?(如何将 Excel 文件导入 SQL Server?)
                  Export table to file with column headers (column names) using the bcp utility and SQL Server 2008(使用 bcp 实用程序和 SQL Server 2008 将表导出到带有列标题(列名称)的文件)
                  Concat field value to string in SQL Server(将字段值连接到 SQL Server 中的字符串)
                  SQL Server Bulk insert of CSV file with inconsistent quotes(SQL Server 批量插入带有不一致引号的 CSV 文件)

                    <bdo id='vLppJ'></bdo><ul id='vLppJ'></ul>

                        <tbody id='vLppJ'></tbody>
                      <i id='vLppJ'><tr id='vLppJ'><dt id='vLppJ'><q id='vLppJ'><span id='vLppJ'><b id='vLppJ'><form id='vLppJ'><ins id='vLppJ'></ins><ul id='vLppJ'></ul><sub id='vLppJ'></sub></form><legend id='vLppJ'></legend><bdo id='vLppJ'><pre id='vLppJ'><center id='vLppJ'></center></pre></bdo></b><th id='vLppJ'></th></span></q></dt></tr></i><div id='vLppJ'><tfoot id='vLppJ'></tfoot><dl id='vLppJ'><fieldset id='vLppJ'></fieldset></dl></div>

                      <small id='vLppJ'></small><noframes id='vLppJ'>

                      1. <tfoot id='vLppJ'></tfoot>
                          1. <legend id='vLppJ'><style id='vLppJ'><dir id='vLppJ'><q id='vLppJ'></q></dir></style></legend>