Одна из главных проблем разыменования в запросах является разыменование составных типовых данных. Типичный пример — обращение к полю Регистратор регистра накопления, у которого регистратором является несколько документов.
Есть две функции, которые внешне позволяют избежать разыменования — ВЫРАЗИТЬ и ССЫЛКА. Сегодня я продемонстрирую, как они работают со стороны SQL. Для примера была взята СУБД — MS SQL 2014, платформа версии 8.3.10.2580 и конфигурация Комплексная автоматизация.
Итак, была взята простейшая выборка — получение поля Дата от Регистратора из регистра накопления ВыручкаИСебестоимостьПродаж. У данного регистра несколько регистраторов, на снимке ниже.
Первоначально текст запроса следующий:
ВЫБРАТЬ ВыручкаИСебестоимостьПродаж.Регистратор.Дата ИЗ РегистрНакопления.ВыручкаИСебестоимостьПродаж КАК ВыручкаИСебестоимостьПродаж ГДЕ ВыручкаИСебестоимостьПродаж.Период МЕЖДУ &НачалоПериода И &КонецПериода
При выполнении такого запроса мы получаем следующий запрос на стороне MS SQL
exec sp_executesql N'SELECT CASE WHEN T1._RecorderTRef = 0x00000265 THEN T2._Date_Time WHEN T1._RecorderTRef = 0x00000303 THEN T3._Date_Time WHEN T1._RecorderTRef = 0x00000253 THEN T4._Date_Time WHEN T1._RecorderTRef = 0x000002CE THEN T5._Date_Time WHEN T1._RecorderTRef = 0x00000309 THEN T6._Date_Time WHEN T1._RecorderTRef = 0x00000305 THEN T7._Date_Time WHEN T1._RecorderTRef = 0x000002CD THEN T8._Date_Time WHEN T1._RecorderTRef = 0x00000366 THEN T9._Date_Time WHEN T1._RecorderTRef = 0x00000242 THEN T10._Date_Time WHEN T1._RecorderTRef = 0x00000307 THEN T11._Date_Time WHEN T1._RecorderTRef = 0x00000264 THEN T12._Date_Time WHEN T1._RecorderTRef = 0x0000031D THEN T13._Date_Time WHEN T1._RecorderTRef = 0x00000364 THEN T14._Date_Time ELSE CAST(NULL AS DATETIME) END FROM dbo._AccumRg46652 T1 LEFT OUTER JOIN dbo._Document613 T2 ON (T1._RecorderTRef = 0x00000265 AND T1._RecorderRRef = T2._IDRRef) AND (T2._Fld1794 = @P1) LEFT OUTER JOIN dbo._Document771 T3 ON (T1._RecorderTRef = 0x00000303 AND T1._RecorderRRef = T3._IDRRef) AND (T3._Fld1794 = @P2) LEFT OUTER JOIN dbo._Document595 T4 ON (T1._RecorderTRef = 0x00000253 AND T1._RecorderRRef = T4._IDRRef) AND (T4._Fld1794 = @P3) LEFT OUTER JOIN dbo._Document718 T5 ON (T1._RecorderTRef = 0x000002CE AND T1._RecorderRRef = T5._IDRRef) AND (T5._Fld1794 = @P4) LEFT OUTER JOIN dbo._Document777 T6 ON (T1._RecorderTRef = 0x00000309 AND T1._RecorderRRef = T6._IDRRef) AND (T6._Fld1794 = @P5) LEFT OUTER JOIN dbo._Document773 T7 ON (T1._RecorderTRef = 0x00000305 AND T1._RecorderRRef = T7._IDRRef) AND (T7._Fld1794 = @P6) LEFT OUTER JOIN dbo._Document717 T8 ON (T1._RecorderTRef = 0x000002CD AND T1._RecorderRRef = T8._IDRRef) AND (T8._Fld1794 = @P7) LEFT OUTER JOIN dbo._Document870 T9 ON (T1._RecorderTRef = 0x00000366 AND T1._RecorderRRef = T9._IDRRef) AND (T9._Fld1794 = @P8) LEFT OUTER JOIN dbo._Document578 T10 ON (T1._RecorderTRef = 0x00000242 AND T1._RecorderRRef = T10._IDRRef) AND (T10._Fld1794 = @P9) LEFT OUTER JOIN dbo._Document775 T11 ON (T1._RecorderTRef = 0x00000307 AND T1._RecorderRRef = T11._IDRRef) AND (T11._Fld1794 = @P10) LEFT OUTER JOIN dbo._Document612 T12 ON (T1._RecorderTRef = 0x00000264 AND T1._RecorderRRef = T12._IDRRef) AND (T12._Fld1794 = @P11) LEFT OUTER JOIN dbo._Document797 T13 ON (T1._RecorderTRef = 0x0000031D AND T1._RecorderRRef = T13._IDRRef) AND (T13._Fld1794 = @P12) LEFT OUTER JOIN dbo._Document868 T14 ON (T1._RecorderTRef = 0x00000364 AND T1._RecorderRRef = T14._IDRRef) AND (T14._Fld1794 = @P13) WHERE ((T1._Fld1794 = @P14)) AND (((T1._Period >= @P15) AND (T1._Period <= @P16)))',N'@P1 numeric(10),@P2 numeric(10),@P3 numeric(10),@P4 numeric(10),@P5 numeric(10),@P6 numeric(10),@P7 numeric(10),@P8 numeric(10),@P9 numeric(10),@P10 numeric(10),@P11 numeric(10),@P12 numeric(10),@P13 numeric(10),@P14 numeric(10),@P15 datetime2(3),@P16 datetime2(3)',0,0,0,0,0,0,0,0,0,0,0,0,0,0,'4017-11-01 00:00:00','4017-11-30 00:00:00'
Как видим в нем очень много соединений, это все наши документы. Попробуем ограничить выборку, использовав функцию ВЫРАЗИТЬ. Получаем следующий запрос:
ВЫБРАТЬ ВЫРАЗИТЬ(ВыручкаИСебестоимостьПродаж.Регистратор КАК Документ.РеализацияТоваровУслуг).Дата КАК Дата ИЗ РегистрНакопления.ВыручкаИСебестоимостьПродаж КАК ВыручкаИСебестоимостьПродаж ГДЕ ВыручкаИСебестоимостьПродаж.Период МЕЖДУ &НачалоПериода И &КонецПериода
На стороне SQL:
exec sp_executesql N'SELECT T2._Date_Time FROM dbo._AccumRg46652 T1 LEFT OUTER JOIN dbo._Document870 T2 ON (T1._RecorderTRef = 0x00000366 AND T1._RecorderRRef = T2._IDRRef) AND (T2._Fld1794 = @P1) WHERE ((T1._Fld1794 = @P2)) AND (((T1._Period >= @P3) AND (T1._Period <= @P4)))',N'@P1 numeric(10),@P2 numeric(10),@P3 datetime2(3),@P4 datetime2(3)',0,0,'4017-11-01 00:00:00','4017-11-30 00:00:00'
Но одновременно с этим еще получаем NULL в результатах. По всей видимости, строка в AccumRg46652 выбирается, но так как левым соединением не соединена соответствующая ему таблица, то получаем NULL.
Попробуем наложить отбор в секции ГДЕ с использованием функции ССЫЛКА.
ВЫБРАТЬ ВыручкаИСебестоимостьПродаж.Регистратор.Дата КАК Дата ИЗ РегистрНакопления.ВыручкаИСебестоимостьПродаж КАК ВыручкаИСебестоимостьПродаж ГДЕ ВыручкаИСебестоимостьПродаж.Период МЕЖДУ &НачалоПериода И &КонецПериода И ВыручкаИСебестоимостьПродаж.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг
На стороне SQL получаем следующую картину:
exec sp_executesql N'SELECT CASE WHEN T1._RecorderTRef = 0x00000265 THEN T2._Date_Time WHEN T1._RecorderTRef = 0x00000303 THEN T3._Date_Time WHEN T1._RecorderTRef = 0x00000253 THEN T4._Date_Time WHEN T1._RecorderTRef = 0x000002CE THEN T5._Date_Time WHEN T1._RecorderTRef = 0x00000309 THEN T6._Date_Time WHEN T1._RecorderTRef = 0x00000305 THEN T7._Date_Time WHEN T1._RecorderTRef = 0x000002CD THEN T8._Date_Time WHEN T1._RecorderTRef = 0x00000366 THEN T9._Date_Time WHEN T1._RecorderTRef = 0x00000242 THEN T10._Date_Time WHEN T1._RecorderTRef = 0x00000307 THEN T11._Date_Time WHEN T1._RecorderTRef = 0x00000264 THEN T12._Date_Time WHEN T1._RecorderTRef = 0x0000031D THEN T13._Date_Time WHEN T1._RecorderTRef = 0x00000364 THEN T14._Date_Time ELSE CAST(NULL AS DATETIME) END FROM dbo._AccumRg46652 T1 LEFT OUTER JOIN dbo._Document613 T2 ON (T1._RecorderTRef = 0x00000265 AND T1._RecorderRRef = T2._IDRRef) AND (T2._Fld1794 = @P1) LEFT OUTER JOIN dbo._Document771 T3 ON (T1._RecorderTRef = 0x00000303 AND T1._RecorderRRef = T3._IDRRef) AND (T3._Fld1794 = @P2) LEFT OUTER JOIN dbo._Document595 T4 ON (T1._RecorderTRef = 0x00000253 AND T1._RecorderRRef = T4._IDRRef) AND (T4._Fld1794 = @P3) LEFT OUTER JOIN dbo._Document718 T5 ON (T1._RecorderTRef = 0x000002CE AND T1._RecorderRRef = T5._IDRRef) AND (T5._Fld1794 = @P4) LEFT OUTER JOIN dbo._Document777 T6 ON (T1._RecorderTRef = 0x00000309 AND T1._RecorderRRef = T6._IDRRef) AND (T6._Fld1794 = @P5) LEFT OUTER JOIN dbo._Document773 T7 ON (T1._RecorderTRef = 0x00000305 AND T1._RecorderRRef = T7._IDRRef) AND (T7._Fld1794 = @P6) LEFT OUTER JOIN dbo._Document717 T8 ON (T1._RecorderTRef = 0x000002CD AND T1._RecorderRRef = T8._IDRRef) AND (T8._Fld1794 = @P7) LEFT OUTER JOIN dbo._Document870 T9 ON (T1._RecorderTRef = 0x00000366 AND T1._RecorderRRef = T9._IDRRef) AND (T9._Fld1794 = @P8) LEFT OUTER JOIN dbo._Document578 T10 ON (T1._RecorderTRef = 0x00000242 AND T1._RecorderRRef = T10._IDRRef) AND (T10._Fld1794 = @P9) LEFT OUTER JOIN dbo._Document775 T11 ON (T1._RecorderTRef = 0x00000307 AND T1._RecorderRRef = T11._IDRRef) AND (T11._Fld1794 = @P10) LEFT OUTER JOIN dbo._Document612 T12 ON (T1._RecorderTRef = 0x00000264 AND T1._RecorderRRef = T12._IDRRef) AND (T12._Fld1794 = @P11) LEFT OUTER JOIN dbo._Document797 T13 ON (T1._RecorderTRef = 0x0000031D AND T1._RecorderRRef = T13._IDRRef) AND (T13._Fld1794 = @P12) LEFT OUTER JOIN dbo._Document868 T14 ON (T1._RecorderTRef = 0x00000364 AND T1._RecorderRRef = T14._IDRRef) AND (T14._Fld1794 = @P13) WHERE ((T1._Fld1794 = @P14)) AND (((T1._Period >= @P15) AND (T1._Period <= @P16)) AND (T1._RecorderTRef = 0x00000366))',N'@P1 numeric(10),@P2 numeric(10),@P3 numeric(10),@P4 numeric(10),@P5 numeric(10),@P6 numeric(10),@P7 numeric(10),@P8 numeric(10),@P9 numeric(10),@P10 numeric(10),@P11 numeric(10),@P12 numeric(10),@P13 numeric(10),@P14 numeric(10),@P15 datetime2(3),@P16 datetime2(3)',0,0,0,0,0,0,0,0,0,0,0,0,0,0,'4017-11-01 00:00:00','4017-11-30 00:00:00'
Таким образом можно сделать итог, что использование функции ССЫЛКА в секции ГДЕ на стороне SQL не отсекает лишние соединения и единственное, что может помочь оптимизировать запрос при разыменовании составного типа данных, это функция ВЫРАЗИТЬ. Но ее необходимо использовать с пониманием механизмов на стороне SQL, чтобы не получить нежелательные NULL, либо дополнительно использовать с функцией ССЫЛКА в секции ГДЕ.