Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Indexed View и исключения в UDF  [new]
голактеко_опасносте
Guest
Добрый день,
имеется следующая проблема: сторонняя программа, использующая как СУБД mssql2008. В сей программе использовано мегарешение - дата и время хранится поле с типом binary(8), например - 0x800005C52301C230, и в итоге для преобразования в datetime каждый раз необходимо делать вызовы типа dbo.toMsDateTime(dbo.fromSEC100(T$RSVOPER.F$CROLEAN2)).
Вот пример этих чудо-функций (на каждое преобразование в итоге вызывается их штук 10) :
CREATE function [dbo].[fromSEC100](@DT BINARY(8)) returns BINARY(8) as
begin
  DECLARE @I BIGINT
  set @I = CONVERT(BIGINT,@DT)^0x8000000000000000
  return dbo.toAtlDateTime2(dbo.dtDATE(dbo.fromDAYS(FLOOR(@I/(24*60*60*100)))),dbo.tFromSEC100(@I % (24*60*60*100)))
end


CREATE function [dbo].[ToAtlDateTime2](@D int, @T int) returns BINARY(8) as
begin
  return CONVERT(BINARY(4), @D|0x80000000)+CONVERT(BINARY(4), @T)
end

CREATE function [dbo].[toMsDateTime](@DT BINARY(8)) returns DATETIME as
begin
  return CONVERT(DATETIME,
                   CONVERT(VARCHAR(4),dbo.dtYear(@DT))+'-'+
                   CONVERT(VARCHAR(2),dbo.dtMonth(@DT))+'-'+
                   CONVERT(VARCHAR(2),dbo.dtDay(@DT))+' '+
                   CONVERT(VARCHAR(2),dbo.dtHour(@DT))+':'+
                   CONVERT(VARCHAR(2),dbo.dtMinute(@DT))+':'+
                   CONVERT(VARCHAR(2),dbo.dtSecond(@DT))+'.'+
                   CONVERT(VARCHAR(2),dbo.dtSec100(@DT)),121);
end


Естественно, при построении отчётов по такой базе всё тормозит просто ЖУТКО, обрабатывается в среднем 6млн записей с такими
полями за отчёт. Дабы ускорить это дело, задумали создать индексированное представление с уже вычисленным полем даты. Да вот какое дело - если на вход этой чудо-функции подать значение, которое данными алгоритмами в datetime не преобразуется - выскочит эксепшн. Посему вопрос - можно ли написать функцию, возвращающую в случае неверных данных например NULL? В какую сторону смотреть? Или подход в корне неправильный и от indexed view толку особо не будет?
31 июл 12, 12:24    [12939657]     Ответить | Цитировать Сообщить модератору
 Re: Indexed View и исключения в UDF  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
По идее, достаточно вычисляемого поля. А вот что за эксепшн - не понятно.
31 июл 12, 12:27    [12939693]     Ответить | Цитировать Сообщить модератору
 Re: Indexed View и исключения в UDF  [new]
aleks2
Guest
1. Вот нафега вам View - вычисляемый материализованный столбец захерачте в таблицу.

2. Функцию написать можна - тока вам придется ЯВНО проверять данные. Перехват ошибки в скалярных UDF не предусмотрен.
31 июл 12, 12:33    [12939741]     Ответить | Цитировать Сообщить модератору
 Re: Indexed View и исключения в UDF  [new]
голактеко_опасносте
Guest
Гавриленко Сергей Алексеевич,

По идее, достаточно вычисляемого поля.

Боюсь исходную программулину поломать, внеся изменения в схему таблицы. Хрен его знает, где у них там вместо списка полей стоит *.

А вот что за эксепшн - не понятно.

SELECT dbo.toMsDateTime(dbo.fromSEC100(0x800005C52301C230))
2011-05-31 15:49:00.000

SELECT dbo.toMsDateTime(dbo.fromSEC100(0x000005C52301C230))
Msg 8115, Level 16, State 2, Line 8
Ошибка арифметического переполнения при преобразовании expression к типу данных int.
31 июл 12, 12:33    [12939746]     Ответить | Цитировать Сообщить модератору
 Re: Indexed View и исключения в UDF  [new]
Glory
Member

Откуда:
Сообщений: 104751
голактеко_опасносте
Посему вопрос - можно ли написать функцию, возвращающую в случае неверных данных например NULL? В какую сторону смотреть? Или подход в корне неправильный и от indexed view толку особо не будет?

В сторону проверки всех частей даты на валидность перед конвертацией в DATETIME
31 июл 12, 12:37    [12939779]     Ответить | Цитировать Сообщить модератору
 Re: Indexed View и исключения в UDF  [new]
голактеко_опасносте
Guest
Glory,
В сторону проверки всех частей даты на валидность перед конвертацией в DATETIME

а в данном случае не лучше написать CLR функцию?
31 июл 12, 13:08    [12940115]     Ответить | Цитировать Сообщить модератору
 Re: Indexed View и исключения в UDF  [new]
Glory
Member

Откуда:
Сообщений: 104751
голактеко_опасносте
а в данном случае не лучше написать CLR функцию?

А в CLR вы надеетесь не делать таких проверок что ли ?
31 июл 12, 13:11    [12940145]     Ответить | Цитировать Сообщить модератору
 Re: Indexed View и исключения в UDF  [new]
голактеко_опасносте
Guest
Glory,
почему ж не делать, просто покопавшись в "логике" работы этих вызывающих друг друга функций (их я насчитал 15, а дёргают друг друга они и вовсе огромное количество раз), сложилось впечатление, что проще переписать эту логику разбора с нуля, а вот переписывать это в TSQL или создать CLR ещё не решил.
31 июл 12, 13:26    [12940321]     Ответить | Цитировать Сообщить модератору
 Re: Indexed View и исключения в UDF  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
голактеко_опасносте,

ISDATE
31 июл 12, 13:32    [12940378]     Ответить | Цитировать Сообщить модератору
 Re: Indexed View и исключения в UDF  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
голактеко_опасносте
Glory,
почему ж не делать, просто покопавшись в "логике" работы этих вызывающих друг друга функций (их я насчитал 15, а дёргают друг друга они и вовсе огромное количество раз), сложилось впечатление, что проще переписать эту логику разбора с нуля, а вот переписывать это в TSQL или создать CLR ещё не решил.
Если переделывать в лоб - CLR быстрее будет. Если отреверсить по человечески, то не факт. Если материализовывать - то будет быстрее всего и там уже не важно, какая функция, была бы детерминированная.
31 июл 12, 13:33    [12940381]     Ответить | Цитировать Сообщить модератору
 Re: Indexed View и исключения в UDF  [new]
голактеко_опасносте
Guest
invm,
да, то что нужно, спасибо. Но уже захотелось переписать всё по-человечески)

Гавриленко Сергей Алексеевич,
собираюсь реверсить, там лишнего ооооочень много. что-то кажется, что вызов можно будет уложить в одну функцию строк в 10, и в конце проверять ISDATE(), тогда и без CLR обойдусь.

Всем спасибо.
31 июл 12, 13:46    [12940519]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить