Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Ошибка 8618, при выполнении запроса с UNION ALL и ORDER BY  [new]
rancid
Member

Откуда: *мск
Сообщений: 114
Добрый день.
Столкнулся со следующей проблемой, после обновления 1С на один из последних релизов, запрос из 1С стал отваливаться с ошибкой СУБД Msg 8618, Level 16, State 2:
The query processor could not produce a query plan because a worktable is required, and its minimum row size exceeds the maximum allowable of 8060 bytes. A typical reason why a worktable is required is a GROUP BY or ORDER BY clause in the query. If the query has a GROUP BY or ORDER BY clause, consider reducing the number and/or size of the fields in the clause. Consider using prefix (LEFT()) or hash (CHECKSUM()) of fields for grouping or prefix for ordering. Note however that this will change the behavior of the query.


Запрос отловил профайлером, вот он:

exec sp_executesql N'SELECT
T1.Q_001_F_000_TYPE,
T1.Q_001_F_000_RTRef,
T1.Q_001_F_000_RRRef,
CAST(CASE WHEN T1.Q_001_F_000_TYPE = 0x08 AND T1.Q_001_F_000_RTRef = @P1 THEN T3._Fld434 ELSE CAST(NULL AS NTEXT) END AS VARCHAR(100)),
CASE WHEN T1.Q_001_F_000_TYPE = 0x08 AND T1.Q_001_F_000_RTRef = @P2 THEN CASE WHEN T4._Code IS NOT NULL THEN 0x03 END WHEN T1.Q_001_F_000_TYPE = 0x08 AND T1.Q_001_F_000_RTRef = @P3 THEN CASE WHEN T3._Code IS NOT NULL THEN 0x05 END ELSE CAST(NULL AS BINARY(1)) END,
CASE WHEN T1.Q_001_F_000_TYPE = 0x08 AND T1.Q_001_F_000_RTRef = @P4 THEN T4._Code WHEN T1.Q_001_F_000_TYPE = 0x08 AND T1.Q_001_F_000_RTRef = @P5 THEN CASE WHEN T3._Code IS NOT NULL THEN 0.0 END ELSE CAST(NULL AS NUMERIC(5, 0)) END,
CASE WHEN T1.Q_001_F_000_TYPE = 0x08 AND T1.Q_001_F_000_RTRef = @P6 THEN CASE WHEN T4._Code IS NOT NULL THEN @P7 END WHEN T1.Q_001_F_000_TYPE = 0x08 AND T1.Q_001_F_000_RTRef = @P8 THEN T3._Code ELSE CAST(NULL AS NVARCHAR(9)) END,
T1.Q_001_F_006_,
T1.Q_001_F_007_,
T1.Q_001_F_001RRef,
CASE WHEN T1.Q_001_F_000_TYPE = 0x08 AND T1.Q_001_F_000_RTRef = @P9 THEN T3._Fld435RRef ELSE CAST(NULL AS BINARY(16)) END,
T1.Q_001_F_002_,
T1.Q_001_F_008_,
T1.Q_001_F_009_,
T1.Q_001_F_004RRef,
T1.Q_001_F_005RRef,
T1.Q_001_F_010_,
1.0
FROM (SELECT
T2._Fld3434_TYPE AS Q_001_F_000_TYPE,
T2._Fld3434_RTRef AS Q_001_F_000_RTRef,
T2._Fld3434_RRRef AS Q_001_F_000_RRRef,
T2._Fld3430RRef AS Q_001_F_001RRef,
T2._Fld3435 AS Q_001_F_002_,
T2._Fld3437RRef AS Q_001_F_003RRef,
T2._Fld3440RRef AS Q_001_F_004RRef,
T2._Fld3439RRef AS Q_001_F_005RRef,
CAST(SUM(T2._Fld3431) AS NUMERIC(26, 8)) AS Q_001_F_006_,
CAST(SUM(T2._Fld3432) AS NUMERIC(26, 8)) AS Q_001_F_007_,
CAST(SUM(T2._Fld3436) AS NUMERIC(27, 8)) AS Q_001_F_008_,
CAST(SUM(T2._Fld3438) AS NUMERIC(27, 8)) AS Q_001_F_009_,
MIN(T2._LineNo3429) AS Q_001_F_010_
FROM _Document143_VT3428 T2 WITH(NOLOCK)
WHERE (T2._Document143_IDRRef = @P10)
GROUP BY T2._Fld3434_TYPE,
T2._Fld3434_RTRef,
T2._Fld3434_RRRef,
T2._Fld3430RRef,
T2._Fld3435,
T2._Fld3437RRef,
T2._Fld3440RRef,
T2._Fld3439RRef) T1
LEFT OUTER JOIN _Reference28 T3 WITH(NOLOCK)
ON T1.Q_001_F_000_TYPE = 0x08 AND T1.Q_001_F_000_RTRef = @P11 AND T1.Q_001_F_000_RRRef = T3._IDRRef
LEFT OUTER JOIN _Reference5958 T4 WITH(NOLOCK)
ON T1.Q_001_F_000_TYPE = 0x08 AND T1.Q_001_F_000_RTRef = @P12 AND T1.Q_001_F_000_RRRef = T4._IDRRef
UNION ALL SELECT
0x08,
@P13,
T5._Fld3456RRef,
CAST(T6._Fld434 AS VARCHAR(100)),
CASE WHEN T6._Code IS NOT NULL THEN 0x05 END,
CASE WHEN T6._Code IS NOT NULL THEN 0.0 END,
T6._Code,
CAST(T5._Fld3457 AS NUMERIC(21, 3)),
CAST(NULL AS NUMERIC(21, 3)),
CAST(NULL AS BINARY(16)),
CAST(NULL AS BINARY(16)),
T5._Fld3458,
CAST(T5._Fld3459 AS NUMERIC(21, 2)),
CAST(0.0 AS NUMERIC(21, 2)),
CAST(NULL AS BINARY(16)),
CAST(NULL AS BINARY(16)),
CAST(CAST(T5._LineNo3455 AS NUMERIC(2, 0)) AS NUMERIC(5, 0)),
2.0
FROM _Document143_VT3454 T5 WITH(NOLOCK)
LEFT OUTER JOIN _Reference28 T6 WITH(NOLOCK)
ON T5._Fld3456RRef = T6._IDRRef
WHERE (T5._Document143_IDRRef = @P14)
UNION ALL SELECT
CAST(NULL AS BINARY(1)),
CAST(NULL AS BINARY(4)),
CAST(NULL AS BINARY(16)),
CAST(NULL AS NVARCHAR(1000)),
CASE WHEN T8._Code IS NOT NULL THEN 0x05 END,
CASE WHEN T8._Code IS NOT NULL THEN 0.0 END,
T8._Code,
CAST(T7._Fld3465 AS NUMERIC(21, 3)),
CAST(NULL AS NUMERIC(21, 3)),
CAST(NULL AS BINARY(16)),
T8._Fld435RRef,
T7._Fld3466,
CAST(T7._Fld3467 AS NUMERIC(21, 2)),
CAST(T7._Fld3469 AS NUMERIC(21, 2)),
CAST(NULL AS BINARY(16)),
CAST(NULL AS BINARY(16)),
CAST(CAST(T7._LineNo3463 AS NUMERIC(2, 0)) AS NUMERIC(5, 0)),
3.0
FROM _Document143_VT3462 T7 WITH(NOLOCK)
LEFT OUTER JOIN _Reference28 T8 WITH(NOLOCK)
ON T7._Fld3470RRef = T8._IDRRef
WHERE (T7._Document143_IDRRef = @P15)
ORDER BY 18,17',N'@P1 varbinary(4),@P2 varbinary(4),@P3 varbinary(4),@P4 varbinary(4),@P5 varbinary(4),@P6 varbinary(4),@P7 nvarchar(4000),@P8 varbinary(4),@P9 varbinary(4),@P10 varbinary(16),@P11 varbinary(4),@P12 varbinary(4),@P13 varbinary(4),@P14 varbinary(16),@P15 varbinary(16)',0x0000001C,0x00001746,0x0000001C,0x00001746,0x0000001C,0x00001746,N'',0x0000001C,0x0000001C,0xBB45000C297E0EBD11E33B8DC74ED099,0x0000001C,0x00001746,0x0000001C,0xBB45000C297E0EBD11E33B8DC74ED099,0xBB45000C297E0EBD11E33B8DC74ED099


Если убрать ORDER BY отсюда, запрос выполняется без ошибок. Когда избавился от sp_executesql (тупо через найти-заменить в SSMS заменил параметры на значения), запрос стал выполняться без ошибок даже при наличии ORDER BY.

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

Версия SQL Server - 9.0.4035 (SP3), ОС - Win Server 2008 R2 EE, 1С - 8.2.19.76.

зы простите за все эти имена и алиасы таблиц/столбцов - я здесь бессилен
12 дек 13, 13:35    [15282285]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка 8618, при выполнении запроса с UNION ALL и ORDER BY  [new]
Glory
Member

Откуда:
Сообщений: 104760
rancid
The query processor could not produce a query plan because a worktable is required, and its minimum row size exceeds the maximum allowable of 8060 bytes

выбранный сервером план выполнения предусматривает создание временной таблицы. А размер записи этой таблицы превышает разрешенные 8060 байт
12 дек 13, 13:41    [15282334]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка 8618, при выполнении запроса с UNION ALL и ORDER BY  [new]
rancid
Member

Откуда: *мск
Сообщений: 114
Glory,

Ок, спасибо. А как в данном случае можно повлиять на план? Можно ли узнать, что за таблицу хочет создать SQL Server? Хотелось бы понять откуда он 8060 байт набирает.
12 дек 13, 13:48    [15282381]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка 8618, при выполнении запроса с UNION ALL и ORDER BY  [new]
Konst_One
Member

Откуда:
Сообщений: 11538
@P7 nvarchar(4000)
12 дек 13, 13:50    [15282395]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка 8618, при выполнении запроса с UNION ALL и ORDER BY  [new]
rancid
Member

Откуда: *мск
Сообщений: 114
Konst_One,

ё-мое, спасибо.
12 дек 13, 13:53    [15282415]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить