sp_cursoropen i równoległość

Występuje problem z wydajnością związany z zapytaniem, które wydaje się niemożliwe.

Wyciągnąłem zapytanie z definicji kursora.

Wykonanie tego zapytania zajmuje kilka sekund

SELECT A.JOBTYPE
FROM PRODROUTEJOB A
WHERE ((A.DATAAREAID=N'IW')
AND ((A.CALCTIMEHOURS<>0)
AND (A.JOBTYPE<>3)))
AND EXISTS (SELECT 'X'
FROM PRODROUTE B
WHERE ((B.DATAAREAID=N'IW')
AND (((((B.PRODID=A.PRODID)
AND ((B.PROPERTYID=N'PR1526157') OR (B.PRODID=N'PR1526157')))
AND (B.OPRNUM=A.OPRNUM))
AND (B.OPRPRIORITY=A.OPRPRIORITY))
AND (B.OPRID=N'GRIJZEN')))
AND NOT EXISTS (SELECT 'X'
FROM ADUSHOPFLOORROUTE C
WHERE ((C.DATAAREAID=N'IW')
AND ((((((C.WRKCTRID=A.WRKCTRID)
AND (C.PRODID=B.PRODID))
AND (C.OPRID=B.OPRID))
AND (C.JOBTYPE=A.JOBTYPE))
AND (C.FROMDATE>{TS '1900-01-01 00:00:00.000'}))
AND ((C.TODATE={TS '1900-01-01 00:00:00.000'}))))))
GROUP BY A.JOBTYPE
ORDER BY A.JOBTYPE

Rzeczywisty plan wykonania wygląda następująco.

Zauważając, że ustawienie całego serwera zostało ustawione na MaxDOP 1 Próbowałem bawić się z ustawieniami maxdop.

Dodanie OPTION (MAXDOP 0)do zapytania lub zmiana ustawień serwera skutkuje znacznie lepszą wydajnością i tym planem zapytań.

Jednak aplikacja (Dynamics AX) nie wykonuje takich zapytań, używa kursorów.

Rzeczywisty przechwycony kod jest taki.

declare @p1 int
set @p1=189527589
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=2
exec sp_cursoropen @p1 output,N'SELECT A.JOBTYPE FROM PRODROUTEJOB A WHERE ((A.DATAAREAID=N''IW'') AND ((A.CALCTIMEHOURS<>0) AND (A.JOBTYPE<>3))) AND EXISTS (SELECT ''X'' FROM PRODROUTE B WHERE ((B.DATAAREAID=N''IW'') AND (((((B.PRODID=A.PRODID) AND ((B.PROPERTYID=N''PR1526157'') OR (B.PRODID=N''PR1526157''))) AND (B.OPRNUM=A.OPRNUM)) AND (B.OPRPRIORITY=A.OPRPRIORITY)) AND (B.OPRID=N''GRIJZEN''))) AND NOT EXISTS (SELECT ''X'' FROM ADUSHOPFLOORROUTE C WHERE ((C.DATAAREAID=N''IW'') AND ((((((C.WRKCTRID=A.WRKCTRID) AND (C.PRODID=B.PRODID)) AND (C.OPRID=B.OPRID)) AND (C.JOBTYPE=A.JOBTYPE)) AND (C.FROMDATE>{TS ''1900-01-01 00:00:00.000''})) AND ((C.TODATE={TS ''1900-01-01 00:00:00.000''})))))) GROUP BY A.JOBTYPE ORDER BY A.JOBTYPE ',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

co skutkuje tym planem wykonania (i niestety takimi samymi wielosekundowymi czasami wykonania).

Próbowałem kilku rzeczy, takich jak upuszczanie planów buforowanych, dodawanie opcji w zapytaniu w definicji kursora, ... Ale żadna z nich nie wydaje mi się planem równoległym.

Szukałem również w Google dość szukając ograniczeń równoległości kursorów, ale nie mogę znaleźć żadnych ograniczeń.

Czy brakuje mi czegoś oczywistego?

Rzeczywista kompilacja SQL jest SQL Server 2008 (SP1) - 10.0.2573.0 (X64), jak wiem, nieobsługiwana, ale nie mogę zaktualizować tego wystąpienia, jeśli uważam to za stosowne. Musiałbym przenieść bazę danych na inny serwer, a to oznaczałoby przeciągnięcie dość dużej nieskompresowanej kopii zapasowej przez powolną sieć WAN.

Flaga śledzenia 4199 nie robi różnicy, podobnie jak OPCJA (RECOMPILE).

Właściwości kursora to:

API | Fast_Forward | Read Only | Global (0)

FAST_FORWARDkursory nie obsługują równoległości (chociaż serwer generujący plan musiałby mieć rok 2012 lub wyższy, aby uzyskać NonParallelPlanReasonjako część XML showplan).

Gdy określisz FAST_FORWARD, optymalizator wybierze pomiędzy STATICi DYNAMICdla ciebie.

Dostarczony plan wykonania pokazuje, że optymalizator wybiera plan podobny do statycznego. Ponieważ zapytanie zawiera agregację, wątpię, aby nawet dynamiczny plan kursora był tutaj możliwy. Niemniej jednak żądanie FAST_FORWARDtypu kursora uniemożliwia równoległy plan.

Powinieneś wyraźnie zmienić typ kursora na jeden STATIClub KEYSET, na przykład. Oba te typy kursorów mogą korzystać z równoległości.

To powiedziawszy, ponieważ jest to kursor API, zmiana typu kursora prawdopodobnie wymagałaby zmiany aplikacji. Oczywiście trzeba by porównać wydajność, aby sprawdzić, czy zmiana typu kursora jest naprawdę najlepszą opcją dla Ciebie.