Возвращение SqlXml

Когда-то в стародавние времена, когда выходили технологии OLE, OLE2, все думали, что в лучших традициях сериалов следующей будет «Возвращение OLE» (c) dimaa. Под возвращением SqlXml в данном случае будем понимать не продолжение сериала, а возвращение его в качестве результата из UDF. Напишем на эту тему простейшую функцию

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.IO;

using System.Xml;

using System.Text;

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlXml Function1()

    {

        MemoryStream ms = new MemoryStream();

        XmlTextWriter xtw = new XmlTextWriter(ms, Encoding.GetEncoding(1251));

        xtw.WriteStartDocument();

        xtw.WriteStartElement("Props");

        xtw.WriteEndElement();

        xtw.WriteEndDocument();

        xtw.Flush();

        SqlXml xml = new SqlXml(ms);

        //xtw.Close(); ms.Close();

        return xml;

    }

};

Скрипт 1

Идейно она практически повторяет https://www.codeproject.com/KB/reporting-services/PassingArraysSQLParameter.aspx?display=PrintAll за исключением закомментаренной строчки. Если честно, я не очень понимаю, как у человека будет работать функция GetXml(), потому что сразу по выходе из using, когда произойдет диспозал XmlWriter, внутри SqlXml не будет ничего. В этом плане поведение SqlXml ровно идентично поведению SqlBytes - см. «Импорт/экспорт блобов в файлы»\Способ 2. По-видимому, ни тот, ни другой не имеют в своей реализации персистентного сториджа, ограничиваясь ссылкой на стрим, которым их инициализировали. Закрытие стрима приводит к потере данных из переменной SqlXml/ SqlBytes. Персистенция экземпляра типа наступает, когда мы оперируем не с локальной переменной в памяти (которой, в частности, является результат функции), а с полем таблицы: см. Импорт/экспорт блобовских полей в файлы - CLR. Что же делать, если XML не слишком велик и мы хотим положить его в локальную переменную SQL Server? Очевидно, в данном примере не нужно закрывать XmlTextWriter / MemoryStream, полагаясь на то, что команду на их диспозал выдаст SQL Serverный CLR, когда сочтет, что они ему больше без надобности. Именно поэтому я волевым решением закомментарил строчку //xtw.Close(); ms.Close(); в коде функции. Однако существует опасение, не останутся ли они болтаться в памяти надолго и не вызовет ли это ее преждевременное окончание. Чтобы это проверить, я решил соорудить достаточно большой XML и погонять функцию в цикле. Изменим код Скрипт 1 на

[Microsoft.SqlServer.Server.SqlFunction]

    public static SqlXml Function1()

    {

        MemoryStream ms = new MemoryStream();

        XmlTextWriter xtw = new XmlTextWriter(ms, Encoding.GetEncoding(1251));

        xtw.WriteStartDocument();

        xtw.WriteStartElement("Props");

        for (int i = 0; i < 10000000; i++)

        {

            xtw.WriteStartElement("Prop");

            xtw.WriteEndElement();

        }

        xtw.WriteEndElement();

        xtw.WriteEndDocument();

        xtw.Flush();

        SqlXml xml = new SqlXml(ms);

        XmlDocument doc = new XmlDocument(); doc.LoadXml(xml.Value); doc.Save(@"c:\Temp\aaa.xml");

        //xtw.Close(); ms.Close();

        return xml;

    }

Скрипт 2

В XML набабахивается абсолютно dummy контент для раздувания объема, и для наглядности этот XML сохраняется в виде файла. Вызываем функцию из SQL Server:

use SQLExpressDemo

select dbo.Function1()

Скрипт 3

и получаем окончание памяти:

Msg 6532, Level 16, State 49, Line 1

.NET Framework execution was aborted by escalation policy because of out of memory.

System.Threading.ThreadAbortException: Thread was being aborted.

System.Threading.ThreadAbortException:

   at System.IO.MemoryStream.set_Capacity(Int32 value)

   at System.IO.MemoryStream.EnsureCapacity(Int32 value)

   at System.IO.MemoryStream.Write(Byte[] buffer, Int32 offset, Int32 count)

   at System.IO.StreamWriter.Flush(Boolean flushStream, Boolean flushEncoder)

   at System.IO.StreamWriter.Write(String value)

   at System.Xml.XmlTextWriter.WriteStartElement(String prefix, String localName, String ns)

   at System.Xml.XmlWriter.WriteStartElement(String localName)

   at UserDefinedFunctions.Function1()

Очевидно, 10 млн. не помещается у него в памяти ни разу. Опять же абсолютно аналогичную ситуацию мы наблюдали в случае SqlBytes, когда хотели затолкать в локальную переменную более-менее весомый файл - «Импорт/экспорт блобов в файлы»\Скрипт 7. Хорошо, умерим аппетит, пусть будет не 10, а 1 миллион. В этом случае Скрипты 2, 3 отрабатывают нормально, и мы можем оценить размер сгенерированого XML по файлу aaa.xml - 11.7 MB. По идее, если SQL Server освобождает XMLные стримы несвоевременно, нам достаточно запустить функцию Function1() 10 раз, чтобы память кончилась. Хорошо, пусть 20, потому что я закомментарю строчку XmlDocument doc = new XmlDocument(); doc.LoadXml(xml.Value); doc.Save(@"c:\Temp\aaa.xml"), чтобы не тратить время на сохранение в файл, коль скоро его размер уже известен. Не будем мелочиться и сделаем в T-SQL цикл на 1000.

declare @i int = 0, @x xml

while @i < 1000 begin

set @x = dbo.Function1()

print @i

set @i += 1

end

Скрипт 4

Скрипт выполнился за 26 мин., ошибок памяти не возникло.