Блокировка таблиц

[Cheb]

Да, индексы перестроил, статистику обновил.
Теперь такой код:
[codebox]
TT := TIME;
GLEntry.RESET;
GLEntry.SETCURRENTKEY("Transaction No.");
GLEntry.SETRANGE("G/L Account No.",'000.');
IF GLEntry.FIND('-') THEN;
MESSAGE('ok %1',TIME - TT);[/codebox]

Результат:
SELECT * FROM "Test$G_L Entry" WITH (READUNCOMMITTED) WHERE (("G_L Account No_"=?)) ORDER BY "Transaction No_","Entry No_" OPTION (FAST 5)
SQL Plan = Filter[3,1];Bookmark Lookup[4,3];Index Scan($4)[6,4]

Для запроса был выбран ключ, указанный в SETCURRENTKEY. Пока для меня ясно, что единственный способ заставить навижен использовать нужный ключ - это использовать SETCURRENTKEY.

[Голышев Михаил]

Не "заставить", а "попросить".

SETCURRENTKEY лишь добавляет ORDER BY в сиквельный запрос.

Индекс выбирает SQL. И это лишь совпадение что он выбирает то что передано в ORDER BY.

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

Или например так:
GLEntry.RESET;
GLEntry.SETCURRENTKEY("Transaction No.");
GLEntry.SETRANGE("G/L Account No.",'000.');
GLEntry.SETFILTER("Entry No.",'<10');


Кстати сколько записей у вас в GLEntry?

P.S. Признаться честно, не понимаю почему SQL 2000 так нелепо выбирает индексы для запроса. Все ваши примеры на 2005 работают по другому. Завтра поставлю 2000 и потестирую.

[Advisor]

У меня на Nav 3.6 + SQL2000
Код

GLEntry.RESET;
GLEntry.SETCURRENTKEY("Transaction No.");
GLEntry.SETRANGE("G/L Account No.",'60007');
GLEntry.SETRANGE("Posting Date",010107D,310307D);
IF GLEntry.FIND('-') THEN ;

отрабатывал в разы быстрее, чем

GLEntry.RESET;
GLEntry.SETRANGE("G/L Account No.",'60007');
GLEntry.SETRANGE("Posting Date",010107D,310307D);
IF GLEntry.FIND('-') THEN ;

Хотя выбор индекса действительно осуществлялся независимо от GLEntry.SETCURRENTKEY("Transaction No.").
Точнее если закомментирована строка GLEntry.SETRANGE("Posting Date",010107D,310307D);, то индекс по "Transaction No.",
а если нет, то индекс, который включал оба фильтруемых поля.

Во втором случае всегда использовался кластерный индекс. весьма небыстро отрабатывал. P.S. 17 таблица более 3 млн записей

[Голышев Михаил]

Я полагаю использование Clustered Index Scan обусловлено хинтом FAST 5, который призывает оптимизатор получить первые 5 записей как можно быстрее. (В ущерб скорости получения остальных записей)

В 2005 мы избавлялись от этих FAST с помощью plan guide. (Кстати в пятерке запросы уже идут без FAST)

Попробуйте сравнить в Query Analizer запросы которые идут по Clustered Index Scan с аналогичным запросом без хинта FAST

[Advisor]

Да, результаты говорят сами за себя
1. c кластерным индексом и FAST ----------> <более минуты

2. с кластерным индексом без FAST -------------> Меньше секунды

3. с использованием SETCURRENTKEY -------------> Меньше секунды

Думаю если сравнить в миллисекундах, то думаю второй вариант быстрее, но и третий обеспечивает хорошую производительность

[smoyk]

А мне кажется логичным, что сервер выбирает тот ключ, по которому идет сортировка. Понитно же, что в упорядоченном наборе данных поиск идет на порядок быстрее. Поэтому мы через SETCURRENTKEY устанавливая сортировку по полям поиска в то же время выбираем и наиболее оптимальный ключ. И то, что выбор сервера совподает с нашим не странно, а закономерно. В тоже время без сортировки (без вызова SETCURRENTKEY) выбор ключа не так очевиден, имхо. Поэтому сервер и выбирает не пойми какой ключ.
Кстати, эффект от вызова SETCURRENTKEY увеличился на порядок после создания ключа на сервере, т.е. возведения MaintainSQLIndex в истину. До этого вызов SETCURRENTKEY дало приращение в скорости 25% (с 4 сек до 3 первый вызов FIND('-')). После - время поиска упало практически до нуля.

p.s. Все же невозможность явно формировать запросы к базе напрягает. То что сервер ставит фиг пойми какие хинты (типа FAST) не радует.

p.p.s. 3.70A, SQL Server 2000.

[Cheb]

Считаю, что golyshev все правильно написал, за что ему респект
SETCURRENTKEY дейсвительно, только добавляет Order By в запрос. Sql сервер на основании условия where и order by решает, какой выбрать ключ. При нормальных ключах и статистике ключ выбирается правильно, даже если не указан setcurrentkey, т.е. только с условием where. Если статистика давно не обновлялась, ключ для запроса с условием where выбирается неправильно. В этом случае использование SETCURRENTKEY добавляет order by, что приводит к выбору правильного ключа.

[Голышев Михаил]

Цитата:

Сообщение от smoyk

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

Слава богу SQL 2005 обладает более динамичной логикой, чем выбор ключа по полям сортировки. (привет MySql)

Можно привести массу примеров, где это утверждение не верно.

Более того, порой просто поражаешься сообразительности SQL в аспекте построения плана запросов.
(кстати по мимо поисков по ключу есть и другие компоненты планов - Table Spool'ы, Bookmark Lookup'ы и прочие)

Цитата:

Сообщение от smoyk

В тоже время без сортировки (без вызова SETCURRENTKEY) выбор ключа не так очевиден, имхо.

Главное, как отметил chebv, следить за статистикой и фрагментацией индексов. Тогда выбор ключа будет постоянен и очевиден.

Цитата:

Сообщение от smoyk

Кстати, эффект от вызова SETCURRENTKEY увеличился на порядок после создания ключа на сервере

Вполне резонно. Создание любого ключа потенциально увеличивает скорость запросов.
Создайте ключи из всех возможных комбинаций полей во всех возможных порядках и все FIND по таблице будут моментальными. Зато изменения в этой таблице станут ойойой какими медленными.

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

[smoyk]

Цитата:

При нормальных ключах и статистике ключ выбирается правильно, даже если не указан setcurrentkey, т.е. только с условием where.

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

Цитата:

Сообщение от ;362029

Можно привести массу примеров, где это утверждение не верно.

Естественно, но так как мы сортируем именно по полям, по которым проводим поиск, то никакие другие примеры здесь не уместны Вызывая SETCURRENTKEY мы не только сортируем (что значительно ускоряет поиск), но и явно указываем выбираемый ключ. Если SQL 2005 выберет другой ключ исходя из своей логики, думаю это будет не правильно. Думаю, он этого и не сделает имхо. Или я не прав и Вы можете такие примеры привести?

[MSI]

Цитата:

Сообщение от smoyk

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

То что Вы указываете SETCURRENTKEY заставляет сиквель лишь упорядочить то, что получится в результате выполнения запроса. Сама выборка будет выполнятся в соответствии с планом запроса, составленного сиквелем когда-то ранее. План сиквель выбирает сам.

Цитата:

Сообщение от smoyk

Естественно, но так как мы сортируем именно по полям, по которым проводим поиск, то никакие другие примеры здесь не уместны Вызывая SETCURRENTKEY мы не только сортируем (что значительно ускоряет поиск), но и явно указываем выбираемый ключ. Если SQL 2005 выберет другой ключ исходя из своей логики, думаю это будет не правильно. Думаю, он этого и не сделает имхо. Или я не прав и Вы можете такие примеры привести?

Наблюдаю массу примеров. Ставлю ключ по таблице - сиквель начинает выполнять Clustered Index Seek. Убираю - вот вам Индексный поиск. Ситуация имеет тенденцию самопроизвольно меняться до наоборот . С ключом Index Seek. Без ключа Clustered Index Seek (логично). Замучившись окончательно понять логику стали прицеплять принудительно планы выполнения к каждому из запросов. Утомительная задачка вышла, однако. На казалось бы одну и ту же примитивную конструкцию в C\AL коде сиквель генерит довольно много разных вариаций одного и того же запроса ( иногда отличающихся лишь одним знаком - напр '<' вместо '<='). И для каждого из таких запросов, как я понял, надо приципить свой план. Геморройно... Или надо что-то не так делать? Может мы не так лечим и надо действительно сделать обновление статистики и сиквель должен начать правильно выстраивать планы выполнения? Или можно как-то назначить план на группу запросов?

[Голышев Михаил]

Цитата:

Сообщение от smoyk

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

Простите, но это бред.
Во первых набор данных будет упорядочен по первичному ключу.
В вторых, поиск будет идти быстрее, поскольку при использовании не кластерного индекса, к нему всегда идет join
кластерного, в котором данные как раз УЖЕ упорядочены по первичному ключу.

Цитата:

Сообщение от smoyk

Вызывая SETCURRENTKEY мы не только сортируем (что значительно ускоряет поиск)

Каким образом сортировка УСКОРЯЕТ поиск?!
Это дополнительное действие, которое либо является отдельным пунктом плана запроса, либо входит в этап джоина таблиц.

Цитата:

Сообщение от smoyk

но и явно указываем выбираемый ключ. Если SQL 2005 выберет другой ключ исходя из своей логики, думаю это будет не правильно. Думаю, он этого и не сделает имхо. Или я не прав и Вы можете такие примеры привести?

Пример будет вечером. Специально сделаю его на CRONUS, чтобы Вам удалось его повторить.

[Голышев Михаил]

Цитата:

Сообщение от MSI

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

Если вы испльзуете Plan Guide - то для каждого запроса свой хинт. (у нас их около 500 на одну БД)

К сожалению, обновление статистики сможет починить лишь запросы, которые идут из кода.
(Тем не менее обновлять статистику нужно обязательно. По особо большим и популярным таблицам - каждую ночь)

При открытии же форм, как раз идут запросы с > >= < и <=.
Например при фильтрации финансовых операций по счету будет запрос:

exec sp_executesql N'SELECT  * FROM "dbo"."КРОК$G_L_Entry" WHERE (("G_L_Account_No_"=@P1)) 
AND  "Entry_No_">=@P2 ORDER BY "Entry_No_" OPTION (FAST 10)',
N'@P1 varchar(20),@P2 int','61100100',4

При этом план строится 1 раз, компилируется и будет использован для параметризированного запроса при любых параметрах.

Очевидно, что если в качестве @P2 первый раз было передано большое число, то план будет построен по кластерному индексу. (Если @P2 маленькое, то план будет правильный - состоять из джоина Index Seek по G_L_Account и Clustered Index Seek)
И в дальнейшем, этот план по кластерному индексу будет использован и для маленьких @P2, что будет приводить к сканированию всей таблицы.

Эти случаи надо фиксить Plan Guide'ами

[Голышев Михаил]

Цитата:

Сообщение от smoyk

Или я не прав и Вы можете такие примеры привести?

CRONUS Россия ЗАО (navision sp3) на ms sql 2005
Типичный пример на 17 таблице.

Прежде всего, заполняем фин книгу (300 записей - это не серьезно)

Name	DataType	Subtype	Length
i	Integer		
dlg	Dialog		
recGenJnlTEMP	Record	Gen. Journal Line	
recGenJnlDimTEMP	Record	Journal Line Dimension	
Gen. Jnl.-Post Line	Codeunit	Gen. Jnl.-Post Line	
---------
dlg.OPEN('@1##################');
FOR i := 1 TO 1000000 DO BEGIN
  dlg.UPDATE(1,i DIV 100);
  recGenJnlTEMP.DELETEALL;
  recGenJnlTEMP.INIT;
  recGenJnlTEMP.VALIDATE("Posting Date" , 010105D);
  recGenJnlTEMP.VALIDATE("Account Type" , recGenJnlTEMP."Account Type"::Customer);
  recGenJnlTEMP.VALIDATE("Account No." , '49633663');
  recGenJnlTEMP.VALIDATE("Document No." , 'INITIAL');
  recGenJnlTEMP.VALIDATE(Description , 'Автозапонение');
  recGenJnlTEMP.VALIDATE(Amount,100);
  recGenJnlTEMP.VALIDATE("Bal. Account Type",recGenJnlTEMP."Bal. Account Type"::"G/L Account");
  recGenJnlTEMP.VALIDATE("Bal. Account No.",'68-800');
  recGenJnlTEMP.VALIDATE("Shortcut Dimension 1 Code",'ПРОДАЖИ');
  recGenJnlTEMP.VALIDATE("Shortcut Dimension 2 Code",'МЕРСЕДЕС');
  recGenJnlTEMP.INSERT;
  recGenJnlDimTEMP.DELETEALL;
  recGenJnlDimTEMP.INIT;
  recGenJnlDimTEMP."Table ID" := 81;
  recGenJnlDimTEMP."Dimension Code" := 'ПРОДМЕНЕД';
  recGenJnlDimTEMP."Dimension Value Code" := 'ВК';
  recGenJnlDimTEMP.INSERT;
  "Gen. Jnl.-Post Line".RunWithCheck(recGenJnlTEMP,recGenJnlDimTEMP);
  COMMIT;
END;

Обновляем статистику, перестраиваем индексы и выполняем запрос:

recGLEntry.RESET;
recGLEntry.SETCURRENTKEY("Source Type","Source No.");
recGLEntry.SETRANGE("Source Type",recGLEntry."Source Type"::Customer);
recGLEntry.SETRANGE("Source No.",'49633663');
recGLEntry.SETRANGE("Document No.",'104005');
recGLEntry.FIND('-');

Код SQL при этом:

exec sp_executesql 
N'SELECT  * FROM "CRONUS"."dbo"."CRONUS Россия ЗАО$G_L Entry" WITH (READUNCOMMITTED)   WHERE (("Source Type"=@P1)) AND (("Source No_"=@P2)) AND (("Document No_"=@P3)) ORDER BY "Source Type","Source No_","G_L Account No_","Global Dimension 1 Code","Global Dimension 2 Code","Business Unit Code","Posting Date","Entry No_" OPTION (FAST 5)',
N'@P1 int,@P2 varchar(20),@P3 varchar(20)'
,1,'49633663','104005'

План запроса:

[MSI]

Наши мелкомягкие друзья, прочухав косяк с планами запроса, выродили UPDATE 6 для 4-го клиента. Смысл UPDATE в следующем - создается ручками на сиквеле табличка ndo$dbconfig в которую можно прописать правила работы Нава с сиквелем такие как:

IndexHint=Yes;Company="DEFAULT COMPANY";Table="Vendor";Key="Search Name";Search Method="-+$";Index=1'; - использовать 1 индекс сиквеля для поиска по ключу Search Name;

UseRecompileForTable="G/L Entry";Company="CRONUS International Ltd.";RecompileMode=1; - делать рекомпайл для каждого запроса по таблице.

т.е. проблема прокэшированного плана запроса вроде-как снимается. Однако, решив пойти по по пути с REcompile, столкнулся с проблемой производительности в весьма тонкой вещи - фильтрация по flow field. Может кто сталкивался и решал подобные проблемы с REcompile?

[Голышев Михаил]

Советую перед установкой Update 6
внимательно прочитать это:
http://www.mibuso.com/forum/viewtopic.php?...02a2f750c740e98

[ekya]

Всем привет.

Не могу понять. Есть запрос
[codebox]select top 100 *
from dbo.[FIRM$G_L Entry]
where [G_L Account No_]='20_03'
and [Posting Date] between '2007-07-01' and '2007-07-31'
--order by [G_L Account No_], [Posting Date][/codebox]


на 2000 сервере план такой

[attachment=723:SQL2000_1.jpg]

а на 2005 сервере вот так выходит

[attachment=724:SQL2005_1.jpg]

на 2005 делал
update statistics dbo.[ЭГЭБ-1$G_L Entry] WITH FULLSCAN
DBCC UPDATEUSAGE (0, [ЭГЭБ-1$G_L Entry])

не помогло.

причем, когда есть ORDER BY индекс выбирается правильный в обоих случаях.
Как заставить 2005 SQL правильно выбирать индексы без ORDER BY?

[grif]

А подскажите пожалуйста как быть в такой ситуации.
Понять её можно на следующем примере.
Имеется таблица с первичным ключём по int полю Ind.
_OVTR - Record для этой таблицы

  FOR _i:= 1 TO 10000 DO
  BEGIN
    IF NOT _OVTR.GET(64) THEN
    BEGIN
      _OVTR.INIT;
      _OVTR.Ind := 64;
      _OVTR.INSERT;
    END;
    _OVTR.GET(64);
    _OVTR.DELETE;
  END;

Во время работы смотрим результат для sp_lock 64 (64 - моя сессия в которой выполянется описанный код):
64 11 0 0 DB S GRANT
64 11 795253988 1 KEY (4000de78af45) X GRANT
64 11 795253988 1 PAG 3:39153 IX GRANT
64 11 795253988 1 KEY (ffffffffffff) RangeS-U GRANT
64 11 795253988 0 TAB IX GRANT

Если создать в Navision новую сессию (66) и попытаться вставить в таблицу строку с Ind=65, то возникает блокировка.
sp_lock 64,66 показывает
66 11 0 0 DB S GRANT
64 11 0 0 DB S GRANT
64 11 795253988 1 KEY (4000de78af45) X GRANT
64 11 795253988 1 PAG 3:39153 IX GRANT
66 11 795253988 1 PAG 3:39153 IX GRANT
64 11 795253988 1 KEY (ffffffffffff) RangeS-U GRANT
66 11 795253988 1 KEY (ffffffffffff) RangeIn- WAIT
64 11 795253988 0 TAB IX GRANT
66 11 795253988 0 TAB IX GRANT

Т.е. возникла блокировка по диапазону индекса, как понимаю от 64 до бесконечности.

Если модифицировать код так

  FOR _i:= 1 TO 10000 DO
  BEGIN
    //IF NOT _OVTR.GET(64) THEN
    BEGIN
      _OVTR.INIT;
      _OVTR.Ind := 64;
      _OVTR.INSERT;
    END;
    _OVTR.GET(64);
    _OVTR.DELETE;
  END;

и снова запустить его в сессии 64, то sp_lock 64 покажет
64 11 0 0 DB S GRANT
64 11 795253988 1 KEY (4000de78af45) X GRANT
64 11 795253988 1 PAG 3:39153 IX GRANT
64 11 795253988 0 TAB IX GRANT

добавление записи с Ind=65 уже не вызывает блокирование т.к. отсутствует блокировка RangeS-U и судя по всему её вызывает GET. Подскажите пожалуйста, как это можно обойти?

[RedFox]

Цитата:

Сообщение от grif

подскажите пожалуйста как быть в такой ситуации.
...
добавление записи с Ind=65 уже не вызывает блокирование т.к. отсутствует блокировка RangeS-U и судя по всему её вызывает GET. Подскажите пожалуйста, как это можно обойти?

GET вызывает блокировку записи перед ней и после неё. Таким образом Вы не можете вставить значение 65 без блокировки. Если работать без GET, то блокировки записей в таблице нет и вы без проблем пытаетесь вставить записи (если не делать 2 раза подряд Insert).

[grif]

Я это понимаю, но в рабочем коде запись может уже существовать и потому нужна проверка. Как быть, чтоб проверка существования была без блокировок?
Пока писал возникла мысль сделать без GET и прочих проверок так:

IF _OVTR.INSERT THEN;

Вроде работает на тестовых примерах, но не соображу как проверить точно. Профайлер какую-то чушь показывает

[Sancho]

IF _OVTR.INSERT THEN;

прекрасно работает! можно не проверять!