ReSQL.ru
     
powered by simpleCommunicator - 2.0.52     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Проблема соединения двух таблиц (26 и 4 млн строк)
25 сообщений из 39, страница 1 из 2
  1  все
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018626
pavifed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pavifed
Гость
Всем здравствуйте!
Столкнулся с непонятной для меня проблемой, когда не могу сджойнить пару таблиц. Уже три дня перебираю различные варианты, пока что успеха не достиг. Это повергает в уныние.
Оптимизатор по умолчанию выбирает 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.
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
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.
Относительно интервалов доступности интервалы занятости могут:
    совпадать с размером рабочей ячейки кабинета
    занимать несколько рабочих ячеек кабинета (не обязательно целых)
    находиться внутри рабочей ячейки
Параллельно может проходить несколько интервалов занятости!

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
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.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
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
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
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, повлиять на выбор способа не нашёл.
...
Рейтинг: 0 / 0
16.11.2020, 00:10
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018658
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
pavifed
не могу сджойнить пару таблиц
Расшифруйте.
...
Рейтинг: 0 / 0
16.11.2020, 09:19
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018659
pavifed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pavifed
Гость
invm
Расшифруйте

Добрый день.

Например, код попытки 2, которая уже не отвечает исходной задаче (считает только занятость интервала, а не количество отпускаемых в интервале услуг) не выполняется ни за какое адекватное время (максимум, сколько ждал - 100 минут) при том, что там уже и вместо LEFT JOIN используется INNER и только по одному кабинету ServicePointId = 5. Ожидаемый план запроса получается, на мой взгляд, красивый. В обоих случаях IndexSeek, ожидаемое количество строк корректное.
...
Рейтинг: 0 / 0
16.11.2020, 09:29
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018661
Фотография env
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
env
Участник
pavifed,

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

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

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

упд. Ну и посмотрите загрузку cpu во время выполнения запроса.
...
Рейтинг: 0 / 0
16.11.2020, 09:34
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018662
pavifed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pavifed
Гость
env
Проблема именно с update?

Не только. SELECT с выводом на экран или в файл также не отрабатывает за адекватное время (результата выполнения я не дождался ни разу, даже при ограничении на 1 кабинет)
...
Рейтинг: 0 / 0
16.11.2020, 09:38
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018665
Фотография env
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
env
Участник
pavifed,

Блокировки проверяли? Может просто одну из таблиц кто-то/что-то блокирует.
...
Рейтинг: 0 / 0
16.11.2020, 09:45
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018670
pavifed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pavifed
Гость
env, спасибо за рекомендации.
Попробовал с OPTION (MAXDOP 1). Уже более 10 минут выполняется по 1 кабинету, результат не получен.
Блокировки отсутствуют.
...
Рейтинг: 0 / 0
16.11.2020, 09:57
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018672
pavifed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pavifed
Гость
env
Если запрос сериализовать во времянку, эффект тот же?

Поясните этот момент, пожалуйста.
...
Рейтинг: 0 / 0
16.11.2020, 09:58
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018676
pavifed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pavifed
Гость
pavifed,

а вот такая картина изначально была, без OPTION(MAXDOP 1)
...
Рейтинг: 0 / 0
16.11.2020, 10:07
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018682
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
pavifed,

Ваши красивые картинки малоинформативны.
Если запрос выполняется "вечно", смотрите его ожидания в sys.dm_waiting_tasks, или в sys.dm_exec_requests.
Или установите себе sp_whoisactive
...
Рейтинг: 0 / 0
16.11.2020, 10:15
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018683
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
invm
sys.dm_waiting_tasks
sys.dm_os_waiting_tasks
...
Рейтинг: 0 / 0
16.11.2020, 10:17
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018689
pavifed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pavifed
Гость
invm
Или установите себе sp_whoisactive

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



В поле locks для своего запроса получаю:
Код: xml
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
<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.
...
Рейтинг: 0 / 0
16.11.2020, 10:38
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018888
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
pavifed,

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


Конечно же это не так, предикат ограничивает поиск только в одной из таблиц. Более того, серверу может быть неизвестна корректная оценка кардинальности. Если Вы указали фактические значения, то сравните с теми, которые предоставлены в ожидаемом плане запроса.
...
Рейтинг: 0 / 0
16.11.2020, 15:07
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018922
pavifed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pavifed
Гость
Владислав Колосов,
количество строк в ожидаемом плане совпадает с реальным.

P.S. ненамеренно оставил запрос работать в фоновой вкладке. По одному кабинету (ServicePointId = 5) запрос отработал за 3 часа 15 минут
...
Рейтинг: 0 / 0
16.11.2020, 15:42
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018931
Фотография Yasha123
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yasha123
Участник
и что, за 3 часа не могли посмотреть, чего сессия ждет?
запустите снова и посмотрите в sys.dm_os_waiting_tasks
...
Рейтинг: 0 / 0
16.11.2020, 16:06
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018938
17-77
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
17-77
Участник
pavifed
Напомню, в левой таблице 28 млн строк, в правой таблице - 4 млн. Пытался ограничить в WHERE для одного кабинета (ServicePointId = 5), при таком раскладе в левой таблице 376 852 строк, в правой - 87 832. Дождаться результата выполнения запроса не удаётся. Смена LEFT на INNER (хоть это и не в интересах моей задачи) положительного результата не даёт. По умолчанию оптимизатор выбирает LOOP JOIN и ожидает получить миллиарды записей. Принудительный хинт в MERGE/HASH успеха не дают.

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

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

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

да и проблема эта была у меня, когда я джоинил не по FK полю, а вот примерно как у вас, периоды дат
...
Рейтинг: 0 / 0
16.11.2020, 16:15
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018941
Фотография env
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
env
Участник
pavifed,

1. Если версия позволяет - запустите с live query и посмотрите на реальный план выполнения.
2. merge join по неравенству выглядит несколько странно, покажите план без хинтования
...
Рейтинг: 0 / 0
16.11.2020, 16:23
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018943
pavifed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pavifed
Гость
Yasha123,
Получаю 129 строк с WITE_TYPE = 'CXPACKET'.
Ранее выше писал, что sp_whoisactive возвращает поле wait_info NULL.
...
Рейтинг: 0 / 0
16.11.2020, 16:24
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018945
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
pavifed,

Повторюсь - ваши картинки неинформативны.
Не хотите сами разбираться с ожиданиями (предположительно) - тогда запустите запрос еще раз и покажите актуальный план выполнения в формате sqlplan, а не картинкой.
...
Рейтинг: 0 / 0
16.11.2020, 16:26
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018947
17-77
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
17-77
Участник
т.е. попробуйте связать таблицы ServicePointScheduleEveryDay и VScheduleItem напрямую, например добавив в VScheduleItem поле ServicePointScheduleEveryDayId + FK + индекс по полю ServicePointScheduleEveryDayId
...
Рейтинг: 0 / 0
16.11.2020, 16:27
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018948
pavifed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pavifed
Гость
env
2. merge join по неравенству выглядит несколько странно, покажите план без хинтования

Без хинтования получаю LOOP в Estimation плане.
...
Рейтинг: 0 / 0
16.11.2020, 16:28
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018949
Фотография env
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
env
Участник
pavifed
Yasha123,
Получаю 129 строк с WITE_TYPE = 'CXPACKET'.


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

CXPACKET это ожидание связанное с параллелизмом. Например, у вас перекос в распределении данных по потокам.
...
Рейтинг: 0 / 0
16.11.2020, 16:28
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018951
pavifed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pavifed
Гость
env
Если версия позволяет - запустите с live query и посмотрите на реальный план выполнения.


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


Ок, вернусь к изначальному запросу и попробую выполнить его с live query. Спасибо!
...
Рейтинг: 0 / 0
16.11.2020, 16:30
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018955
pavifed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pavifed
Гость
env
А какие ожидания с maxdop 1 ?

При использовании OPTION (MAXDOP 1) представление sys.dm_os_waiting_tasks возвращает пустой набор.
...
Рейтинг: 0 / 0
16.11.2020, 16:33
| Ответить | Цитировать | Написать  
Проблема соединения двух таблиц (26 и 4 млн строк)
    #40018958
Фотография Yasha123
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yasha123
Участник
ну так криво он поделил работу,
видите же, что другие thread ждут работающего, которому досталось больше.
вам предлагали при maxdop = 1 запускать,
попробуйте снова и посмотрим, что там за ожидания, исключим CXPACKET
...
Рейтинг: 0 / 0
16.11.2020, 16:35
| Ответить | Цитировать | Написать  
25 сообщений из 39, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Проблема соединения двух таблиц (26 и 4 млн строк)
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=40018948&tid=1685404]:
 0ms
get settings:
 9ms
get forum list:
 12ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 58ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 52ms
get tp. blocked users:
 1ms
others: 311ms
total:  458ms
созд. / загр.: 458ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]