[SQL] 왜 관계 설정 기반의 쿼리는 커서보다는 더 낫다?

왜 관계 설정 기반의 쿼리는 커서보다는 더 낫다?

TSQL 또는 PLSQL 같은에서 데이터베이스 쿼리를 작성할 때, 우리는 종종 작업을 수행하기 위해 커서 행을 통해 반복, 또는 모두 한 번에 같은 일을 하나의 SQL 문을 공예의 선택이있다.

또한, 우리는 단순히 데이터가 우리의 응용 프로그램에 백업의 큰 세트를 당겨 다음 C #이나 자바 나 PHP 또는 무엇 이건,이 행 단위 처리를 선택할 수 있습니다.

왜 더 나은 세트 기반 쿼리를 사용할 수 있나요? 이 선택 뒤에 이론은 무엇인가? 커서 기반 솔루션과의 관계 동등의 좋은 예는 무엇인가?

해결법

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

   1.제가 알고하는 가장 큰 이유는 집합 기반 작업이 여러 스레드를 통해이를 실행하여 엔진을 최적화 할 수 있다는 것입니다. 예를 들어, 퀵 생각 - 당신이 자신의 스레드에서 일종의 각각 별도로 여러 개의 "덩어리"로 당신이 분류하고 목록을 분리 할 수 ​​있습니다. SQL 엔진은 한 세트 기반 쿼리에 엄청난 양의 데이터와 비슷한 일을 할 수있다.

   제가 알고하는 가장 큰 이유는 집합 기반 작업이 여러 스레드를 통해이를 실행하여 엔진을 최적화 할 수 있다는 것입니다. 예를 들어, 퀵 생각 - 당신이 자신의 스레드에서 일종의 각각 별도로 여러 개의 "덩어리"로 당신이 분류하고 목록을 분리 할 수 ​​있습니다. SQL 엔진은 한 세트 기반 쿼리에 엄청난 양의 데이터와 비슷한 일을 할 수있다.

   당신이 커서 기반 작업을 수행 할 때, 엔진은 순차적으로 실행할 수 있으며 작업이 단일 스레드 수있다.

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

   2.(훌륭한 솔루션 인) "는 DBMS가 일을하자"위뿐만 아니라, DBMS에 질의를 떠나 몇 가지 다른 좋은 이유가 있습니다 :

   (훌륭한 솔루션 인) "는 DBMS가 일을하자"위뿐만 아니라, DBMS에 질의를 떠나 몇 가지 다른 좋은 이유가 있습니다 :

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

   3.설정을 기반으로 쿼리 (일반적으로) 빨리 때문입니다 :

   설정을 기반으로 쿼리 (일반적으로) 빨리 때문입니다 :

   중간 계층에 외부로 데이터를 잡아 당기면는 (확장 할 수있는 가장 어려운 일이며, 일반적으로뿐만 아니라 다른 일을하는) DB 서버 오프 처리 오버 헤드를 제거하기 때문에 그것은하지만, 유용 할 수 있습니다 처리합니다. 또한, 일반적으로 중간 계층에서 같은 오버 헤드 (또는 이익)가 없습니다. 트랜잭션 로깅 같은 것들, 내장 등, 잠금 및 차단은 - 때로는 이러한 그들은 단지 자원의 낭비있어, 다른 시간 필요하고 유용합니다.

   전화 교환을 기반으로 지역 코드를 지정합니다 프로 시저 로직 대 세트를 기반으로 예 (T-SQL)와 간단한 커서 :

   --Cursor
   DECLARE @phoneNumber char(7)
   DECLARE c CURSOR LOCAL FAST_FORWARD FOR
      SELECT PhoneNumber FROM Customer WHERE AreaCode IS NULL
   OPEN c
   FETCH NEXT FROM c INTO @phoneNumber
   WHILE @@FETCH_STATUS = 0 BEGIN
      DECLARE @exchange char(3), @areaCode char(3)
      SELECT @exchange = LEFT(@phoneNumber, 3)
   
      SELECT @areaCode = AreaCode 
      FROM AreaCode_Exchange 
      WHERE Exchange = @exchange
   
      IF @areaCode IS NOT NULL BEGIN
          UPDATE Customer SET AreaCode = @areaCode
          WHERE CURRENT OF c
      END
      FETCH NEXT FROM c INTO @phoneNumber
   END
   CLOSE c
   DEALLOCATE c
   END
   
   --Set
   UPDATE Customer SET
       AreaCode = AreaCode_Exchange.AreaCode
   FROM Customer
   JOIN AreaCode_Exchange ON
       LEFT(Customer.PhoneNumber, 3) = AreaCode_Exchange.Exchange
   WHERE
       Customer.AreaCode IS NULL
   

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

   4.당신은 몇 가지 실제 예제를 원했다. 우리 회사는 30,000 레코드를 처리 40 분간했다 커서를했다 (나는 20 만 개 기록을 통해 업데이트하는 데 필요한 경우가 있었다). 그것은 커서없이 동일한 작업을 45 초 걸렸다. 다른 경우 나 커서를 제거 분 미만에 24 시간 이상에서의 처리 시간을 보냈다. 하나는 대신 선택의 값 절을 사용하여 삽입이었고, 다른 하나는 대신의 사용 변수 조인 업데이 트였다. 엄지 손가락의 좋은 규칙은 삽입, 업데이트 또는 삭제하는 경우, 당신은 작업을 수행 할 수있는 세트 기반의 방법을 찾아해야한다는 것입니다.

   당신은 몇 가지 실제 예제를 원했다. 우리 회사는 30,000 레코드를 처리 40 분간했다 커서를했다 (나는 20 만 개 기록을 통해 업데이트하는 데 필요한 경우가 있었다). 그것은 커서없이 동일한 작업을 45 초 걸렸다. 다른 경우 나 커서를 제거 분 미만에 24 시간 이상에서의 처리 시간을 보냈다. 하나는 대신 선택의 값 절을 사용하여 삽입이었고, 다른 하나는 대신의 사용 변수 조인 업데이 트였다. 엄지 손가락의 좋은 규칙은 삽입, 업데이트 또는 삭제하는 경우, 당신은 작업을 수행 할 수있는 세트 기반의 방법을 찾아해야한다는 것입니다.

   커서는 용도가 (또는 코드는 처음부터 자신을되지 않을 것),하지만 (이를 사용하도록 최적화되어 오라클 제외) 관계형 데이터베이스를 쿼리 할 때 그들은 매우 드문해야한다. 선행하는 레코드 (누계)의 값을 기준으로 계산을 수행 할 때 더 빨리 할 수있는 한 장소입니다. 그러나 테스트해야합니다도있다.

   커서를 사용하는 또 다른 제한된 경우 일부 일괄 처리를하는 것입니다. 당신이 세트 기반 방식으로 너무 한 번에 수행하려는 경우 다른 사용자가 테이블을 잠글 수 있습니다. 당신이 진정으로 큰 세트를졌다 너희 경우, 너무 오래 한 다음 커서를 사용하여 세트를 통해 실행 잠금을 유지하지 않습니다 작은 세트 기반 삽입, 업데이트 또는 삭제로를 나누는 것이 가장 좋습니다.

   커서의 제 3 사용은 입력 값들의 그룹을 통해 시스템 저장 발동을 실행하는 것이다. 이는 일반적으로 작은 세트로 제한하고 시스템 발동에 아무도해야 엉망이는 adminstrator가 수행하는 대한 수용 가능한 일이기 때문에. 나는 큰 배치 및 재사용 코드를 처리하기 위해 사용자가 만든 저장된 프로 시저와 같은 일을하지 않는 것이 좋습니다. 대부분의 경우 성능 우선해야 코드 재사용과 같은 더 나은 연기자가 될 것이다 세트 기반 버전을 작성하는 것이 좋습니다.

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

   5.나는 모두가 하나가 더있는에 따라 달라, 프로그래밍에 접근처럼 진짜 대답은, 생각합니다. 그게이 할 수 있도록 설계되었습니다 무엇 때문에 일반적으로, 설정을 기반으로 언어, 더 효율적으로 될 것입니다. 커서는 이점에 두 개의 장소가있다 :

   나는 모두가 하나가 더있는에 따라 달라, 프로그래밍에 접근처럼 진짜 대답은, 생각합니다. 그게이 할 수 있도록 설계되었습니다 무엇 때문에 일반적으로, 설정을 기반으로 언어, 더 효율적으로 될 것입니다. 커서는 이점에 두 개의 장소가있다 :

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

   6.나는 데이터베이스를 사용할 수 있도록 설계되었다되어 사용에 온다 생각합니다. 관계형 데이터베이스 서버 특별히 개발 및 세트 논리로 표현 질문에 최선을 응답하도록 최적화되어 있습니다.

   나는 데이터베이스를 사용할 수 있도록 설계되었다되어 사용에 온다 생각합니다. 관계형 데이터베이스 서버 특별히 개발 및 세트 논리로 표현 질문에 최선을 응답하도록 최적화되어 있습니다.

   기능적으로, 커서에 대한 처벌은 제품에서 제품에 상당히 달라질 수 있습니다. 일부 (대부분?)의 RDBMS는 적어도 부분적으로 ISAM 엔진의 상단에 내장되어 있습니다. 질문이 적절한 경우 및 베니어 얇은만큼, 그것은 사실 커서를 사용하여 효율적으로 될 수 있습니다. 그러나 당신이 그것을 시도하기 전에, DBMS의 브랜드 측면에서, 속속들이 잘 알고 있어야하는 일 중 하나입니다.

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

   7.말한 바와 같이, 데이터베이스 설정 작업에 최적화되어 있습니다. 말 그대로 엔지니어들은 앉아서 디버깅 / 오랜 기간 동안 해당 데이터베이스를 조정. 그들을 최적화 밖으로 당신의 가능성은 매우 희박. 당신은 당신이 배치 디스크 읽기와 같은 작업을하려면 데이터가있는 경우에 재생할 수있는 재미있는 트릭의 모든 종류의 / 캐싱, 멀티 스레딩, 함께 기록이 있습니다. 또한 일부 작업은 높은 오버 헤드 비용을 가지고 있지만 한 번에 데이터의 무리를 할 경우 데이터의 개당 비용이 낮습니다. 한 번에 하나 개의 행을 작업하는 경우, 이러한 방법 및 운영의 많은 단지 일어날 수 없습니다.

   말한 바와 같이, 데이터베이스 설정 작업에 최적화되어 있습니다. 말 그대로 엔지니어들은 앉아서 디버깅 / 오랜 기간 동안 해당 데이터베이스를 조정. 그들을 최적화 밖으로 당신의 가능성은 매우 희박. 당신은 당신이 배치 디스크 읽기와 같은 작업을하려면 데이터가있는 경우에 재생할 수있는 재미있는 트릭의 모든 종류의 / 캐싱, 멀티 스레딩, 함께 기록이 있습니다. 또한 일부 작업은 높은 오버 헤드 비용을 가지고 있지만 한 번에 데이터의 무리를 할 경우 데이터의 개당 비용이 낮습니다. 한 번에 하나 개의 행을 작업하는 경우, 이러한 방법 및 운영의 많은 단지 일어날 수 없습니다.

   예를 들어, 단지 데이터베이스 조인 방법을 확인합니다. 살펴보면에서 당신이 조인 몇 가지 방법을 볼 수 있습니다 계획을 설명합니다. 대부분의 가능성이 커서 당신은 하나 개의 테이블에 행 단위로 이동 한 다음 다른 테이블에서 필요한 값을 선택합니다. 기본적으로는 (대부분 기계 언어로 컴파일 슈퍼 최적화) 루프의 압박감없이 중첩 루프 같다. 자체 SQL Server는 가입 방법의 전체 무리가있다. 하나 개의 테이블이 작 으면 행이 정렬되면, 병합 알고리즘의 몇몇 형식을 사용하는 것, 그것이 해시 룩업 테이블로 한 테이블을 설정하고, 상기 룩업 테이블로 한 테이블 O (1) 검색을 수행함으로써 가입 할 수있다. 많은 DBMS가이 커서에서 하나 개의 테이블에서 값을 찾는 당신을 이길이 조인 전략을 여러 가지가 있습니다.

   그냥 해시 룩업 테이블을 생성하는 예를 살펴. 두 테이블의 길이 중 하나 n과 m은 작은 테이블 길이 m 중 하나에 가입하는 경우 테이블을 구축하는 것은 아마 작업을 해요. 즉 N 연산 각각의 룩업은 일정 시간이어야한다. 그래서 기본적 해시 효율 가입 + N (조회) m (설치) 주위. 당신이 스스로를하고 더 조회 / 인덱스 없다고 가정 한 후 n 행 각각에 대해 당신이 m 기록을 검색 할 경우 (평균은 m / 2 검색 동일합니다). 그래서 기본적으로 작업의 레벨 * m에 m + N (한 번에 기록의 무리에 합류)에서 이동 N (커서를 통해 조회 일) / 2. 또한 작업을 단순화이다. 커서의 각 행을 가져 오는, 커서 유형에 따라 첫 번째 테이블에서 다른 선택을하고 동일 할 수있다.

   잠금 장치는 또한 당신을 죽일. 당신이 행을 잠그는 테이블에 커서가있는 경우 (SQL 서버에이 정적 및 FORWARD_ONLY 커서 덜 심각 ...하지만 내가 볼 커서 코드의 대부분은이 옵션을 지정하지 않고 커서를 엽니 다). 당신이 세트에서 작업을 할 경우, 행은 여전히 ​​잠겨 있지만, 시간의 적은 양됩니다. 또한 옵티마이 저는 당신이 무엇을하고 있는지 볼 수 있고 전체 테이블 대신 행 또는 페이지의 무리를 고정하는 것이 더 효율적입니다 결정할 수있다. 당신이 라인으로 라인을 이동하지만 옵티마이 저는 아무 생각이 없습니다.

   다른 것은 내가 오라클의 경우는 슈퍼가 SQL 서버에 그대로 오라클의 커서를 대 집합 기반 작업에 대해 동일한 처벌 근처에 아무데도 때문에 커서 작업을 할 수 있도록 최적화되어 들었습니다된다. 내가 확실히 말할 수 있도록 나는 오라클 전문가가 아니에요. 하지만 더 이상의 오라클 사람이 커서이 방법으로 오라클에서 더 효율적 말해 줬어했다. 오라클에 대한 귀하의 맏아들을 희생 그렇다면 당신은, 커서에 대해 걱정할 필요가 해당 지역의 높은 지불 오라클 DBA를 참조하지 않을 수 있습니다 :)

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

   8.쿼리에서 일을 선호 뒤에 아이디어는 데이터베이스 엔진을 재구성하여 최적화 할 수 있다는 것입니다. 즉 당신은 DB가 실제로 무엇을하는지보고, 쿼리에 EXPLAIN 실행 싶어하는 이유이기도합니다. (예를 들면, 인덱스 테이블 크기 및 열의 값의 분포에 관한 때로는 기술을 이용하는).

   쿼리에서 일을 선호 뒤에 아이디어는 데이터베이스 엔진을 재구성하여 최적화 할 수 있다는 것입니다. 즉 당신은 DB가 실제로 무엇을하는지보고, 쿼리에 EXPLAIN 실행 싶어하는 이유이기도합니다. (예를 들면, 인덱스 테이블 크기 및 열의 값의 분포에 관한 때로는 기술을 이용하는).

   즉, 당신이 휘거나 규칙을 중단 할 수 있습니다 실제 콘크리트의 경우 좋은 성능을 얻을 수 말했다.

   아, 또 다른 이유는 제약이 될 수 있습니다 제약은 모든 업데이트 후 검사하지만, 하나씩을하는 경우에 충돌을 생성하는 경우 하나의 고유 열을 증가하는 것은 괜찮을 수 있습니다.

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

   9.기초 세트는 한 번의 작업으로 이루어집니다 커서의 행 집합 많은 작업으로 커서

   기초 세트는 한 번의 작업으로 이루어집니다 커서의 행 집합 많은 작업으로 커서

10. ==============================

    10.진짜 대답은 E.F. 커드의 책 중 하나를 얻을 관계 대수에 브러시 갈 것입니다. 그런 다음 큰 O 표기법에 좋은 책을 얻을. IT의 거의 2 년 후에이, 이럴 현대 MIS 또는 CS 정도의 큰 비극 중 하나입니다 : 거의 실제로 계산을 공부합니다. 당신은 알고 ... "컴퓨터"의 "계산"부분을? 구조적 쿼리 언어 (및 모든 상위 집합)은 단순히 관계 대수의 실용적인 응용 프로그램입니다. 예, RDBMS는 메모리 관리를 최적화하고 읽기 / 쓰기하지만 같은 절차 적 언어라고 할 수있다. 나는 그것을 읽으면서, 원래의 질문은 IDE 소프트웨어에 대해이 아니라 다른 대 계산 한 방법의 효율성에 대해.

    진짜 대답은 E.F. 커드의 책 중 하나를 얻을 관계 대수에 브러시 갈 것입니다. 그런 다음 큰 O 표기법에 좋은 책을 얻을. IT의 거의 2 년 후에이, 이럴 현대 MIS 또는 CS 정도의 큰 비극 중 하나입니다 : 거의 실제로 계산을 공부합니다. 당신은 알고 ... "컴퓨터"의 "계산"부분을? 구조적 쿼리 언어 (및 모든 상위 집합)은 단순히 관계 대수의 실용적인 응용 프로그램입니다. 예, RDBMS는 메모리 관리를 최적화하고 읽기 / 쓰기하지만 같은 절차 적 언어라고 할 수있다. 나는 그것을 읽으면서, 원래의 질문은 IDE 소프트웨어에 대해이 아니라 다른 대 계산 한 방법의 효율성에 대해.

    큰 O 표기법에도 빠른 친숙 데이터 세트를 처리 할 때, 반복이 선언적인 문장보다 비싼 이유에 빛을 창고 시작합니다.

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

    11.간단히 쉽게 데이터베이스가 당신을 위해 그것을 할 수 있도록 / 더 빠른, 대부분의 경우에 넣어.

    간단히 쉽게 데이터베이스가 당신을 위해 그것을 할 수 있도록 / 더 빠른, 대부분의 경우에 넣어.

    인생에서 데이터베이스의 목적은 / 검색 세트 형식으로 데이터를 조작하고 정말 빨리 수 / 저장하는 것입니다. 귀하의 VB.NET/ASP.NET 코드는 전용 데이터베이스 엔진으로 갑자기 근처 빠른 것 같다. 이를 활용하면 자원의 현명한 이용이다.

from https://stackoverflow.com/questions/24168/why-are-relational-set-based-queries-better-than-cursors by cc-by-sa and MIT license