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

Откуда:
Сообщений: 12
У меня есть таблица примерно вот такого вида:


Организация Подразделение Отдел Группа Номер .....
----------- ------------- ----- ------ ------------- -------
01 01 01 01 01_01_01_01_1


  • Поле Номер должно быть уникально и заполняться автоматически (требования бизнеса).
  • Алгоритм заполнения: Организация + Подразделение + Отдел + Группа + [Очередной порядковый номер в пределах четырех предыдущих полей].
  • Табличка будет большая - основная таблица в БД, миллионы записей.
  • Пользователей много, 99% из них работают с этой таблицей. 80% чтений.

    Как лучше решить такую задачу?

    Делать


    SELECT MAX(...) FROM TABLE WHERE [Организация] = ... AND [Подразделение] = ... AND [Отдел] = .... AND [Группа] = ....

    даже при наличии индексов по этим полям мне кажется не очень удачной идеей.

    Autoincremented поля и SEQUENCE здесь не подходят в силу того, что нумерация не сквозная, а рамках группы записей.

    Пока думаю над вариантом создания еще одной таблицы, в которой будет храниться следующей номер, который должен быть выделен в пределах выбранной группы полей:


    Организация Подразделение Отдел Группа Следующий номер
    ----------- ------------- ----- ------ -------------
    01 01 01 01 1

    Соответвенно при вставке записи в основную таблицу в триггере получать значение [Следующий номер], использовать его в основной таблице, а потом увеличивать значение в вспомогательной таблице.

    Может есть решения лучше ?
  • 22 май 16, 07:43    [19203455]     Ответить | Цитировать Сообщить модератору
     Re: Последовательная нумерация в пределах группы записей  [new]
    expl0rer
    Member

    Откуда:
    Сообщений: 12
    Да, забыл добавить - сгенерированный номер не должен меняться для записи. Это номер, который пойдет в бумажные документы и по нему потом будет искаться запись.
    22 май 16, 07:47    [19203456]     Ответить | Цитировать Сообщить модератору
     Re: Последовательная нумерация в пределах группы записей  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 30778
    expl0rer
    Может есть решения лучше ?
    Посмотрите Организация пользовательских счетчиков (генераторов) в Microsoft SQL Server, оно уже стало классикой :-)
    22 май 16, 11:16    [19203592]     Ответить | Цитировать Сообщить модератору
     Re: Последовательная нумерация в пределах группы записей  [new]
    expl0rer
    Member

    Откуда:
    Сообщений: 12
    Да, спасибо - похоже то, что нужно. Я правильно понял, что на основную таблицу мне нужно повесить триггер на вставку. В этом триггере я буду проверять если у меня уже в таблице счетчиков счетчик с нужной комбинацией ключевых полей. Если нет - создавать его. А триггер повешенный на таблицу счетчиков будет создавать SEQUENCE и функции для работы с ней?
    22 май 16, 12:07    [19203654]     Ответить | Цитировать Сообщить модератору
     Re: Последовательная нумерация в пределах группы записей  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9122
    alexeyvg
    оно уже стало классикой :-)
    Классика - когда не нужна транзакционность при генерации.
    ТС'у же наоборот, транзакционность генерации нужна.

    expl0rer
    SELECT MAX(...) FROM TABLE WHERE [Организация] = ... AND [Подразделение] = ... AND [Отдел] = .... AND [Группа] = ....
    даже при наличии индексов по этим полям мне кажется не очень удачной идеей.
    Нормальная идея. И индекс нужен один: (Организация, Подразделение, Отдел, Группа, Номер).
    Запрос должен выглядеть так:
    SELECT @ПоследнийНомер = MAX(...) FROM TABLE with (serializable, updlock) WHERE [Организация] = ... AND [Подразделение] = ... AND [Отдел] = .... AND [Группа] = ....
    
    и быть в одной транзакции со вставкой в таблицу.

    Если потребуется одновременная генерации по нескольким сочетаниям (Организация, Подразделение, Отдел, Группа), запрос и индекс придется доработать.
    22 май 16, 12:35    [19203699]     Ответить | Цитировать Сообщить модератору
     Re: Последовательная нумерация в пределах группы записей  [new]
    expl0rer
    Member

    Откуда:
    Сообщений: 12
    invm
    Классика - когда не нужна транзакционность при генерации.


    А в чем именно нарушается транзакционность в предложенном решении? Точнее, какие именно следствия в моему случае вследствие нарушения транзакционности?
    22 май 16, 12:56    [19203773]     Ответить | Цитировать Сообщить модератору
     Re: Последовательная нумерация в пределах группы записей  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9122
    expl0rer
    Точнее, какие именно следствия в моему случае вследствие нарушения транзакционности?
    К нарушению непрерывности номеров.

    Это лечится отключением "автономности" генерации в этом решении. Но даже если так сделать - это не особо поможет.
    Вам, по сути, требуется множество генераторов на одну таблицу. А такого там не предусмотрено. Следовательно, придется дорабатывать под свои нужды.

    Как по мне, для вашего конкретного случая гораздо проще и эффективнее сделать примерно так:
    +
    use tempdb;
    go
    
    create table dbo.t (id int identity primary key, a int, b int, c int, n int, s varchar(100));
    create index IX_t__a__b__c__n on dbo.t (a, b, c, n desc)
    go
    
    create trigger dbo.tr_t__iofinsert
    on dbo.t
    instead of insert
    as
    begin
     set nocount on;
    
     declare @g table (a int, b int, c int, base int, primary key (a, b, c));
    
     with s as
     (
      select top (1) with ties
       t.a, t.b, t.c, t.n
      from
       (select distinct a, b, c from inserted) i join
       dbo.t t with (index = IX_t__a__b__c__n, serializable, updlock) on t.a = i.a and t.b = i.b and t.c = i.c
      order by
       t.n desc
     )
     insert into dbo.t
      (a, b, c, n, s)
      select
       i.a, i.b, i.c, isnull(s.n, 0) + row_number() over (partition by i.a, i.b, i.c order by (select 1)), i.s
      from
       inserted i left join
       s on s.a = i.a and s.b = i.b and s.c = i.c
    end;
    go
    
    insert into dbo.t
    values
     (1, 1, 1, null, 'a'), (1, 1, 1, null, 'b'),
     (1, 1, 2, null, 'c'), (1, 1, 2, null, 'd')
    
    select * from dbo.t order by a, b, c, n;
    go
    
    insert into dbo.t
    values
     (1, 1, 1, null, 'e'), (1, 1, 1, null, 'f'),
     (1, 1, 2, null, 'g'), (1, 1, 2, null, 'h')
    
    select * from dbo.t order by a, b, c, n;
    go
    
    drop table dbo.t;
    go
    
    22 май 16, 14:31    [19203977]     Ответить | Цитировать Сообщить модератору
     Re: Последовательная нумерация в пределах группы записей  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 30778
    invm
    ТС'у же наоборот, транзакционность генерации нужна.
    Ну, вообще он про это не писал.

    Если транзакционность нужна, что можно использовать ту схему, про которую ТС писал, в чём проблема?

    expl0rer
    Пока думаю над вариантом создания еще одной таблицы, в которой будет храниться следующей номер, который должен быть выделен в пределах выбранной группы полей:

    Впрочем, можно использовать и MAX....

    invm
    Вам, по сути, требуется множество генераторов на одну таблицу. А такого там не предусмотрено. Следовательно, придется дорабатывать под свои нужды.
    Там просто пример реализации автономной транзакции.

    Собственно, там используется та же отдельная таблица счётчиков, как у ТС, но с обращением в отдельной транзакции.

    Дмитрий Костылев в докладах иллюстрировал этот код как раз такими сложными случаями, как у ТС, типа отдельная нумерация для периодов, для подразделений, для "групп по 1000", для клиентов, и прочими причудливыми потребностями бизнес-пользователей.
    22 май 16, 18:49    [19204501]     Ответить | Цитировать Сообщить модератору
     Re: Последовательная нумерация в пределах группы записей  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9122
    alexeyvg
    Ну, вообще он про это не писал.
    "Очередной порядковый номер в пределах четырех предыдущих полей" как бы намекает на непрерывность. По крайней мере, я понял именно так.

    alexeyvg
    Там просто пример реализации автономной транзакции.

    Собственно, там используется та же отдельная таблица счётчиков, как у ТС, но с обращением в отдельной транзакции.

    Дмитрий Костылев в докладах иллюстрировал этот код как раз такими сложными случаями, как у ТС, типа отдельная нумерация для периодов, для подразделений, для "групп по 1000", для клиентов, и прочими причудливыми потребностями бизнес-пользователей.
    Вот именно. Принцип один - либо доп. таблица, либо на основе основной.
    А весь огород с CLR необходим если нужны "автономные" транзакции или возможность получать номера функцией.
    22 май 16, 20:03    [19204626]     Ответить | Цитировать Сообщить модератору
     Re: Последовательная нумерация в пределах группы записей  [new]
    expl0rer
    Member

    Откуда:
    Сообщений: 12
    Прошу прощения, что сразу не написал. В таком виде транзакционность мне не нужна - пропуски могут быть, главное чтобы их было не очень много. Автоинкрементируемое поле не устраивает клиента своим размером - им нужен короткий 3-4 символьный номер. Если нумеровать в пределах Организация+Подразделение+Отдел+Группа - так и выходит.

    А если некоторые номера будут пропущены, не страшно.
    22 май 16, 20:39    [19204703]     Ответить | Цитировать Сообщить модератору
     Re: Последовательная нумерация в пределах группы записей  [new]
    invm
    Member

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

    Варианты вам были предложены. Даже с примером.
    Но выбрать вы должны самостоятельно.
    22 май 16, 20:54    [19204744]     Ответить | Цитировать Сообщить модератору
     Re: Последовательная нумерация в пределах группы записей  [new]
    expl0rer
    Member

    Откуда:
    Сообщений: 12
    да, спасибо
    22 май 16, 20:59    [19204759]     Ответить | Цитировать Сообщить модератору
     Re: Последовательная нумерация в пределах группы записей  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 30778
    expl0rer
    В таком виде транзакционность мне не нужна - пропуски могут быть, главное чтобы их было не очень много
    Ну, тогда выбор простой - по любому своя таблица (таблицы) с номерами, и дальше либо простые запросы для получения номера, если производительность некритична, либо отдельные транзакции через CLR как в статье выше, если множество конкурентных обращений.

    PS ждём реализации в MSSQL нативных автономных транзакций :-)
    23 май 16, 10:25    [19206029]     Ответить | Цитировать Сообщить модератору
    Между сообщениями интервал более 1 года.
     Re: Последовательная нумерация в пределах группы записей  [new]
    Cristiano_Rivaldo
    Member

    Откуда:
    Сообщений: 323
    Всем привет.Ничего нового по этой теме не появилось ?
    24 апр 18, 14:04    [21364420]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить