Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 вставка данных в таблицу с разбором значений полей на лету  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
Добрый день, подскажите, пожалуйста, как изящнее решить следующую задачу:

1. Есть программка, которая только того и умеет, что вставлять в базу данных пары значений (calldate, userfield), вызывая
insert into [...smth...] (calldate, userfield) values (дата, значение)
, где имя таблицы настраивается

2. При этом оказывается, что значение userfield собрано из двух различных по смыслу значений (будем считать их номерами телефонов, а сам процесс - регистрацией телефонных звонков), разделенных спецсимволом, приблизительно так:
userfield = 'телефон абонента * телефон оператора'


3. Необходимо, чтоб все эти данные клались в БД, в определенную "целевую" таблицу, однако разная по смыслу информация находилась в различных колонках этой таблицы.


Сразу скажу, что от использования вычисляемых колонок я отказался: в моей ситуации по вычисляемой колонке нельзя создать индекс (индекс требует установки параметра сессии arithabort в on, а программка коннектится с установками по умолчанию, соотв., попытка выполнить insert при существующем индексе приводит к ошибке), а создать в целевой таблице persisted колонки непросто из-за большого количества уже существующих строк (порядка 10 млн).

Для решения данной задачи я хочу создать некоторое фиктивное представление для целевой таблицы, триггер "instead off" для него, и вставлять данные в целевую таблицу, делая insert не в таблицу, а в представление приблизительно так:

create table tb_target(
  calldate datetime primary key,
  callerid varchar(10),
  calledid varchar(10)
); -- целевая таблица

create table tb_dummy(
  userfield varchar(max)
);

go

create view vw_target
  as
select
  calldate,
  userfield
from
  tb_target
left join
  tb_dummy
on
  1=2

go

create trigger vw_target_trg
on dbo.vw_target
instead of insert
as
begin
  insert into tb_target 
    (calldate, callerid, calledid)
  select
    calldate,
    left(userfield,i-1),
    substring(userfield,i+1,666) 
  from (
    select calldate, userfield, charindex('*',userfield) i from inserted
  ) ins
end
go

insert into vw_target (calldate,userfield) values (getDate(),'6666666666*7777777777')

select * from tb_target

Зачем здесь нужно представление, я надеюсь, понятно: чтобы не нужно было хранить разбираемое триггером поле в БД.

А теперь вопрос к знатокам: где здесь грабли? В первую очередь, в плане производительности. Каким бы было лучшее решение?

Данные в tb_target кладутся параллельно, общий поток данных большой.
2 июн 11, 13:04    [10751975]     Ответить | Цитировать Сообщить модератору
 Re: вставка данных в таблицу с разбором значений полей на лету  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Зачем dummy таблица? Можно без нее.
create table tb_target(
  calldate datetime primary key,
  callerid varchar(10),
  calledid varchar(10),
); -- целевая таблица

go

create view vw_target
  as
select
  calldate,
  callerid + '*' + calledid as userfield
from
  tb_target

go

create trigger vw_target_trg
on dbo.vw_target
instead of insert
as
begin
  insert into tb_target 
    (calldate, callerid, calledid)
  select
    calldate,
    left(userfield,i-1),
    substring(userfield,i+1,666) 
  from (
    select calldate, userfield, charindex('*',userfield) i from inserted
  ) ins
end
go

insert into vw_target (calldate,userfield) values (getDate(),'6666666666*7777777777')

select * from tb_target
И возьмите себе за правило всегда явно указывать схему объектов.
2 июн 11, 13:15    [10752063]     Ответить | Цитировать Сообщить модератору
 Re: вставка данных в таблицу с разбором значений полей на лету  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
invm
Зачем dummy таблица? Можно без нее.
...
go

create view vw_target
  as
select
  calldate,
  callerid + '*' + calledid as userfield
from
  tb_target

go
...

insert into vw_target (calldate,userfield) values (getDate(),'6666666666*7777777777')

select * from tb_target


О, действительно. Благодарю.

invm
И возьмите себе за правило всегда явно указывать схему объектов.


Можно спросить, почему? Интуитивно, я, наоборот, взял себе за правило НЕ указывать схему объектов, но основания к этому у меня, в основном, эстетические. Чем я рискую, и какие технические основания есть к тому, чтобы указывать схему?
2 июн 11, 14:17    [10752730]     Ответить | Цитировать Сообщить модератору
 Re: вставка данных в таблицу с разбором значений полей на лету  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Shlippenbaranus
Можно спросить, почему? Интуитивно, я, наоборот, взял себе за правило НЕ указывать схему объектов, но основания к этому у меня, в основном, эстетические. Чем я рискую, и какие технические основания есть к тому, чтобы указывать схему?

//http://dinesql.blogspot.com/2011/05/object-name-resolution-sql-server.html
2 июн 11, 14:41    [10753026]     Ответить | Цитировать Сообщить модератору
 Re: вставка данных в таблицу с разбором значений полей на лету  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
А лучше вот этот документ почитайте.
2 июн 11, 14:57    [10753203]     Ответить | Цитировать Сообщить модератору
 Re: вставка данных в таблицу с разбором значений полей на лету  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
invm
А лучше вот этот документ почитайте.


Спасибо. Что-то там не то написано. Вот этот код
use tempdb
exec dbo.xp_enum_oledb_providers
в реальности дает ошибку, тогда как по схеме из документа получается, что не найдя процедуру в tempdb.dbo, движок должен отправиться искать ее в master.dbo, где она и найдется.

Т.е. по схеме приведенный код должен дать тот же результат, что и
use tempdb
exec xp_enum_oledb_providers
, который благополучно отрабатывает.
2 июн 11, 19:19    [10755352]     Ответить | Цитировать Сообщить модератору
 Re: вставка данных в таблицу с разбором значений полей на лету  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Shlippenbaranus
индекс требует установки параметра сессии arithabort в on, а программка коннектится с установками по умолчанию, соотв., попытка выполнить insert при существующем индексе приводит к ошибке
Упёртым надо быть. Установки по умолчанию можно изменить. В свойствах сервера галку поставьте и будет вам щасье. Или скрипт.
EXEC sp_configure 'user options', 64
RECONFIGURE WITH OVERRIDE;
ArithAbort ON Это из области must set.
2 июн 11, 20:04    [10755511]     Ответить | Цитировать Сообщить модератору
 Re: вставка данных в таблицу с разбором значений полей на лету  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Shlippenbaranus
invm
А лучше вот этот документ почитайте.


Спасибо. Что-то там не то написано. Вот этот код
use tempdb
exec dbo.xp_enum_oledb_providers
в реальности дает ошибку, тогда как по схеме из документа получается, что не найдя процедуру в tempdb.dbo, движок должен отправиться искать ее в master.dbo, где она и найдется.

Т.е. по схеме приведенный код должен дать тот же результат, что и
use tempdb
exec xp_enum_oledb_providers
, который благополучно отрабатывает.

Может там действительно и не то написано. Сейчас, честно говоря, лень разбираться. Суть в том, что если вы не указываете явно схему при обращении к объекту и в БД появится юзер с дефолтной схемой не dbo, то вас ожидают "чудеса".
3 июн 11, 00:08    [10756421]     Ответить | Цитировать Сообщить модератору
 Re: вставка данных в таблицу с разбором значений полей на лету  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Shlippenbaranus
xp_...
На не то вы смотрите, частности с "xp_" и "sp_" мало кого интересуют и про master забудте.
Поймите главное. Если схема указана, то компилятор ничего не ищет, а тупо берёт объект. План запроса поэтому однозначный, теперь не нужно перед вызовом процедуры каждый раз проверять "окружение" и искать объекты, схема железобетонно всё закрепила.
Видимость объектов и правила квалификации
SQL Server Best Practices – Implementation of Database Object Schemas:
BOL
Conclusion:
  • Always refer to the objects using a fully qualified name. At the very least, use the schema name followed by the object name, separated by a period (.).
  • 3 июн 11, 00:50    [10756490]     Ответить | Цитировать Сообщить модератору
     Re: вставка данных в таблицу с разбором значений полей на лету  [new]
    Shlippenbaranus
    Member

    Откуда:
    Сообщений: 241
    Mnior
    Упёртым надо быть. Установки по умолчанию можно изменить. В свойствах сервера галку поставьте и будет вам щасье. Или скрипт.
    EXEC sp_configure 'user options', 64
    RECONFIGURE WITH OVERRIDE;
    ArithAbort ON Это из области must set.

    Спасибо за совет.

    invm
    Суть в том, что если вы не указываете явно схему при обращении к объекту и в БД появится юзер с дефолтной схемой не dbo, то вас ожидают "чудеса".

    Mnior
    Поймите главное. Если схема указана, то компилятор ничего не ищет, а тупо берёт объект. План запроса поэтому однозначный, теперь не нужно перед вызовом процедуры каждый раз проверять "окружение" и искать объекты, схема железобетонно всё закрепила.


    Убедительно, но: это значит, что разработчик должен включать в системные требования имя той схемы, в которую должен быть "подсажен" его код. Больших ограничений из этого не проистекает - нужную степень свободы дает возможность выбрать имя базы, - но все равно, эстетически неприятно :). Интересно, зачем тогда в принципе в MS SQL добавлена возможность создавать разные схемы в пределах БД?

    (Впрочем, это вопрос риторический.)
    3 июн 11, 18:57    [10761299]     Ответить | Цитировать Сообщить модератору
     Re: вставка данных в таблицу с разбором значений полей на лету  [new]
    iap
    Member

    Откуда: Москва
    Сообщений: 47142
    Shlippenbaranus
    Интересно, зачем тогда в принципе в MS SQL добавлена возможность создавать разные схемы в пределах БД?
    Понятие "схема" описано в стандартах ISO и ANSI-92 (а может, и ещё раньше).
    Microsoft стремится со временем им соответствовать.
    3 июн 11, 19:27    [10761392]     Ответить | Цитировать Сообщить модератору
     Re: вставка данных в таблицу с разбором значений полей на лету  [new]
    invm
    Member

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

    Отделение пользователей от схем
    3 июн 11, 20:09    [10761536]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить