Подтвердить что ты не робот

Если я остановлю длинный запрос, откатывается ли он?

Запрос, который используется для циклического перехода через 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
4b9b3361

ОТВЕТЫ

Ответ 1

нет, сервер sql не откатывает удаленные файлы, которые он уже выполнил, если вы прекратили выполнение запроса. oracle требует явной комплитации запросов на действие или данные откатываются назад, но не mssql.

с сервером sql он не откатится, если вы специально не выполняетесь в контексте транзакции, и вы откатываете эту транзакцию или соединение закрывается без транзакции. но я не вижу контекста транзакции в указанном выше запросе.

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

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

Ответ 2

Ваш запрос не заключен в транзакцию, поэтому он не отменит изменения, уже сделанные отдельными инструкциями delete.

Я специально проверил это самостоятельно на своем собственном 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 миллионов потребуется несколько дней.

Вы должны задать новый вопрос о том, как эффективно удалять повторяющиеся строки.

Ответ 3

Если вы ничего не делаете в отношении транзакций, то соединение будет находиться в режиме autocommit transaction. В этом режиме каждый оператор SQL считается транзакцией.

Вопрос заключается в том, означает ли это, что отдельные операторы SQL являются транзакциями и, следовательно, совершаются по ходу процесса, или что внешний цикл WHILE считается транзакцией.

В описании конструкции WHILE, похоже, не обсуждается это на MSDN. Однако, поскольку оператор WHILE не может напрямую изменить базу данных, кажется логичным, что он не выполняет транзакцию с автоматическим фиксацией.

Ответ 4

Неявные транзакции

Если не задано "Неявные транзакции", каждая итерация в вашем цикле совершила изменения.

Для любого SQL Server можно установить "Неявные транзакции". Это настройка базы данных (по умолчанию OFF). Вы также можете иметь неявные транзакции в свойствах конкретного запроса внутри Management Studio (щелкните правой кнопкой мыши в области запроса), по умолчанию в настройках клиента или инструкции SET.

SET IMPLICIT_TRANSACTIONS ON;

В любом случае, если это так, вам все равно нужно выполнить явный COMMIT/ROLLBACK независимо от прерывания выполнения запроса.

Ссылка на неявные транзакции:

http://msdn.microsoft.com/en-us/library/ms188317.aspx

http://msdn.microsoft.com/en-us/library/ms190230.aspx

Ответ 5

Я унаследовал систему, у которой была логика, похожая на вашу, реализованную в SQL. В нашем случае мы пытались связать строки, используя нечеткое сопоставление с похожими именами/адресами и т.д., И эта логика была выполнена исключительно в SQL. В то время, когда я унаследовал это, у нас было около 300 000 строк в таблице, и в соответствии с таймингами мы подсчитали, что для соответствия всем их потребуется ГОД.

В качестве эксперимента, чтобы узнать, насколько быстрее я мог бы сделать это за пределами SQL, я написал программу для dump-таблицы в плоские файлы, чтения плоских файлов в С++-программу, создания собственных индексов и создания нечетких логика там, затем reimport плоские файлы в базу данных. То, что заняло A YEAR в SQL, заняло около 30 секунд в приложении С++.

Итак, мой совет: даже не пытайтесь делать то, что вы делаете в SQL. Экспорт, процесс, повторный импорт.

Ответ 6

DELETES, которые были выполнены до этой точки, не будут откатны.

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

RecordId лучше быть PRIMARY KEY. Я не имею в виду ИДЕНТИЧНОСТЬ, я имею в виду ПЕРВИЧНЫЙ КЛЮЧ. Подтвердите это, используя sp_help

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

SELECT *
FROM MyTable
WHERE @long = longitude
  AND @lat = latitude
  AND @businessname = BusinessName
  AND @phoneNumber = Phone

До и после добавления этого индекса проверьте план запроса, чтобы узнать, было ли добавлено сканирование индекса.

Ответ 7

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

Если вы не используете его явно в транзакции, он откатит только исполняемый оператор.

Ответ 8

Я думаю, что этот запрос был бы намного более эффективным, если бы он был переписан с использованием однопроходного алгоритма с использованием курсора. Вы должны заказать таблицу курсора по долготе, широте, BusinessName И @phoneNumber. Вы заходите по строкам по одному. Если строка имеет одинаковую долготу, широту, бизнес-имя и номер телефона в предыдущей строке, то удалите ее.

Ответ 9

Я думаю, вам нужно серьезно подумать о своей методологии.  Вам нужно начать думать в наборах (хотя для производительности вам может потребоваться пакетная обработка, но не строка за строкой против 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

Ответ 10

Также попробуйте подумать о другом методе удаления повторяющихся строк:

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
)

Я полагаю, что у вас есть столбец с целым числом в вашей таблице.

Ответ 11

Если ваш компьютер не оснащен очень современным оборудованием, для завершения этой команды может потребоваться очень много времени для сервера sql. Я не знаю точно, как эта операция выполняется под капотом, но на основе моего опыта это можно было бы сделать более эффективно, выведя записи из базы данных и в память для программы, которая использует древовидную структуру с удаленным дублирующимся правилом для вставки. Попробуйте прочитать всю таблицу в chuncks (скажем, 10000 строк за раз) в С++-программу с использованием ODBC. Однажды в программе на С++ используйте и std:: map, где ключ является уникальным ключом, а struct - структурой, которая содержит остальную часть данных в переменных. Переверните все записи и выполните вставку на карту. Функция вставки карты будет обрабатывать удаление дубликатов. Поскольку поиск внутри карты - это lg (n), время намного меньше времени, чтобы найти дубликаты, чем использование цикла while. Затем вы можете удалить всю таблицу и добавить кортежи обратно в базу данных с карты, сформировав запросы вставки и выполнив их через odbc или создав текстовый файл script и запустив его в студии управления.

Ответ 12

Я уверен, что это отрицательно. В противном случае, какова будет точка транзакций?