Переключение на Главную Страницу Страницы: 1 [2]  ОтправитьПечать
Горячая тема (более 10 ответов) Пытаюсь въехать в прямые запросы (число прочтений - 5083 )
Djelf
God Member
*****
Отсутствует


Ubuntu + wine@etersoft
+ 1C 7.7

Сообщений: 631
Местоположение: Питер
Зарегистрирован: 02. Ноября 2007
Пол: Мужской
Re: Пытаюсь въехать в прямые запросы
Ответ #15 - 24. Октября 2019 :: 16:18
Печать  
Arbuz писал(а) 24. Октября 2019 :: 10:58:
План запроса:
Код
Выбрать все
7  SCAN TABLE Журнал AS Жур2 VIRTUAL TABLE INDEX 1:ACDATETIM;    14 !" 0
13 SCAN TABLE Регистр.ПартииНаличие AS Движения2 VIRTUAL TABLE INDEX 0:IDLINE;    10 !  0p nHyd!    
18 SCAN TABLE Справочник.Партии AS Партии VIRTUAL TABLE INDEX 0:IDD;    10 !  0p nHyd! %k8
25 SCAN TABLE Регистр.ОстаткиТМЦ AS ОстаткиТМЦ VIRTUAL TABLE INDEX 0:IDLINE;    15 !  0p nHy$40t  Q  HB `8
33 SCAN TABLE Регистр.Продажи AS Продажи VIRTUAL TABLE INDEX 0:IDLINE;    20 !  0p nHy$40d  !%$9 @, 
86 USE TEMP B-TREE FOR ORDER BY 


ничего, правда, мне здесь непонятно.
Что я делаю не так? Или так и должно быть? Может мой подход в корне не верный?


Да с планом то все просто...

Сначала планировщик поменял местами Журнал и ПартииНаличие , потому что понял что так будет быстрее. Это из-за INNER JOIN, c LEFT такого не бывает. Это правильное решение Планировщика!
Затем набор запросов по индексу IDLINE = IDDOC,LINENO,ACTNO (для такого запроса это оптимальный индекс, хотя в нем используется только IDDOC)
Ну и самая тормозная часть: создание временных индексов и объединение таблиц.

Ты же вытаскиваешь ВСЮ информацию из регистров за период.
Это довольно большой объем, а объединение таблиц такого объема требует значительных затрат.

Возможно слегка ускорит замена "ON Продажи.IDDOC = Движения2.IDDOC "на "ON Продажи.IDDOC = Жур2.IDDOC" и аналогичных связей.
Просто потому что записей в Жур2.IDDOC значительно меньше чем в Движения2.IDDOC
Еще можно при сравнении "=" добавить в конце строки (после ON) COLLATE BINARY. В этом случае, отключится преобразование UTF8 в ASCII (встроенное в 1sqlite)
При сравнении "=" по идентификатору документов и т.п. это не требуется.

Я прогнал "это", на последней версии 1sqlite 3.30.0.24, "не фонтан" конечно, но и не в разы.

Увы, чтобы подобный запрос работал быстро придется перетащить нужные параметры в Реквизиты ПартииНаличие, иначе быстрее, видимо, никак.
  
Наверх
www  
IP записан
 
Arbuz
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 57
Зарегистрирован: 06. Февраля 2019
Re: Пытаюсь въехать в прямые запросы
Ответ #16 - 25. Октября 2019 :: 09:37
Печать  
Sserj писал(а) 24. Октября 2019 :: 12:56:
Если у регистра поставить флажок Быстрая обработка движений то ничего джойнить не надо будет DateTimeIDDOC будет уже в самом регистре.

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

Djelf писал(а) 24. Октября 2019 :: 16:18:
Да с планом то все просто...

Спасибо, стало немного ясней.

Djelf писал(а) 24. Октября 2019 :: 16:18:
Возможно слегка ускорит замена "ON Продажи.IDDOC = Движения2.IDDOC "на "ON Продажи.IDDOC = Жур2.IDDOC" и аналогичных связей.
Просто потому что записей в Жур2.IDDOC значительно меньше чем в Движения2.IDDOC

Не меняется время, видимо из-за того, что у меня в ON условия ещё (AND) и на другое(ие) поля в бОльшей таблице.

Djelf писал(а) 24. Октября 2019 :: 16:18:
Еще можно при сравнении "=" добавить в конце строки (после ON) COLLATE BINARY. В этом случае, отключится преобразование UTF8 в ASCII (встроенное в 1sqlite)
При сравнении "=" по идентификатору документов и т.п. это не требуется.

Вот тут странно, что не меняется время выполнения совсем и не ругается на синтаксис. COLLATE BINARY надо добавлять в конце строки ON после всех условий (ну AND, OR, etc) или после каждого элементарного условия?

Djelf писал(а) 24. Октября 2019 :: 16:18:
Ну и самая тормозная часть: создание временных индексов и объединение таблиц.

Ты же вытаскиваешь ВСЮ информацию из регистров за период.
Это довольно большой объем, а объединение таблиц такого объема требует значительных затрат.

...

Увы, чтобы подобный запрос работал быстро придется перетащить нужные параметры в Реквизиты ПартииНаличие, иначе быстрее, видимо, никак.

Я именно это и имел ввиду когда спрашивал, что может так и должно быть. Вообще, надо сказать, в типовом ТиСе регистры и ПартииНаличие в частности "сархитектурен" просто безобразно. К сожалению замена структуры регистров рассматривается мной в последнюю очередь из-за объёма базы, неохота всё перепроводить, хрен его что может вылезти за все года  Улыбка Просто, хотя бы: ни в одном регистре, кроме партий, не привязываются строки, что не даёт однозначно связать движения из разных регистров, хотя может я и не прав - это не нужно. т.е. я пока осторожно пробую возможности прямых запросов. И мне очень нравится. 1sqlite - просто шикарен. Текущая реализация SQL в скулайте очень хороша, все эти оконные функции, фильтра, группировки - глаза разбегаются и слюна течёт. Смех

Теперь по делу. Вот такой запрос делает тоже самое, но на порядок быстрее предыдущего. Подмигивание вкратце, я джойню не регистры, а подзапросы из них
Код (SQL)
Выбрать все
	|--EXPLAIN QUERY PLAN
	|SELECT
	|	Д1.Номенклатура [Номенклатура :Справочник.Номенклатура]
	|	,Д1.LINENO LINENO
	|	,Д1.Фирма [Фирма :Справочник.Фирмы]
	|	,Д1.МОЛ [МОЛ :Справочник.ФизЛица]
	|	,О1.Склад [Склад :Справочник.Склады]
	|	,Д1.Поставщик [Поставщик :Справочник.Контрагенты]
	|	,П1.Покупатель [Покупатель :Справочник.Контрагенты]
	|	,Д1.Движение
	|	,Д1.Приход
	|	,Д1.Расход
	|	,Д1.СуммаУпр
	|	,Д1.Партия [Партия :Справочник.Партии]
	|	,Д1.ДатаПартии
	|	,Д1.СтатусПартии [СтатусПартии :Перечисление.СтатусыПартии]
	|	,П1.Себестоимость
	|	,П1.Продстоимость
	|	,Д1.Документ [Документ :Документ]
	|FROM (
	|	SELECT
	|		Движения2.Номенклатура Номенклатура
	|		,Движения2.LINENO LINENO
	|		,Движения2.Фирма Фирма
	|		,Движения2.МОЛ МОЛ
	|		,Партии.Поставщик Поставщик
	|		,Движения2.Количество * (1 - Движения2.DEBKRED * 2) Движение
	|		,Движения2.Количество * (1-Движения2.DEBKRED) Приход
	|		,Движения2.Количество * Движения2.DEBKRED Расход
	|		,Движения2.СуммаУпр СуммаУпр
	|		,Движения2.Партия Партия
	|		,Движения2.ДатаПартии ДатаПартии
	|		,Движения2.СтатусПартии СтатусПартии
	|		,Жур2.IDDOCDEF||Движения2.IDDOC Документ
	|
	|	FROM
	|		[Регистр.ПартииНаличие] Движения2
	|	INNER JOIN [Журнал] Жур2 ON Жур2.IDDOC = Движения2.IDDOC AND Жур2.DATE BETWEEN :Дата1 AND :Дата2
	|	LEFT JOIN [Справочник.Партии] Партии ON Партии.ID = Движения2.Партия
	|) Д1
	|LEFT JOIN (
	|	SELECT
	|		ОстаткиТМЦ.Номенклатура
	|		,ОстаткиТМЦ.Фирма
	|		,ОстаткиТМЦ.Склад
	|		,Жур.IDDOCDEF||ОстаткиТМЦ.IDDOC Документ
	|	FROM
	|		[Регистр.ОстаткиТМЦ] ОстаткиТМЦ
	|	INNER JOIN [Журнал] Жур ON Жур.IDDOC = ОстаткиТМЦ.IDDOC AND Жур.DATE BETWEEN :Дата1 AND :Дата2
	|) О1 ON Д1.Документ = О1.Документ AND Д1.Номенклатура = О1.Номенклатура
	|LEFT JOIN (
	|	SELECT
	|		Продажи.Номенклатура
	|		,Продажи.LINENO
	|		,Продажи.Фирма
	|		,Продажи.Поставщик
	|		,Продажи.Покупатель
	|		,Продажи.Себестоимость
	|		,Продажи.ПродСтоимость
	|		,Жур3.IDDOCDEF||Продажи.IDDOC Документ
	|	FROM
	|		[Регистр.Продажи] Продажи
	|	INNER JOIN [Журнал] Жур3 ON Жур3.IDDOC = Продажи.IDDOC AND Жур3.DATE BETWEEN :Дата1 AND :Дата2
	|) П1 ON Д1.Документ = П1.Документ AND Д1.Номенклатура = П1.Номенклатура AND Д1.Поставщик = П1.Поставщик
	|ORDER BY Д1.Номенклатура, Д1.Партия, Д1.LINENO
 



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

добавлено: из двух последних подзапросов нужно выкинуть неиспользуемые поля LINENO и Фирма, что несколько уменьшает RAM
  
Наверх
 
IP записан
 
Djelf
God Member
*****
Отсутствует


Ubuntu + wine@etersoft
+ 1C 7.7

Сообщений: 631
Местоположение: Питер
Зарегистрирован: 02. Ноября 2007
Пол: Мужской
Re: Пытаюсь въехать в прямые запросы
Ответ #17 - 25. Октября 2019 :: 09:57
Печать  
Arbuz писал(а) 25. Октября 2019 :: 09:37:
Вот тут странно, что не меняется время выполнения совсем и не ругается на синтаксис. COLLATE BINARY надо добавлять в конце строки ON после всех условий (ну AND, OR, etc) или после каждого элементарного условия?

Писать надо вот так:
ON Д1.Документ = О1.Документ AND Д1.Номенклатура = О1.Номенклатура  COLLATE BINARY

Возможно без указания COLLATE BINARY при группировке не попадает в COLLATE _1C, а может и попадает, но я весь COLLATE _1C переписал, он совсем немного уже уступает COLLATE BINARY.
  
Наверх
www  
IP записан
 
Djelf
God Member
*****
Отсутствует


Ubuntu + wine@etersoft
+ 1C 7.7

Сообщений: 631
Местоположение: Питер
Зарегистрирован: 02. Ноября 2007
Пол: Мужской
Re: Пытаюсь въехать в прямые запросы
Ответ #18 - 25. Октября 2019 :: 10:19
Печать  
Arbuz писал(а) 25. Октября 2019 :: 09:37:
добавлено: что, на мой взгляд, несколько странно. по идее должно быть наоборот, кучка подзапросов должна медленнее работать чем сразу джойны к индексированным таблицам, разве не?


Насчет группировки я не сильно прав был.
Запрос выполняется чуток по другому.
Вот кусок полного плана.
Выделенный кусок выполняется в цикле.
Т.е. выше мы получили список всех iddoc и товаров.
А потом для каждого iddoc и товара просматриваем три таблицы последовательно.
Т.е. приходится дергать индекс.
В твоем втором случае надо смотреть отличие...
  
Наверх
www  
IP записан
 
Arbuz
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 57
Зарегистрирован: 06. Февраля 2019
Re: Пытаюсь въехать в прямые запросы
Ответ #19 - 25. Октября 2019 :: 11:25
Печать  
насколько я понял из этого
https://yadi.sk/i/Y7PeFWHPU9njEw
сначала собрал подзапрос журнал-остатки, видимо как самую маленькую из таблиц и поместил во временную таблицу, не знаю, что такое MATERIALIZE
потом журнал-продажи и тоже MATERIALIZE
потом журнал-партииНаличие-партии
затем сделал SEARCH по каждой "временной таблице", причём "USING AUTOMATIC COVERING INDEX", чтобы это не значило.
затем отсортировал
  
Наверх
 
IP записан
 
Djelf
God Member
*****
Отсутствует


Ubuntu + wine@etersoft
+ 1C 7.7

Сообщений: 631
Местоположение: Питер
Зарегистрирован: 02. Ноября 2007
Пол: Мужской
Re: Пытаюсь въехать в прямые запросы
Ответ #20 - 25. Октября 2019 :: 11:44
Печать  
MATERIALIZE создает временную таблицу
COVERING INDEX - покрывающий индекс, индекс по нескольким полям

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

В принципе можно еще попробовать через union all попробовать объединять таблицы...
  
Наверх
www  
IP записан
 
Arbuz
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 57
Зарегистрирован: 06. Февраля 2019
Re: Пытаюсь въехать в прямые запросы
Ответ #21 - 25. Октября 2019 :: 11:49
Печать  
сначала я опробовал именно через UNION ALL, тоже быстро, но я не понял как сгруппировать потом, чтобы получить ту же структуру таблицы на выходе
  
Наверх
 
IP записан
 
Djelf
God Member
*****
Отсутствует


Ubuntu + wine@etersoft
+ 1C 7.7

Сообщений: 631
Местоположение: Питер
Зарегистрирован: 02. Ноября 2007
Пол: Мужской
Re: Пытаюсь въехать в прямые запросы
Ответ #22 - 25. Октября 2019 :: 12:22
Печать  
Arbuz писал(а) 25. Октября 2019 :: 11:49:
сначала я опробовал именно через UNION ALL, тоже быстро, но я не понял как сгруппировать потом, чтобы получить ту же структуру таблицы на выходе


Творчески Подмигивание

Ну вот мы знаем что при группировке
GROUP BY Выборка.Номенклатура,Выборка.Фирма,Выборка.Документ
Склад у нас фактически уникальный
Но не везде он есть. Где нет, заполняем как NULL
Все UNION во вложенной таблице Выборка
А в первом SELECT пишем max(Выборка.Склад), или min, NULL туда не попадет ни так ни сяк.
« Последняя редакция: 25. Октября 2019 :: 14:39 - Djelf »  
Наверх
www  
IP записан
 
Переключение на Главную Страницу Страницы: 1 [2] 
ОтправитьПечать