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

Откуда: Moscow Square
Сообщений: 624
Добрый день!

SELECT @@VERSION

Microsoft SQL Server 2005 - 9.00.5000.00 (X64)
Dec 10 2010 10:38:40
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Задача реализовать хранение истории в виде последних 10 записей, которые пользователь набирал в поиске.
Пользователи ищут артикулы.
Соответственно Web сервер вызывает процедуру сохранения 1 записи, или выборки - если нужно показать историю поиска.

Интересует насколько правильно реализовано хранение очереди в MSSQL.
Ну и критика сообщества :)

CREATE SCHEMA [Stat]
GO

CREATE TABLE [Stat].[SearchHistory_WebAuth](
       [SearchHistoryId] [bigint] IDENTITY(1,1) NOT NULL,
       [UserId] [bigint] NOT NULL,
       [DetailNum] [varchar](20) NOT NULL,
       [DetailNameRus] [varchar](50) NOT NULL,
       [SortOrder] [bigint] NULL
) ON [PRIMARY]
-- поле SortOrder = NULL сделано по историческим причинам
GO

CREATE CLUSTERED INDEX [CL_UserId] ON [Stat].[SearchHistory_WebAuth]
(
       [UserId] ASC,
       [SortOrder] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


CREATE PROCEDURE [Stat].[pSetSearchHistory_WebAuth]
(
       @UserId BIGINT, -- пользователь
       @DetailNum varchar(20), -- деталь
       @DetailNameRus varchar(50) -- название детали
)
AS 
BEGIN
       -- WEBTEST
       -- EXEC [Stat].[pSetSearchHistory_WebAuth] 109, '113901', 'деталька'
       -- SELECT * FROM Stat.SearchHistory_WebAuth AS shwa WHERE UserId = 109
       
       SET NOCOUNT ON

       -- обновляем данные в очереди
       UPDATE h
       SET 
             SortOrder = nextSortOrder, -- увеличиваем сортировку +1
             DetailNum = @DetailNum, -- вносим новый номер в историю
             DetailNameRus = @DetailNameRus -- вносим название детали
       FROM Stat.SearchHistory_WebAuth h
       CROSS APPLY
       (
             SELECT 
                    MIN(SortOrder) minSortOrder, -- минимальное значение в истории (его и будем менять) 
                    MAX(SortOrder)+1 nextSortOrder, -- меняем на максимальное +1
                    COUNT(*) Qty -- количество всего (чтобы история не содержала более чем N записей)
             FROM Stat.SearchHistory_WebAuth
             WHERE UserId = @UserId
       ) hx
       WHERE 
             UserId = @UserId
             AND SortOrder = minSortOrder
             AND Qty >= 10 -- 10 или более записей (но более 10 быть не должно, они чистятся при выборке)

       -- если реального апдейта не произошло, значит записей или нет, или их недостаточно
       -- добавим
       IF @@ROWCOUNT = 0 
       BEGIN
             INSERT INTO Stat.SearchHistory_WebAuth (UserId, DetailNum, DetailNameRus, SortOrder)
             SELECT 
                    @UserId,
                    @DetailNum,
                    @DetailNameRus,
                    ISNULL(MAX(SortOrder),0)+1
             FROM Stat.SearchHistory_WebAuth
             WHERE UserId = @UserId
       END 
END

GO

-- дает историю по пользователю 
CREATE PROCEDURE [Stat].[pGetSearchHistory_WebAuth]
(
       @UserId BIGINT -- пользователь
)
AS 
BEGIN
       SET NOCOUNT ON
       -- WEBTEST
       -- EXEC [Stat].[pGetSearchHistory_WebAuth] 110
       -- SELECT * FROM Stat.SearchHistory_WebAuth wa
       
       SELECT 
             UserId, 
             DetailNum, 
             DetailNameRus, 
             SortOrder
       FROM Stat.SearchHistory_WebAuth WITH(NOLOCK)
       WHERE UserId = @UserId
       ORDER BY SortOrder DESC

       -- если получилось более 10 записей, удаляем лишние   
       IF @@ROWCOUNT > 10
       BEGIN
             -- здесь скорость не так важна, поэтому несколько коряво (ISNULL)
             -- в эту логику процедура будет сваливаться очень редко
             DELETE TOP (1) wa
             FROM Stat.SearchHistory_WebAuth wa
             OUTER APPLY
             (
                    SELECT MIN(ISNULL(SortOrder,0)) minSortOrder 
                    FROM Stat.SearchHistory_WebAuth
                    WHERE UserId = @UserId
             ) wax
             WHERE UserId = @UserId
             AND ISNULL(SortOrder,0) = wax.minSortOrder
       END
END
7 май 14, 12:08    [15984660]     Ответить | Цитировать Сообщить модератору
 Re: Хранение очереди в MSSQL  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Удаление в реальном времени - плохая идея. Сделайте робота-чистильщика и запускайте его во время наименьшей активности пользователей.
При вставке набивайте таблицу с автоприращением запросами без каких-либо проверок с указанием ID пользователя.
При выборке просто берите топ 10 по ID пользователе, отсортированные в обратном порядке по ID истории.
UPDATE зачем там нужен?
7 май 14, 12:32    [15984814]     Ответить | Цитировать Сообщить модератору
 Re: Хранение очереди в MSSQL  [new]
РС_Николай
Member

Откуда:
Сообщений: 11
Вариант с Job-ом уже проходили.
Когда вставляемых данных много и идут они практически круглосуточно - очистка даже ночью по расписанию, может стать сложной задачей и занимать несколько часов (блокировка таблицы, удаление пакетно и т.д.)
Если партицировать таблицу (например по дням) и чистить все кроме последнего дня - теряется история у пользователей.
Пользователю ведь надо выдавать последних 10 (!) поисковых запросов.

У топик стартера реализован механизм очереди - т.е. не более 10 записей на каждого пользователя в таблице. При этом нет удаления, а есть только апдейт.

Но пока неясно насколько это хорошо работает, и я бы с радостью тоже узнал кто как реализовывает очереди на MSSQL.
7 май 14, 13:42    [15985341]     Ответить | Цитировать Сообщить модератору
 Re: Хранение очереди в MSSQL  [new]
хмхмхм
Guest
РС_Николай,

удаление само по себе отнимает много времени, будь это удаление сразу пачкой, либо по одной записи в результате работы процедуры. Зачем вообще удалять лишние записи, ведь даже на большой таблице запрос вида:

select top 10 search_string from [search]
order by created_on desc
where user_id = ...


отрабатывает очень быстро. Если на ней есть нужные индексы.

По-моему более логично было бы дать таблице расти и раз в месяц создавать новую таблицу, куда по каждому пользователю отправлять top 10 записей, а старую очищать truncate table.

РС_Николай, вы такой вариант не пробовали?
7 май 14, 14:55    [15985872]     Ответить | Цитировать Сообщить модератору
 Re: Хранение очереди в MSSQL  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
хмхмхм
По-моему более логично было бы дать таблице расти и раз в месяц создавать новую таблицу, куда по каждому пользователю отправлять top 10 записей, а старую очищать truncate table.

РС_Николай, вы такой вариант не пробовали?
мы изобретём своё секционирование, с преферансом и куртизанками!
7 май 14, 15:08    [15985995]     Ответить | Цитировать Сообщить модератору
 Re: Хранение очереди в MSSQL  [new]
хмхмхм
Guest
Гадя Петрович
хмхмхм
По-моему более логично было бы дать таблице расти и раз в месяц создавать новую таблицу, куда по каждому пользователю отправлять top 10 записей, а старую очищать truncate table.

РС_Николай, вы такой вариант не пробовали?
мы изобретём своё секционирование, с преферансом и куртизанками!


Не-а, немного не так. Я не предлагаю реализовывать секционирование, я предлагаю раз в месяц\день\год\нужное_указать удалять весь ненужный хлам с помощью truncate table, а нужное заливать в новую таблицу и затем по ней создавать нужные индексы.

В данном примере переключение между секциями не нужно, разве что только для того, чтобы потом очистить секцию.
7 май 14, 15:25    [15986120]     Ответить | Цитировать Сообщить модератору
 Re: Хранение очереди в MSSQL  [new]
invm
Member

Откуда: Москва
Сообщений: 9412
РС_Николай
У топик стартера реализован механизм очереди
Это не очередь, а кольцевой буфер. Например, можно вот так реализовать:
+
use tempdb;
go

create table dbo.RingBuffer (id int not null identity primary key, v int not null, rv rowversion not null);
go

create procedure dbo.spAddItemToRingBuffer
 @v int
as
begin
 set nocount on;

 with t as
 (
  select
   v, row_number() over (order by rv) as rn, count(*) over () as c
  from
   dbo.RingBuffer
 ),
 s as
 (
  select 1 as rn, @v as v
 )
 merge into t
 using s on s.rn = t.rn and t.c = 10
 when not matched then
  insert (v) values (s.v)
 when matched then
  update set v = s.v;
end;
go

exec dbo.spAddItemToRingBuffer 10;
exec dbo.spAddItemToRingBuffer 20;
exec dbo.spAddItemToRingBuffer 30;
exec dbo.spAddItemToRingBuffer 40;
exec dbo.spAddItemToRingBuffer 50;
exec dbo.spAddItemToRingBuffer 60;
exec dbo.spAddItemToRingBuffer 70;
exec dbo.spAddItemToRingBuffer 80;
exec dbo.spAddItemToRingBuffer 90;
exec dbo.spAddItemToRingBuffer 100;
select * from dbo.RingBuffer order by rv;

exec dbo.spAddItemToRingBuffer 110;
select * from dbo.RingBuffer order by rv;

exec dbo.spAddItemToRingBuffer 120;
select * from dbo.RingBuffer order by rv;
go

drop procedure dbo.spAddItemToRingBuffer;
drop table dbo.RingBuffer;
go
7 май 14, 15:48    [15986269]     Ответить | Цитировать Сообщить модератору
 Re: Хранение очереди в MSSQL  [new]
РС_Николай
Member

Откуда:
Сообщений: 11
invm,
Спасибо!

Решение заточено под 2008-ой (оператор MERGE) но идея использовать rowversion + row_number хорошая.
Не нужно считать MIN-MAX, как у ТС
7 май 14, 17:23    [15986861]     Ответить | Цитировать Сообщить модератору
 Re: Хранение очереди в MSSQL  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
автор
Если партицировать таблицу (например по дням)

Не по дням, а по флагу (битовому полю) старый - новый. Понадобится update новой секции при insert записи. Зато никаких долговременных блокировок и мгновенная чистка устаревших. Одна проблема - должен быть установлен относительно дорогой ЕЕ.
7 май 14, 18:03    [15987146]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить