Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
balykovdron
Member

Откуда:
Сообщений: 97
Есть задача: в справочник (ID int primary key, Value nvarchar(4000)) нужно вставлять строки и получить на выходе ID новой строки или существующей (если есть таковая). Ограничение одно - строки не должны повторяться!
И тут настает сложность: поле Value - нельзя проиндексировать и установить уникальность на уровне индекса (ограничение SQL - 900 байт на индексируемое поле), а искать по нему надо.

Сложность задачи еще в том, что это будет высоконагруженный блок с большим кол-вом данных (млн+) в котором данные льются с большой скоростью. И вставка должна быть максимально быстрой....

Чтобы ускорить поиск по текстовому полю - мы ввели поле ValueHash bigint (индексируем это поле). Теперь поиск получается по ValueHash быстрым - но это не гарантия выбора единственной записи...

В итоге - Если использовать IF (NOT EXISTS(...)) INSERT INTO... (или MERGE - что похоже) то все равно складывается ситуация, когда 2 потока могут вставить запись с одинаковым Value (при уровне изоляции транзакции меньше чем SERIALIZABLE - а если SERIALIZABLE - то дэдлок).

Помогите решить вопрос - наверняка кто-то сталкивался с такой задачей в больших системах... Нужно организовать вставку с получением ID без задвоения Value и без дэдлоков...

Прикладываю хранимку на вставку...

CREATE PROCEDURE dbo.ModuleNameInsertValue
	@value nvarchar(4000),
	@valueHash bigint
AS
SET NOCOUNT ON;

-- переменная для возврата значения
declare @Id int	SET @Id = 0

--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION 

BEGIN TRY  
	-- сливаем записи в @hashed - точность хеша не 100%, но сюда попадется очень МАЛО (чаще всего одна) записей полюбасу
	declare @hashed table (Id int, [Value] nvarchar(4000), [ValueHash] bigint)
	INSERT INTO @hashed
		SELECT Id, [Value], [ValueHash] FROM [dbo].[ModuleName] WHERE [valueHash] = @valueHash

	-- т.к. записей мало - то даже поиск по nvarchar(4000) будет быстрым
	SELECT @Id = Id FROM @hashed WHERE [ValueHash] = @valueHash AND [Value] = @value -- тут уже точно одна
	IF (@Id = 0) BEGIN
		INSERT INTO dbo.[ModuleName] ([Value], [ValueHash]) VALUES (@value, @valueHash)
		SET @Id = SCOPE_IDENTITY(); 
	END
	IF @@TRANCOUNT > 0  COMMIT TRANSACTION
END TRY  
BEGIN CATCH  
	BEGIN
		IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
		THROW;
	END
END CATCH;



RETURN @Id
GO
17 авг 16, 14:12    [19552299]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 941
автор
а искать по нему надо

Почитайте про бинарные коллейшены - http://aboutsqlserver.com/2015/01/20/optimizing-substring-search-performance-in-sql-server/
17 авг 16, 14:21    [19552337]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 941
С другой стороны... Стоп. Почему FTS игнорируете? Он весьма хорошо справляется с поиском полной фразы.
17 авг 16, 14:24    [19552353]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 941
Далее... чем хеш получаете? CHECKSUM? Он не гарантирует ничего уникального на больших данных. Там может лучше использовать SHA1? Если хранить его как BINARY то весить он будет копейки в таблице:

ALTER TABLE dbo.tbl
    ADD hash_column AS CAST(HASHBYTES('SHA1', text_col) AS BINARY(20)) PERSISTED
GO
17 авг 16, 14:28    [19552373]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
aleks2
Guest
Варианты:
1. Хэш сделайте ближе к 900 байтам.
И индекс то по хэшу захерачьте.

2. Насрите на неуникальность - валите все. Потом удалите лишнее.
17 авг 16, 14:30    [19552386]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 941
ALTER TABLE dbo.ModuleName
    ADD ValueHash AS CAST(HASHBYTES('SHA1', Value) AS BINARY(20)) PERSISTED
GO

CREATE NONCLUSTERED INDEX ix ON dbo.ModuleName (ValueHash)
GO

CREATE PROCEDURE dbo.ModuleNameInsertValue
(
    @value NVARCHAR(4000)
)
AS BEGIN

    SET NOCOUNT ON

    DECLARE @valueHash BINARY(20) = HASHBYTES('SHA1', @value)
          , @id INT

    SELECT TOP(1) @id = Id
    FROM dbo.ModuleName
    WHERE valueHash = @valueHash

    IF @id IS NULL BEGIN
        INSERT INTO dbo.ModuleName (Value) VALUES (@value)
        SET @id = SCOPE_IDENTITY()
    END

    RETURN @id

END
17 авг 16, 14:36    [19552413]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 26587
balykovdron
Нужно организовать вставку с получением ID без задвоения Value и без дэдлоков...
Можно ещё сделать логически так же, как сделали вы (т.е. поиск по хэш-функци + сравнение Value), но оформить по другому, без временной таблицы, как один стейтмент INSERT ... WHERE NOT EXISTS(...)
17 авг 16, 14:41    [19552434]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
aleks2
Guest
И научитесь не писать херни в процедурах

CREATE PROCEDURE dbo.ModuleNameInsertValue
	@value nvarchar(4000),
	@valueHash bigint
AS
SET NOCOUNT ON;

INSERT INTO dbo.[ModuleName] ([Value], [ValueHash]) 
  select @value, @valueHash
  -- индекс по [valueHash] крайне желателен
  where not exists( SELECT * FROM [dbo].[ModuleName] WHERE [valueHash] = @valueHash and [Value] = [Value] );
   
if @@ROWCOUNT = 0 return 0;

return SCOPE_IDENTITY();
17 авг 16, 14:42    [19552445]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 3602
alexeyvg
balykovdron
Нужно организовать вставку с получением ID без задвоения Value и без дэдлоков...
Можно ещё сделать логически так же, как сделали вы (т.е. поиск по хэш-функци + сравнение Value), но оформить по другому, без временной таблицы, как один стейтмент INSERT ... WHERE NOT EXISTS(...)


Эту задачу часто спрашивают на собеседовании. Ожидая услышать, что будет добавлено поле хеш с ключом.
17 авг 16, 14:43    [19552450]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
o-o
Guest
a_voronin
Эту задачу часто спрашивают на собеседовании. Ожидая услышать, что будет добавлено поле хеш с ключом.

a хотите, я назову имя собеседователя?
17 авг 16, 14:48    [19552475]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
aleks2
Guest
o-o
a_voronin
Эту задачу часто спрашивают на собеседовании. Ожидая услышать, что будет добавлено поле хеш с ключом.

a хотите, я назову имя собеседователя?

Что нам это даст?
17 авг 16, 14:56    [19552515]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
o-o
Guest
фу, взял всю загадку испортил.
сам voronin это самое и спрашивает, ожидая хэш в ответе
17 авг 16, 15:05    [19552574]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
invm
Member

Откуда: Москва
Сообщений: 7797
balykovdron
Нужно организовать вставку с получением ID без задвоения Value и без дэдлоков...
create procedure dbo.ModuleNameInsertValue
 @value nvarchar(4000),
 @valueHash bigint,
 @id int output
as
begin
 set nocount on;

 insert into dbo.ModuleName
  (Value, ValueHash)
  select
   @value, @valueHash
  where
   not exists(select * from dbo.ModuleName with (updlock, serializable) WHERE ValueHash = @valueHash and Value = @value);

 if @@rowcount > 0
  select @id = scope_identity()
 else
  select @id = null;

 return 0;
end;

Либо через MERGE
create procedure dbo.ModuleNameInsertValue
 @value nvarchar(4000),
 @valueHash bigint,
 @id int output
as
begin
 set nocount on;

 merge into dbo.ModuleName t with (serializable)
 using (values (@value, @valueHash)) s(Value, ValueHash) on s.ValueHash = t.ValueHash and s.Value = t.Value
 when not matched then
  insert
   (Value, ValueHash)
  values
   (s.Value, s.ValueHash)

 if @@rowcount > 0
  select @id = scope_identity()
 else
  select @id = null;

 return 0;
end;

Плюс необходим индекс по ValueHash.
17 авг 16, 15:06    [19552579]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
balykovdron
Member

Откуда:
Сообщений: 97
aleks2
И научитесь не писать херни в процедурах


В условии задачи - возвращать еще ID существующей записи
17 авг 16, 15:09    [19552594]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 5547
balykovdron,

правильный хэш в бигинт не поместится.
17 авг 16, 15:15    [19552640]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
aleks2
Guest
balykovdron
aleks2
И научитесь не писать херни в процедурах


В условии задачи - возвращать еще ID существующей записи


Ну, занафига вам сдался этот ид - я не знаю.
Но дозволяю написать

if @@ROWCOUNT = 0 return (select id ...);
17 авг 16, 15:53    [19552954]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
aleks2
Guest
Впрочем, можно и покороче
create procedure dbo.ModuleNameInsertValue
 @value nvarchar(4000),
 @valueHash bigint
as
begin

 set nocount on;

 declare  @id int;

 merge into dbo.ModuleName as t using (values (@value, @valueHash)) as s(Value, ValueHash) on s.ValueHash = t.ValueHash and s.Value = t.Value
   when not matched then insert (Value, ValueHash) values (s.Value, s.ValueHash)
   when matched then update @id = t.id;
   
 return isnull(@id, scope_identity());

end;
17 авг 16, 16:03    [19553056]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
balykovdron
Member

Откуда:
Сообщений: 97
Спасибо всем, в моем случае проблема решилась благодаря решению [invm], отдельное спасибо ему. Ни разу мои многопоточные тесты не выявили нарушения условий вставки.

CREATE PROCEDURE dbo.ModuleNameInsertValue
	@value nvarchar(4000),
	@valueHash bigint
AS
SET NOCOUNT ON;

-- переменная для возврата значения
declare @Id int	

insert into dbo.ModuleName ([Value], ValueHash)
  select  @value, @valueHash
  where not exists(select * from dbo.ModuleName with (updlock, serializable) WHERE ValueHash = @valueHash and [Value] = @value);

if @@rowcount > 0
  select @id = scope_identity()
else
  select @id = id from dbo.ModuleName WHERE ValueHash = @valueHash and [Value] = @value;

RETURN @Id
GO
17 авг 16, 16:39    [19553325]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 3602
o-o
a_voronin
Эту задачу часто спрашивают на собеседовании. Ожидая услышать, что будет добавлено поле хеш с ключом.

a хотите, я назову имя собеседователя?


А вы знаете именно его имя (не фамилию)?
17 авг 16, 16:55    [19553463]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
o-o
Guest
a_voronin
o-o
пропущено...

a хотите, я назову имя собеседователя?


А вы знаете именно его имя (не фамилию)?

конечно.
у меня же 100% внимательность.
но раз сам напросился, выдаю секрет фирмы:
WILDBERRIES\voronin.aleksey
я даже тему помню, где это в явном виде на картинке
17 авг 16, 17:10    [19553583]     Ответить | Цитировать Сообщить модератору
 Re: Вставка НЕПОВТОРЯЮЩИХСЯ текстовых строк (nvarchar(4000))  [new]
aleks2
Guest
balykovdron
Спасибо всем, в моем случае проблема решилась благодаря решению [invm], отдельное спасибо ему. Ни разу мои многопоточные тесты не выявили нарушения условий вставки.

CREATE PROCEDURE dbo.ModuleNameInsertValue
	@value nvarchar(4000),
	@valueHash bigint
AS
SET NOCOUNT ON;

-- переменная для возврата значения
declare @Id int	

insert into dbo.ModuleName ([Value], ValueHash)
  select  @value, @valueHash
  where not exists(select * from dbo.ModuleName with (updlock, serializable) WHERE ValueHash = @valueHash and [Value] = @value);

if @@rowcount > 0
  select @id = scope_identity()
else
  select @id = id from dbo.ModuleName WHERE ValueHash = @valueHash and [Value] = @value;

RETURN @Id
GO

И эти люди запрещают нам ковырять в носу!

balykovdron
высоконагруженный блок с большим кол-вом данных (млн+)


1. нахрена декларация ненужной переменной?
2. нахрена ненужное присваивание?

if @@rowcount > 0 return scope_identity();

RETURN (select id from dbo.ModuleName WHERE ValueHash = @valueHash and [Value] = @value)
17 авг 16, 17:25    [19553705]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить