Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Разный выбор плана для STATIC и DYNAMIC курсора  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1747
DECLARE
  @A1 VARCHAR(20) = 'SPI006179093',
  @A2 VARCHAR(20) = 'SPI006179406',
  @A3 VARCHAR(20) = 'SPI006179719',
  @A4 VARCHAR(20) = 'SPI006180149',
  @A5 VARCHAR(20) = 'SPI006180151',
  @A6 VARCHAR(20) = 'SPI006180161',
  @A7 VARCHAR(20) = 'SPI006180179',
  @A8 VARCHAR(20) = 'SPI006182704',
  @A9 VARCHAR(20) = 'SPI006182705',
  @A10 VARCHAR(20) = ''

DECLARE cur  CURSOR LOCAL DYNAMIC FOR
  SELECT [Document No_], [Line No_]
FROM   "Shate-M-test"."dbo"."Shate-M$Sales Invoice Line" WITH (READUNCOMMITTED)
WHERE  ("Document No_" IN (@A1,@A2,@A3,@A4,@A5,@A6,@A7,@A8,@A9))
       AND (("No_" <> @A10))
ORDER BY
       "Document No_",
       "Line No_"  OPTION(OPTIMIZE FOR UNKNOWN)


DECLARE
  @A1 VARCHAR(20) = 'SPI006179093',
  @A2 VARCHAR(20) = 'SPI006179406',
  @A3 VARCHAR(20) = 'SPI006179719',
  @A4 VARCHAR(20) = 'SPI006180149',
  @A5 VARCHAR(20) = 'SPI006180151',
  @A6 VARCHAR(20) = 'SPI006180161',
  @A7 VARCHAR(20) = 'SPI006180179',
  @A8 VARCHAR(20) = 'SPI006182704',
  @A9 VARCHAR(20) = 'SPI006182705',
  @A10 VARCHAR(20) = ''

DECLARE cur  CURSOR LOCAL STATIC FOR
  SELECT [Document No_], [Line No_]
FROM   "Shate-M-test"."dbo"."Shate-M$Sales Invoice Line" WITH (READUNCOMMITTED)
WHERE  ("Document No_" IN (@A1,@A2,@A3,@A4,@A5,@A6,@A7,@A8,@A9))
       AND (("No_" <> @A10))
ORDER BY
       "Document No_",
       "Line No_"  OPTION(OPTIMIZE FOR UNKNOWN)

Кластерный индекс естественный по двум полям в выборке.
Планы приложены.
Почему для динамического он использует SCAN, а для статического SEEK ?
Запрос привел к удобному виду, на самом деле ORM генерирует sp_cursorprepexec

К сообщению приложен файл. Размер - 46Kb
8 сен 16, 10:37    [19640799]     Ответить | Цитировать Сообщить модератору
 Re: Разный выбор плана для STATIC и DYNAMIC курсора  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
X-Cite,

Не все операторы плана поддерживают динамические курсоры (Properties of Iterators).
Dynamic cursor support
The iterators used in a dynamic cursor query plan have special properties. Among other things, a dynamic cursor must be able to return a subset of the result set at a time, must be able to scan forward or backward, and must be able to acquire scroll locks. To support this functionality, an iterator must be able to save and restore its state, scan forward or backward, must process one input row for each output row it produces, and must be non-blocking. Not all iterators have all of these properties.

For a query to be executed using a dynamic cursor, the optimizer must be able to find a query plan that uses only iterators that support dynamic cursors. This is not always possible and this is why some queries cannot be executed using a dynamic cursor.

Hint: Just a creating the right index can enable the optimizer to eliminate a sort, for the same reason, it can sometimes (though not always) enable the optimizer also to find a dynamic cursor query plan. Unfortunately, this is not always possible. For example, there is no way to perform aggregation without violating the one input row for each output row property (though it is sometimes possible to work around this problem using an indexed view).

Есть подозрение, что вы показали не весь план, т.к. обычно для такой формы плана за Merge Interval следует оператор сортировки, которые предотвращает использование динамического курсора.

Можете форсировать поиск по индексу подсказкой forceseek и посмотреть, что курсор, скорее всего будет преобразован в keyset, как сказано тут Using Implicit Cursor Conversions.

В целом, есть ряд задач, которые можно решать курсорами, но их не так много, так что, может быть, имеет смысл вам опубликовать задачу целиком, вдруг кто-то подскажет ее решение без курсоров.
8 сен 16, 11:38    [19641308]     Ответить | Цитировать Сообщить модератору
 Re: Разный выбор плана для STATIC и DYNAMIC курсора  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1747
Проблема в том, что код генерирует ORM и определение какой тип курсора идет тоже исходит только из ORM.
В профайлере я словил это:
declare @p1 int
set @p1=-1
declare @p2 int
set @p2=0
declare @p5 int
set @p5=12290
declare @p6 int
set @p6=8193
declare @p7 int
set @p7=1
exec sp_cursorprepexec @p1 output,@p2 output, N'@P1 varchar(20),@P2 varc.......

Из описания процедуры sp_cursorprepexec я определил что set @p5=12290 это параметр scrollopt откуда по битовой маске получаем AUTO_FETCH, PARAMETERIZED_STMT, DYNAMIC
8 сен 16, 13:11    [19642027]     Ответить | Цитировать Сообщить модератору
 Re: Разный выбор плана для STATIC и DYNAMIC курсора  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1747
Верхний план для DYNAMIC и он полный
Нижний для STATIC, да я обрезал его справа, но там не надо это, т.к. нижний то использует SEEK и он быстрый...
8 сен 16, 13:32    [19642202]     Ответить | Цитировать Сообщить модератору
 Re: Разный выбор плана для STATIC и DYNAMIC курсора  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
X-Cite,

странные вы... вам же сказали почему + план кусок выдрали КАРТИНКОЙ.... и хотите что бы вам ответили почему..
читайте про Merge Interval и что он даст и вообщем он скорее и урезает вам запрос до seek
вот неплохо написано https://www.simple-talk.com/sql/learn-sql-server/showplan-operator-of-the-week-merge-interval/
ну и всё это сводится к тому что SW вам и сказал
автор
Не все операторы плана поддерживают динамические курсоры
8 сен 16, 14:01    [19642421]     Ответить | Цитировать Сообщить модератору
 Re: Разный выбор плана для STATIC и DYNAMIC курсора  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1747
Зайдем с другой стороны.
Есть индекс [$7] по "No_"
В таблице 37 млн записей.
ОРМ (Microsoft Dynamics Navision) генерирует вот такой запрос (выдрал из профайлера)
declare @p1 int
set @p1=-1
declare @p2 int
set @p2=0
declare @p5 int
set @p5=12290
declare @p6 int
set @p6=8193
declare @p7 int
set @p7=1
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(20),@P2 datetime,@P3 int',N'SELECT  * FROM "Shate-M-Pod"."dbo"."Shate-M RU$Registered Whse_ Activity Line" WITH (READUNCOMMITTED)  WHERE (("No_"=@P1)) AND (("Posted Date"=@P2)) AND  "Activity Type">@P3 ORDER BY "Activity Type","No_","Line No_" OPTION (OPTIMIZE FOR UNKNOWN)',@p5 output,@p6 output,@p7 output,'RPUTP0136541','2017-07-06 00:00:00',1
select @p1, @p2, @p5, @p6, @p7

В плане SEEK по кластерному.
Почему идет по кластерному, а не по индексу [$7]
По [$7] SEEK + Lookup
По кластерному logical reads 1370397, physical reads 1514, read-ahead reads 407864 CPU time = 4359 ms, elapsed time = 20069 ms
По [$7] logical reads 7, physical reads 0, read-ahead reads 0 CPU time = 15 ms, elapsed time = 11 ms.
Max Degree of Parallelism = 1 (Изменить нельзя)
Если я переделаю параметр @p5 с 12290 (DYNAMIC) на 12296 (STATIC), то уже будет использовать индекс [$7]
Повлиять на тип курсора нельзя. Как добиться, чтобы он для динамического курсора заюзал корректный индекс? Или на основании чего он так решил?
6 июл 17, 18:54    [20620660]     Ответить | Цитировать Сообщить модератору
 Re: Разный выбор плана для STATIC и DYNAMIC курсора  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1747
P.S.
--Кластерный индекс
Activity Type -- совершенно не селективное поле Всего 3 значения на 37 млн.
No_
Line No_

[$7]
No_

и есть еще индекс
[$1]
No_
Line No_
Activity Type

То что SEEK кластерного выдает столько чтений понятно.
Если перенести в ORDER BY сортировку по Activity Type с первого на другое место, тогда динамический курсор станет использовать [$1] индекс.
Если убрать это поле из сортировки, тогда станет использовать [$7] индекс.
Статический курсор всегда использует [$7] индекс во всех опытах.
P.S. Кластерный индекс изменить нельзя.
Как заставить Динамический курсор при таких индексах и указанном выше запросе вести себя как статический?
6 июл 17, 19:42    [20620791]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить