Оптимальный запрос

Форум Программирование Общие вопросы программирования (Dev)

1. nazirovramzil 18.08.21 13:27 Сейчас в теме

Здравствуйте. Дано: есть документ "Накладная", регистр сведений (2млн записей) "Идентификатор" с измерением "объект" составного типа (один из которых документ "накладная". Запрос строится левым соединением Документ - Регистр. Вопрос: Как в запросе корректно наложить условия для максимального быстродействия?

По теме из базы знаний

Ответы

Подписаться на ответы Инфостарт бот

Свернуть все

2. FatPanzer 18.08.21 13:31 Сейчас в теме

"ВЫБРАТЬ
|    ВЫРАЗИТЬ(Идентификатор.Объект КАК Документ.Накладная) КАК Ссылка
|ИЗ 
|   РегистрСведлений.Идентификатор КАК Идентификатор
|ГДЕ 
|    ВЫРАЗИТЬ(Идентификатор.Объект КАК Документ.Накладная) = &ВотЭтуСсылкуИщу"

А при соединении вообще ничего не надо. там связь по значению идет, соответственно никакой дополнительной типизации в запросе не надо

3. nazirovramzil 18.08.21 13:36 Сейчас в теме

Я попробовал сделать вот так

ВЫБРАТЬ
	втЗаказы.Ссылка КАК Ссылка,
	ИдентификаторыОбмена.OID КАК OID
ИЗ
	втЗаказы КАК втЗаказы
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ск_ИдентификаторыОбменаВнешнегоИсточника КАК ИдентификаторыОбмена
		ПО (втЗаказы.Ссылка = (ВЫРАЗИТЬ(ИдентификаторыОбмена.Объект КАК Документ.усЗаказНаОтгрузку)))
ГДЕ
	ИдентификаторыОбмена.Объект ССЫЛКА Документ.усЗаказНаОтгрузку

Показать

и у меня запрос начал выполняться дольше

4. RustamZz 18.08.21 13:44 Сейчас в теме

(3) Не нужно тут ни ГДЕ, ни ВЫРАЗИТЬ. втЗаказы большая таблица?

6. nazirovramzil 18.08.21 13:57 Сейчас в теме

(4)нет не большая

7. nazirovramzil 18.08.21 13:58 Сейчас в теме

(4)таблица идентификаторов составного типа с >20 объектами и 2 млн записей

5. Release 18.08.21 13:44 Сейчас в теме

Может так?

ВЫБРАТЬ
	ИдентификаторыОбмена.Объект,
	ИдентификаторыОбмена.OID
ПОМЕСТИТЬ втИдентификаторы
ИЗ
	РегистрСведений.ск_ИдентификаторыОбменаВнешнегоИсточника КАК ИдентификаторыОбмена
ГДЕ
	ИдентификаторыОбмена.Объект ССЫЛКА Документ.усЗаказНаОтгрузку
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	втЗаказы.Ссылка,
	ИдентификаторыОбмена.OID
ИЗ
	втЗаказы КАК втЗаказы
		ЛЕВОЕ СОЕДИНЕНИЕ втИдентификаторы КАК ИдентификаторыОбмена
		ПО КорректировкаЗаписейРегистров.Ссылка = втИдентификаторы.Объект

Показать

8. RustamZz 18.08.21 13:58 Сейчас в теме

(5) Если будет много записей с Объект ССЫЛКА Документ.усЗаказНаОтгрузку, а в втЗаказы мало, то только хуже станет.

11. Release 18.08.21 14:20 Сейчас в теме

(8) Вы его тестировали относительно (9) или просто так считаете?

12. RustamZz 18.08.21 14:27 Сейчас в теме

(11) Да.

Прикрепленные файлы:

13. Release 18.08.21 14:34 Сейчас в теме

(12) А база точно содержит 2КК записей по регистру? Что-то по времени уж очень мало на обоих примерах. На "малых" базах результат может быть иным.
Хотя, конечно, может уже как-то оптимизировали работу соединения в последних версиях платформы. Раньше сравнение по составному типу разбивалось на несколько соединений в количестве участвующих типов в составе.

15. nazirovramzil 18.08.21 14:46 Сейчас в теме

(13)У меня сейчас так:

ВЫБРАТЬ
	втЗаказы.Ссылка КАК Ссылка,
	ИдентификаторыОбмена.OID КАК OID
ИЗ
	втЗаказы КАК втЗаказы
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ск_ИдентификаторыОбменаВнешнегоИсточника КАК ИдентификаторыОбмена
		ПО втЗаказы.Ссылка = ИдентификаторыОбмена.Объект

Этот запрос выполняется примерно 90-100 мс
А этот около 60 -80 мс:

ВЫБРАТЬ
	втЗаказы.Ссылка КАК Ссылка,
	ИдентификаторыОбмена.OID КАК OID
ИЗ
	втЗаказы КАК втЗаказы
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ск_ИдентификаторыОбменаВнешнегоИсточника КАК ИдентификаторыОбмена
		ПО втЗаказы.Ссылка = ИдентификаторыОбмена.Объект
ГДЕ
	ИдентификаторыОбмена.Объект ССЫЛКА Документ.усЗаказНаОтгрузку

Показать

разве сперва не выполняется условие соединения а потом накладывается условие из "ГДЕ"? почему этот вариант влияет у меня на производительность?

16. RustamZz 18.08.21 14:51 Сейчас в теме

(13) Меньше. Но будет, только в худшую. Смотрите сами получаем во временную не индексированную таблицу миллион записей, из которой SQL нужно найти 50 строк. Будет table scan. В случае соединения регистра index seek.
(15) У них и результат разный будет. В первом левое соединение, во втором внутреннее.

19. nazirovramzil 18.08.21 15:27 Сейчас в теме

(16)а если у меня внутреннее соединение, то тогда вариант с "ГДЕ
ИдентификаторыОбмена.Объект ССЫЛКА Документ.усЗаказНаОтгрузку" лучше?

21. RustamZz 18.08.21 15:45 Сейчас в теме

(19)

SEL ECT
T1._Q_000_F_000RRef,
T2._Fld23070
FR OM #tt4 T1 WITH(NOLOCK)
INNER JOIN dbo._InfoRg23069 T2
ON (0x08 = T2._Fld23071_TYPE AND 0x0000002D = T2._Fld23071_RTRef AND T1._Q_000_F_000RRef = T2._Fld23071_RRRef)
WHERE ((T2._Fld14043 = @P1)) AND ((T2._Fld23071_TYPE = 0x08 AND T2._Fld23071_RTRef = 0x0000002D))

План выполнения точно такой же как и в первом случае

17. Release 18.08.21 14:52 Сейчас в теме

(15) Возможно срабатывает оптимизация. Попробуйте ещё так:

ВЫБРАТЬ
    втЗаказы.Ссылка КАК Ссылка,
    ИдентификаторыОбмена.OID КАК OID
ИЗ
    втЗаказы КАК втЗаказы
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ск_ИдентификаторыОбменаВнешнегоИсточника КАК ИдентификаторыОбмена
        ПО втЗаказы.Ссылка = ИдентификаторыОбмена.Объект
                И (ИдентификаторыОбмена.Объект ССЫЛКА Документ.усЗаказНаОтгрузку)

18. RustamZz 18.08.21 15:22 Сейчас в теме

(15)

SEL ECT
T1._Q_000_F_000RRef,
T2._Fld23070
FR OM #tt4 T1 WITH(NOLOCK)
LEFT OUTER JOIN dbo._InfoRg23069 T2
ON ((0x08 = T2._Fld23071_TYPE AND 0x0000002D = T2._Fld23071_RTRef AND T1._Q_000_F_000RRef = T2._Fld23071_RRRef)) AND (T2._Fld14043 = @P1)

(17) 1С сама добавляет условие по типу (выделил жирным)

Прикрепленные файлы:

20. RustamZz 18.08.21 15:35 Сейчас в теме

(13)

SEL ECT
T1._Q_000_F_000RRef,
T2._Q_001_F_001
FR OM #tt4 T1 WITH(NOLOCK)
LEFT OUTER JOIN #tt57 T2 WITH(NOLOCK)
ON (0x08 = T2._Q_001_F_000_TYPE AND 0x0000002D = T2._Q_001_F_000_RTRef AND T1._Q_000_F_000RRef = T2._Q_001_F_000_RRRef)

вот ваш вариант запроса с планом его выполнения

Прикрепленные файлы:

22. nazirovramzil 18.08.21 15:47 Сейчас в теме

Если не сложно, можете коротко объяснить?) я относительно недавно начал постигать азы производительных запросов, собираюсь настроить у себя тоже тех журнал для изучения планов

23. RustamZz 18.08.21 16:13 Сейчас в теме

(22) Это план запроса из (5). объяснял я в (16) если из моего текста не все ясно, задавайте вопрос.

24. nazirovramzil 18.08.21 17:45 Сейчас в теме

(23)в целом все понятно. Сейчас у нас как раз таки используется (9), который вы описали и производительность нормальная. Но наш скуль специалист поизучал как внутри sql крутится и сказал что запрос не оптимальный (строится куча левых соединений). Хотел бы узнать один момент:

ВЫБРАТЬ
    втЗаказы.Ссылка КАК Ссылка,
    ИдентификаторыОбмена.OID КАК OID
ИЗ
    втЗаказы КАК втЗаказы
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ск_ИдентификаторыОбменаВнешнегоИсточника КАК ИдентификаторыОбмена
        ПО втЗаказы.Ссылка = ИдентификаторыОбмена.Объект
ГДЕ
    ИдентификаторыОбмена.Объект ССЫЛКА Документ.усЗаказНаОтгрузку

Показать

этот вариант по моим исследованиям, работает быстрее. Подскажите, в планировщике он строит только одно левое соединение или на все 20 составных объектов?

27. Release 18.08.21 18:17 Сейчас в теме

(24) Ксати, проверьте установлен ли флаг индексации для поля Объект в регистре. Если индекса нет, должны быть множественные соединения.

31. nazirovramzil 18.08.21 19:03 Сейчас в теме

(27)Посмотрел, флага нет...

32. nazirovramzil 18.08.21 19:06 Сейчас в теме

(27)Т.е. если флаг был бы, то запрос

ВЫБРАТЬ
    втЗаказы.Ссылка КАК Ссылка,
    ИдентификаторыОбмена.OID КАК OID
ИЗ
    втЗаказы КАК втЗаказы
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ск_ИдентификаторыОбменаВнешнегоИсточника КАК ИдентификаторыОбмена
        ПО втЗаказы.Ссылка = ИдентификаторыОбмена.Объект

отрабатывал бы без множественных соединений?
Можно ли вообще, например, сейчас поставить флаг, таблица с 2 млн записей нормально отреструктуризируется?

35. Release 18.08.21 19:11 Сейчас в теме

(32) таблица, по сути, не должна трогаться (вы же не меняете её структуру, добавляя или удаляя поля, или меняя их типы), должна добавиться ещё одна таблица (скрытая) индексов, т.е. размер базы вырастет.
Но, перед любыми манипуляциями со структурой БД рекомендуется резервная копия.

39. RustamZz 19.08.21 08:50 Сейчас в теме

(24) Соединение будет в любом случае одно не знаю где он кучу соединений увидел. Вы лучше показали бы как строится таблица втЗаказы, может там увидим источник проблемы.

41. nazirovramzil 19.08.21 09:03 Сейчас в теме

(39)Вот план запроса, как видите лесенки есть

Прикрепленные файлы:

1НеоптимальныйПланЗапроса_по_регистру_InfoRg6803.sqlplan

42. RustamZz 19.08.21 09:54 Сейчас в теме

(41) Это какой-то другой запрос справочник соединяется с РС через документы

43. nazirovramzil 19.08.21 10:02 Сейчас в теме

(42)там разные объекты соединяются, чтобы каждый мог получить свой идентификатор. Я просто тестирую отдельный кусок запроса, пытаясь вычислить производительность

44. RustamZz 19.08.21 10:54 Сейчас в теме

(43) Вы показываете один запрос, а проблемы с совершенно другим. Как вы думаете мы сможем помочь?

45. nazirovramzil 19.08.21 11:37 Сейчас в теме

(44) Понимаете, мне скинули этот файл, сказали "запросы обмена к этому регистру не оптимальны, оптимизируй". Вот я и хотел узнать, как корректно типизировать составной регистр. Сейчас у нас система работает на том примере запроса, который предложили вы, наш sql-щик говорит "куча множественных соединений". В целом запрос выполняется быстро, в теории "Объект Ссылка Документ.ЗаказНаОтгрузку" должна была ее еще улучшить, а получил обратных эффект.

46. RustamZz 19.08.21 11:54 Сейчас в теме

(45) Это план не того запроса, что вы оптимизируете. А вот такого:

SEL ECT T1._IDRRef, T1._Marked, T1._Code,T1._Description, T1._Fld6837 FR OM dbo._Reference280 T1

47. nazirovramzil 19.08.21 12:19 Сейчас в теме

(46)Огромное спасибо за помощь! У себя настроил трассировку запросов, теперь вижу что платформа сама походу типизирует мой кусок запроса. Нужно весь запрос целиком отлаживать и смотреть схемы. Нету ли у вас ссылки на какую либо статью, где описано как правильно расшифровывать файлы трассировки? (что означает hash match, index scan ....итд)

49. RustamZz 19.08.21 13:10 Сейчас в теме

(47) Нет не подскажу. seek лучше чем scan, merge join лучше чем hash match в большинстве случаев.

48. nazirovramzil 19.08.21 13:06 Сейчас в теме

(46)вот план запроса, я пока только начал изучать что означает содержимое, это нормальное поведение? Возможно я некорректно настроил трассировку

Прикрепленные файлы:

не опт вар1.SQLPlan

51. RustamZz 20.08.21 09:28 Сейчас в теме

(48) А сам запрос какой?

52. nazirovramzil 23.08.21 11:30 Сейчас в теме

(51)Извините, не увидел уведомления вот запрос

ВЫБРАТЬ
	усЕдиницыИзмерения.Ссылка КАК Ссылка,
	усЕдиницыИзмерения.ПометкаУдаления КАК ПометкаУдаления,
	усЕдиницыИзмерения.Код КАК Код,
	усЕдиницыИзмерения.Наименование КАК Наименование,
	усЕдиницыИзмерения.КодFirebird КАК КодFirebird,
	ск_ИдентификаторыОбменаВнешнегоИсточника.US_OID КАК US_OID
ИЗ
	Справочник.усЕдиницыИзмерения КАК усЕдиницыИзмерения
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ск_ИдентификаторыОбменаВнешнегоИсточника КАК ск_ИдентификаторыОбменаВнешнегоИсточника
		ПО (ск_ИдентификаторыОбменаВнешнегоИсточника.Объект.Ссылка = усЕдиницыИзмерения.Ссылка)
ГДЕ
	НЕ усЕдиницыИзмерения.ПометкаУдаления
	И усЕдиницыИзмерения.КодFirebird = &КодFirebird

Показать

Убрал "Ссылка" тут ск_ИдентификаторыОбменаВнешнегоИсточника.Объект.Ссылка и запрос начал работать нормально

53. RustamZz 23.08.21 11:33 Сейчас в теме

(52) Тогда понятно, почему добавление условия на тип ускоряло запрос.

25. nazirovramzil 18.08.21 17:49 Сейчас в теме

(23)Если я правильно понял в (20) только одно

26. Release 18.08.21 18:04 Сейчас в теме

(25) Да в (20) одно, так как идёт предварительная выборка по типу объекта. Именно множественных соединений я и пытался избежать в своем примере.
https://its.1c.ru/db/pubapplied/content/331/hdoc

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

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

Хотя в вашем случае там только ссылки должны быть.

В (5) в запросе можно добавить индексацию по полю Объект, возможно это ускорит выполнение запроса.

29. nazirovramzil 18.08.21 18:57 Сейчас в теме

(26) я пробовал сделать вот так:

ВЫБРАТЬ
	ВЫБОР
		КОГДА ск_ИдентификаторыОбменаВнешнегоИсточника.Объект ССЫЛКА Документ.усЗаказНаОтгрузку
			ТОГДА ВЫРАЗИТЬ(ск_ИдентификаторыОбменаВнешнегоИсточника.Объект КАК Документ.усЗаказНаОтгрузку)
	КОНЕЦ КАК Объект,
	ск_ИдентификаторыОбменаВнешнегоИсточника.OID КАК OID,
	ск_ИдентификаторыОбменаВнешнегоИсточника.US_OID КАК US_OID
ПОМЕСТИТЬ Идентификаторы
ИЗ
	РегистрСведений.ск_ИдентификаторыОбменаВнешнегоИсточника КАК ск_ИдентификаторыОбменаВнешнегоИсточника
ГДЕ
	ск_ИдентификаторыОбменаВнешнегоИсточника.Объект ССЫЛКА Документ.усЗаказНаОтгрузку
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	втЗаказы.Ссылка КАК Ссылка,
	Идентификаторы.OID КАК OID,
	Идентификаторы.US_OID КАК US_OID
ИЗ
	втЗаказы КАК втЗаказы
		ЛЕВОЕ СОЕДИНЕНИЕ Идентификаторы КАК Идентификаторы
		ПО втЗаказы.Ссылка = Идентификаторы.Объект

Показать

Производительность запроса упала, а индексы не дает добавить на составной тип

30. Release 18.08.21 19:03 Сейчас в теме

(29) Использование ВЫБОР будет замедлять запрос. И зачем он вам, если вы уже делаете отбор по типу?

ГДЕ
    ск_ИдентификаторыОбменаВнешнегоИсточника.Объект ССЫЛКА Документ.усЗаказНаОтгрузку

34. nazirovramzil 18.08.21 19:09 Сейчас в теме

(30)делал как тут рекомендовано:ИТС

33. Release 18.08.21 19:09 Сейчас в теме

(29) Лучше добавьте индексирование:

ВЫБРАТЬ
    ВЫРАЗИТЬ(ск_ИдентификаторыОбменаВнешнегоИсточника.Объект КАК Документ.усЗаказНаОтгрузку) КАК Объект,
    ск_ИдентификаторыОбменаВнешнегоИсточника.OID КАК OID,
    ск_ИдентификаторыОбменаВнешнегоИсточника.US_OID КАК US_OID
ПОМЕСТИТЬ Идентификаторы
ИЗ
    РегистрСведений.ск_ИдентификаторыОбменаВнешнегоИсточника КАК ск_ИдентификаторыОбменаВнешнегоИсточника
ГДЕ
    ск_ИдентификаторыОбменаВнешнегоИсточника.Объект ССЫЛКА Документ.усЗаказНаОтгрузку

ИНДЕКСИРОВАТЬ ПО
    Объект

Показать

В использовании ВЫРАЗИТЬ также не уверен, что это поможет увеличить скорость выполнения, а не наоборот.

36. Release 18.08.21 19:43 Сейчас в теме

(29)

индексы не дает добавить на составной тип

Впервые о таком слышу. Может причина в установленном флаге Ведущее?
Но если так, то по данному измерению таблица индексации итак должна была создаться.

37. nazirovramzil 18.08.21 21:08 Сейчас в теме

(36) ссылка на ImgBB
такая ошибка была, но да, когда типизировал поля она пропала.
меня сейчас интересует этот запрос:

ВЫБРАТЬ
    втЗаказы.Ссылка КАК Ссылка,
    ИдентификаторыОбмена.OID КАК OID
ИЗ
    втЗаказы КАК втЗаказы
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ск_ИдентификаторыОбменаВнешнегоИсточника КАК ИдентификаторыОбмена
        ПО втЗаказы.Ссылка = ИдентификаторыОбмена.Объект
ГДЕ
    ИдентификаторыОбмена.Объект ССЫЛКА Документ.усЗаказНаОтгрузку

Показать

т.к. это сейчас единственный вариант, который отрабатывает быстрее остальных. Если он с точки зрений оптимальности хороший и не образует множественные соединения, то буду пробовать его в тестовую базу. Эх надо самому настроить тех журнал, что осматривать планы запросов.

38. Sashares 35 19.08.21 06:05 Сейчас в теме

(37) А если так:

ВЫБРАТЬ
ИдентификаторыОбмена.Объект КАК Объект,
ИдентификаторыОбмена.OID КАК OID
Поместить ТабИдентификаторы
ИЗ
РегистрСведений.ск_ИдентификаторыОбменаВнешнегоИсточника КАК ИдентификаторыОбмена
ГДЕ ИдентификаторыОбмена.Объект В (Выбрать втЗаказы.Ссылка Из втЗаказы КАК втЗаказы)
;
ВЫБРАТЬ
втЗаказы.Ссылка КАК Ссылка,
ТабИдентификаторы.OID КАК OID
ИЗ
втЗаказы КАК втЗаказы
ЛЕВОЕ СОЕДИНЕНИЕ ТабИдентификаторы КАК ТабИдентификаторы
ПО втЗаказы.Ссылка = ТабИдентификаторы.Объект

Показать

28. Release 18.08.21 18:36 Сейчас в теме

(20) Т.е. в моем примере стоимость соединения меньше, хотя стоимость просмотра таблицы больше, чем поиск по индексу. Кроме того, что насчёт Probe Residual в Hash Match? Есть или нет? Интересно бы глянуть. Я бы и сам этим занялся, но к сожалению на данный момент не имею возможности, а было бы очень интересно окончательно прояснить этот вопрос оптимизации.

40. RustamZz 19.08.21 09:00 Сейчас в теме

(28)

Прикрепленные файлы:

План с двумя таблицами.SQLPlan

50. Release 19.08.21 20:22 Сейчас в теме

(40) У меня нет сейчас доступа к SQL Server Management Studio, но всё равно спасибо. Посмотрю, как будет возможность.

9. RustamZz 18.08.21 14:01 Сейчас в теме

(7)

ВЫБРАТЬ
    втЗаказы.Ссылка,
    ИдентификаторыОбмена.OID
ИЗ
    втЗаказы КАК втЗаказы
        РегистрСведений.ск_ИдентификаторыОбменаВнешнегоИсточника КАК ИдентификаторыОбмена
        ПО втЗаказы.Ссылка = ИдентификаторыОбмена.Объект

самый оптимальный

10. nazirovramzil 18.08.21 14:02 Сейчас в теме

(9)т.е. без выразить? без ССЫЛКА?

14. soft_wind 18.08.21 14:39 Сейчас в теме

(2) это что?

ссылка она и есть ссылка, прямо по ней и ищите
вот это тоже самое

Оставьте свое сообщение

E-mail: