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

Откуда:
Сообщений: 8
Есть таблица T1: Col1 primary key clustered, Col2 foreign key на таблицу T2(Col2).
Создана материализованная вьюха VT1 на таблице T1:
select Col2, count_big(*) as Cnt from T1
group by Col2
кластерный индекс вьюхи VT1 по столбцу Col2.
В таблице T1 около 20 млн строк. Таблица T2 - маленький справочник около 1-ой тысячи строк.
Я хочу иметь возможность быстро удалять строки из справочника T2.
Для этого можно создать индекс в таблице T1 по столбцу Col2. Но таблица T1 большая => индекс тоже будет много весить. Второй способ как то подсказать sql-серверу чтобы при удалении из таблицы T2 он пользовался вьюхой VT1. Можно ли это сделать?
16 сен 15, 13:40    [18155825]     Ответить | Цитировать Сообщить модератору
 Re: Indexed View для оптимизации скорости удаления  [new]
o-o
Guest
ничего не понимаю.
есть таблица в 1000 строк и из нее медленно удаляется?
это же наверное 1 страница всего.
а чем больше придется пересчитывать всяческих мат. вьюх при удалении,
тем дольше все будет происходить.
так зачем еще что-то создавать?
16 сен 15, 14:01    [18156043]     Ответить | Цитировать Сообщить модератору
 Re: Indexed View для оптимизации скорости удаления  [new]
Romarik
Member

Откуда:
Сообщений: 8
o-o
есть таблица в 1000 строк и из нее медленно удаляется?

Из таблицы-справочника T2 медленно удаляется потому, что есть таблица T1, содержащая 20 млн. строк и ссылающаяся на таблицу T2. Чтобы удалить из T2, нужно убедиться, что этого ключа нет в таблице T1.
16 сен 15, 14:04    [18156082]     Ответить | Цитировать Сообщить модератору
 Re: Indexed View для оптимизации скорости удаления  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Romarik
o-o
есть таблица в 1000 строк и из нее медленно удаляется?

Из таблицы-справочника T2 медленно удаляется потому, что есть таблица T1, содержащая 20 млн. строк и ссылающаяся на таблицу T2. Чтобы удалить из T2, нужно убедиться, что этого ключа нет в таблице T1.
Кто на кого ссылается?
Имеются в виду FK?
Скрипты CREATE TABLE с PK и FK показать можете?
16 сен 15, 14:06    [18156101]     Ответить | Цитировать Сообщить модератору
 Re: Indexed View для оптимизации скорости удаления  [new]
o-o
Guest
Romarik
o-o
есть таблица в 1000 строк и из нее медленно удаляется?

Из таблицы-справочника T2 медленно удаляется потому, что есть таблица T1, содержащая 20 млн. строк и ссылающаяся на таблицу T2. Чтобы удалить из T2, нужно убедиться, что этого ключа нет в таблице T1.

ну. и еще потому, что вьюха пересчитывается.
так чтоб этого не было, надо все поудалять. и ФК, и вью.
а добавлять зачем, чтоб еще больше думало?

или надо "использовать имеющееся мат. вью"?
а оно чем поможет, если удалять надо все равно из T1?
надо же найти физически где лежит то значение в T1.
и что, мат.вью знает, где удаляемое значение лежит в T1?
т.е. я никак не пойму, зачем вообще мат. вью при удалении?
16 сен 15, 14:12    [18156149]     Ответить | Цитировать Сообщить модератору
 Re: Indexed View для оптимизации скорости удаления  [new]
Romarik
Member

Откуда:
Сообщений: 8
Мы можем удалять значение X из справочника T2 только если на удаляемое значение не ссылаются в таблице T1. Если в индексированной вьюхе VT1 есть значение X => удалять из таблицы T2 нельзя - нарушится FK из T1 --> T2.
Если в индексированной вьюхе VT1 нет значения X => удалять из таблицы T2 можно, т.к. на X нет ссылки из таблицы T1 и дополнительно не надо искать в большой таблице T1 существование значения Col2 = X.
16 сен 15, 14:28    [18156269]     Ответить | Цитировать Сообщить модератору
 Re: Indexed View для оптимизации скорости удаления  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Однако же, можно:
  • сделать FK ON DELETE CASCADE
  • написать для VIEW триггер INSTEAD OF DELETE, в котором сначала удалять из T1, а уж потом из T2
  • 16 сен 15, 14:31    [18156286]     Ответить | Цитировать Сообщить модератору
     Re: Indexed View для оптимизации скорости удаления  [new]
    iap
    Member

    Откуда: Москва
    Сообщений: 47001
    iap
    Однако же, можно:
  • сделать FK ON DELETE CASCADE
  • написать для VIEW триггер INSTEAD OF DELETE, в котором сначала удалять из T1, а уж потом из T2
  • Или даже триггер не для VIEW, а для T2
    16 сен 15, 14:32    [18156292]     Ответить | Цитировать Сообщить модератору
     Re: Indexed View для оптимизации скорости удаления  [new]
    iap
    Member

    Откуда: Москва
    Сообщений: 47001
    iap
    iap
    Однако же, можно:
  • сделать FK ON DELETE CASCADE
  • написать для VIEW триггер INSTEAD OF DELETE, в котором сначала удалять из T1, а уж потом из T2
  • Или даже триггер не для VIEW, а для T2
    Можно и не удалять из T1, а проапдейтить NULLом
    16 сен 15, 14:33    [18156303]     Ответить | Цитировать Сообщить модератору
     Re: Indexed View для оптимизации скорости удаления  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9413
    Romarik
    Второй способ как то подсказать sql-серверу чтобы при удалении из таблицы T2 он пользовался вьюхой VT1. Можно ли это сделать?
    При наличии DRI - нет. Можете отказаться от DRI и поддерживать целостность триггерами. В них и реализовать желаемое.
    Romarik
    Но таблица T1 большая => индекс тоже будет много весить.
    Попробуйте примерно прикинуть сколько будет весить индекс на 20 млн. строк из 2-х int'ов.
    16 сен 15, 14:38    [18156341]     Ответить | Цитировать Сообщить модератору
     Re: Indexed View для оптимизации скорости удаления  [new]
    o-o
    Guest
    Romarik
    Мы можем удалять значение X из справочника T2 только если на удаляемое значение не ссылаются в таблице T1

    Вы об этом в самом начале не писали.
    Может, каскадное удаление выставлено, кто же знает? И если так, то снова: при чем тут вью?
    16 сен 15, 14:38    [18156343]     Ответить | Цитировать Сообщить модератору
     Re: Indexed View для оптимизации скорости удаления  [new]
    будет много весить
    Guest
    o-o
    Romarik
    Мы можем удалять значение X из справочника T2 только если на удаляемое значение не ссылаются в таблице T1

    Вы об этом в самом начале не писали.
    Может, каскадное удаление выставлено, кто же знает? И если так, то снова: при чем тут вью?

    так нет, он же наоборот никакого каскада не хочет.
    хочет чтоб запрет на удаление был, но работал быстро.
    для этого вместо создания индекса по FK-полю придумал материализованную вьюху, которая скорее всего дословно "select distinct <FK поле> from 20M".
    теперь хочет чтобы на этом квадратном велосипеде можно было сальто крутить.

    автор
    Для этого можно создать индекс в таблице T1 по столбцу Col2. Но таблица T1 большая => индекс тоже будет много весить. Второй способ как то подсказать sql-серверу чтобы при удалении из таблицы T2 он пользовался вьюхой VT1. Можно ли это сделать?
    16 сен 15, 14:56    [18156472]     Ответить | Цитировать Сообщить модератору
     Re: Indexed View для оптимизации скорости удаления  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9413
    Если же делать индекс или отказываться от DRI не хочется и удаления из справочника будут редки, то можно примерно так сделать:
    +
    use tempdb;
    go
    
    create table dbo.t1 (id int primary key);
    create table dbo.t2 (id int primary key, t1_id int not null constraint FK_t2_t1 foreign key references dbo.t1 (id));
    go
    
    create view dbo.vt2
    with schemabinding
    as
    select
     t1_id, count_big(*) as c
    from
     dbo.t2
    group by
     t1_id;
    go
    
    create unique clustered index IX_vt2__t1_id on dbo.vt2 (t1_id);
    go
    
    create trigger dbo.tr_t2_iod
    on dbo.t1
    instead of delete
    as
    begin
     set nocount on;
    
     alter table dbo.t2 nocheck constraint FK_t2_t1;
    
     if exists(select * from deleted d join dbo.vt2 t on t.t1_id = d.id)
      begin
       raiserror('...', 16, 1);
       rollback;
       return;
      end;
    
     delete t from deleted d join dbo.t1 t on t.id = d.id;
    
     alter table dbo.t2 with nocheck check constraint FK_t2_t1;
    end;
    go
    
    insert into dbo.t1 values (1), (2);
    insert into dbo.t2 values (1, 1), (2, 1);
    go
    
    delete from dbo.t1 where id = 1;
    go
    
    drop view dbo.vt2;
    drop table dbo.t2, dbo.t1;
    go
    
    16 сен 15, 15:04    [18156547]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить