SQL서버 응응프로그램의 속도향상

SQL서버 응응프로그램의 속도향상

아래 원문을 정리한 것임. http://www.ftponline.com/vsm/2004_07/magazine/columns/databasedesign/default_pf.aspx 아래에서 사용된 질의에서는 Northwind database를 사용하여 테스트하는 것으로 함. 1. 데이터의 존재여부를 파악시 count(*)대신 exists를 사용하라. 변경전 if (select count(*) from orders where shipvia = 3) > 0 print 'You cannot delete this shipper' 변경후 if exists(select 1 from orders where shipvia = 3) print 'You cannot delete this shipper' 변경전 : orders테이블의 모든 row에 대해 수행 변경후 : orders테이블에서 조건에 만족하는 row가 발견될때까지만 수행됨. 실제 orders테이블에 255개의 row가 존재하지만 백만개 정도의 row가 있다고 하자. 변경전 질의는 백만개row를 모두 처리하지만, 변경후 질의는 조건에 만족하는 row가 발견될때까지만 처리한다. 즉, 조건에 만족하는 첫번째 row가 10번째에 있다고 하면 10개의 row만 처리하고 끝난다. 1000000 : 10 즉, 단순계산에 의해서 봐도 천배의 차이가 있다. 2. 목록값이 적은 경우에만 where (not) in을 사용할 것. where in에 지정된 목록이 어느 정도 길어지면 수많은 nested loop가 수행되어 버릴 수 있다. 목록이 길어질때는 outer join을 사용하도록 한다. 변경전 select * from customers where customerID not in (select customerID from orders) 변경후 select c.* from customers c left outer join orders o on o.customerID = c.customerID where o.customerID is null left outer join이므로 customers의 모든 rows에 대해 orders와 비교. where조건에서 customerID가 null이라는 것은 customers에만 존재하고 orders에는 없는 row이므로 변경전 질의와 결론적으로 동일한 결과를 얻을 수 있다. 원문에서는 50초 걸리던 질의가 500밀리초로 확 줄었다며 놀라운 결과를 말하고 있다. 놀라워라!!! 명규: in을 사용하는 질의는 되도록 쓰지 않도록 한다. 오라클의 이전버전에서도 in에 대한 이와 유사한 버그(?)가 있었다. 결론은 DBMS의 옵티마이저를 너무 신뢰하지 말라는 것이다. 자신이 할 수 있는 최대한으로 SQL문을 최적화하도록 한다. 3. newid()를 이용한 결과셋의 랜덤화 상식적인 이야기이므로 대충 정리만 해보자. 아래 질의는 order테이블의 데이터를 무작위순서로 보여준다. select * from products order by newid() 1개만 보려면 다음과 같이 할 수 있을 것이다. select top 1 * from products order by newid() 이는 대량의 row를 갖는 테이블에 대해 무척 좋지 않다. 왜냐? products테이블의 모든 row를 처리후 1개의 row만 가져오는 것이므로, products테이블이 100만개의 row를 갖는다면 한참 기다려야 결과가 나올 것이다. 결론적으로 이런 경우에는 테이블의 일정 결과셋만을 가져와 처리하도록 한다. 명규: 사실 위의 경우는 일상생활에서 쉽게 발견할 수 있다. 특정 사이트의 게시판이 무지 느리다면 위의 경우와 같이 테이블의 전체 row에 대해 처리하고 있는 경우를 의심할 수 있겠다. 부분처리는 성능에 있어 그 영향이 크다 하겠다. Oralce, MS SQL Server와는 달리 MySQL은 이 부분에 있어 프로그래머들의 사랑을 받을 것이다. 4. 필드크기가 크면 디폴트 패킷크기를 증가시킬 것. 서버측 네트웍라이브러리와 클라이언트측 네트웍라이브러리의 통신시 사용되는 패킷크기를 변경. 디폴트로 ADO는 4096바이트, ADO.NET는 8192바이트. 최대가능크기는 32767바이트 만일 이미지데이터나 크기가 큰 XML데이터를 처리한다면, 크기를 늘리도록 한다. ADO, ADO.NET에서 연결문자열에 다음을 추가한다. "...;Packet Size=32767;.." 원문에서는 500K의 XML데이터에 대해 32767로 늘여서 테스트시 2배정도 속도가 빨라졌다고 한다. 명규: 클라이언트/서버간 되도록이면 통신회수를 줄이는 것은 중요한 APP성능튜닝 요소이다. 이에 대해 일반적으로 적용되는 프로그래밍 방식은 일괄처리방식(Batch statements)이다. 5. 작성한 프로그램에 이름을 지정해 둬라. 사실 이것은 속도향상과는 상관없고, 모니터링시 해당 프로세스를 쉽게 찾자는 것이다. SQL 프로필러에는 추적속성의 데이터열에 ApplicationName이 존재하고, EM에서는 관리 - 현재 동작 - 프로세스 정보에 보면 응용 프로그램 열이 존재하는데 이 놈이 우리가 지정한 이름으로 사용되는 것이다. 연결문자열에 다음을 포함 "...;Application Name=updatebatch;..." 6. SQL서버 별칭을 사용. 이것 또한 속도향상과는 관련이 없고, 응용프로그램에 융통성을 주자는 의도이다. SQL서버를 설치하면 '클라이언트 네트워크 유틸리티'라는 놈이 같이 설치된다. 별칭을 사용한다면, DB서버의 IP나 프로토콜이 변경되더라도 모든 연결문자열을 변경할 필요없이 별칭만 다시 설정하면 될 것이다. 별칭은 커넥션을 리다이렉션할 수 있는 기능도 가진다. 즉, 다른 SQL서버로 연결을 보낼 수 있다는 것이다. 한 놈의 db서버가 죽었을때 다른 놈으로 신속히 REDIRECTION시 유용할 것이다. 클라이언트 네트워크 유틸리티가 설치되어 있지 않다면, MDAC을 설치하면 cliconfg명령어로 사용할 수 있다. 7. 트리거를 drop하지 말고 disable하라. 속도 향상과 무관. 별 중요한 내용도 아니므로 SKIP alter table 테이블명 disable trigger 트리거명 alter table 테이블명 enable trigger 트리거명 8. 스크립트기반으로 한 객체명을 변경하지 마라. 속도 향상과 무관. 뷰, 스토어드 프로시저, 사용자정의 함수의 질의문은 syscomments의 text컬럼에 저장되고, sysobjects에 이에 대한 row가 생성됨. EM이나 sp_rename을 사용하여 객체명을 변경하면, sysobjects에만 반영되고, syscomments에는 변경전 이름으로 남는다. 권장방법: 객체제거후 새로운 이름으로 재생성 9. 자동생성된 스크립트를 전적으로 믿지는 말라. EM, Visual Studio, 기타 툴등에서 자동적으로 생성되는 질의문을 그대로 쓰지 말라는 의미. DB를 잘 모르는 사람들은 자동생성기능을 좋아라 하는데, 보고 있는 사람은 답답하다. 기능이 중요한 것이 아니라 성능이 중요한 것이다. 생성된 코드를 반드시 확인하도록 한다. alter table로 가능한 것을 drop table -> recreate하는 툴들도 있다고 한다. 정말? 10. SQL 프로필러를 사용하여 작성한 프로그램을 검증하라. 프로그램에서 잘못 사용된 SQL구문이 존재하는지 파악한다는 의미. ex) Stored Procedures-RPC:Completed, TSQL-SQL:BatchCompleted 이벤트를 100ms Duration으로 추적. This article comes from dbakorea.pe.kr (Leave this line as is)