복붙노트

[SQL] 나는 긴 쿼리의 실행을 중지하면, 롤백합니까?

SQL

나는 긴 쿼리의 실행을 중지하면, 롤백합니까?

제거 중복에 17 개 백만 레코드를 루프에 사용되는 쿼리는 약 16 시간 동안 지금 실행 된 나는 쿼리가 삭제 문을 완료 할 경우 지금 중지 된 경우 알고 싶어하거나 실행하는 동안 삭제 된 경우 질문? 내가 그것을 중지 할 경우 사실, 그것은 다시 삭제 또는 롤을 완성 하는가?

나는이 작업을 수행 할 때 것을 발견했다

 select count(*) from myTable

(이 쿼리를하는 동안)가 반환하는 행 그건 덜 시작 행 개수가 무엇인지 5에 관한 것입니다. 분명히 서버 자원 때문에이 과정이 5 개 중복 (수천 실제로이있는 경우) 찾기 위해 16 시간했음을 의미하는 것이 수행하고,이 일을 위해 실행 될 수있는 매우 가난한?

이 쿼리는 테스트 데이터의 2000 행에 6 초 갔고, 나는 그것이 완전한 세트 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

해결법

  1. ==============================

    1.아니, SQL 서버는 쿼리 실행을 중지하는 경우 이미 수행 한 삭제를 롤백하지 않습니다. 오라클은 실행 쿼리의 명시적인 언질을 필요로하거나 데이터가 아닌 경우 MSSQL, 롤백됩니다.

    아니, SQL 서버는 쿼리 실행을 중지하는 경우 이미 수행 한 삭제를 롤백하지 않습니다. 오라클은 실행 쿼리의 명시적인 언질을 필요로하거나 데이터가 아닌 경우 MSSQL, 롤백됩니다.

    SQL 서버에 당신이 특별히 트랜잭션의 컨텍스트에서 실행되지 않는 롤백되지 않으며 해당 트랜잭션을 롤백하거나 트랜잭션없이 연결 닫히고이 커밋 된. 하지만 난 당신의 위의 쿼리에서 트랜잭션 컨텍스트를 볼 수 없습니다.

    당신은 또한 좀 더 효율적으로 삭제하도록 재 구조화에게 쿼리를 시도 할 수 있지만 상자의 사양은 최대 스너프에없는 본질적 경우에 당신은 그것을 기다리고 붙어있을 수 있습니다.

    향후, 당신은 다시 통과 할 필요가 자신을 유지하기 위해 테이블에 고유 인덱스를 생성해야합니다.

  2. ==============================

    2.이미 개별 삭제 문에 의한 변경을 롤백하지 않도록 귀하의 쿼리 트랜잭션에 싸여 있지 않습니다.

    이미 개별 삭제 문에 의한 변경을 롤백하지 않도록 귀하의 쿼리 트랜잭션에 싸여 있지 않습니다.

    내가 특별히 다음과 같은 쿼리를 사용하여 내 자신의 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시간 며칠 또는 몇 주가 걸릴 것으로 예상된다. 당신의 while 루프의 각 반복이 2000 개 행이 수행 한 후 1700 만 개 행이 상당히 오래 걸릴 것 때문에 6 초마다 2,000 레코드를 처리 할 수있는 귀하의 추정이 잘못되었습니다. 쿼리 2000 행에 대해 훨씬 적은 다음 두 번째 소요하지 않는 그래서, 모든 17000000에 대한 일이 소요됩니다.

    당신은 당신이 효율적으로 중복 행을 삭제하는 방법에 대한 새로운 질문을 요청해야합니다.

  3. ==============================

    3.당신이 거래에 대해 명시 적으로 아무것도하지 않는 경우, 연결이 자동 커밋 트랜잭션 모드가됩니다. 이 모드에서 모든 SQL 문은 트랜잭션으로 간주됩니다.

    당신이 거래에 대해 명시 적으로 아무것도하지 않는 경우, 연결이 자동 커밋 트랜잭션 모드가됩니다. 이 모드에서 모든 SQL 문은 트랜잭션으로 간주됩니다.

    문제는이 개별 SQL 문을 거래하고 당신이 가서 때문에 최선을 다하고되고, 또는 외부 WHILE 루프 카운트 여부를 거래 수단 여부입니다.

    MSDN에 WHILE 구조에 대한 설명이 어떤 토론이있을 것 같지 않습니다. 잠시 문을 데이터베이스에 직접 수정할 수 없습니다 이후는 자동 커밋 트랜잭션을 시작하지 않습니다 그러나, 그것은 논리적 인 것 같다.

  4. ==============================

    4.더 '암시 적 거래'가 설정되어 있지 않은 경우, 루프의 각 반복은 변경 사항을 커밋.

    더 '암시 적 거래'가 설정되어 있지 않은 경우, 루프의 각 반복은 변경 사항을 커밋.

    어떤 SQL Server가 '암시 적 거래'로 설정하는 것이 가능하다. 이 데이터베이스의 설정입니다 (기본값은 해제되어 있습니다). 또한 클라이언트의 기본 설정에 의한 관리 Studio (쿼리 창에서 마우스 오른쪽 클릭> 옵션)의 특정 쿼리의 내부 또는 SET 문의 특성에 암시 적 거래를 할 수 있습니다.

    SET IMPLICIT_TRANSACTIONS ON;

    이 경우라면 어느 쪽이든은, 당신은 여전히 ​​명시 적 / ROLLBACK에 관계없이 쿼리 실행의 중단의 COMMIT 실행해야합니다.

    암시 적 트랜잭션 참조 :

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

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

  5. ==============================

    5.나는 SQL에서 구현 당신 같은 논리 무언가를 가지고 시스템을 물려 받았다. 우리의 경우, 우리는 등 유사한 이름 / 주소를했다 퍼지 매칭을 사용하여 링크 함께 행하려고하고, 그 논리는 SQL에서 순수하게 이루어졌다. 나는 그것을 상속 당시 우리는 테이블에 300,000에 대한 행을했고, 타이밍에 따라 계산 우리는 그들 모두를 일치 년을 걸릴 것이다.

    나는 SQL에서 구현 당신 같은 논리 무언가를 가지고 시스템을 물려 받았다. 우리의 경우, 우리는 등 유사한 이름 / 주소를했다 퍼지 매칭을 사용하여 링크 함께 행하려고하고, 그 논리는 SQL에서 순수하게 이루어졌다. 나는 그것을 상속 당시 우리는 테이블에 300,000에 대한 행을했고, 타이밍에 따라 계산 우리는 그들 모두를 일치 년을 걸릴 것이다.

    실험 내가 SQL 외부에서 그것을 할 수 얼마나 빨리보고, 나는, 플랫 파일에 DB 테이블 덤프는 C로 플랫 파일을 읽을 ++ 프로그램을, 내 자신의 인덱스를 구축하고,이 퍼지 논리를 할 수있는 프로그램을 작성 다음 데이터베이스에 플랫 파일을 다시 가져옵니다. 무엇 SQL에서 일년했다하면 C ++ 응용 프로그램에서 30초 걸렸다.

    그래서, 내 조언은, 심지어 당신은 SQL에서 무엇을하고 있는지하지 않습니다. 수출, 프로세스, 다시 가져옵니다.

  6. ==============================

    6.이 시점까지 수행 된 삭제 작업 롤백되지 않습니다.

    이 시점까지 수행 된 삭제 작업 롤백되지 않습니다.

    원래의 질문에 코드의 저자 및 성능 인덱스에 의존 할 것이라는 경고를 발행 한 것으로,이 속도를 높이기 위해 다음과 같은 항목을 제안합니다.

    recordId를 더 PRIMARY KEY합니다. 그럴 평균 IDENTITY, I 평균 PRIMARY KEY을한다. 이 사용 sp_help는 확인

    일부 지수는이 쿼리를 평가하는데 사용되어야한다. 그림은이 네 개의 열 중 어느 밖으로 최소 반복 및 색인이있다 ...

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

    전에이 인덱스를 추가 한 후, 인덱스 스캔이 추가되어 있는지 확인하려면 쿼리 계획을 확인합니다.

  7. ==============================

    7.루프로 쿼리에도 적절한 인덱스, 확장 성 투쟁 할 것이다. 쿼리는이에 이전 질문과 제안에 따라, 하나의 문을 다시 작성해야합니다.

    루프로 쿼리에도 적절한 인덱스, 확장 성 투쟁 할 것이다. 쿼리는이에 이전 질문과 제안에 따라, 하나의 문을 다시 작성해야합니다.

    당신이 트랜잭션 내에서 명시 적으로 실행하지 않을 경우에만 실행 문을 롤백합니다.

  8. ==============================

    8.나는 커서를 사용하여 단일 패스 알고리즘을 사용하여 다시 작성되었습니다 경우이 쿼리는 훨씬 더 효율적이 될 것이라고 생각합니다. 당신은 경도, 위도, BUSINESSNAME 및 @phoneNumber에 의해 테이블에 커서를 주문합니다. 한 번에 행 하나를 단계별 것입니다. 행이 이전 행과 동일한 경도, 위도, BUSINESSNAME 및 PHONENUMBER이있는 경우,이를 삭제합니다.

    나는 커서를 사용하여 단일 패스 알고리즘을 사용하여 다시 작성되었습니다 경우이 쿼리는 훨씬 더 효율적이 될 것이라고 생각합니다. 당신은 경도, 위도, BUSINESSNAME 및 @phoneNumber에 의해 테이블에 커서를 주문합니다. 한 번에 행 하나를 단계별 것입니다. 행이 이전 행과 동일한 경도, 위도, BUSINESSNAME 및 PHONENUMBER이있는 경우,이를 삭제합니다.

  9. ==============================

    9.난 당신이 심각하게 methodolology을 고려할 필요가 있다고 생각합니다. (성능을 일괄 처리를해야 할 수도 있습니다,하지만 1,700 만 기록 테이블에 대해 행에 의해 행 아니지만.) 당신은 세트 사고를 시작해야합니다

    난 당신이 심각하게 methodolology을 고려할 필요가 있다고 생각합니다. (성능을 일괄 처리를해야 할 수도 있습니다,하지만 1,700 만 기록 테이블에 대해 행에 의해 행 아니지만.) 당신은 세트 사고를 시작해야합니다

    첫 번째 레코드의 모든 중복을해야합니까? 당신이해야 할 완 제일 먼저 중복이 레코드 만에 처리를 제한하는, 그래서 내가 아니라 생각한다. 이 큰 테이블과 다른 처리는 무슨 일이 일어나고 있는지에 따라 시간이 지남에 따라 일괄 적으로 삭제를해야 할 수 있기 때문에, 당신은 먼저 그 다음 색인 자신의 테이블에 당신이 처리 할 레코드를 당깁니다. 당신이 이제까지는 다른 현명한 데이터베이스에서 테이블을 만들고 마지막에 드롭 중지하지 않고 동시에이 모든 것을 할 수있을 위하여려고하는 경우에 당신은 또한 임시 테이블을 사용할 수 있습니다.

    같은 뭔가 (주 나는 당신이 자신까지 그 볼 수 있습니다 파악, 인덱스 문에서도를 만들 작성하지 않은) :

    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. ==============================

    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
)

    난 당신이 테이블에 정수 ID 열이 있다고 가정합니다.

  11. ==============================

    11.기계가 매우 고급 하드웨어가없는 경우는 해당 명령을 완료하기 위해 SQL 서버에게 시간이 오래 걸릴 수 있습니다. 이 작업이 후드를 수행하지만,이 프로그램에 대한 데이터베이스에서 메모리로 레코드를 가져 와서보다 효율적으로 수행 할 수있는 내 경험을 기반으로 어떻게 확실히 모르는하는 제거 중복 된 규칙을 사용하는 트리 구조를 삽입. ODBC를 사용하여 C ++ 프로그램에 chuncks (한 번에 10,000 행을 말한다)의 테이블 전체를 읽어보십시오. 키는 고유 키와 구조체 인 C ++ 프로그램 사용과 표준 : :지도에서 일단 변수 데이터의 나머지 부분을 유지하는 구조체이다. 루프는 모든 레코드를 통해 맵에 삽입을 수행합니다. 지도 삽입 기능은 중복 제거 처리합니다. 지도 내 검색 (n)의 시간을 훨씬 적은 시간을 LG되어 있기 때문에 당신의 while 루프를 사용하는 것보다 중복을 찾을 수 있습니다. 그런 다음 전체 테이블을 삭제하고 쿼리를 삽입 형성 및 ODBC를 통해이를 실행 또는 텍스트 파일 스크립트를 구축하고 관리 스튜디오에서 실행하여지도에서 데이터베이스로 다시 튜플을 추가 할 수 있습니다.

    기계가 매우 고급 하드웨어가없는 경우는 해당 명령을 완료하기 위해 SQL 서버에게 시간이 오래 걸릴 수 있습니다. 이 작업이 후드를 수행하지만,이 프로그램에 대한 데이터베이스에서 메모리로 레코드를 가져 와서보다 효율적으로 수행 할 수있는 내 경험을 기반으로 어떻게 확실히 모르는하는 제거 중복 된 규칙을 사용하는 트리 구조를 삽입. ODBC를 사용하여 C ++ 프로그램에 chuncks (한 번에 10,000 행을 말한다)의 테이블 전체를 읽어보십시오. 키는 고유 키와 구조체 인 C ++ 프로그램 사용과 표준 : :지도에서 일단 변수 데이터의 나머지 부분을 유지하는 구조체이다. 루프는 모든 레코드를 통해 맵에 삽입을 수행합니다. 지도 삽입 기능은 중복 제거 처리합니다. 지도 내 검색 (n)의 시간을 훨씬 적은 시간을 LG되어 있기 때문에 당신의 while 루프를 사용하는 것보다 중복을 찾을 수 있습니다. 그런 다음 전체 테이블을 삭제하고 쿼리를 삽입 형성 및 ODBC를 통해이를 실행 또는 텍스트 파일 스크립트를 구축하고 관리 스튜디오에서 실행하여지도에서 데이터베이스로 다시 튜플을 추가 할 수 있습니다.

  12. ==============================

    12.나는 반대적인입니다 확신 해요. 그렇지 않으면 거래의 포인트는 무엇을 할 것인가?

    나는 반대적인입니다 확신 해요. 그렇지 않으면 거래의 포인트는 무엇을 할 것인가?

    13. from https://stackoverflow.com/questions/161960/if-i-stop-a-long-running-query-does-it-rollback by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] MySQL의 제한 범위  (0) 2020.07.07
[SQL] 더블 도트 테이블 규정  (0) 2020.07.07
[SQL] ORA-00942 : 테이블 또는 뷰 (때 별도의 SQL 작업을하지만, 오라클 함수 내에서 작업을하지 않는) 존재하지 않습니다  (0) 2020.07.07
[SQL] PDO SQL 상태 "00000"하지만 여전히 오류? [복제]  (0) 2020.07.07
[SQL] #OR에 전달 관계는 구조적으로 호환되어야합니다. 호환 값 : : 참고 문헌]  (0) 2020.07.07
,

티스토리툴바