Медленный запрос count(*)

Я решил немного поиграться с относительно большими объемами данных. Создал просту
базу данных из двух таблиц и написал для нее программку, которая заполнила её данными. 
Первая вот такая: 

CREATE TABLE Cities
(
    Id int IDENTITY(1,1) NOT NULL PRIMARY KEY, 
    Name nvarchar(50) NULL
)


Она содержит 100 строк.
Вторая, главная: 

CREATE TABLE People
(
    Id int IDENTITY(1,1) NOT NULL,
    CityId int NOT NULL,
    Name nvarchar(50) NULL,
    LastName nvarchar(50) NULL,
    CONSTRAINT People_Id_Idx PRIMARY KEY CLUSTERED (Id ASC)
)

ALTER TABLE People  WITH CHECK ADD  CONSTRAINT fk_cities 
FOREIGN KEY(CityId) REFERENCES Cities (Id)


Она содержит 100 миллионов строк. Люди распределены по городам равномерно. 
Так вот вопрос. Когда я пишу очень простой запрос:

SELECT COUNT(*) -- COUNT(Id) даёт такой же результат
FROM People 


то он отрабатывает очень долго (около 100-130 секунд). Я конечно понимаю, что 10
миллионов записей - это довольно много, но всё-таки полторы-две минуты на такой запро
это очень много. Тем более, что необходимость узнать количество записей в таблице возникает довольно часто. Тем более, что сто миллионов записей - это совсем не предел. Ведь какие-нибудь банки ворочают таблицами с куда большим числом записей и столбцов. Как вообще они борются за производительность? 

Дополнительный данные: 


Версия sql-сервер: 
Microsoft SQL Server 2014 Enterprise Edition (64-bit)
План запроса: 


    

Ответы

Ответ 1

Ок, сначала стоит выяснить почему именно тормозит ваш запрос.

Я вбил в таблицу 100 млн записей. Запустил count с измерением времени (с отключенным Show Actual Execution Plan - эта опция влияет на результаты измерений!):

set statistics io on
set statistics time on
select count(*) from People

Table 'People'. Scan count 9, logical reads 942693, physical reads 0, 
   read-ahead reads 18, lob logical reads 0, lob physical reads 0, 
   lob read-ahead reads 0.

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


Запрос выполнился чуть больше чем за секунду. План выполнения, если его просмотреть, совпадает с вашим:



Что плохо в таком плане выполнения, и почему запрос отработал быстро на моей машине, но медленно - на вашей?

В вашей таблице есть кластерный индекс (это хорошо). Что такое кластерный индекс
Это большое дерево поиска, состоящие из 8-килобайтных страниц (BTree, прошитое по уровням), в листьях которого хранятся данные записей.

Как SQL сервер считает count(*) без условия? Он считает количество записей в Primar
Key. Делает он это, проходя по нижнему уровню (листьям) дерева и подсчитывая количество записей в каждой странице. 

Каждое обращение к странице, которая уже находится в памяти - это logical read. Ка
видите, SQL Server-у пришлось перебрать 942693*8 = примерно 7.5Gb данных ради подсчет
количества строк. Это не сверхстрашно, но только в случае, когда данные таблицы уже оказались в памяти. В моем случае они лежали в памяти только потому, что SQL Server только что вставил данные в эту таблицу, и решил пока не сбрасывать кэш.

Что происходит, если страницы, из которой нужно прочитать, в памяти не оказывается
Например, когда у вас просто мало оперативной памяти, и база не влазит в ее целиком. Тогда logical read превращается в physical read - реальное чтение с диска. И вы получаете:

Table 'People'. Scan count 9, logical reads 942693, physical reads 942693...

SQL Server Execution Times:
   CPU time = ??? ms,  elapsed time = 100000 ms (условно)

Потому что SQL Server пытается читать с диска в 4/8/?? потоков, а даже линейное чтени
7.5Gb с диска по 100Mb/s дает вам задержку в 76.8 секунд (судя по скорости чтения - у вас HDD, а не SSD). Потери на нелинейность и одновременность (HDD это очень не любят) - и вот ваши 130 секунд в результате.

Что можно сделать:


Поставить больше памяти (ее никогда не бывает много!)
Поставить SSD (значительно смягчит проблему)
Заставить SQL Server читать меньше данных при подсчете count. Например, создать дополнительный некластерный индекс, в котором будут только ID:

CREATE NONCLUSTERED INDEX [People_Id_Only] ON [dbo].[People]
(
    [Id] ASC
)


... и SQL Server начнет использовать его для подсчета количества строк:

Table 'People'. Scan count 9, logical reads 125415, 


Всего прочитанных 979 мегабайт на 10 млн записей - не так уж и плохо!
Использовать материализованные представления (Indexed Views) - оптимальный вариант. Хорошо расписан в ответе от i-one, я бы предложил вам выбрать именно это решение.


Стоит отметить, что решения с индексами (3 и 4) не бесплатны с точки зрения поддержки
Индексы требуют расходов на хранение (особенно видно в (3)) и на поддержание их в актульно
состоянии (что чуть-чуть увеличивает время вставки записи). Решение на indexed view так же заметно усложняет план вставки каждой записи. Не удивляйтесь, если обнаружите что простая вставка 

insert into People (CityId, Name, LastName)
values (1, 'test', 'one')


начнет выполняться вот так:



Процентам в плане слишком сильно верить нельзя, но стоит обращать на них внимание. Хорошо видно что решения (3) и (4) совсем не бесплатны.



Кроме того, индексы обычно решают проблемы конкретных запросов. Например, созданны
в (3) индекс будет неприменим при группировке по городам. А индекс с группировкой п
городам может оказаться неэффективен при группировке по другому полю. Иногда наличие индекса может значительно ухудшить результат (например, в случае update на базе сложной выборке).

Стоит всегда измерять результат до и после оптимизации, и не забывать, что индек
может повлиять не только на тот запрос, который вы оптимизировали, но и на те запросы, что вы трогать не собирались.


Ответ 2

Если COUNT нужен в целях оценки, то, как верно отметили в комментариях, проще всего обратиться к DMV. Например к sys.dm_db_partition_stats:

select sum(row_count)
from sys.dm_db_partition_stats
where object_id = object_id('People') and index_id in (0, 1);


это даст приблизительное число строк в таблице (иногда точное, но точность не гарантируется).

Если нужно точное число строк в таблице, то придётся честно выполнить запрос SELEC
COUNT(*) FROM People. В ответе участника PashaPash хорошо описаны причины, по которы
такой запрос может быть медленным и способы устранения. Мне также нравится ответ участника MaxU. Я бы использовал этот подход, только оформил бы это в виде материализованного представления:

create view mvPeopleCityCnt
with schemabinding
as
    select CityId, Cnt = count_big(*)
    from dbo.People
    group by CityId;
GO
create unique clustered index mvPeopleCityCnt_Pk on mvPeopleCityCnt (CityId);
GO


Отмечу, что можно было бы материализовать запрос select count_big(*) from Peopl
(не делая группировку по CityId). Учитывая, однако, что число городов относительно небольшое, материализация с группировкой по CityId может быть выгоднее, т.к. в этом случае в дополнение к запросу

select count(*)
from People;


обращением к индексу mvPeopleCityCnt_Pk также могут быть удовлетворены, например, и такие запросы:

select CityId, count(*)
from People
group by CityId;

select distinct CityId
from People;


При их выполнении в случае наличия материализованного представления, SqlServer обратится к нему, а не к таблице People.

Ниже сравнение производительности запроса SELECT COUNT(*) FROM People для четырёх разных подходов:

clustered index (Id):
Table 'People'. Scan count 5, logical reads 1290799 ...
CPU time = 7333 ms,  elapsed time = 2051 ms.

nonclustered index (Id):
Table 'People'. Scan count 5, logical reads 124188 ...
CPU time = 6911 ms,  elapsed time = 1840 ms.

nonclustered columnstored index (Id):
Table 'People'. Scan count 4, ..., lob logical reads 116054 ...
CPU time = 999 ms,  elapsed time = 252 ms.

materialized view:
Table 'mvPeopleCityCnt'. Scan count 1, logical reads 2 ...
CPU time = 0 ms,  elapsed time = 0 ms.


Ответ 3

Если надо быстро посмотреть количество строк в таблице, то можно воспользоватьс
хранимкой sp_spaceused

EXEC sys.sp_spaceused @objname = N'People'


Второй столбец в результате - "rows" - и будет количеством строк в таблице на текущий момент.


Ответ 4

Я думаю в данном случае надо каким-то образом кешировать уже агрерированные данные.

Один из вариантов создать таблицу:

CREATE TABLE CityStats(
    CityId      int,
    Population  int,
    CONSTRAINT CityStats_CityId_Idx PRIMARY KEY CLUSTERED (CityId ASC)
);


и обновлять (поддерживать) ее тригером на People...


Ответ 5

Узнавать число строк в таблице не имеет смысла - это число может устареть в следующи
же момент. Если только вы не повесите блокировку на всю таблицу целиком.  Если у вас такая необходимость возникает довольно часто - вы что-то делаете не так.

Для диагностики можете взять какое-нибудь приближенное решение отсюда: https://habrahabr.ru/post/271797/

А если по какой-то причине нужны точные данные, в любой момент и быстро - можно воспользоваться индексированным представлением:

create view PeopleCount with schemabinding 
as select count_big(*) as [count] from dbo.People
go

create unique clustered index PK_PeopleCount ON PeopleCount([count])


Использование:


на младших редакциях SQL Server: select [count] from PeopleCount with (noexpand)
на старших редакциях SQL Server можно сделать и так: select count(*) from People


Ответ 6

Количество строк можно узнать из системного представления dm_db_partition_stats по кластерному индексу:

SELECT SUM(s.row_count)
FROM sys.dm_db_partition_stats s
WHERE s.[object_id] = OBJECT_ID('схема.таблица')
    AND s.index_id < 2;


Также если неожиданно стал тормозить запрос типа:

SELECT COUNT(*)
FROM схема.таблица;


, то возможно стоит обновить метаданные по этой таблице с помощью команды UPDATEUSAGE:

DBCC UPDATEUSAGE(БД, 'схема.таблица') WITH COUNT_ROWS;


Более детально обсуждалось здесь


Ответ 7

Попробуй сделать индекс по People.ID

Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,


У таблицы People нет первичного ключа и поиск ведётся перебором всех записей.

Детально проблему покажет 

explain select count(*) from people;