Есть регистр в котором хранятся цены. Много. База SQL. Регистр большой и встаёт проблема скорости получения данных из него.

В регистре есть три измерения Номенклатура, ТипЦен, Регион и реквизиты Цена, ДатаЦены. Ресурсов нет.
1С создаёт для регистра индексы
#----Indexes------
# Name                           |Descr         |Unique|Indexed fields
I=PK_RA47241                     |of IDDOC+LineN|1     |IDDOC,LINENO_,ACTNO
I=DATETIME                       |Date+Time+IDOC|1     |DATE_TIME_IDDOC,LINENO_,ACTNO
I=VIA58286                       |VIA58286      |1     |SP58286,DATE_TIME_IDDOC,LINENO_,ACTNO
I=VIA58287                       |VIA58287      |1     |SP58287,DATE_TIME_IDDOC,LINENO_,ACTNO
и не создаёт самого нужного - Номенклатура+ТипЦен+Регион+Дата

Попытался ускорить выборку прямым запросом, но плохо получилось. Прямой запрос работает даже медленней, чем выборка через объект регистр. В процессе работы быстродействие прямого запроса выходит на уровень выборки данных через объект Регистр, возможно из-за кеширования и накопления статистики SQLем. Но хотелось бы всё же получить повышение быстродействия.

Прямой запрос выглядит так:
|SELECT TOP 1
|CAST(LEFT(рег.DATE_TIME_IDDOC, 8) as DateTime) as Дата,
|$рег.Цена as Цена,
|$рег.Номенклатура as Номенклатура
|from $Регистр.ЦеныНоменклатуры as рег
|WHERE
|$рег.Номенклатура = ? AND $рег.ТипЦен = ? AND $рег.Регион = ? AND рег.DATE_TIME_IDDOC <= ?
|order by $рег.Номенклатура, $рег.ТипЦен, $рег.Регион, рег.DATE_TIME_IDDOC desc

Можно ли тут выжать еще скорости?

какой кластерный индекс ?

+(1)
извиняюсь. увидел.PK_RA47241

создайте индекс сами.

тебе для списка номенклатуры надо или для 1 позиции?
если для позиции, то нахрена сортировка такая сложная? достаточно по DATE_TIME_IDDOC

+(3)
сделайте индекс сразу прокрывающим.

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

SELECT
    $РегТПС.Товар [Товар $Справочник.Номенклатура]
,    SUM(CASE WHEN РегТПС.DebKred = 1 THEN $РегТПС.ОстатокТовара ELSE 0 END) КолРасход
,    SUM(CASE WHEN РегТПС.DebKred = 1 THEN $РегТПС.Стоимость ELSE 0 END) СумРасход
FROM
    _1sJourn Журнал
INNER JOIN
    $Регистр.ТоварыПоСкладам РегТПС ON РегТПС.IDDoc = Журнал.IDDoc
WHERE
    Журнал.Date_Time_IDDoc BETWEEN :ДатаНач AND :ДатаКон~
    AND
       (Журнал.Closed & 1 = 1)
        AND
    Журнал.RF" + Мета.ИДРегистра("ТоварыПоСкладам") + " = 1
    AND
    $РегТПС.ВидДвижения IN (" + Мета.ПолучитьСтрокуИзСЗ(спДв) + ")

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

ща придут спецы и навтыкают...

Вот это не надо, в регистр и так только проведенные попадают.
       (Журнал.Closed & 1 = 1)

Вот это тоже вряд ли надо.
    Журнал.RF" + Мета.ИДРегистра("ТоварыПоСкладам") + " = 1

Ив зависимости от включения документа в разные компоненты (бух учет , оперучет, расчет)

тут может быть разный признак проведенности

(Журнал.Closed & 1 = 1)

это основано на запросах выловленных из профайлера
rf там точно был, а closed не помню точно, медленнее точно не становится

(9) мимо, & решает вопрос вне зависимости от бух / опер / расчет

Ну и если нужно сильно быстро, можно включить флаг Бвстрая обработка движений, тогда не нужен будет join с журналом. Поле Date_Time_IDDoc появится в таблице движений.

Мне не нужно никаких джойнов и журналов документов. Задача простая - выбрать из движений регистров последнюю запись (с учетом номенклатуры, типа цены, региона).
1С для таблицы движений регистра не создаёт индекса, как для таблицы итогов, по всем измерениям. Только отдельно по каждому измерению/ресурсу, у которого стоит галочка "Отбор движений". Поэтому, как я понимаю, sql сначала делает index seek по значению номенклатуры, потом небыстрый index scan, перебирая все движения по этой номенклатуре.
Можно ли что-то сделать в конфигураторе, что бы он создал более подходящий индекс, я не знаю.

(12) с быстрой обработкой все равно медленнее, проверял на регистре с этой галкой - что собственно и удивило
(13) тебе выложили готовое решение - тупо проверь

В конфигураторе нет. Как сказали в (3) Можешь создать сам в скуле.

(3) "создайте индекс сами"
1С будет его удалять при каждой реструктуризации. Придется его регулярно создавать заново. А у меня тут баз полтеррабайта. :( Можно еще, говорят, как-то извратиться, что-то поправить в системных хранимых процедурах, что бы 1Ска не видела пользовательских индексов, но на 2005 SQL что-то не получилось. Пробовал, но видно протупил где-то.
(4) "если для позиции, то нахрена сортировка такая сложная? достаточно по DATE_TIME_IDDOC"
У меня цены хранятся в регистре. И нужно выбрать последнюю (ранее указанной даты) цену по номенклатуре/типу цен/региону. DATE_TIME_IDDOC использую только для того, что бы получить дату цены, не обращаясь к журналу документов.

(16) для ТОР 1, фильтра по номенклатуре и получения ПОСЛЕДНЕГО  значения у тебя первым и единственным значением сортировки должна быть дата

(0)
1.сортировку убери
2.поставить еще условие
рег.DATE_TIME_IDDOC >= НачалаМесяца (если период итогов = Месяц ) это тоже ускорит запрос
3. можно включить хинт (nolock) но это нужно вникать
можно ли это делать - зависит от задачи.

+18  первое условие не нужно
но сортировку осавить по дате

(12) Включена уже быстрая обработка движений. И прямой запрос только к одной таблице. Но всё равно медленней, чем надо.
(14) "тебе выложили готовое решение - тупо проверь"
Там джойн с журналом. Тормоза будут вообще офигительные.
(15) Видно придётся с этим мудрить.
ВОПРОС! Ни у кого нет опыта в патче SQL 2005, что бы 1С созданных вручную индексов не видела и не удаляла?
(17) Пробовал оставить только сортировку по дате. На быстродействие никак не повлияло. Единственное, в плане запроса одна стрелочка из жирной превратилась в тонкую, но больше ничего не изменилось.
(18) "поставить еще условие
рег.DATE_TIME_IDDOC >= НачалаМесяца (если период итогов = Месяц )"
период итогов не интересен, у меня обращение исключительно к движениям регистра. Ресурсов у регистра нет, период итогов вообще не имеет смысла.
"можно включить хинт"
Вот тут интересно подробней.А нет ли каких хинтов, что бы в этой ситуации SQL побыстрее отработал? Я в хинтах абсолютно не разбираюсь.

(20) не тупи, просто проверь
я же написал, что решение странное, но быстрое

(20) и вообще странно немного - с сортировкой разобраться не можешь, а решение в планах запросов и построении собственных индексов ищешь.

(20) Создать свой индекс, наверно, более верный вариант.
Патчить sql не нужно.
Достаточно в dds после приведенных в (0) строк добавить описание своего индекса, типа:
I=MyIndexPK_RA47241|мой супер индекс|1     |SPxxx1,SPxxx2,SPxxx3,SPxxx4,DATE_TIME_IDDOC
тогда при реструктуризации он удалятся не будет.
Для того чтобы после сохранения md и перестроения dds эта запись появлялась автоматически есть примочка к openconf

select * from $Регистр.ЦеныНоменклатуры
|CAST(LEFT(рег.DATE_TIME_IDDOC, 8) as DateTime) as Дата,
|$рег.Цена as Цена,
|$рег.Номенклатура as Номенклатура
|from $Регистр.ЦеныНоменклатуры as рег (nolock)
|WHERE
|$рег.Номенклатура = :Зн_Товар AND $рег.ТипЦен = :Зн_Цена AND $рег.Регион = :Зн_Регион
|and рег.DATE_TIME_IDDOC = (
|  select  max(рег1.DATE_TIME_IDDOC) from $Регистр.ЦеныНоменклатуры as рег1
|  WHERE
|  $рег1.Номенклатура = :Зн_Товар AND $рег1.ТипЦен = :Зн_Цена AND $рег1.Регион = :Зн_Регион
|)

(23) у меня почему то индекс сноситься ( при смене конфигурации ) но потом после внесения индекса в dds
и заново создание индекса все ок.
Так как смена конфигурации нечасто не очень сильно обременяет.

(19) "первое условие не нужно"
Какое? Это что ли "$рег.Номенклатура = ?" ???
Мне нужно выбрать движения регистра с отбором по номенклатуре, типу цен и региону. Все эти три отбора указывают в where.
(21) "не тупи, просто проверь я же написал, что решение странное, но быстрое"
Не подходит такое решение. Ты выбираешь документы за период из журнала, по ним ищешь движения.
В моём случае будет из журнала выбрано порядка полусотни документов (с учетом возможных отборов по фирме и группе типов цен), каждый делает тысячи движений по регистру. До 10-12 тысяч движений в самом худшем случае.
SQL повесится по такому объему выбирать движения с отбором по DATE_TIME_IDDOC.

(26) упертый однако
сдаюсь

эм... бывает, что having работает быстрее, чем where

+(23)
DDX для Опенконфа

(0) Прямые запросы не знаю, попробуй что-то в таком духе:


SELECT *
FROM Регистр
JOIN
  (SELECT max(ДатаЦены) AS МаксДата
   FROM Регистр
   WHERE Номенклатура = $Номенклатура
     AND ТипЦен = $ТипЦен
     AND Регион = $Регион
     AND ДатаЦены <= $ДатаЦены) AS МД ON Номенклатура = $Номенклатура
AND ТипЦен = $ТипЦен
AND Регион = $Регион
AND ДатаЦены = МД.МаксДата

(30) только join надо заменить на inner join
и тогда получим тоже самое что и в (24)

(27) Попробовал я твой вариант, не переживай :)
Всего-то в полтора раза медленнее моего.
(23) А ты случайно не в курсе, в РБД она работает, когда конфигурация загружается автоматически при обмене?
(24)(31) Чуть медленней, чем в (6)

(0) Ну а теперь посмотри план выполнения запроса
Убери топ 1 и упорядочивание и еще раз посмотри
Теперь много думать :)

(32) что то не понимаю как можно сравнивать (24) и (6)

(31) Нет, не то же самое.
(33) Там со временем время выполнения будет только нарастать.

(20)
есть опыт.
используйте ddl триггеры

(33) Сейчас пробую вот такой вариант запроса:
|SELECT
|   SUM($РегЦены.Цена) Цена,
|   $РегЦены.Номенклатура as [Номенклатура $Справочник.Номенклатура]
|FROM
|   _1sJourn Журнал
|INNER
|   JOIN $Регистр.ЦеныНоменклатуры РегЦены ON РегЦены.IDDoc = Журнал.IDDoc
|WHERE
|   (Журнал.Date_Time_IDDoc BETWEEN :ДатаНач AND :ДатаКон~)
|   AND (Журнал.Closed & 1 = 1)
|   AND (Журнал.RF" + глMDW.ИДРегистра("ЦеныНоменклатуры") + " = 1)
|   AND $РегЦены.Номенклатура = :Номенклатура
|   AND $РегЦены.ТипЦен = :ТипЦен
|   AND $РегЦены.Регион = :Регион
|  and РегЦены.DATE_TIME_IDDOC = (
|    select  max(рег1.DATE_TIME_IDDOC) from $Регистр.ЦеныНоменклатуры as рег1
|    WHERE
|       $рег1.Номенклатура = :Номенклатура AND $рег1.ТипЦен = :ТипЦен AND $рег1.Регион = :Регион
|  )
|group by $РегЦены.Номенклатура
выполняется в лучшем случае за 0.0023 секунды. Долговато.

(36) А можно подробней? Видел описание, как внести изменения в хранимые процедуры SQL2005, что бы 1С не видела определенных индексов с определенным префиксом в наименовании. Но не получилось внести изменения в хранимые процедуры. И запускал в однопользовательском режиме, и ещё какие-то советы из интернета выполнял - все равно только на просмотр были нужные хранимые процедуры.

>выполняется в лучшем случае за 0.0023 секунды. Долговато
имхо капец сравнение. при таких замерах тупо лежит вся выборка в кэше или нет дает слишком большой разбег.
Т.е. разница 1 сек - это еще можно за показатель принять, но разница в 0,01 сек - сомнительное сравнение

(0) Регистр сами ваяли или как ?..

(32) " А ты случайно не в курсе, в РБД она работает, когда конфигурация загружается автоматически при обмене? "
В курсе, не работает

(40) Регистр сам проектировал. Есть заметно лучшие варианты? Справочник мне не подходит в связи с наличием РБД. У справочника единая настройка миграции, а у регистра вместе с документом.

(33)

замените
(Журнал.Closed & 1 = 1)
на
(Журнал.Closed in (1,3,5,7))

чтобы использовать поле Closed в индексах

не понял, зачем соединение с журналом , если поле DATE_TIME_IDDOC есть в таблице движений

(42) У справочника не единая миграция. Более того, имея скульную базу можно сделать миграцию по схеме звезды, многоуровневую структуру и пр.
Вопросы:
- почему разделили измерения и реквизиты? Как по мне то все в измерениях хранить, темболее что почти одинаковый функционал.
- стоят галочки "отбор движений" ?

+45 - ну и галочки "быстрая обработка движений" тоже нада включить.
- зачем ДатаЦены? Ведь дата есть в документе. Или у вас есть случаи установки цен будущей датой?

(45)(46) Со справочником сложнее получалось оранизовать нужную миграцию - что бы цены уходили только на те ПБ, куда предназначены. С регистром получилось всё штатными средствами.
"Более того, имея скульную базу можно сделать миграцию..."
К сожалению, у меня не на всех филиалах SQL.
"почему разделили измерения и реквизиты"
Для красоты. Структура таблицы и возможности создавать индексы, как я понимаю, все равно получается одинаковой, хоть измерением делай, хоть реквизитом.
Галочки "Отбор движений" и "Быстрая обработка движений" стоят.
ДатаЦены нужна для того, что бы на DBFных периферийках, где выборка цен идет через объект Регистры, получать дату найденной цены не через .ТекущийДокумент.ДатаДок. ДатаЦены всегда совпадает с ДатаДок, но лишнее обращение к журналу мне было не к чему.

(42) Для начала можешь
из измерений сделать реквизиты.
В том числе и цена.
Этим самым значительно сократится размер таблицы rg47241 и индексов по ней.

Ну а далее как бы два пути
Путь 1. Создавать на описанной таблице sql индекс
я даже бы сказал уникальный индекс
Путь 2. получившиеся реквизиты без цены упаковываем
в один реквизит поле char(27) ( Товар,ТипЦен,Регион )
и включив быстрые движения получишь стандартный индекс 1с
т.е твой регист будет :
реквизит1 : Стр_Товар_ТипЦены_Регион  - строка 27
реквизит2 : Цена - число

Т.к у тебя УРБД то на мой взгляд второй путь предпочтительней

(48) "Этим самым значительно сократится размер таблицы rg47241 и индексов по ней"
У меня в регистре нет ни одного ресурса. Таблица rg47241 нулевого размера.
"Создавать на описанной таблице sql индекс"
Планирую этим путём и пойти. Осталось только выяснить, как на SQL2005+ создать индекс, что бы 1С его не удаляла при реструктуризации.
"получившиеся реквизиты без цены упаковываем
в один реквизит поле "
Вполне вариант. Если не получится первый вариант, буду пробовать этот.

(23) На нераспределенной базе все получилось(руками без скрипта)

как измениться скрипт ddx для УРБД если смена конфигурации
в переферийной базе с реорганизацией данных.
Или может надо на каком то диалоге вручную поменять dds
в этом тоже ничего сложного - это все таки лучше чем пересоздавать индекс

Вторая идея похожая на эту же.Может кто делал?
некоторые поля int на tinyint
в dds это замена типа I  на тип Y.
У меня все работало но только с рестуктаризацией
данных.Если это побороть то будет круто
(может быть у меня и не получилось без рестуктаризации
из-за каких то особенностей смены конфигурации в ПБ УРБД)
Т.е. цель заменить некоторые поля в таблицах
с  int sp102  на   tinyint sp102 и чтобы это соответсвие
не нарушалось при смене конфигурации с реорганизацией данных.
есть еще несколько мыслей но хотелось бы сначала сделать tinyint

(50) "как измениться скрипт ddx для УРБД если смена конфигурации
в переферийной базе с реорганизацией данных.
Или может надо на каком то диалоге вручную поменять dds
в этом тоже ничего сложного - это все таки лучше чем пересоздавать индекс "
При ручной загрузке можно попробовать на диалоге "Загрузка успешно завершена!" вызвать ProcessDD() скрипта, но точно не знаю про УРБД