Если я остановлю длительный запрос, он откатится?
Запрос, который используется для циклического прохождения 17 миллионов записей с целью удаления дубликатов, выполняется уже около 16 часов, и я хотел знать, остановлен ли запрос прямо сейчас, завершит ли он операторы удаления или он был удален во время выполнения этого запрос? Действительно, если я остановлю это, завершит ли удаление или откатит?
Я обнаружил, что когда я делаю
select count(*) from myTable
То, что строки, которые он возвращает (при выполнении этого запроса), примерно на 5 меньше, чем количество начальных строк. Очевидно, что ресурсы сервера крайне бедны, значит ли это, что этот процесс занял 16 часов, чтобы найти 5 дубликатов (когда их на самом деле тысячи), и это могло бы продолжаться несколько дней?
Этот запрос занял 6 секунд для 2000 строк тестовых данных, и он отлично работает с этим набором данных, поэтому я решил, что для полного набора потребуется 15 часов.
Есть идеи?
Ниже приведен запрос:
--Declare the looping variable
DECLARE @LoopVar char(10)
DECLARE
--Set private variables that will be used throughout
@long DECIMAL,
@lat DECIMAL,
@phoneNumber char(10),
@businessname varchar(64),
@winner char(10)
SET @LoopVar = (SELECT MIN(RecordID) FROM MyTable)
WHILE @LoopVar is not null
BEGIN
--initialize the private variables (essentially this is a .ctor)
SELECT
@long = null,
@lat = null,
@businessname = null,
@phoneNumber = null,
@winner = null
-- load data from the row declared when setting @LoopVar
SELECT
@long = longitude,
@lat = latitude,
@businessname = BusinessName,
@phoneNumber = Phone
FROM MyTable
WHERE RecordID = @LoopVar
--find the winning row with that data. The winning row means
SELECT top 1 @Winner = RecordID
FROM MyTable
WHERE @long = longitude
AND @lat = latitude
AND @businessname = BusinessName
AND @phoneNumber = Phone
ORDER BY
CASE WHEN webAddress is not null THEN 1 ELSE 2 END,
CASE WHEN caption1 is not null THEN 1 ELSE 2 END,
CASE WHEN caption2 is not null THEN 1 ELSE 2 END,
RecordID
--delete any losers.
DELETE FROM MyTable
WHERE @long = longitude
AND @lat = latitude
AND @businessname = BusinessName
AND @phoneNumber = Phone
AND @winner != RecordID
-- prep the next loop value to go ahead and perform the next duplicate query.
SET @LoopVar = (SELECT MIN(RecordID)
FROM MyTable
WHERE @LoopVar < RecordID)
END
12 ответов
Нет, сервер sql не откатит уже выполненные удаления, если вы остановите выполнение запроса. oracle требует явной фиксации запросов действий, или данные откатываются, но не mssql.
с сервером sql он не будет откатываться, если вы специально не работаете в контексте транзакции и не откатываете эту транзакцию, или соединение закрывается без фиксации транзакции. но я не вижу контекста транзакции в вашем запросе выше.
Вы также можете попробовать реструктурировать свой запрос, чтобы сделать удаление немного более эффективным, но, по сути, если спецификации вашего ящика не соответствуют требованиям, вы можете застревать в ожидании.
В дальнейшем вы должны создать уникальный индекс для таблицы, чтобы избежать необходимости повторять это снова.
Ваш запрос не включен в транзакцию, поэтому он не откатит изменения, уже внесенные отдельными операторами удаления.
Я специально проверил это на своем собственном SQL Server, используя следующий запрос, и таблица ApplicationLog была пустой, хотя я отменил запрос:
declare @count int
select @count = 5
WHILE @count > 0
BEGIN
print @count
delete from applicationlog;
waitfor time '20:00';
select @count = @count -1
END
Однако ваш запрос может занять много дней или недель, гораздо дольше, чем 15 часов. Ваша оценка того, что вы можете обрабатывать 2000 записей каждые 6 секунд, неверна, потому что каждая итерация в цикле while будет занимать значительно больше времени с 17 миллионами строк, чем с 2000 строками. Таким образом, если ваш запрос не займет значительно меньше секунды для 2000 строк, для всех 17 миллионов потребуется несколько дней.
Вам следует задать новый вопрос о том, как эффективно удалять дублирующиеся строки.
Неявные транзакции
Если "неявные транзакции" не установлены, то каждая итерация в вашем цикле фиксирует изменения.
Для любого SQL Server можно установить "неявные транзакции". Это настройка базы данных (по умолчанию выключено). Вы также можете иметь неявные транзакции в свойствах определенного запроса внутри Management Studio (щелкните правой кнопкой мыши на панели запросов> параметры), по умолчанию в клиенте или в инструкции SET.
SET IMPLICIT_TRANSACTIONS ON;
В любом случае, если бы это было так, вам все равно нужно было бы выполнить явный COMMIT/ROLLBACK независимо от прерывания выполнения запроса.
Ссылка на неявные транзакции:
Если вы ничего не сделаете в отношении транзакций, тогда соединение будет в режиме транзакций с автоматическим подтверждением. В этом режиме каждый оператор SQL считается транзакцией.
Вопрос в том, означает ли это, что отдельные операторы SQL являются транзакциями и, следовательно, фиксируются на ходу или внешний цикл WHILE считается транзакцией.
Похоже, что это не обсуждается в описании конструкции WHILE на MSDN. Однако, поскольку оператор WHILE не может напрямую изменить базу данных, может показаться логичным, что он не запускает транзакцию автоматической фиксации.
Я унаследовал систему, в которой логика была похожа на вашу, реализованную в SQL. В нашем случае мы пытались связать вместе строки, используя нечеткое сопоставление с одинаковыми именами / адресами и т. Д., И эта логика была сделана исключительно в SQL. В то время, когда я унаследовал его, у нас было около 300000 строк в таблице, и согласно времени мы рассчитали, что для их сопоставления потребуется ГОД.
В качестве эксперимента, чтобы увидеть, насколько быстрее я смогу сделать это за пределами SQL, я написал программу для выгрузки таблицы db в плоские файлы, чтения плоских файлов в программу на C++, построения собственных индексов и выполнения там нечеткой логики, затем повторно импортируйте плоские файлы в базу данных. То, что заняло ГОД в SQL, заняло около 30 секунд в приложении C++.
Итак, мой совет, даже не пытайтесь делать то, что вы делаете в SQL. Экспорт, обработка, реимпорт.
УДАЛЕНИЯ, которые были выполнены до этого момента, не будут отменены.
Как первоначальный автор рассматриваемого кода и выпустив предупреждение, что производительность будет зависеть от индексов, я бы предложил следующие пункты, чтобы ускорить это.
Запись лучше быть ПЕРВИЧНЫМ КЛЮЧОМ. Я не имею в виду ИДЕНТИЧНОСТЬ, я имею в виду ПЕРВИЧНЫЙ КЛЮЧ. Подтвердите это с помощью sp_help
Некоторый индекс должен использоваться при оценке этого запроса. Выясните, какой из этих четырех столбцов имеет наименьшее количество повторений, и индекс, который...
SELECT *
FROM MyTable
WHERE @long = longitude
AND @lat = latitude
AND @businessname = BusinessName
AND @phoneNumber = Phone
До и после добавления этого индекса проверьте план запроса, чтобы увидеть, было ли добавлено сканирование индекса.
В качестве цикла ваш запрос будет плохо масштабироваться даже при наличии соответствующих индексов. Запрос должен быть переписан в одно утверждение в соответствии с предложениями в вашем предыдущем вопросе по этому вопросу.
Если вы не запускаете его явно в транзакции, он только откатит выполняющуюся инструкцию.
Я думаю, что вы должны серьезно рассмотреть вашу методологию. Вы должны начать думать в наборах (хотя для производительности вам может потребоваться пакетная обработка, но не строка за строкой против таблицы с 17 миллионами записей).
Сначала все ваши записи имеют дубликаты? Я подозреваю, что нет, поэтому первое, что вы хотите сделать, это ограничить обработку только теми записями, которые имеют дубликаты. Поскольку это большая таблица, и вам может потребоваться время от времени удалять пакеты, в зависимости от того, что происходит при другой обработке, вы сначала извлекаете записи, с которыми вы хотите работать, в собственную таблицу, которую затем индексируете. Вы также можете использовать временную таблицу, если вы собираетесь делать все это одновременно, не останавливая ее, в противном случае создайте таблицу в своей базе данных и удалите ее в конце.
Что-то вроде (заметьте, я не писал создания индексов, я думаю, вы сами можете это посмотреть):
SELECT min(m.RecordID), m.longitude, m.latitude, m.businessname, m.phone
into #RecordsToKeep
FROM MyTable m
join
(select longitude, latitude, businessname, phone
from MyTable
group by longitude, latitude, businessname, phone
having count(*) >1) a
on a.longitude = m.longitude and a.latitude = m.latitude and
a.businessname = b.businessname and a.phone = b.phone
group by m.longitude, m.latitude, m.businessname, m.phone
ORDER BY CASE WHEN m.webAddress is not null THEN 1 ELSE 2 END,
CASE WHEN m.caption1 is not null THEN 1 ELSE 2 END,
CASE WHEN m.caption2 is not null THEN 1 ELSE 2 END
while (select count(*) from #RecordsToKeep) > 0
begin
select top 1000 *
into #Batch
from #RecordsToKeep
Delete m
from mytable m
join #Batch b
on b.longitude = m.longitude and b.latitude = m.latitude and
b.businessname = b.businessname and b.phone = b.phone
where r.recordid <> b.recordID
Delete r
from #RecordsToKeep r
join #Batch b on r.recordid = b.recordid
end
Delete m
from mytable m
join #RecordsToKeep r
on r.longitude = m.longitude and r.latitude = m.latitude and
r.businessname = b.businessname and r.phone = b.phone
where r.recordid <> m.recordID
Я думаю, что этот запрос был бы гораздо более эффективным, если бы он был переписан с использованием однопроходного алгоритма с использованием курсора. Вы бы упорядочили таблицу курсоров по долготе, широте,BusinessName AND @phoneNumber. Вы бы шагали по строкам по одному. Если строка имеет ту же долготу, широту, название компании и номер телефона, что и предыдущая строка, удалите ее.
Если на вашей машине нет очень продвинутого оборудования, то для выполнения этой команды серверу sql может потребоваться очень много времени. Я не знаю наверняка, как эта операция выполняется скрытно, но исходя из моего опыта, это можно сделать более эффективно, перенеся записи из базы данных и в память для программы, которая использует древовидную структуру с правилом удаления дубликатов. для вставки. Попробуйте прочитать всю таблицу в чанках (скажем, 10000 строк за раз) в программу на C++ с использованием ODBC. Однажды в программе на C++ используйте и std::map, где ключ - это уникальный ключ, а struct - это структура, которая хранит остальные данные в переменных. Зациклите все записи и выполните вставку в карту. Функция вставки карты будет обрабатывать удаление дубликатов. Поскольку поиск по карте занимает lg(n) времени, гораздо меньше времени для поиска дубликатов, чем при использовании вашего цикла while. Затем вы можете удалить всю таблицу и добавить кортежи обратно в базу данных с карты, сформировав запросы на вставку и выполнив их через odbc или создав сценарий текстового файла и запустив его в Management Studio.
Также попробуйте подумать о другом методе удаления повторяющихся строк:
delete t1 from table1 as t1 where exists (
select * from table1 as t2 where
t1.column1=t2.column1 and
t1.column2=t2.column2 and
t1.column3=t2.column3 and
--add other colums if any
t1.id>t2.id
)
Я полагаю, что у вас есть столбец целочисленного идентификатора в вашей таблице.
Я уверен, что это отрицательный. Иначе какой будет точка транзакции?