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

Откуда:
Сообщений: 360
В таблицу документов будут грузится документы для которых уникальность определяется тремя полями Дата, Тип, Номер во внешней системе. Документы будут грузится параллельными сессиями. Поскольку отбор документов идет по сложным условиям - в разных сессиях может оказаться один и тот же документ (либо банально два пользователя могут запустить две пересекающиеся загрузки). Нужно обеспечить уникальность при загрузке. Какой способ лучше с точки зрения производительности
1) Создание уникального индекса - далее при вставке отслеживание ошибки уникальности
2) Создание уникального констрейнта (constraint) - - далее при вставке отслеживание ошибки уникальности
3) Блокировка таблицы и запрос по ней на проверку существующих значений. (поля поиска индексированы)

P S Возможно есть какие то другие способы? Идеально бы конечно было бы наличие в MS SQL структур данных которые а) хранятся в памяти б) для них имелся доступ со всех процессов в) без записи на диск и г) которые имели бы интерфейс работы как таблицей (блокировки, вставки и т.д.) , но таких я как понимаю нет
P P S
Теоретически способы 1 и 2 самые лучшие. Возможно 2 самый лучший но не могу объяснить почему
12 ноя 15, 12:06    [18405215]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
iap
Member

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

если можно просто проигнорировать попытку вставки дубликата,
то уникальный индекс можно создать с WITH( IGNORE_DUP_KEY=ON )
12 ноя 15, 12:13    [18405288]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
create index ... with (ignore_dup_key = on)
12 ноя 15, 12:14    [18405295]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
selis76
Member

Откуда:
Сообщений: 360
Игнорировать вставку дубликата не хотелось бы, так как по таблице еще запросы по отчетам могут идти параллельно. Нужна целостность
12 ноя 15, 12:39    [18405496]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
selis76
Игнорировать вставку дубликата не хотелось бы, так как по таблице еще запросы по отчетам могут идти параллельно. Нужна целостность
Дубликат всё равно вставлять запрещено.
Целостность-то как раз сохраняется.
12 ноя 15, 12:43    [18405553]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
aleks2
Guest
selis76
Игнорировать вставку дубликата не хотелось бы, так как по таблице еще запросы по отчетам могут идти параллельно. Нужна целостность

А чаво ты собрался делать с дубликатами?
12 ноя 15, 12:43    [18405555]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
a_voronin
Member

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

Для ускорения поиска существующих документов можете попробовать сделать хеш по этой комбинации " Дата, Тип, Номер во внешней системе." и индекс по этому хешу и с последующей допроверкой совпадения этих значений.
12 ноя 15, 12:47    [18405606]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
o-o
Guest
iap
selis76
Игнорировать вставку дубликата не хотелось бы, так как по таблице еще запросы по отчетам могут идти параллельно. Нужна целостность
Дубликат всё равно вставлять запрещено.
Целостность-то как раз сохраняется.

думаю, товарищ не понял, что первое из одинаковых значений будет вставлено.
ну типа решил, что просто ни одно из повторяющихся не вставится совсем
12 ноя 15, 12:58    [18405725]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1741
selis76
В таблицу документов будут грузится документы для которых уникальность определяется тремя полями Дата, Тип, Номер во внешней системе. Документы будут грузится параллельными сессиями. Поскольку отбор документов идет по сложным условиям - в разных сессиях может оказаться один и тот же документ (либо банально два пользователя могут запустить две пересекающиеся загрузки). Нужно обеспечить уникальность при загрузке. Какой способ лучше с точки зрения производительности
1) Создание уникального индекса - далее при вставке отслеживание ошибки уникальности
2) Создание уникального констрейнта (constraint) - - далее при вставке отслеживание ошибки уникальности
3) Блокировка таблицы и запрос по ней на проверку существующих значений. (поля поиска индексированы)

P S Возможно есть какие то другие способы? Идеально бы конечно было бы наличие в MS SQL структур данных которые а) хранятся в памяти б) для них имелся доступ со всех процессов в) без записи на диск и г) которые имели бы интерфейс работы как таблицей (блокировки, вставки и т.д.) , но таких я как понимаю нет
P P S
Теоретически способы 1 и 2 самые лучшие. Возможно 2 самый лучший но не могу объяснить почему


Под уникальностью при загрузке что понимаем? Что один документ содержится в итоговой таблице один раз?
Реализуйте загрузку данных пользователем во временную таблицу, данные в основную переносите слиянием (Merge) - там большой выбор настроек и вариантов действий.
Или добавьте поле с датой и временем вставки, заполняемое автоматически (и второе - с именем пользователя, создавшего запись), и сделайте запрос, который будет выбирать наиболее актуальную информацию по документам.
12 ноя 15, 13:04    [18405766]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
selis76
Member

Откуда:
Сообщений: 360
aleks2
selis76
Игнорировать вставку дубликата не хотелось бы, так как по таблице еще запросы по отчетам могут идти параллельно. Нужна целостность

А чаво ты собрался делать с дубликатами?

Если обнаружен дубликать - то пускаем его не по ветке создания нового документа а по ветке обновления документа
12 ноя 15, 13:44    [18406108]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
selis76
Member

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

Да не понял, но сейчас прояснилось
12 ноя 15, 13:45    [18406121]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
selis76
aleks2
пропущено...

А чаво ты собрался делать с дубликатами?

Если обнаружен дубликать - то пускаем его не по ветке создания нового документа а по ветке обновления документа
Если версия сервера >=SQL2008, то - MERGE в триггере INSTEAD OF INSERT
Если <SQL2008, то не MERGE, а INSERT ... WHERE NOT EXISTS() + UPDATE
12 ноя 15, 13:53    [18406181]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
selis76
Member

Откуда:
Сообщений: 360
a_voronin
selis76,

Для ускорения поиска существующих документов можете попробовать сделать хеш по этой комбинации " Дата, Тип, Номер во внешней системе." и индекс по этому хешу и с последующей допроверкой совпадения этих значений.

Вы memory optimized tables имеете ввиду?
https://msdn.microsoft.com/en-us/library/dn511014.aspx
Как понимаю они только в 2014 появились. А в 2008 вроде все структуры в памяти видны в пределах одной сессии
12 ноя 15, 13:56    [18406207]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
a_voronin
Для ускорения поиска существующих документов можете попробовать сделать хеш по этой комбинации " Дата, Тип, Номер во внешней системе." и индекс по этому хешу и с последующей допроверкой совпадения этих значений.
+ Ускорятелям поиска посвящается
use tempdb;
go

create table dbo.t
(
 id int not null identity primary key clustered,
 dt date not null,
 doc_type varchar(10) not null,
 doc_num varchar(40) not null,
 hash1 as checksum(dt, doc_type, doc_num),
 hash2 as hashbytes('MD5', convert(varchar(8), dt, 112) + cast(doc_type as char(10)) + cast(doc_num as char(40)))
);
go

insert into dbo.t
select top (1000000)
 dateadd(day, rand(checksum(newid())) * 1000, getdate()),
 cast(rand(checksum(newid())) * 10 as int),
 newid()
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

create unique index UIX_t__dt__doc_type__doc_num on dbo.t (dt, doc_type, doc_num);
create index IX_t__hash1 on dbo.t (hash1) include (dt, doc_type, doc_num);
create index IX_t__hash2 on dbo.t (hash2) include (dt, doc_type, doc_num);
go

declare @t table
(
 dt date not null,
 doc_type varchar(10) not null,
 doc_num varchar(40) not null,
 hash1 int,
 hash2 varbinary(8000)
);

insert into @t
 (dt, doc_type, doc_num, hash1, hash2)
 select
  dt, doc_type, doc_num, hash1, hash2
 from
  dbo.t tablesample(30 percent);

declare @id int;

set statistics time on;

select
 @id = b.id
from
 @t a join
 dbo.t b with (index = UIX_t__dt__doc_type__doc_num, forceseek) on b.dt = a.dt and b.doc_type = a.doc_type and b.doc_num = a.doc_num
option
 (maxdop 1);

select
 @id = b.id
from
 @t a join
 dbo.t b with (index = IX_t__hash1, forceseek) on b.hash1 = a.hash1
where
 b.dt = a.dt and b.doc_type = a.doc_type and b.doc_num = a.doc_num
option
 (maxdop 1);

select
 @id = b.id
from
 @t a join
 dbo.t b with (index = IX_t__hash2, forceseek) on b.hash2 = a.hash2
where
 b.dt = a.dt and b.doc_type = a.doc_type and b.doc_num = a.doc_num
option
 (maxdop 1);

set statistics time off;
go

drop table dbo.t;
go
 Время работы SQL Server:
Время ЦП = 1092 мс, затраченное время = 1091 мс.

Время работы SQL Server:
Время ЦП = 1279 мс, затраченное время = 1292 мс.

Время работы SQL Server:
Время ЦП = 2043 мс, затраченное время = 2046 мс.
12 ноя 15, 14:03    [18406263]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
invm
a_voronin
Для ускорения поиска существующих документов можете попробовать сделать хеш по этой комбинации " Дата, Тип, Номер во внешней системе." и индекс по этому хешу и с последующей допроверкой совпадения этих значений.
+ Ускорятелям поиска посвящается
use tempdb;
go

create table dbo.t
(
 id int not null identity primary key clustered,
 dt date not null,
 doc_type varchar(10) not null,
 doc_num varchar(40) not null,
 hash1 as checksum(dt, doc_type, doc_num),
 hash2 as hashbytes('MD5', convert(varchar(8), dt, 112) + cast(doc_type as char(10)) + cast(doc_num as char(40)))
);
go

insert into dbo.t
select top (1000000)
 dateadd(day, rand(checksum(newid())) * 1000, getdate()),
 cast(rand(checksum(newid())) * 10 as int),
 newid()
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

create unique index UIX_t__dt__doc_type__doc_num on dbo.t (dt, doc_type, doc_num);
create index IX_t__hash1 on dbo.t (hash1) include (dt, doc_type, doc_num);
create index IX_t__hash2 on dbo.t (hash2) include (dt, doc_type, doc_num);
go

declare @t table
(
 dt date not null,
 doc_type varchar(10) not null,
 doc_num varchar(40) not null,
 hash1 int,
 hash2 varbinary(8000)
);

insert into @t
 (dt, doc_type, doc_num, hash1, hash2)
 select
  dt, doc_type, doc_num, hash1, hash2
 from
  dbo.t tablesample(30 percent);

declare @id int;

set statistics time on;

select
 @id = b.id
from
 @t a join
 dbo.t b with (index = UIX_t__dt__doc_type__doc_num, forceseek) on b.dt = a.dt and b.doc_type = a.doc_type and b.doc_num = a.doc_num
option
 (maxdop 1);

select
 @id = b.id
from
 @t a join
 dbo.t b with (index = IX_t__hash1, forceseek) on b.hash1 = a.hash1
where
 b.dt = a.dt and b.doc_type = a.doc_type and b.doc_num = a.doc_num
option
 (maxdop 1);

select
 @id = b.id
from
 @t a join
 dbo.t b with (index = IX_t__hash2, forceseek) on b.hash2 = a.hash2
where
 b.dt = a.dt and b.doc_type = a.doc_type and b.doc_num = a.doc_num
option
 (maxdop 1);

set statistics time off;
go

drop table dbo.t;
go
 Время работы SQL Server:
Время ЦП = 1092 мс, затраченное время = 1091 мс.

Время работы SQL Server:
Время ЦП = 1279 мс, затраченное время = 1292 мс.

Время работы SQL Server:
Время ЦП = 2043 мс, затраченное время = 2046 мс.


Сойдемся на том, что это depends

+
use tempdb;
go

create table dbo.t
(
id int not null identity,-- primary key clustered,
dt date not null,
doc_type varchar(10) not null,
doc_num varchar(500) not null,
hash1 as checksum(dt, doc_type, doc_num),
--hash2 as hashbytes('MD5', convert(varchar(8), dt, 112) + cast(doc_type as char(10)) + cast(doc_num as char(40)))
);
go

insert into dbo.t
select top (1000000)
dateadd(day, rand(checksum(newid())) * 1000, getdate()),
cast(rand(checksum(newid())) * 10 as int),
concat(newid(), newid(), newid(), newid(), newid())
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;

create unique index UIX_t__dt__doc_type__doc_num on dbo.t (dt, doc_type, doc_num);
create clustered index IX_t__hash1 on dbo.t (hash1,dt, doc_type, doc_num);
--create index IX_t__hash2 on dbo.t (hash2) include (dt, doc_type, doc_num);
go

declare @t table
(
dt date not null,
doc_type varchar(10) not null,
doc_num varchar(500) not null,
hash1 int
--,hash2 varbinary(8000)
);

insert into @t
(dt, doc_type, doc_num, hash1)
select
dt, doc_type, doc_num, hash1
from
dbo.t tablesample(1 percent);

declare @id int;

set statistics time on;

select
@id = b.id
from
@t a join
dbo.t b on b.dt = a.dt and b.doc_type = a.doc_type and b.doc_num = a.doc_num
option
(maxdop 1);

select
@id = b.id
from
@t a join
dbo.t b on b.hash1 = a.hash1
where
b.dt = a.dt and b.doc_type = a.doc_type and b.doc_num = a.doc_num
option
(maxdop 1);

set statistics time off;
go

drop table dbo.t;
go


SQL Server parse and compile time:
CPU time = 982 ms, elapsed time = 995 ms.

SQL Server Execution Times:
CPU time = 188 ms, elapsed time = 236 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 4 ms.

SQL Server Execution Times:
CPU time = 93 ms, elapsed time = 101 ms.
12 ноя 15, 14:37    [18406487]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
selis76
a_voronin
selis76,

Для ускорения поиска существующих документов можете попробовать сделать хеш по этой комбинации " Дата, Тип, Номер во внешней системе." и индекс по этому хешу и с последующей допроверкой совпадения этих значений.

Вы memory optimized tables имеете ввиду?
https://msdn.microsoft.com/en-us/library/dn511014.aspx
Как понимаю они только в 2014 появились. А в 2008 вроде все структуры в памяти видны в пределах одной сессии


См выше. Хеш в обычном понимании
12 ноя 15, 14:37    [18406493]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
a_voronin
Сойдемся на том, что это depends
Не сойдемся. По крайней мере, пока вы не начнете выполнять тесты в нормальном тестовом окружении, где на элементарных запросах нет вот такого:
a_voronin
SQL Server parse and compile time:
CPU time = 982 ms, elapsed time = 995 ms.


Ну и заодно расскажите как будете решать задачу ТС предложенным вами методом и какие у него преимущества по сравнению с уникальным индексом с ignore_dup_key.
12 ноя 15, 15:17    [18406726]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
a_voronin
Member

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

Выполните в "нормальном окружении", покажите, что у вас получилось
12 ноя 15, 15:23    [18406770]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
o-o
Guest
у меня нейтральная территория + гарантированная неподдельность картинок.
вариант с 30% слева, с 1% справа.
выводы делайте сами [мне оч. смешно, когда что-то подделывают]

К сообщению приложен файл. Размер - 72Kb
12 ноя 15, 15:30    [18406823]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
a_voronin
Выполните в "нормальном окружении", покажите, что у вас получилось
Не имеет смысла.
Только что обратил внимание - вы сознательно исказили тест, что бы получить нужный результат.
12 ноя 15, 15:33    [18406840]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
invm
a_voronin
Выполните в "нормальном окружении", покажите, что у вас получилось
Не имеет смысла.
Только что обратил внимание - вы сознательно исказили тест, что бы получить нужный результат.


А вы сразу не заметили, что я изменил скрипт? И естественно я получил нужный результат -- с хешом быстрее.
12 ноя 15, 15:37    [18406880]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
o-o,

Справа не мой скрипт

В моём

declare @t table
(
dt date not null,
doc_type varchar(10) not null,
doc_num varchar(500) not null,
hash1 int
--,hash2 varbinary(8000)
);
12 ноя 15, 15:38    [18406884]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
o-o
Guest
невинным голосом и крайне удивленно:
a_voronin
А вы сразу не заметили, что я изменил скрипт?

можно было и в тэги заключить,
и подсветкой выделить.
типа, смотрите, граждане, вот если так переделать...
---
мне в глаза бросилось 30% vs 1%.
выходит, там еще что-то изменено, раз у меня результат по-прежнему в пользу invm.
но ломать глаза и выискивать я не буду,
а копирую себе я только цветной читаемый код,
где сразу видно, что нет никаких бомб замедленного действия.
поэтому у меня на картинке представлен вариант invm,
где справа изменен процент с 30 на 1
12 ноя 15, 15:44    [18406923]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
o-o
Guest
a_voronin
o-o,

Справа не мой скрипт

В моём

declare @t table
(
dt date not null,
doc_type varchar(10) not null,
doc_num varchar(500) not null,
hash1 int
--,hash2 varbinary(8000)
);

теперь вижу.
вот так всегда и делайте,
а то получается, втихушку исказили
12 ноя 15, 15:50    [18406978]     Ответить | Цитировать Сообщить модератору
 Re: Вставка с отслеживанием уникальности - какой способ лучше  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
a_voronin
А вы сразу не заметили, что я изменил скрипт? И естественно я получил нужный результат -- с хешом быстрее.
Само-собой быстрее. Потому что занимаетесь подгонкой. А корректно сравнивать надо было так
+
use tempdb;
go

create table dbo.t
(
 id int not null identity,-- primary key clustered,
 dt date not null,
 doc_type varchar(10) not null,
 doc_num varchar(500) not null,
 hash1 as checksum(dt, doc_type, doc_num),
);
go

insert into dbo.t
select top (1000000)
 dateadd(day, rand(checksum(newid())) * 1000, getdate()),
 cast(rand(checksum(newid())) * 10 as int),
 concat(newid(), newid(), newid(), newid(), newid())
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

create unique clustered index UIX_t__dt__doc_type__doc_num on dbo.t (dt, doc_type, doc_num);
go

declare @t table
(
 dt date not null,
 doc_type varchar(10) not null,
 doc_num varchar(500) not null,
 hash1 int
);

insert into @t
(dt, doc_type, doc_num, hash1)
select
 dt, doc_type, doc_num, hash1
from
 dbo.t tablesample(1 percent);

declare @id int;

set statistics time on;

select
 @id = b.id
from
 @t a join
 dbo.t b on b.dt = a.dt and b.doc_type = a.doc_type and b.doc_num = a.doc_num
option
(maxdop 1);

set statistics time off;

drop index dbo.t.UIX_t__dt__doc_type__doc_num;
create clustered index IX_t__hash1 on dbo.t (hash1,dt, doc_type, doc_num);

set statistics time on;

select
 @id = b.id
from
 @t a join
 dbo.t b on b.hash1 = a.hash1
where
 b.dt = a.dt and b.doc_type = a.doc_type and b.doc_num = a.doc_num
option
(maxdop 1);

set statistics time off;
go

drop table dbo.t;
go
 SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 66 ms.
SQL Server parse and compile time:
CPU time = 156 ms, elapsed time = 370 ms.

SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 95 ms.
12 ноя 15, 15:54    [18407005]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить