Возвращение 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 мин., ошибок памяти не возникло.