Возвращение 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


 


Идейно она практически повторяет http://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 мин., ошибок памяти не возникло.


Comments (0)

Skip to main content