SQL, Подготовка данных

Секреты выборки данных из очень больших таблиц

Время прочтения: 4 мин.

Часто перед IT-специалистами ставятся довольно интересные и амбициозные задачи, требующие «особого» подхода, о сроках выполнения говорить вообще не стоит, данные должны быть обработаны и предоставлены в кратчайшие сроки. Например, за интересующий профильного сотрудника интервал времени выявить отсутствующих (по кадровому учёту) сотрудников с определенными должностями, у которых имеются записи о входах в различные автоматизированные системы. Первое что приходит в голову — обычный JOIN таблиц. Но в жизни все иначе, первое пришедшее в голову решение не всегда верное, и вот почему: во-первых, в компании огромное количество сотрудников, что уже будет приводить к долгому времени работы программы, во-вторых, не стоит забывать, что каждый сотрудник в течение дня «логинится» хотя бы в одной системе, коих по факту значительное количество. Проведя подробный анализ задачи, было решено декомпозировать её на несколько частей.

Первая часть – создать таблицу отсутствующих сотрудников:

create table table_absence (
	[ID]			bigint
	,[LOGIN]		nvarchar(250)
	,[DATE_BEGIN]		date
	,[DATE_END]		date
	,[NUMBER_DAYS]	int
	,[BRANCH]		int
	,[OFFICE]		int
)

Данные по отсутствующим сотрудникам и занимаемым ими должностями за интересующий промежуток времени необходимо извлечь из таблиц кадрового учета и добавить в созданную нами таблицу table_absence. Так же стоит заметить, что для удобства работы (упрощения дальнейшего анализа, увеличения скорости обработки информации), данные должны быть проиндексированы и каждая отдельная запись должна быть уникальной. Далее, необходимо произвести обогащение данных информацией из таблицы о фактах входа сотрудниками в автоматизированные системы. Для этого создадим временную таблицу @tbl:

DECLARE  @tbl TABLE(
	[ID] 		[bigint] NULL,
	[LOGIN] 	[nvarchar](250) NULL,
	[DATE_BEGIN]	[date] NULL,
	[DATE_END]	[date] NULL,
	[NUMBER_DAYS]	[int] NULL,
	[BRANCH] 	[int] NULL,
	[OFFICE] 	[int] NULL
)
INSERT INTO @tbl
select distinct 
	[ID],
	[LOGIN],
	[DATE_BEGIN],
	[DATE_END],
	[NUMBER_DAYS],
	[BRANCH],
	[OFFICE]
from table_absence

Для каждого столбца – создана переменная:

declare @ID as bigint
declare @LOGIN as nvarchar(250)
declare @DATE_BEGIN as date
declare @DATE_END as date
declare @BRANCH as int
declare @OFFICE as int

Вторая часть нашего алгоритма – создание результирующей таблицы, куда будет производиться запись всей необходимой информации. Назовем таблицу [table_result].

Перебираем данные, содержащиеся в нашей временной таблице. Для этого создаем цикл, работающий до тех пор, пока существуют записи в таблице @tbl. Далее, извлекаем информацию из таблицы о фактах входа в автоматизированные системы, параллельно применяя требуемые ограничения, указанные в переменных, а также ограничения, которые может предоставить профильный сотрудник (должность, подразделение, список АС в которые был произведен вход и т.д.). После этого, извлеченная запись добавляется в результирующую таблицу. В завершении цикла запись необходимо удалить из временной таблицы @tbl. Применение такого подхода позволит избежать соединения таблиц с большим объемом данных, что является ресурсоемким и энергозатратным процессом, значительно потребляющим время серверов.

while exists(select  * from @tbl)		--цикл, пока в таблице @tbl имеются записи
  begin
    select top 1 
      @ID		=	ID		--извлекаем одну запись
      ,@LOGIN		=	[LOGIN]	        --из таблицы @tbl
      ,@DATE_BEGIN	=	DATE_BEGIN	--и полученные значения	
      ,@DATE_END	=	DATE_END	--записываем
      ,@BRANCH	        =	BRANCH		--в соответствующие
      ,@OFFICE	        =	OFFICE		--переменные
    from @tbl

    insert into [table_result]		        --добавляем данные
    select  [DECRIPTION]			--в результирующую таблицу
        ,[EVENT_DAY]
        ,[EVENT_DATE]
        ,[STATUS]
        ,[IP_ADDRESS]
        ,@ID
        ,@LOGIN
        ,@ DATE_BEGIN
        ,@ DATE_END
    from [table_data] [td] WITH (NOLOCK)
    WHERE [td].[ID]			=	@ID		--накладываем ограничения
      AND [td].[USER_APP]		=	@LOGIN		--на данные
      AND [td].[BRANCH]		        =	@BRANCH	        --используем значения
      AND [td].[OFFICE]		        =	@OFFICE	        --переменных
      AND [td].[EVENT_DAY]		>=	@DATE_BEGIN	
      AND [td].[EVENT_DAY]		<=	@DATE_END	
      AND [td].[FUNCTION_ID]	        =	1234		--дополнительные ограничения
      AND [td].[SUBSYSTEM_ID]	        =	5		--на извлекаемые данные    delete @tbl						        --удаляем отработанную
    WHERE	[ID]			=	@ID		--запись из таблицы @tbl
      AND [LOGIN]			=	@LOGIN
      AND [DATE_BEGIN]		        =	@DATE_BEGIN 
      AND [DATE_END]			=	@DATE_END
end

Можно обогатить алгоритм дополнительным функционалом, например, посчитать сколько времени отрабатывается одна запись. Для этого перед извлечением одной строки и после удаления отработанной записи необходимо сохранить текущее время (дату). Разница между двумя этими величинами и составляет время, потраченное на отработку данной записи.  Обладая информаций в какое время происходила отработка той или иной записи можно определить периоды максимальной и минимальной нагрузки серверов за время отработки всех записей.

Используя этот приём циклической отработки специально подготовленных данных можно извлечь информацию из сколь угодно больших таблиц, не рискуя получить «бан» от администраторов баз данных.

Советуем почитать