본문 바로가기

IT

SQLServer : SQL문 올바르게 작성하기

반응형

원문 : http://debop.egloos.com/2652763



오늘 DB Part 개발자의 DB를 보면서... 문득 몇 가지 생각나서 적어보고자한다.

우선 DB 설계는 고급 개발자가 하는 경우가 많으므로 차후 정리하기로 하고, 먼저 SQL 문을 작성할 때의 고려할 점, 주의할 점을 위주로 적어보기로 하자.  
예를 들면서 하면 좋겠지만, 화면 덤프 뜨는 게 영 시원치 않으니, 우선 글을 쓰고, 나중에 추가하기로 하겠다.

1. SELECT 절에 명시적으로 필요한 컬럼만을 지정하라.

SELECT * FROM TABLE_NAME 과 같이 모든 컬럼 정보를 가져와야 할 경우도 있지만, 대부분은 그렇지 않을 것이다.
"개발 시에 필요에 따라 컬럼 정보도 변경 될 수 있고, 조회할 컬럼이 바뀔 수도 있는데요?"
맞습니다. 그건 개발 시에 그렇죠. 그러니 최종 Release 할 때, 점검해서 필요한 컬럼만으로 줄여야 합니다.

이게 귀찮거든 TEXT, NTEXT, IMAGE 수형의 컬럼을 VARCHAR(MAX), VARBINARY(MAX)로 변경해라. 이중의 IO 작업을 줄일 수 있다.

2. WHERE 조건이 최적인지 확인하라

  우선 WHERE 조건이나 TABLE의 JOIN 에 쓰이는 컬럼이 인덱스로 잡혔는지 확인하라. 잡혀있어야 JOIN 성능이나 필터링이 좋다.

여 러 필터링 중에 예상되는 결과 레코드 수가 적은 것부터 WHERE절에 써라 (이건 SQL Server 2000 부터는 SQL 실행계획이 알아서 해준다.) 다만, 여러번의 쿼리를 통해 임시테이블을 만들고, 조작해야 할 때는 SQL 작성자가 유의해서 작성해야 한다.

3. 컬럼 값의 변형을 WHERE절에 쓰는 경우를 최소화 해라.

  SELECT * FROM Customer WHERE Age + 1 > 20 보다는 WHERE Age > 19 이 빠르다.
  
물론 ISNULL 등도 마찮가지다.

4. Ctrl + L (SQLServer2008 기준) 를 이용해 실행계획을 확인해라.

  SQL 문의 실행계획을 보면, 처음에는 무슨 말인지 모를 수도 있다. 하지만 자꾸 보고, 쿼리 문을 변경하면서 실행계획이 변경되는 것을 보면서 익혀야 한다.

5. COUNT를 셀 것인가? exists 를 사용할 것인가?

  테이블에 조건에 맞는 레코드가 있음을 확인하는 방법은 count를 세는 방식과 exists 로 검사하는 방식이 있다.

  1. COUNT(*) 로 확인

      select @count = count(*) from Customers where name='aaaa'
      if @count > 0 
      begin
-- xxxxx
      end

  2. exists 사용
    
      if exists (select * from Customers where name='aaaa') 
      begin
           --- xxxx
      end

   어떻게 좋을까? exists 가 좋다. count는 모든 레코드 중 관련된 것을 필터링 한 후 count 함수를 수행하지만, exists는 필터링 시 하나라도 레코드가 있음을 인지했을 때, 반환한다. 
   테이블의 전체 레코드 수가 작을 때는 구별 안되지만, 많을 때는 exists 가 효과적이다.

6. count(컬럼명) 을 쓰지말고, count(*) 를 써라.

  count(컬 럼명) 은 해당 컬럼 값이 NULL인 경우, count 수에 포함되지 않는다. (여러 집합함수가 다 그렇다). DB에서 NULL값은 오묘하니, 잘 이해해야 한다. DB에서 NULL은 산술 식에서 0을 의미하는 것이 아니라 N/A를 의미한다. 그러므로 비교도 않한다.

7. 커서를 최대한 자제하라.

  RDBMS 에서는 Set 방식을 사용하라 라는 말이 있다. 이 말은 레코드 단위로 addressing을 하는 커서 사용을 자제하라는 것이다.
커서 사용은 쿼리 문 실행을 느리가 하는 주범이다.
어쩔 수 없이 사용해야 할 경우도 있지만, 이 경우도 잘 생각하면, 여러 단계의 SET 방식을 사용해서 해결 할 수 도 있다.
타 시스템의 조직 정보를 재구성하는데 기존 커서 방식이 30초가 걸리던 것을 SET 방식으로 병경해서 3초 이하로 실행시간을 줄인 경우도 있다. 사실 더 줄일 수도 있다.

  또 하나는 임시 테이블을 사용하는 것인데, 이 때 주의할 점은 SQLServer tempdb의 SIZE 설정을 운영 시스템의 환경에 맞게 적절히 설정해야 한다. 기본적으로 data=3Mb(10%), log=1Mb(10%) 일 것이다. 이를 data=10~50Mb(10Mb), log=10Mb(10Mb) 로 변경해주면, 임시테이블 사용시 성능 향상을 가져 올 것이다. (참고 : Temp DB 최적화)

  더 좋은 방법은 테이블 변수를 사용하는 것입니다. 개인적으로 컬럼이 너무 많은 테이블 변수를 정의하는 게 싫어서...
꽁수로 필요한 최소한의 것만 테이블 변수에 정의하여 작업하고, 필요 시 실제 TABLE과 join을 수행하여, 결과 SET을 반환하도록 합니다.

8. VIEW 사용을 자제하라.

개발 편의상, 보안상 여러가지 이유로 VIEW는 참 좋은 개념이고, 사용할만한 가치가 있다. 
다만 성능 상의 문제 때문에 자제하라는 것이다.
2 번 WHERE 조건절 관련해서 언급했듯이, 결과 레코드가 가장 작게 가져오는 (HIT RATIO 가 높은) 문장이 먼저 실행되어야 하는데, VIEW를 먼저 가져오는 작업이 그 뒤에 실행되는 WHERE 절보다 더 많은 결과를 가져오게 하는 것이 대부분이므로, 성능에는 불리할 수 밖에 없다.
또한 여러 개의 테이블이 조인되는 경우 인덱스 사용에 제약이 많다. 물론 INDEXED VIEW가 있어서 어느정도 성능에는 효과가 있지만, 관련 테이블 변경 시 문제가 발생한다.

9. Table Hints 를 사용해라.

SELECT * FROM Customers with (nolock) 에서 nolock 같은 것이 table hint라 한다.
FASTFIRSTROW, IGNORE_CONSTRAINT, IGNORE_TRIGGERS, NOWAIT, PAGELOCK, TABLELOCK, ROWLOCK, UPDLOCK, XLOCK 등 여러가지가 있다. 
일반적인 쿼리문에 적절한 Table Hint를 사용해서, 성능을 높힐 수 있다.

10. 왠만한 Data-Centric Product에서는 Procedure를 사용해라.

Procedure의 장점은 여러가지가 있지만, 몇가지만 나열하면
  
-. 데이터 무결성 시행
-. 복잡한 비지니스 규칙 표현
-. 캡슐화 가능
-. 네트웍 트래픽 감소 (긴 SQL 구문 축소)
-. 실행 계획 및 컴파일 캐시를 통한 실행 속도 향상
-. 보안 강화 (SQL Injection 방지) - 다른 방안도 있지만.

data-centric한 제품은 가능하면 Procedure를 제작해서 사용하는 것이 좋다. (개인적으로는 NHibernate를 사용하므로, procedure 쓸 일은 거의 없다.)
한 가지 주의할 점은 procedure 이름의 접두사에 'sp_' 를 사용하지 말라는 것이다. 이 접두사는 SQLServer가 시스템 Procedure인 줄 알고, 거기부터 뒤진다. 대신 'usp_' 나 제품약어를 사용하는 것이 좋다.

11. 시스템 Procedure, 시스템 View를 잘 활용해라.

제품 자체는 아니지만 SQLServer 시스템의 정보를 활용하거나, 참조해야 할 때 sp_xxxx나 sysXXXX 등 master db에 있는 procedure나 view를 활용하는 것이 좋다.

특히 sql injection도 피하고, 컴파일 및 캐시도 지원하는 sp_executesql 를 잘 활용해라. 한마디로 동적 procedure라 할 수 있다.
다만 parameter가 있는 경우, 실제 값의 크기가 아닌 해당 parameter의 크기로 직접 설정하는 것이 더 성능이 좋다.

예 :  
      1. sp_executesql 'update name=''aaa'' where customers where name=@p0', '@p0 varchar(3)' @p0='bbb'
      2. sp_executesql 'update name=''aaa'' where customers where name=@p0', '@p0 varchar(5)' @p0='bbbbb'
위 두개의 쿼리 문은 똑 같지만 parameter 의 datatype이 달라, 전혀 다른 procedure로 컴파일된다. 가능하면 customer의 name 수형과 같은 수형을 parameter 수형으로 지정해 줘야 한다.

12. 기본 스키마 정보를 SQL 문장에 적어줘라.

select * from Customers 말고 select * from Northwind.dbo.Customers 를 쓰라는 말인데, 이는 sys, INFORMATION_SCHEMA 등 시스템용 schema와 구분짓기 위해서이고, 시스템이 객체 검색 시 효율적이기 때문이다.

13. SET NOCOUNT ON / OFF 를 사용해라.

Procedure 의 경우 결과 셋으로 보내는 게 아니라, 내부적으로 중간 단계에서 사용하기 위해 조회를 수행하는 경우가 많다. 이 때 SET NOCOUNT ON 을 지정하고 수행해야, Client에게 조회 결과가 전달되지 않는다. 최종 결과만을 client에 보내기 위해서 SET NOCOUNT OFF 를 설정한 후, 최종 결과 조회 문을 실행해야한다.
이렇게 하는 것이 쓸데 없는 정보가 네트웍을 통해 반환되는 것을 방지한다. 많이 들어 봤겠지만 Network round-trip 이 성능에 가장 큰 적임을 잊지 마라.

14. 되도록 한꺼번에 SQL 문장을 실행하라.

Connection Pool 이 있어, Connection resource에 의한 성능 저하는 그리 없겠지만, 계속된 network round-trip은 성능에 많은 영향을 미친다. 되도록 한꺼번에 요청하고, 반환받아 round-trip을 최소화 해야 한다.


반응형