Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 sqlclr: fn_WriteXML - кошерно ли ?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1130
Сделал фунуцию для выгрузки XML в файлы (каждый в xml в отдельный файл)
Но есть вопрос, насколько кошерно использоваьть sqlclr функцию для записи в файл?
Можно сделать и процедуру, но её придётся вызывать в цикле, а функцию можно прямо в селекте.

Буду благодарен за предмерную критику и советы.

<SqlFunction(Name:="fn_WriteXML", Isdeterministic:=False, IsPrecise:=False)> _
Public Shared Function fn_WriteXML(Value As SqlXml, FullFilePath As SqlString) As SqlBoolean

        If Value.IsNull Then
            Return New SqlBoolean(False)
        Else
            Using fs As New IO.FileStream(FullFilePath, IO.FileMode.CreateNew)
                Dim bytes() = System.Text.Encoding.UTF8.GetBytes(Value.Value)
                fs.Write(bytes, 0, bytes.Length)
            End Using
            Return New SqlBoolean(True)
        End If
End Function

<SqlFunction(Name:="fn_ReadXML", Isdeterministic:=False, IsPrecise:=False)> _
Public Shared Function fn_ReadXML(FullFilePath As SqlString) As SqlXml
        If FullFilePath.IsNull Then
            Return New SqlXml
        ElseIf IO.File.Exists(FullFilePath) Then
            Dim ds As New DataSet
            ds.ReadXml(FullFilePath)
            Dim ms As New IO.MemoryStream(System.Text.Encoding.UTF8.GetBytes(ds.GetXml))
            Return New SqlXml(ms)
        Else
            Return New SqlXml
        End If
End Function
16 июн 17, 16:46    [20570656]     Ответить | Цитировать Сообщить модератору
 Re: sqlclr: fn_WriteXML - кошерно ли ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7780
Alexander Us,

а если SSIS освоить для выгрузок?
16 июн 17, 16:56    [20570689]     Ответить | Цитировать Сообщить модератору
 Re: sqlclr: fn_WriteXML - кошерно ли ?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1130
Владислав Колосов
Alexander Us,
а если SSIS освоить для выгрузок?

SSIS в данном проекте не используется, нужно решение на уровне сервера баз.

Кроме того, написать select .... fn_WriteXML... гораздо быстрее чем то же в SSIS.
16 июн 17, 17:05    [20570714]     Ответить | Цитировать Сообщить модератору
 Re: sqlclr: fn_WriteXML - кошерно ли ?  [new]
msLex
Member

Откуда:
Сообщений: 8093
Alexander Us
Владислав Колосов
Alexander Us,
а если SSIS освоить для выгрузок?

SSIS в данном проекте не используется, нужно решение на уровне сервера баз.

Кроме того, написать select .... fn_WriteXML... гораздо быстрее чем то же в SSIS.

Вы же в курсе, что сервер имеет право вызвать любую из функций в запросе столько раз, сколько ему захочется?
16 июн 17, 17:07    [20570720]     Ответить | Цитировать Сообщить модератору
 Re: sqlclr: fn_WriteXML - кошерно ли ?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Alexander Us
Но есть вопрос, насколько кошерно использоваьть sqlclr функцию для записи в файл?
Да нормально...

Я бы только сделал табличную функцию, её нелзя случайно вызвать много раз.
А то напишите
select fn_WriteXML(XMLValue, FullXMLFilePath)
from XmlTable
where ID = @ID
а она вызовется для всех записей, хотя в условии фильтр по ПК.
16 июн 17, 17:10    [20570723]     Ответить | Цитировать Сообщить модератору
 Re: sqlclr: fn_WriteXML - кошерно ли ?  [new]
msLex
Member

Откуда:
Сообщений: 8093
alexeyvg
Я бы только сделал табличную функцию, её нелзя случайно вызвать много раз.
А то напишите
select fn_WriteXML(XMLValue, FullXMLFilePath)
from XmlTable
where ID = @ID

а она вызовется для всех записей, хотя в условии фильтр по ПК.



а так

select *
from XmlTable
cross apply fn_WriteXML(XMLValue, FullXMLFilePath)
where ID = @ID


не та же проблема?

ТС-у же нужна функция "прямо в селекте"
16 июн 17, 17:23    [20570746]     Ответить | Цитировать Сообщить модератору
 Re: sqlclr: fn_WriteXML - кошерно ли ?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1130
alexeyvg,

С табличной функцией попробую обязательно.

Пока что попробрвал:

declare @id int = 5

select 
 BusinessEntityID
,tools.dbo.fn_WriteXML
(
 (select * from AdventureWorks2014.Person.Person p2 where p1.BusinessEntityID=p2.BusinessEntityID for xml path('Table1'), Root('DocumentElement'))
,'G:\Dateien\TEST3\Person_' + cast(BusinessEntityID as varchar(20)) + '.xml'
)
from AdventureWorks2014.Person.Person p1 
where BusinessEntityID = @ID


Выгрузился ровно один файл. (хоия, это конечно не значит, что не может возникнуть ситуация, о которой Вы и msLex предупреждаете)

При повторных вызовах можно проверять есть ли уже файл.
Если есть, просто возврвщать 1.
16 июн 17, 17:25    [20570753]     Ответить | Цитировать Сообщить модератору
 Re: sqlclr: fn_WriteXML - кошерно ли ?  [new]
msLex
Member

Откуда:
Сообщений: 8093
Alexander Us
alexeyvg,

С табличной функцией попробую обязательно.

Пока что попробрвал:

declare @id int = 5

select 
 BusinessEntityID
,tools.dbo.fn_WriteXML
(
 (select * from AdventureWorks2014.Person.Person p2 where p1.BusinessEntityID=p2.BusinessEntityID for xml path('Table1'), Root('DocumentElement'))
,'G:\Dateien\TEST3\Person_' + cast(BusinessEntityID as varchar(20)) + '.xml'
)
from AdventureWorks2014.Person.Person p1 
where BusinessEntityID = @ID



Выгрузился ровно один файл. (хоия, это конечно не значит, что не может возникнуть ситуация, о которой Вы и msLex предупреждаете)

При повторных вызовах можно проверять есть ли уже файл.
Если есть, просто возврвщать 1.


Если прям сильно хочется селектом генерить много файлов, сделайте агрегатную функцию. В нее точно только то, что прошло все фильтры попадет.
16 июн 17, 17:30    [20570768]     Ответить | Цитировать Сообщить модератору
 Re: sqlclr: fn_WriteXML - кошерно ли ?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1130
msLex
сделайте агрегатную функцию. В нее точно только то, что прошло все фильтры попадет.

Спасибо за идею, пропробую обязательно.
16 июн 17, 17:32    [20570773]     Ответить | Цитировать Сообщить модератору
 Re: sqlclr: fn_WriteXML - кошерно ли ?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1130
msLex
Вы же в курсе, что сервер имеет право вызвать любую из функций в запросе столько раз, сколько ему захочется?


А как такой вариант можно репродуцировать?

Вот так, с table scan, получается тоже ровно по 1 разу:
(если конечно не писать fn_WriteXML ещё и в where)

select 
 BusinessEntityID
,PersonType 
,FirstName 
,LastName 
,(select * from AdventureWorks2014.Person.Person p2 where p1.BusinessEntityID=p2.BusinessEntityID for xml path('Table1'), Root('DocumentElement')) xmlVal
,'G:\Dateien\TEST3\Person_' + cast(BusinessEntityID as varchar(20)) + '.xml' FullPath
into #PERS
from AdventureWorks2014.Person.Person p1 

select 
 BusinessEntityID
,tools.dbo.fn_WriteXML(xmlVal, FullPath)
from #PERS
where LastName like '%ab%'
16 июн 17, 17:46    [20570807]     Ответить | Цитировать Сообщить модератору
 Re: sqlclr: fn_WriteXML - кошерно ли ?  [new]
msLex
Member

Откуда:
Сообщений: 8093
Alexander Us
msLex
Вы же в курсе, что сервер имеет право вызвать любую из функций в запросе столько раз, сколько ему захочется?


А как такой вариант можно репродуцировать?

Вот так, с table scan, получается тоже ровно по 1 разу:
(если конечно не писать fn_WriteXML ещё и в where)

select 
 BusinessEntityID
,PersonType 
,FirstName 
,LastName 
,(select * from AdventureWorks2014.Person.Person p2 where p1.BusinessEntityID=p2.BusinessEntityID for xml path('Table1'), Root('DocumentElement')) xmlVal
,'G:\Dateien\TEST3\Person_' + cast(BusinessEntityID as varchar(20)) + '.xml' FullPath
into #PERS
from AdventureWorks2014.Person.Person p1 
where LastName like '%ab%'

select 
 BusinessEntityID
,tools.dbo.fn_WriteXML(xmlVal, FullPath)
from #PERS

гарантированно только так
16 июн 17, 17:55    [20570837]     Ответить | Цитировать Сообщить модератору
 Re: sqlclr: fn_WriteXML - кошерно ли ?  [new]
aleks2
Guest
Что за хрень?
Какие-то кривые clr для записи xml.

FileTable + insert - все уже придумано.
16 июн 17, 18:26    [20570908]     Ответить | Цитировать Сообщить модератору
 Re: sqlclr: fn_WriteXML - кошерно ли ?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1130
aleks2,

спс,
посмотю для 2014.
но для 2008 наверное, остаётся использовать "хрень"?
16 июн 17, 18:36    [20570928]     Ответить | Цитировать Сообщить модератору
 Re: sqlclr: fn_WriteXML - кошерно ли ?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Alexander Us
Выгрузился ровно один файл. (хоия, это конечно не значит, что не может возникнуть ситуация, о которой Вы и msLex предупреждаете)
Это уж как оптимизатор решит.
Я вот натолкнулся на эту особенность, в коде, который работал до этого несколько лет без проблем.
msLex
а так
select *
from XmlTable
cross apply fn_WriteXML(XMLValue, FullXMLFilePath)
where ID = @ID

не та же проблема?
Вот не могу сказать, может, действительно так тоже может лишнее вызвать?

Гарантированно надёжно для скалярной функции - вызывать в CASE:
declare @id int = 5

select 
    BusinessEntityID
    ,CASE WHEN BusinessEntityID = @ID THEN tools.dbo.fn_WriteXML
          (
              (select * from AdventureWorks2014.Person.Person p2 where p1.BusinessEntityID=p2.BusinessEntityID for xml path('Table1'), Root('DocumentElement'))
              ,'G:\Dateien\TEST3\Person_' + cast(BusinessEntityID as varchar(20)) + '.xml'
          ) ELSE NULL END
from AdventureWorks2014.Person.Person p1 
where BusinessEntityID = @ID

Или действительно сделать агрегатную функцию.
17 июн 17, 00:08    [20571501]     Ответить | Цитировать Сообщить модератору
 Re: sqlclr: fn_WriteXML - кошерно ли ?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1130
alexeyvg,

Тогда проблему можно решать внутри функции:

-проверять есть ли уже файл, если да, выход
или
-приделать третий параметр, @write_if_true as bit

и вызывать например как то так:
...
tools.dbo.fn_WriteXML(@XML, @Path, IIF(BusinessEntityID = @ID,1,0))
where 
...  


PS: Ваш совет с аггрегатом обязательно порообую.
17 июн 17, 00:24    [20571529]     Ответить | Цитировать Сообщить модератору
 Re: sqlclr: fn_WriteXML - кошерно ли ?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Alexander Us
-проверять есть ли уже файл, если да, выход
Хм, а если нужно переписать файл ещё раз?
Ну и вообще, если оптимизатор вызовет функцию для всех записей, а не только для BusinessEntityID = @ID, то такая проверка не поможет (если конечно вам не надо выгружать файлы для всех записей).

Alexander Us
-приделать третий параметр, @write_if_true as bit

и вызывать например как то так:
...
tools.dbo.fn_WriteXML(@XML, @Path, IIF(BusinessEntityID = @ID,1,0))
where 
...  
Ну, это в общем ведь другая форма CASE, просто не снаружи функции, а при указании параметра. Это уж как вам удобнее.
17 июн 17, 00:32    [20571536]     Ответить | Цитировать Сообщить модератору
 Re: sqlclr: fn_WriteXML - кошерно ли ?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
alexeyvg
Ну, это в общем ведь другая форма CASE, просто не снаружи функции, а при указании параметра. Это уж как вам удобнее.
... но это будет медленнее, т.к. стоимость вызова скалярной функции сама по себе высока, и для большой таблицы замедление будет заметно "на глаз".
17 июн 17, 00:34    [20571540]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить