SQL ВСТАВЛЯЕТ, но избегает дубликатов
Эй, я все еще застреваю в том лагере также. Приложение сторонних производителей, которое мы должны поддерживать, НАКОНЕЦ идет в 2K5, таким образом, мы почти вне леса. Но я чувствую Вашу боль 8^D
Однако от всего, что я получил известие от нашего DBA, ключ должен преобразовать базу данных в 8,0 форматов сначала, и затем перейти к 2005. Я полагаю, что они использовали созданный в инструментах миграции/обновления для этого. Существуют некоторые большие шаги между 6,5 и 8.0, которые лучше решены там, чем движение от 6,5 до 2005 непосредственно.
Ваша САМАЯ БОЛЬШАЯ боль, если Вы уже не знали, то, что DTS не стало в пользу SSIS. Существует модуль типа оболочки, который выполнит Ваши существующие пакеты DTS, но Вы собираетесь хотеть вручную воссоздать их всех в SSIS. Простота этого будет зависеть от сложности самих пакетов, но я сделал некоторых на работе до сих пор, и они были довольно гладкими.
4 ответа
РЕДАКТИРОВАТЬ : для предотвращения состояний гонки в параллельных средах используйте WITH (UPDLOCK) в коррелированном подзапросе или EXCEPT 'd ВЫБРАТЬ . Тестовый сценарий, который я написал ниже, не требует этого, поскольку он использует временные таблицы, которые видны только текущему соединению, но в реальной среде, работающей с пользовательскими таблицами, это было бы необходимо.
MERGE не требует UPDLOCK .
Вдохновленный ответом mcl re: unique index и позволяя базе данных выдавать ошибку, я решил протестировать условные вставки vs. try / catch .
Похоже, что результаты поддерживают условную вставку вместо try / catch, но YMMV. Это очень простой сценарий (один столбец, небольшая таблица и т. Д.), выполняется на одной машине и т. д.
Вот результаты (SQL Server 2008, сборка 10.0.1600.2):
duplicates (short table)
try/catch: 14440 milliseconds / 100000 inserts
conditional insert: 2983 milliseconds / 100000 inserts
except: 2966 milliseconds / 100000 inserts
merge: 2983 milliseconds / 100000 inserts
uniques
try/catch: 3920 milliseconds / 100000 inserts
conditional insert: 3860 milliseconds / 100000 inserts
except: 3873 milliseconds / 100000 inserts
merge: 3890 milliseconds / 100000 inserts
straight insert: 3173 milliseconds / 100000 inserts
duplicates (tall table)
try/catch: 14436 milliseconds / 100000 inserts
conditional insert: 3063 milliseconds / 100000 inserts
except: 3063 milliseconds / 100000 inserts
merge: 3030 milliseconds / 100000 inserts
Обратите внимание, даже на уникальные вставки, есть немного накладных расходов на попытку / уловить, чем условная вставка. Интересно, зависит ли это от версии, процессора, количества ядер и т. Д.
Я не тестировал условные вставки IF , а просто WHERE . Я предполагаю, что вариант IF будет показывать больше накладных расходов, поскольку а) у вас будет два оператора и б) вам нужно будет заключить два оператора в транзакцию и установить уровень изоляции на сериализуемый (!). Если кто-то хочет проверить это, вам нужно будет изменить временную таблицу на обычную пользовательскую таблицу (сериализуемая таблица не применяется к локальным временным таблицам).
Вот сценарий:
-- tested on SQL 2008.
-- to run on SQL 2005, comment out the statements using MERGE
set nocount on
if object_id('tempdb..#temp') is not null drop table #temp
create table #temp (col1 int primary key)
go
-------------------------------------------------------
-- duplicate insert test against a table w/ 1 record
-------------------------------------------------------
insert #temp values (1)
go
declare @x int, @y int, @now datetime, @duration int
select @x = 1, @y = 0, @now = getdate()
while @y < 100000 begin
set @y = @y+1
begin try
insert #temp select @x
end try
begin catch end catch
end
set @duration = datediff(ms,@now,getdate())
raiserror('duplicates (short table), try/catch: %i milliseconds / %i inserts',-1,-1,@duration,@y) with nowait
go
declare @x int, @y int, @now datetime, @duration int
select @x = 1, @y = 0, @now = getdate()
while @y < 100000 begin
set @y = @y+1
insert #temp select @x where not exists (select * from #temp where col1 = @x)
end
set @duration = datediff(ms,@now,getdate())
raiserror('duplicates (short table), conditional insert: %i milliseconds / %i inserts',-1,-1,@duration, @y) with nowait
go
declare @x int, @y int, @now datetime, @duration int
select @x = 1, @y = 0, @now = getdate()
while @y < 100000 begin
set @y = @y+1
insert #temp select @x except select col1 from #temp
end
set @duration = datediff(ms,@now,getdate())
raiserror('duplicates (short table), except: %i milliseconds / %i inserts',-1,-1,@duration, @y) with nowait
go
-- comment this batch out for SQL 2005
declare @x int, @y int, @now datetime, @duration int
select @x = 1, @y = 0, @now = getdate()
while @y < 100000 begin
set @y = @y+1
merge #temp t using (select @x) s (col1) on t.col1 = s.col1 when not matched by target then insert values (col1);
end
set @duration = datediff(ms,@now,getdate())
raiserror('duplicates (short table), merge: %i milliseconds / %i inserts',-1,-1,@duration, @y) with nowait
go
-------------------------------------------------------
-- unique insert test against an initially empty table
-------------------------------------------------------
truncate table #temp
declare @x int, @now datetime, @duration int
select @x = 0, @now = getdate()
while @x < 100000 begin
set @x = @x+1
insert #temp select @x
end
set @duration = datediff(ms,@now,getdate())
raiserror('uniques, straight insert: %i milliseconds / %i inserts',-1,-1,@duration, @x) with nowait
go
truncate table #temp
declare @x int, @now datetime, @duration int
select @x = 0, @now = getdate()
while @x < 100000 begin
set @x = @x+1
begin try
insert #temp select @x
end try
begin catch end catch
end
set @duration = datediff(ms,@now,getdate())
raiserror('uniques, try/catch: %i milliseconds / %i inserts',-1,-1,@duration, @x) with nowait
go
truncate table #temp
declare @x int, @now datetime, @duration int
select @x = 0, @now = getdate()
while @x < 100000 begin
set @x = @x+1
insert #temp select @x where not exists (select * from #temp where col1 = @x)
end
set @duration = datediff(ms,@now,getdate())
raiserror('uniques, conditional insert: %i milliseconds / %i inserts',-1,-1,@duration, @x) with nowait
go
truncate table #temp
declare @x int, @now datetime, @duration int
select @x = 0, @now = getdate()
while @x < 100000 begin
set @x = @x+1
insert #temp select @x except select col1 from #temp
end
set @duration = datediff(ms,@now,getdate())
raiserror('uniques, except: %i milliseconds / %i inserts',-1,-1,@duration, @x) with nowait
go
-- comment this batch out for SQL 2005
truncate table #temp
declare @x int, @now datetime, @duration int
select @x = 1, @now = getdate()
while @x < 100000 begin
set @x = @x+1
merge #temp t using (select @x) s (col1) on t.col1 = s.col1 when not matched by target then insert values (col1);
end
set @duration = datediff(ms,@now,getdate())
raiserror('uniques, merge: %i milliseconds / %i inserts',-1,-1,@duration, @x) with nowait
go
-------------------------------------------------------
-- duplicate insert test against a table w/ 100000 records
-------------------------------------------------------
declare @x int, @y int, @now datetime, @duration int
select @x = 1, @y = 0, @now = getdate()
while @y < 100000 begin
set @y = @y+1
begin try
insert #temp select @x
end try
begin catch end catch
end
set @duration = datediff(ms,@now,getdate())
raiserror('duplicates (tall table), try/catch: %i milliseconds / %i inserts',-1,-1,@duration,@y) with nowait
go
declare @x int, @y int, @now datetime, @duration int
select @x = 1, @y = 0, @now = getdate()
while @y < 100000 begin
set @y = @y+1
insert #temp select @x where not exists (select * from #temp where col1 = @x)
end
set @duration = datediff(ms,@now,getdate())
raiserror('duplicates (tall table), conditional insert: %i milliseconds / %i inserts',-1,-1,@duration, @y) with nowait
go
declare @x int, @y int, @now datetime, @duration int
select @x = 1, @y = 0, @now = getdate()
while @y < 100000 begin
set @y = @y+1
insert #temp select @x except select col1 from #temp
end
set @duration = datediff(ms,@now,getdate())
raiserror('duplicates (tall table), except: %i milliseconds / %i inserts',-1,-1,@duration, @y) with nowait
go
-- comment this batch out for SQL 2005
declare @x int, @y int, @now datetime, @duration int
select @x = 1, @y = 0, @now = getdate()
while @y < 100000 begin
set @y = @y+1
merge #temp t using (select @x) s (col1) on t.col1 = s.col1 when not matched by target then insert values (col1);
end
set @duration = datediff(ms,@now,getdate())
raiserror('duplicates (tall table), merge: %i milliseconds / %i inserts',-1,-1,@duration, @y) with nowait
go
Другой вариант: создать уникальный индекс для рассматриваемых полей (SecurityCode, BuyPrice, SellPrice), выполнить простую вставку и позволить базе данных решить, будет ли записи дублируются. Вставка завершится неудачно при попытке вставить дубликат.
Использование кода (будь то внешний язык или процедура SQL) для гарантии уникальности недостаточно строго и в конечном итоге приведет к появлению тех самых дубликатов, которые вы надеетесь предотвратить.
РЕДАКТИРОВАТЬ : чтобы предотвратить состояние гонки в параллельной среде, используйте WITH (UPDLOCK) в коррелированном подзапросе.
I подумайте, что это будет стандартный метод:
INSERT INTO MarketPrices (SecurityCode, BuyPrice, SellPrice, IsMarketOpen)
SELECT @SecurityCode, @BuyPrice, @SellPrice, @IsMarketOpen
WHERE NOT EXISTS (
SELECT * FROM MarketPrices WITH (UPDLOCK)
WHERE SecurityCode = @SecurityCode
AND BuyPrice = @BuyPrice
AND SellPrice = @SellPrice
)
Если какое-либо из ваших полей допускает значение NULL, вам придется добавить это к условию.
Ваш первый метод интересен, но требования для EXCEPT заставляют вас прыгать через обручи. Этот метод, по сути, тот же, но он помогает обойти проблему сопоставления столбцов.
В качестве альтернативы:
INSERT INTO MarketPrices (SecurityCode, BuyPrice, SellPrice, IsMarketOpen)
SELECT SecurityCode, BuyPrice, SellPrice, @IsMarketOpen
FROM (
SELECT @SecurityCode, @BuyPrice, @SellPrice
EXCEPT
SELECT SecurityCode, BuyPrice, SellPrice FROM MarketPrices WITH (UPDLOCK)
) a (SecurityCode, BuyPrice, SellPrice)
Хорошая особенность EXCEPT в этом случае заключается в том, что он обрабатывает NULL без какого-либо дополнительного кодирования с вашей стороны. Чтобы добиться того же в первом примере, вам нужно будет протестировать каждую пару на NULL, а также на равенство, длинную руку.
Ваш второй метод подходит, но вам не нужна переменная. См. Решение Томалака, он его хорошо почистил. Кроме того, вам нужно было бы явно обрабатывать возможность одновременных вставок, если бы это было проблемой.
Я бы всегда хотел найти семантическое решение. Ваши два предложения кажутся мне довольно непонятными (хотя последнее лучше первого).
IF NOT EXISTS (
SELECT 1
FROM MarketPrices
WHERE SecurityCode = @SecurityCode
AND BuyPrice = @BuyPrice
AND SellPrice = @SellPrice
)
BEGIN
INSERT MarketPrices
(SecurityCode, BuyPrice, SellPrice, IsMarketOpen)
VALUES
(@SecurityCode, @BuyPrice, @SellPrice, @IsMarketOpen)
END
При индексе конгломерата выше SecurityCode, BuyPrice, SellPrice запрос EXISTS должен работать разумно быстро.
Бенчмаркинг - это вопрос времени цикла WHILE , я бы сказал. Проверьте это и убедитесь сами.