Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
pavifed
Member

Откуда:
Сообщений: 20
Всем здравствуйте!
Столкнулся с непонятной для меня проблемой, когда не могу сджойнить пару таблиц. Уже три дня перебираю различные варианты, пока что успеха не достиг. Это повергает в уныние.
Оптимизатор по умолчанию выбирает LOOP, но и принудительный перевод в HASH/MERGE не даёт результата даже при ограничении выборки, не говоря уже о полном наборе данных в таблицах :'(

Преднастройки:
  • SQL Server Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64) Jun 15 2019 00:26:19 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
  • БД в режиме совместимости SQL Server 2017 (140)
  • Статистики таблиц запроса пересчитаны с FULLSCAN

Таблицы:
В кабинетах, работающих по расписанию, производится отпуск услуг.

Таблица ServicePointScheduleEveryDay с информацией о рабочих по расписанию интервалах кабинетов на каждый календарный день. Интервалы не пересекаются. Количество строк за всё время 28 206 685.
+
CREATE TABLE staging.ServicePointScheduleEveryDay
(
	ServicePointId dbo.SD_ID NOT NULL,	--кабинет 
	[DayOfWeek] dbo.SD_ID NOT NULL,		--номер дня недели
	ScheduleId dbo.SD_ID NOT NULL,		--id расписания
	StartDateTime datetime NULL,		--начало рабочего интервала кабинета в конкретную календарную дату
	EndDateTime datetime NULL,			--конец рабочего интервала кабинета в конкретную календарную дату
	
	CONSTRAINT [PK_ServicePointScheduleEveryDay] PRIMARY KEY CLUSTERED 
	(
		[ServicePointId] ASC,
		[StartDateTime] ASC,
		[EndDateTime] ASC
	)
) 

Пример данных:
Картинка с другого сайта.

Таблица VScheduleItem с информацией об отпусках услуг, можно считать их интервалами занятости. Количество строк за всё время: 3 894 942.
Относительно интервалов доступности интервалы занятости могут:
  • совпадать с размером рабочей ячейки кабинета
  • занимать несколько рабочих ячеек кабинета (не обязательно целых)
  • находиться внутри рабочей ячейки
Параллельно может проходить несколько интервалов занятости!

+
CREATE TABLE staging.VScheduleItem
(
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	ServicePointId dbo.SD_ID NULL,		--кабинет
	StartDateTime datetime NOT NULL,	--начало отпуска услуги (на одно время может приходиться несколько услуг) / интервала занятости кабинета в конкретную календарную дату 
	EndDateTime datetime NULL			--конец отпуска услуги (на одно время может приходиться несколько услуг) / интервала занятости кабинета в конкретную календарную дату 
	
	CONSTRAINT [PK_VScheduleItem] PRIMARY KEY NONCLUSTERED 
	(
		[Id] ASC
	)	
)

CREATE CLUSTERED INDEX pr_id ON staging.VScheduleItem
(
	ServicePointId ASC,
	StartDateTime ASC,
	EndDateTime ASC
)

Пример данных:
Картинка с другого сайта.

Задача
Для каждого из рабочих интервалов кабинетов на каждый календарный день (ServicePointScheduleEveryDay) определить количество приходящихся интервалов занятости (VScheduleItem).

Проблема/предпринимаемые попытки решения
Попытка 1. Изначально пытался зачем-то вставлять результат в новую таблицу ServicePointScheduleEDScheduleItemCount, где поля совпадают с таблицей ServicePointScheduleEveryDay, плюс добавляется новое поле ScheduleItemCount.
INSERT INTO [staging].[ServicePointScheduleEDScheduleItemCount]
           ([ServicePointId]
           ,[DayOfWeek]
           ,[ScheduleId]
           ,[StartDateTime]
           ,[EndDateTime]
           ,[ScheduleItemCount])
	SELECT 
		sps.ServicePointId
		,sps.DayOfWeek
		,sps.ScheduleId
		,sps.StartDateTime
		,sps.EndDateTime
		,SUM(CASE WHEN si.StartDateTime IS NOT NULL THEN 1 ELSE 0 END) ScheduleItemCount
	FROM 
		staging.ServicePointScheduleEveryDay sps
		LEFT MERGE/*HASH*/ JOIN staging.VScheduleItem si ON 
			sps.ServicePointId = si.ServicePointId 
			AND sps.StartDateTime < si.EndDateTime AND sps.EndDateTime > si.StartDateTime --условие пересечения временных интервалов
	WHERE sps.ServicePointId = 5 --даже при ограничении на один кабинет результат не удаётся получить
	GROUP BY 
		sps.ServicePointId
		,sps.DayOfWeek
		,sps.ScheduleId
		,sps.StartDateTime
		,sps.EndDateTime

Напомню, в левой таблице 28 млн строк, в правой таблице - 4 млн. Пытался ограничить в WHERE для одного кабинета (ServicePointId = 5), при таком раскладе в левой таблице 376 852 строк, в правой - 87 832. Дождаться результата выполнения запроса не удаётся. Смена LEFT на INNER (хоть это и не в интересах моей задачи) положительного результата не даёт. По умолчанию оптимизатор выбирает LOOP JOIN и ожидает получить миллиарды записей. Принудительный хинт в MERGE/HASH успеха не дают.

В попытках 2 и 3 временно отказался от результата в виде количества приходящихся интервалов занятости, а просто хотел определить был ли занят рабочий интервал или нет: в таблицу ServicePointScheduleEveryDay добавил битовое поле IsBusySegment.

Попытка 2. Обновлять ServicePointScheduleEveryDay.IsBusySegment
UPDATE ed
SET IsBusySegment = 1 
FROM
	staging.ServicePointScheduleEveryDay ed
	INNER MERGE JOIN staging.VScheduleItem sps ON
			sps.ServicePointId = ed.ServicePointId 
			AND (sps.StartDateTime < ed.EndDateTime) 
			AND (sps.EndDateTime > ed.StartDateTime)
			AND sps.ServicePointId = 5 		
WHERE 
	ed.ServicePointId = 5


Попытка 3. Обновлять ServicePointScheduleEveryDay.IsBusySegment для случаев WHERE EXISTS записи в таблице интервалов занятости. При таком раскладе оптимизатор выбирает LOOP JOIN, повлиять на выбор способа не нашёл.

К сообщению приложен файл. Размер - 21Kb


Сообщение было отредактировано: 16 ноя 20, 00:08
16 ноя 20, 00:10    [22232547]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
invm
Member

Откуда: Москва
Сообщений: 9489
pavifed
не могу сджойнить пару таблиц
Расшифруйте.
16 ноя 20, 09:19    [22232609]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
pavifed
Member

Откуда:
Сообщений: 20
invm
Расшифруйте

Добрый день.

Например, код попытки 2, которая уже не отвечает исходной задаче (считает только занятость интервала, а не количество отпускаемых в интервале услуг) не выполняется ни за какое адекватное время (максимум, сколько ждал - 100 минут) при том, что там уже и вместо LEFT JOIN используется INNER и только по одному кабинету ServicePointId = 5. Ожидаемый план запроса получается, на мой взгляд, красивый. В обоих случаях IndexSeek, ожидаемое количество строк корректное.

К сообщению приложен файл. Размер - 40Kb
16 ноя 20, 09:29    [22232613]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
pavifed,

Проблема именно с update? Если запрос сериализовать во времянку, эффект тот же?

Какие блокировки были/есть во время выполнения запроса?

С maxdop 1 эффект тот же?

упд. Ну и посмотрите загрузку cpu во время выполнения запроса.

Сообщение было отредактировано: 16 ноя 20, 09:30
16 ноя 20, 09:34    [22232617]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
pavifed
Member

Откуда:
Сообщений: 20
env
Проблема именно с update?

Не только. SELECT с выводом на экран или в файл также не отрабатывает за адекватное время (результата выполнения я не дождался ни разу, даже при ограничении на 1 кабинет)
16 ноя 20, 09:38    [22232619]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
pavifed,

Блокировки проверяли? Может просто одну из таблиц кто-то/что-то блокирует.
16 ноя 20, 09:45    [22232622]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
pavifed
Member

Откуда:
Сообщений: 20
env, спасибо за рекомендации.
Попробовал с OPTION (MAXDOP 1). Уже более 10 минут выполняется по 1 кабинету, результат не получен.
Блокировки отсутствуют.

К сообщению приложен файл. Размер - 63Kb
16 ноя 20, 09:57    [22232629]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
pavifed
Member

Откуда:
Сообщений: 20
env
Если запрос сериализовать во времянку, эффект тот же?

Поясните этот момент, пожалуйста.

Сообщение было отредактировано: 16 ноя 20, 09:55
16 ноя 20, 09:58    [22232632]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
pavifed
Member

Откуда:
Сообщений: 20
pavifed,

а вот такая картина изначально была, без OPTION(MAXDOP 1)

К сообщению приложен файл. Размер - 148Kb
16 ноя 20, 10:07    [22232641]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
invm
Member

Откуда: Москва
Сообщений: 9489
pavifed,

Ваши красивые картинки малоинформативны.
Если запрос выполняется "вечно", смотрите его ожидания в sys.dm_waiting_tasks, или в sys.dm_exec_requests.
Или установите себе sp_whoisactive
16 ноя 20, 10:15    [22232653]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
invm
Member

Откуда: Москва
Сообщений: 9489
invm
sys.dm_waiting_tasks
sys.dm_os_waiting_tasks
16 ноя 20, 10:17    [22232654]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
pavifed
Member

Откуда:
Сообщений: 20
invm
Или установите себе sp_whoisactive

Спасибо, установил, выполнил
EXEC sp_whoisactive @get_locks = 1, @get_task_info = 1, @get_transaction_info = 1


В поле locks для своего запроса получаю:
<Database name="demo_30b">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="ServicePointScheduleEveryDay" schema_name="staging">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="9" />
        <Lock resource_type="PAGE" page_type="*" index_name="PK_ServicePointScheduleEveryDay" request_mode="U" request_status="GRANT" request_count="173" />
      </Locks>
    </Object>
    <Object name="VScheduleItem" schema_name="staging">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IS" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>


В поле wait_info - NULL.
16 ноя 20, 10:38    [22232664]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8047
pavifed,

автор
WHERE для одного кабинета (ServicePointId = 5), при таком раскладе в левой таблице 376 852 строк, в правой - 87 832


Конечно же это не так, предикат ограничивает поиск только в одной из таблиц. Более того, серверу может быть неизвестна корректная оценка кардинальности. Если Вы указали фактические значения, то сравните с теми, которые предоставлены в ожидаемом плане запроса.
16 ноя 20, 15:07    [22232994]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
pavifed
Member

Откуда:
Сообщений: 20
Владислав Колосов,
количество строк в ожидаемом плане совпадает с реальным.

P.S. ненамеренно оставил запрос работать в фоновой вкладке. По одному кабинету (ServicePointId = 5) запрос отработал за 3 часа 15 минут

К сообщению приложен файл. Размер - 119Kb
16 ноя 20, 15:42    [22233033]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
Yasha123
Member

Откуда:
Сообщений: 1929
и что, за 3 часа не могли посмотреть, чего сессия ждет?
запустите снова и посмотрите в sys.dm_os_waiting_tasks
16 ноя 20, 16:06    [22233048]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
17-77
Member

Откуда:
Сообщений: 1471
pavifed
Напомню, в левой таблице 28 млн строк, в правой таблице - 4 млн. Пытался ограничить в WHERE для одного кабинета (ServicePointId = 5), при таком раскладе в левой таблице 376 852 строк, в правой - 87 832. Дождаться результата выполнения запроса не удаётся. Смена LEFT на INNER (хоть это и не в интересах моей задачи) положительного результата не даёт. По умолчанию оптимизатор выбирает LOOP JOIN и ожидает получить миллиарды записей. Принудительный хинт в MERGE/HASH успеха не дают.

у меня была вроде аналогичная проблема - в каждой из таблиц было немного строк, а джоин безбожно тупил, в итоге мне пришло в голову, что он пытается мне выдать все комбинации, потому что вы сами говорите, что в один период дат одной таблицы может быть несколько совпадений из другой таблицы, т.е. максимальное кол-во записей, которое потенциально может выдать селект - это 376 852 * 87 832 = ~33 млрд

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

в моем случае, насколько я помню, все в итоге решилось довольно просто, но не помню что я сделал, то ли условие в джоине поменял, то ли структуру БД

да и проблема эта была у меня, когда я джоинил не по FK полю, а вот примерно как у вас, периоды дат

Сообщение было отредактировано: 16 ноя 20, 16:15
16 ноя 20, 16:15    [22233058]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
pavifed,

1. Если версия позволяет - запустите с live query и посмотрите на реальный план выполнения.
2. merge join по неравенству выглядит несколько странно, покажите план без хинтования
16 ноя 20, 16:23    [22233067]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
pavifed
Member

Откуда:
Сообщений: 20
Yasha123,
Получаю 129 строк с WITE_TYPE = 'CXPACKET'.
Ранее выше писал, что sp_whoisactive возвращает поле wait_info NULL.

К сообщению приложен файл. Размер - 126Kb
16 ноя 20, 16:24    [22233069]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
invm
Member

Откуда: Москва
Сообщений: 9489
pavifed,

Повторюсь - ваши картинки неинформативны.
Не хотите сами разбираться с ожиданиями (предположительно) - тогда запустите запрос еще раз и покажите актуальный план выполнения в формате sqlplan, а не картинкой.
16 ноя 20, 16:26    [22233071]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
17-77
Member

Откуда:
Сообщений: 1471
т.е. попробуйте связать таблицы ServicePointScheduleEveryDay и VScheduleItem напрямую, например добавив в VScheduleItem поле ServicePointScheduleEveryDayId + FK + индекс по полю ServicePointScheduleEveryDayId
16 ноя 20, 16:27    [22233074]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
pavifed
Member

Откуда:
Сообщений: 20
env
2. merge join по неравенству выглядит несколько странно, покажите план без хинтования

Без хинтования получаю LOOP в Estimation плане.

К сообщению приложен файл. Размер - 61Kb
16 ноя 20, 16:28    [22233077]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
pavifed
Yasha123,
Получаю 129 строк с WITE_TYPE = 'CXPACKET'.


А какие ожидания с maxdop 1 ?

CXPACKET это ожидание связанное с параллелизмом. Например, у вас перекос в распределении данных по потокам.
16 ноя 20, 16:28    [22233078]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
pavifed
Member

Откуда:
Сообщений: 20
env
Если версия позволяет - запустите с live query и посмотрите на реальный план выполнения.


invm
Повторюсь - ваши картинки неинформативны.
Не хотите сами разбираться с ожиданиями (предположительно) - тогда запустите запрос еще раз и покажите актуальный план выполнения в формате sqlplan, а не картинкой.


Ок, вернусь к изначальному запросу и попробую выполнить его с live query. Спасибо!
16 ноя 20, 16:30    [22233080]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
pavifed
Member

Откуда:
Сообщений: 20
env
А какие ожидания с maxdop 1 ?

При использовании OPTION (MAXDOP 1) представление sys.dm_os_waiting_tasks возвращает пустой набор.
16 ноя 20, 16:33    [22233084]     Ответить | Цитировать Сообщить модератору
 Re: Проблема соединения двух таблиц (26 и 4 млн строк)  [new]
Yasha123
Member

Откуда:
Сообщений: 1929
ну так криво он поделил работу,
видите же, что другие thread ждут работающего, которому досталось больше.
вам предлагали при maxdop = 1 запускать,
попробуйте снова и посмотрим, что там за ожидания, исключим CXPACKET
16 ноя 20, 16:35    [22233087]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить