Переключение на Главную Страницу Страницы: [1] 2 3  ОтправитьПечать
Очень популярная тема (более 25 ответов) Тема Использование индексов (ДБФ) часть 2. (число прочтений - 26354 )
kiruha
1c++ power user
Отсутствует



Сообщений: 1249
Зарегистрирован: 11. Апреля 2007
Тема Использование индексов (ДБФ) часть 2.
13. Июля 2007 :: 09:08
Печать  
Эта тема является продолжением http://www.1cpp.ru/forum/YaBB.pl?num=1176287559
(использование индексов)
Если кратко – то было установлено, что драйвер FoxPro не использует индексы 1С,
из за чего не наблюдается существенного ускорения при наложении фильтров.

Было экспериментально установлено, что использование
Collating Sequence=MACHINE ускоряет работу в среднем в 3-4 раза.
Рекомендация вошла в FAQ по прямым запросам.
Данная настройка у меня используется уже 3 месяца в живой базе –
ускорение подтверждаю, глюков не замечено.

Тем не менее.
Метод оптимизации по индексу обещает ускорение до 10 000 раз – с ускорением в 3 раза это как то не вяжется.
Также можно установить индекс по товару в регистре (например «Продажи», галочку у «Отбор движений») и сравнить время выполнения запроса с условием

Код
Выбрать все
WHERE $РегПродажи.Товар = :ВыбТовар 



Есть ли индекс, нет ли индекса – время выполнения не меняется.
  
Наверх
 
IP записан
 
kiruha
1c++ power user
Отсутствует



Сообщений: 1249
Зарегистрирован: 11. Апреля 2007
Re: Тема Использование индексов (ДБФ) часть 2.
Ответ #1 - 13. Июля 2007 :: 09:10
Печать  
Структура индексов в 1С.
Чтобы посмотреть структуру индексов в 1С понадобится установить FoxPro 9.
Ассоциируем программу с файлами ДБФ при установке.
Щелкаем по файлу (таблице) ДБФ, например 1SJOURN.
FoxPro спросит Code Page (кодовую страницу).
Выбираем «1251 Russian Windows».
Будет открыта таблица.
Далее идем в меню, путь «View / Table Designer».
Откроется окно «структуры таблицы», выбираем закладку Indexes и изучаем (см. Картинку)

Что мы видим?
Order (сортировка) – возрастание.
Collate – Machine (т.е. действительно Machine, а не Russian или что то другое).
Самое интересное Expression – собственно выражения по которым строится индекс.
В 1С они практически всегда - строковые сложные выражения.
Исключение IDDOC – строковое простое.

Например расммотрим индекс ACDATETIM (по дате в журнале)
Программист 1С ожидает , что это
    - просто «дата».
В файле 1CV7.DD написано
   -«ACDATETIM | Date+Time+ID  |0     |DATE,TIME,IDDOC»
Фактически же это
  - «DTOS(date)+time+iddoc»
  

2.jpg ( 77 KB | Загрузки )
2.jpg
Наверх
 
IP записан
 
kiruha
1c++ power user
Отсутствует



Сообщений: 1249
Зарегистрирован: 11. Апреля 2007
Re: Тема Использование индексов (ДБФ) часть 2.
Ответ #2 - 13. Июля 2007 :: 09:13
Печать  
Оптимизация(использование индеков) в FoxPro (продолжение)

В FoxPro как и в 1С допускается хранение индексов в файлах CDX.
CDX файл это не индекс , а «контейнер» хранящий несколько индексов по соответствующей таблице.

Русскоязычная статья  по оптимизации – http://www.foxclub.ru/articles/index.php?id=45
(допонительно кому нужно «Русский Help для Visual FoxPro» включая функции
http://www.foxclub.ru/rhproject/project/ )
Основная статья по оптимизации (к сожалению на англ.)
"Using Rushmore Query Optimization to Speed Data Access"
http://msdn2.microsoft.com/en-us/library/1f5d2sa3(VS.80).aspx

Наиболее важные моменты для оптимизации языка запросов.
Оптимизации поддаются предложения WHERE, JOIN, ORDER BY.

Создание базового Optimizable выражений
Основные оптимизируемые выражения могут быть одной из двух следующих форм :
eIndex relOp eExp
- или
eExpr relOp eIndex
Где
•      eIndex  - полностью соответствуют выражению в индексе.
•      eExpr   - является любое выражение и может включать переменные и поля от других, не связанных таблиц.
•      relOp только следующие реляционные операторы: <, >, =, <=, >=, <>, #, ==, or != Вы также можете использовать ISNULL( ) , BETWEEN( ) , or INLIST( ) функции (или их эквиваленты SQL, как IS NULL и т.д.).

Очень важно понять, когда запросы будут оптимизированы и, когда они не будут. Visual FoxPro оптимизирует поиск условий, найдя точное соответствие между левой части выражение фильтра и индекса.
Если условие является сложным(много условий на таблицу), то такое сложное условие также может быть оптимизировано.

Результат (оптимизация) при соединении выражений.

Basic Expression 1      Operator      Basic Expression 2      Query Result
Optimizable      AND      Optimizable      Fully Optimizable
Optimizable      OR      Optimizable      Fully Optimizable
Optimizable      AND      Not Optimizable      Partially Optimizable
Optimizable      OR      Not Optimizable      Not Optimizable
Not Optimizable      AND      Not Optimizable      Not Optimizable
Not Optimizable      OR      Not Optimizable      Not Optimizable
—      NOT      Optimizable      Fully Optimizable
—      NOT      Not Optimizable      Not Optimizable

Рекомендации – первое условие должно быть полностью оптимизируемым, второе – желательно.
При соединении нескольких таблиц – первой должна быть наиболее оптимизируемая таблица с соответствующими оптимизирующими выражениями.
  
Наверх
 
IP записан
 
kiruha
1c++ power user
Отсутствует



Сообщений: 1249
Зарегистрирован: 11. Апреля 2007
Re: Тема Использование индексов (ДБФ) часть 2.
Ответ #3 - 13. Июля 2007 :: 09:14
Печать  
Применение в 1С. (продолжение)

Вы должны посмотреть структуру индекса, который хотите использовать, в FoxPro.

Если FoxPro нету, то смотрим в 1СV7.DD "Indexed fields"
через запятую указаны поля. Нужно просто запятые заменить на "+" , если поле имеет тип DATE то
это поле преобразуется в строку при помощи DTOS(), если в скобках Upper то поле преобразуется функцией
Upper(), если число(Numeric) то функ. STR(ВыбПоле,Width) где Width размер числового поля,
в остальных случаях(ссылки ) без функций.


Например мы хотим выбрать документы из 1SJOURN за период .
Подходящий индекс – ACDATETIM (дата/время) : «DTOS(date)+time+iddoc»
Если использовать
Код
Выбрать все
|WHERE	Жур.Date BETWEEN :НачДата~~ AND :КонДата~~ 


то оптимизатор не найдет индекса, так как «Жур.Date»  не соответствует «DTOS(date)+time+iddoc»
     К счастью FoxPro поддерживает упорядочение строк – и можно использовать оператор          BETWEEN .
Длина строки «time+iddoc» равна 15.
Определим переменные МинСтрока= "               "
                                        МаксСтрока= "ZZZZZZZZZZZZZZZ"
Собственно условие
Код
Выбрать все
WHERE   DTOS(date)+time+iddoc BETWEEN (DTOS(:НачДата~~)+'		   ') AND (DTOS(:КонДата~~)+'ZZZZZZZZZZZZZZZ') 


(для простоты переменные МинСтрока и МаксСтрока не используются)
* kiruha отредактировано 13 июля 15:09
« Последняя редакция: 13. Июля 2007 :: 11:16 - kiruha »  
Наверх
 
IP записан
 
kiruha
1c++ power user
Отсутствует



Сообщений: 1249
Зарегистрирован: 11. Апреля 2007
Re: Тема Использование индексов (ДБФ) часть 2.
Ответ #4 - 13. Июля 2007 :: 09:18
Печать  
Тест
Исходный запрос.
|
Код
Выбрать все
SELECT
	|   Жур.$ОбщийРеквизит.Фирма as Фирма ,
	|   Жур.iddoc,
	|   Жур.IDDocDef
	|FROM
	|   1sjourn as Жур
	|
	|WHERE
	|   Жур.Date BETWEEN :НачДата~~ AND :КонДата~~
	| "; 



Модифицированный

Код
Выбрать все
 |SELECT
	|Жур.$ОбщийРеквизит.Фирма as Фирма ,
	|Жур.iddoc,
	|Жур.IDDocDef,
	| DTOS(date)+time+iddoc  as Индекс
	|FROM
	|1sjourn as Жур
	|
	|		WHERE
	| DTOS(date)+time+iddoc BETWEEN (DTOS(:НачДата~~)+'		   ') AND (DTOS(:КонДата~~)+'ZZZZZZZZZZZZZZZ')
	|
	|ORDER BY  Индекс
    | "; 


Испытание в живой базе, выборка за 1 день.
Первый – 2.5 сек
Второй -  0.015
Разница – в 150 раз.

P.S.
Данное исследование потребовало от меня много сил и времени (читай денег) , если не трудно – при использовании упоминайте автора Улыбка
в предыдущем случае (с Collating Sequence=MACHINE) этого не было Улыбка

Также остались нерассмотренными сложные запросы с соединением
таблиц и с переменными динамическими условиями (требуется ручная настройка условий для использования индексов – оптимизация запроса) – если кто присоединится к изучению этого вопроса – будет здорово Улыбка

Хотел прикрепить статью - но прав нету.
  
Наверх
 
IP записан
 
kiruha
1c++ power user
Отсутствует



Сообщений: 1249
Зарегистрирован: 11. Апреля 2007
Re: Тема Использование индексов (ДБФ) часть 2.
Ответ #5 - 13. Июля 2007 :: 10:41
Печать  
Еще пример.
Запрос к регистру остатков на ТА.

У регистров остатков всегда есть индекс вида
DTOS(period)+измерение1+измерение2+....

Типичная задача - остатки товара при заданной фирме и складу
Код
Выбрать все
| SELECT
	|		$РегОстатков.Товар as Товар,
	|		$РегОстатков.ОстатокТовара as ОстатокНаСкладе
	|		FROM
	|			$РегистрИтоги.ОстаткиТоваров as РегОстатков
	|		WHERE
	|			РегОстатков.PERIOD = :ДатаПериодаОстатков~~
	|	     AND ($РегОстатков.Фирма=:выбФирма)
	|	     AND ($РегОстатков.Товар=:ВыбНоменкл)
	|	     AND ($РегОстатков.Склад=:выбСклад)
 



Оптимизированный по индексу
Код
Выбрать все
	| SELECT
	|		$РегОстатков.Товар as Товар,
	|		$РегОстатков.ОстатокТовара as ОстатокНаСкладе
	|		FROM
	|			$РегистрИтоги.ОстаткиТоваров as РегОстатков
	|		WHERE
	|			д = DTOS(:ДатаПериодаОстатков~~)+:выбФирма+:ВыбНоменкл+:выбСклад
 



Первый - 0.45 сек
Второй - 0.005 сек
  
Наверх
 
IP записан
 
spock
1c++ developer
1c++ moderator
Отсутствует



Сообщений: 822
Местоположение: Новосибирск
Зарегистрирован: 19. Мая 2006
Пол: Мужской
Re: Тема Использование индексов (ДБФ) часть 2.
Ответ #6 - 13. Июля 2007 :: 11:07
Печать  
В 150 и 90 раз?  Ужас
1С перед каждым замером закрывалась/открывалась?
  
Наверх
ICQ  
IP записан
 
kiruha
1c++ power user
Отсутствует



Сообщений: 1249
Зарегистрирован: 11. Апреля 2007
Re: Тема Использование индексов (ДБФ) часть 2.
Ответ #7 - 13. Июля 2007 :: 11:24
Печать  
spock писал(а) 13. Июля 2007 :: 11:07:
В 150 и 90 раз?  Ужас
1С перед каждым замером закрывалась/открывалась?


Измерения проводились на живой базе, которая хранится на сервере.
Работает сейчас 24 чел.
На тестовой базе на локальном диске отличия существенно меньше - видимо
из за того, что не нужно качать файлы.
1С не закрывалась. Даже OLE подключение не прерывалось.

Выше я написал - как узнать индекс не имея FoxPro.

Все результаты желательно проверить у других участников.
  
Наверх
 
IP записан
 
varelchik
God Member
*****
Отсутствует


I Love YaBB 2!

Сообщений: 788
Зарегистрирован: 22. Мая 2006
Re: Тема Использование индексов (ДБФ) часть 2.
Ответ #8 - 13. Июля 2007 :: 11:25
Печать  
Ну на счет 150 и 90 я бы не сказал а вот на счет 50 это точно!
Вот только на счет сетки не пробовал, но в принципе можно будет и по сети спробовать.
  
Наверх
 
IP записан
 
varelchik
God Member
*****
Отсутствует


I Love YaBB 2!

Сообщений: 788
Зарегистрирован: 22. Мая 2006
Re: Тема Использование индексов (ДБФ) часть 2.
Ответ #9 - 13. Июля 2007 :: 11:45
Печать  
Так же подтверждаю что по сети действительно выигрыш есть около 50-60 раз при чем очень сильно зависит от периода! меньше период больше выигрыш!
  
Наверх
 
IP записан
 
JohnyDeath
1c++ power user
1c++ donor
Отсутствует



Сообщений: 3050
Местоположение: Волгоград
Зарегистрирован: 19. Мая 2006
Пол: Мужской
Re: Тема Использование индексов (ДБФ) часть 2.
Ответ #10 - 13. Июля 2007 :: 11:54
Печать  
Тут как-то не всё так однозначно.
Я помню твою прошлую проблему с кодировкой. Так вот у меня вообще не имеет значения стоит в строке подключения Рашн или Машин.
Потестировал у себя запрос, приведённый тобой выше по выборке из журнала документов (убрал из второго запроса сортировку и выборку поля "Индекс"). Вот результаты:

Цитата:
Период - 1 день:
Время выполнения по старой методике  = 0.111
Время выполнения по новой методике  = 0.002

Период - 1 месяц:
Время выполнения по старой методике  = 0.142
Время выполнения по новой методике  = 0.039

Период - 1 квартал:
Время выполнения по старой методике  = 0.188
Время выполнения по новой методике  = 0.117

Период - полгода:
Время выполнения по старой методике  = 0.229
Время выполнения по новой методике  = 0.187

Период - год:
Время выполнения по старой методике  = 0.53
Время выполнения по новой методике  = 0.654

Итого:
1) на периоде в 1 день увеличение скорости в ~50 раз
2) на периоде в 1 год уменьшение скорости в ~1,2 раза
3) у меня время выполнения запроса не меняется при смене "Collating Sequence" с "RUSSIAN" на "Machine"
  
Наверх
 
IP записан
 
varelchik
God Member
*****
Отсутствует


I Love YaBB 2!

Сообщений: 788
Зарегистрирован: 22. Мая 2006
Re: Тема Использование индексов (ДБФ) часть 2.
Ответ #11 - 13. Июля 2007 :: 12:28
Печать  
JohnyDeath писал(а) 13. Июля 2007 :: 11:54:
Тут как-то не всё так Итого:
1) на периоде в 1 день увеличение скорости в ~50 раз
2) на периоде в 1 год уменьшение скорости в ~1,2 раза
3) у меня время выполнения запроса не меняется при смене "Collating Sequence" с "RUSSIAN" на "Machine"

Ты ведь сам и ответил.
Ведь чем меньше период тем больше выигрыш.
Естетсвенно выходит что попадаем таки в индекс.
А на больших периодах это тута уже нандо на нескольких попытках пробовать.
Так что предложенная методика действительно позволяет правильно использовать индекс в запросе.
  
Наверх
 
IP записан
 
kiruha
1c++ power user
Отсутствует



Сообщений: 1249
Зарегистрирован: 11. Апреля 2007
Re: Тема Использование индексов (ДБФ) часть 2.
Ответ #12 - 13. Июля 2007 :: 12:35
Печать  
JohnyDeath писал(а) 13. Июля 2007 :: 11:54:
Тут как-то не всё так однозначно.
Я помню твою прошлую проблему с кодировкой. Так вот у меня вообще не имеет значения стоит в строке подключения Рашн или Машин.
Потестировал у себя запрос, приведённый тобой выше по выборке из журнала документов (убрал из второго запроса сортировку и выборку поля "Индекс"). Вот результаты:


Итого:
1) на периоде в 1 день увеличение скорости в ~50 раз
2) на периоде в 1 год уменьшение скорости в ~1,2 раза
3) у меня время выполнения запроса не меняется при смене "Collating Sequence" с "RUSSIAN" на "Machine"


Насчет периодов - так и должно быть.
При периоде в год стоимость расчета функции превышает пользу от применения индекса.
Индексные выражения нужно применять только когда есть высокая селективность по индексу
(думаю что прикидочно - от 10 раз)

У меня цифры на периоде в год
1. Старый - 3.0 сек
2.Новый - 2.5 сек.      Но это наверно потому, что у вас селективность по году меньше.

Вообщем не дай бог их везде подряд применять Улыбка

Насчет сортировки - нужно проверить через FoxPro.
Еще возможно дело в том - что есть  пара индексов в самых тяжелых таблицах - IDDOC в 1SJOURN например,
а также все ID в справочниках - которые не являются сложными строковыми выражениями.
Соответственно чтобы шла оптимизаци при JOIN по этим полям достаточно просто
проставить правильный "Collating Sequence" . Видимо отсюда и появилось ускорение в 3 раза.
Те запросы, которые не используют такие поля - соответственно не ускоряются.
  
Наверх
 
IP записан
 
kiruha
1c++ power user
Отсутствует



Сообщений: 1249
Зарегистрирован: 11. Апреля 2007
Re: Тема Использование индексов (ДБФ) часть 2.
Ответ #13 - 13. Июля 2007 :: 13:16
Печать  
13 сообщение в пятницу 13-го Улыбка

Кто нибудь знает - как порушить выбранный файл CDX?
Блокнотом не удалось.
Также рубил второго пользователя во время "обычного" запроса.
Ничего не изменилось, хотя программа попросила при входе переиндексироваться.

Хочу посмотреть как поведет себя оптимизатор FoxPro если нарушен индекс.
  
Наверх
 
IP записан
 
varelchik
God Member
*****
Отсутствует


I Love YaBB 2!

Сообщений: 788
Зарегистрирован: 22. Мая 2006
Re: Тема Использование индексов (ДБФ) часть 2.
Ответ #14 - 13. Июля 2007 :: 14:03
Печать  
Касательно остатков понятно.
а вот как быть с оборотами?
Там же даты нету.
Есть только IDDOC
по нем соединяться?
  
Наверх
 
IP записан
 
Переключение на Главную Страницу Страницы: [1] 2 3 
ОтправитьПечать