SQL Server 2000 디자인 및 사용자 정의 함수 (Building High Function Applications With SQL Server ™ 2000 )

1. SQL Server 2000 디자인 및 사용자 정의 함수 (Building High Function Applications With SQL Server™ 2000) 우 철웅㈜인브레인 기술이사 chwoong@inbrein.com www.inbrein.com

2. 세션의 목적 • 동적 Cross-Tab 생성(35) 다양한 격자형 데이터 조회를 저장 프로시저를 이용하여 동적으로 구현하기 • Cache 기능의 이해(50) Ad-hoc Query와 저장 프로시저의 캐싱 비교 Ad-hoc Query의 강제 캐싱하기 • 사용자 정의 함수 이해(35) 사용자 정의 함수로 효과적인 프로그래밍 구현하기 • SQL Server 데이터베이스 디자인 시 고려 사항 사항(40) 테이블을 분리해야 하는 기준과 키 코드 결정시 고려 사항

3. 동적 Cross-Tab 생성 서버 사이드 프로그래밍 기초 지식 • 동적 Cross-Tab을 사용해야 하는 이유 • 변수의 선언과 사용 • 로컬 변수 • 전역 변수 • 임시 테이블의 선언과 사용 • 로컬 임시 테이블 • 전역 임시 테이블 • 배치의 이해 • 구문 오류 • 오브젝트 오류 • 제약조건 오류

4. 동적 Cross-Tab 생성 배치에 대한 이해 USE TempDB GO CREATE TABLE TEST (id int PRIMARY KEY) GO INSERT TEST Values(1) INSERST TEST Values(2) -- 구문 에러 INSERT TEST Values(3) GO INSERT TEST Values(4) INSERT TESST Values(5) -- 개체 에러 INSERT TEST Values(6) GO INSERT TEST Values(7) INSERT TEST Values(7) -- 기본키 에러 INSERT TEST Values(8) GO

5. 동적 Cross-Tab 생성 배치에 대한 이해

6. 동적 Cross-Tab 생성 세션에서 하위 영역과 상위 영역 이해 세션의 상위 영역에서 만든 오브젝트에 대해 하위 영역에서 인식 가능 SELECT * INTO #Sales1 FROM Pubs.dbo.Sales EXEC ('SELECT COUNT(*) FROM #Sales1') GO 세션의 하위 영역에서 만든 오브젝트에 대해 상위 영역에서 인식 불가능 EXEC ('SELECT * INTO #Sales2 FROM Pubs.dbo.Sales') SELECT * FROM #Sales2 GO 위의 쿼리가 수행되려면 모든 것이 하위 영역에서 수행되어야 함 EXEC ('SELECT * INTO #Sales2 FROM Pubs.dbo.Sales SELECT * FROM #Sales2') GO

7. 동적 Cross-Tab 생성 Dynamic Cross-Tab 만들기 Create Proc up_Cross_tab AS …. Create Table #CrossTab (ColKey varchar(20)) While … exec ('ALTER TABLE #Matrix ADD…' ) … DECLARE Data_Cursor CURSOR FOR … WHILE @@FETCH_STATUS = 0 BEGIN ….. END SELECT * FROM #CrossTab

8. Cache 기능의 이해효과적인 어플리케이션 • 어플리케이션 수행속도와 확장성을 위해 살펴 보아야 할 내용 • 최적화된 실행 계획 • 실행 계획의 재사용 • 네트웍 라운드트립(roundtrip)의 최소화 • 데이터를 접근하는 방법에 따라 수행 속도의 결과는 굉장한 차이를 보일 수 있다.

9. 실행 계획의 캐싱아키텍쳐 살펴보기 • 쿼리한 SQL 구문은 파서에 의해 쿼리 트리로 만들어 진다. • 옵티마이저는 최적화된 실행 계획을 만들기 위해 쿼리 트리를 만든다. • 정규화 (Normalization) • 통계 정보를 이용한 최적화 (비용 기준) • 가장 빠른 경로를 위한, 최소 비용 처리 방법 발견 • 실행 계획 – 데이터 구조 생성 • 실행 계획의 저장 • 같은 실행 컨텍스트 내에서 재사용

10. 실행 계획의 캐싱(Caching)캐싱 메커니즘의 형태 • 임의 질의에 대한 캐싱 • 임의 질의(Ad-hoc) 캐싱 • 자동 파라메터화 • 고정된 질의에 대한 캐싱 • 저장 프로시저 • ExecuteSQL • Prepare/Execute/Unprepare

11. 임의 질의에 대한 캐싱어떻게 작업하는가? • 임의 질의 구문에 대한 실행 계획 캐싱 • 정확히 같은 구문을 수행한다면 캐시된 수행계획을 재 사용 예 : Q1: select * from employees where fname like ‘S%’ Q2: select * from employees where fname like ‘D%’ Q3: select * from employees where fname like ‘S%’ • Q3 는 Q1의 캐시된 실행 계획을 사용하게 된다. • Q2 컴파일을 필요로 한다.

12. 캐싱 정보를 담고있는 시스템 테이블master.dbo.syscacheobjects

13. 자동 파라메터화어떻게 작업하는가? • 단순 구문에 대해서만 처리된다. • 옵티마이저는 상수에 대해 파라메터화 할 것인지 검토한다. • 만약 파라메터화가 결정되면 템플릿 실행 계획은 캐시에 저장된다. • 연속적으로 동종의 쿼리가 수행되어 캐시의 저장된 템플릿 실행 계획을 사용한다면 “safe”로 만들것을 고려한다. • 권고 사항: 잘 정의된 작업에 대해 이런 기능을 믿고 작업하지는 않는 것이 좋겠다.

14. 자동 파라메터화지원하는 기본 구문 • INSERT <table> VALUES ({constant} | NULL | DEFAULT}, ..) • DELETE <table> WHERE <key-exp> • UPDATE <table> SET <col> = <constant> WHERE <key-exp> • SELECT <col-list> FROM <table> WHERE <key-exp> ORDER BY <col-list>

15. 자동 파라메터화템플릿 예제 • Safe: • INT @P, SELECT fname, lname FROM employees WHERE emp_id = @P • Unsafe: • SELECT fname, lname FROM employees WHERE (salary + bonus) > 30000 • 옵티마이저의 이러한 결정은 매우 보수적이다.

16. 저장 프로시저어떻게 작업 하는가? • 서버에 컴파일된 구문 저장 • 객체 관리 계속적 지원 • 어플리케이션에 의한 파라메터 명시 • 캐시에 있는 컴파일된 계획 실행 • WITH RECOMPILE 옵션으로 재 컴파일 • 저장 프로시저로 동적 생성 구문 수행 • Sp_executesql, EXEC

17. 저장 프로시저장점 • 데이터에 대한 비즈니스 로직의 은익화 • 수행 속도 • 재사용성 • 네트웍 트래픽의 감소 • RPC 수행을 통한 구문 파싱과 파라메터 프로세싱 작업을 안함 • 사용자 정의 함수(UDFs)도 비슷한 장점이 제공됨

18. 저장 프로시저잘못된 저장 프로시저 호출 방법 • 값으로 직접 호출하지 마라… //나쁜 수행 방법이다. //호출 할 때 마다 보내어진 값 ‘myval’에 대해 컴파일 한다. //RPC event 대신에 SQLBatch로 수행되어 진다. SQLExecDirect(hStmt,“execute mysp‘myval’”, SQL_NTS); //최적화된 상태는 아니다. //호출할 때마다 값에 대해 컴파일하여야 하기 때문에 SQLExecDirect(hStmt,“{ CALL mysp(‘myval’)}”, SQL_NTS);

19. 저장 프로시저SP를 효과적으로 호출하는 방법 • 파라메터를 이용한 호출 구문 // Bind parameterschar szParam[32];SQLBindParameter(hStmt,…,szParam);// Set parametersstrcpy(&szParam[0], “myval”);// Execute the stored procedureSQLExecDirect(hStmt, “{ CALL mysp(?) }”, SQL_NTS); //Even better on SQL Server 2000 SQLPrepare ( hStmt, “{ CALL mysp(?) }”, SQL_NTS); SQLExecute ( hStmt ); 다음 호출시 캐시에 있는 플랜을 사용

20. ExecuteSQL어떻게 작업되는가? • 어플리케이션에 의해 생성되며 • 지속적 객체 관리가 안됨 Example: Sp_ExecuteSQL 'insert T1 values (@p) ', '@p float',1 Sp_ExecuteSQL 'insert T1 values (@p) ', '@p float', 3 Sp_ExecuteSQL 'insert T1 values (@p) ', '@p float', 1 • ODBC SQLExecDirect 또는 OLEDB ICommandWithParameters 로 구현 가능

21. Prepare / Execute어떻게 작업하는가? • 어플리케션에 의해 생성 • 파라메터를 Question marks(?)와 문자열로 쿼리를 수행한다. • 반복적 수행에 대해 효과적인 지원 • UnPrepare가 발생할 때 초기화됨 • ODBC, OLEDB, or DB-Library의 ICommandPrepare에 의해 지원됨. • DELETE FROM AUTHORS WHERE au_fname = ? AND au_lname = ?

22. 부적절한 실행 계획의 공유 • 캐시의 수행 계획을 사용하고 싶지 않을 때 • 최적화된 수행 계획은 선택 파라메터에 의해 결정되기 때문 예제 : Select * from T where c>=5 and c<=10 (index lookup) Select * from T where c>=5 and c<=100 (table scan) • 단일 실행 계획으로 두 구문에 대해 최적화된 실행 계획을 가질 수 없다.

23. 적은 Round-TripsSQL Server 2000 개선 부분 • Prepare/Execute model: • 모든 파라메터의 타입을 명시할 수 있음 • Prepare와 Execute 간의 요청 사항 없음 • N 번의 라운드트립은 N 번의 수행을 이야기 한다. • 권고 사항 : 항상 Prepare/Execute 사용하자!

24. Prepare Execute Execute Prepare Execute Execute UnPrepare UnPrepare Prepare Execute Execute Response Prepare Execute Execute Response Handle Response Response UnPrepare Handle Response UnPrepare Response Prepare/Execute Round-TripsSQL 서버 7.0에서 User Code … … 7.0 Client … … SQL Server 7.0

25. Prepare Execute Execute Prepare Execute Execute UnPrepare UnPrepare Prepare+Execute UnPrep+Prep+Exec Execute Execute Response Response NewHandle+Response Handle +Response Prepare/Execute Round-TripsSQL 서버 2000에서 User Code … … 2000 Client … … SQL Server 2000

26. 권고 사항SQL 서버 2000에서 • 가능한 저장 프로시저를 사용하라. • Prepare/Execute를 사용하라. (SQLExecute 대신) • 가능한 파라메터화 하라 • 파라메터의 변동 폭이 큰 경우는 주의할 필요가 있다. • 가능하다면 어플리케이션에서 파라메터의 데이터 형을 명시하라. • 공통적인 룰을 관찰하고 적용하라. • 툴을 사용하여 compiles/recompiles에 대한 통계를 관측하여 적용하라.

27. 사용자 정의 함수 이해사용자 정의 함수의 사용 목적 • 자주 사용하는 반복 작업을 은닉화하고 처리 속도를 향상시키는 목적으로 사용 • 매개 변수를 가질 수 있으며 결과 값이나 결과셋을 반환할 수 있다. • 임시 테이블을 만들어 작업해야 하는 복잡한 쿼리에 대한 뷰의 역할을 수행할 수 있다. • 매개 변수를 가진 뷰의 역할을 수행할 수 있다.

28. 사용자 정의 함수 이해사용자 정의 함수에 사용 가능한 구문 • 변수 선언을 위한 Declare 구문 • 변수 대입을 위한 Set, Select 구문 • 임시 테이블 저장을 위한 Select 구문 • 흐름 제어 구문 • 커서 사용을 위한 구문 • 확장 프로시저 수행 구문 사용자 정의 함수에 사용 불가한 구문 • 결과 값을 반환하는 쿼리 • 테이블을 갱신하는 쿼리

29. 사용자 정의 함수 이해값반환 함수 CREATE FUNCTION MonthLastDay (@DATE smalldatetime) RETURNS smalldatetime AS BEGIN RETURN(DATEADD(mm,1,CONVERT(char(8),@DATE,112))-DATEPART(dd,@DATE)) END GO SELECT dbo.MonthLastDay('20000205'), dbo.MonthLastDay(GETDATE()) GO SELECT *,dbo.MonthFirstDay(Ord_Date) FROM Sales GO SELECT * FROM dbo.Sales WHERE Ord_Date BETWEEN dbo.MonthFirstDay(GETDATE()) and dbo.MonthLastDay(GETDATE()) GO

30. 사용자 정의 함수 이해테이블 반환 함수 CREATE FUNCTION fn_SalesQty (@Qty int) RETURNS TABLE AS RETURN (SELECT stor_id, title_id, qty FROM Sales WHERE qty > @Qty) GO SELECT * FROM dbo.fn_SalesQty(40) Order By qty Desc SELECT stor_id,SUM(Qty) FROM dbo.fn_SalesQty(40) GROUP BY stor_id GO SELECT s.stor_id, t.stor_name, s.title_id, s.qty FROM dbo.fn_SalesQty(40) s, Stores t WHERE s.stor_id = t.stor_id GO

31. 데이터베이스 디자인 시 고려 사항 데이터베이스 모델링에 따라 많은 수행속도의 차이가 있다. 그리고 실제 병목 현상이 발생하여 운영하고 있는 데이터베이스 스키마를 변경하기란 쉽지가 않다. 따라서 처음부터 수행 속도를 고려한 디자인을 하는 것이 좋겠다. 여기서는 일반적인 데이터베이스 디자인 고려 사항 보다는 세부적으로 주요한 몇 가지 팁을 다루고자 한다.

32. 데이터베이스 디자인 시 고려 사항 • 기본키의 사이즈를 줄이자.(클러스터 인덱스의 사이즈를 줄이자.) • 컬럼의 특성에 따라 테이블을 분리하자. • 조회 위주의 컬럼과 갱신 위주의 컬럼을 분리. • 자주 사용되는 컬럼과 종종 사용되는 컬럼을 분리. • 이벤트 처리를 위한 임시 컬럼을 분리. • 데이터량이 많은 경우 히스토리 테이블로 분리. • 가능한 자동 증가형을 사용하고 프로그램 차원의 증가형을 구현하지 말자. • 트리거 구현 시 단순하게 처리할 수 있는 구조로 만들자. • 키의 변화가 잦은 경우에는 대리키를 사용하자. • 여러 컬럼을 비교하여 차집합을 구해야 하는 경우가 있다면 하나의 컬럼으로 비교 가능한 대리키를 사용하자.

33. 데이터베이스 디자인 시 고려 사항 기본키의 사이즈를 줄이자.(클러스터 인덱스의 사이즈를 줄이자.)

34. 데이터베이스 디자인 시 고려 사항 조회 위주의 컬럼과 갱신 위주의 컬럼을 분리

35. 데이터베이스 디자인 시 고려 사항 자주 사용되는 컬럼과 종종 사용되는 컬럼을 분리. 이벤트 처리를 위한 임시 컬럼을 분리

36. 데이터베이스 디자인 시 고려 사항 데이터량이 많은 경우 히스토리 테이블로 분리

37. 데이터베이스 디자인 시 고려 사항 가능한 자동 증가형을 사용하고 프로그램 차원의 증가형을 구현하지말자.

38. 데이터베이스 디자인 시 고려 사항 가능한 자동 증가형을 사용하고 프로그램 차원의 증가형을 구현하지말자.

39. 데이터베이스 디자인 시 트리거 구현 시 단순하게 처리할 수 있는 구조를 만들자. CREATE TRIGGER Ins_Insert ON Ins FOR INSERT AS UPDATE CStocks SET Qty = s.Qty + i.Qty FROM (SELECT PID, SUM(Qty) AS Qty FROM Inserted GROUP BY PID) i , CStocks s WHERE i.PID = s.PID INSERT INTO CStocks SELECT PID, SUM(Qty) FROM Inserted WHERE PID NOT IN( SELECT PID FROM CStocks ) GROUP BY PID GO CREATE TRIGGER Products_Insert ON Products FOR INSERT AS INSERT INTO CStocks SELECT PID, 0 FROM Inserted GO

40. 데이터베이스 디자인 시 고려 사항키의 변화가 잦은 경우에는 대리키를 사용하자.

41. 데이터베이스 디자인 시 고려 사항여러 컬럼을 차집합을 위해 가능한 대리키를 사용하거나 긍정형 비교처리로 바꾸자. SELECT * INTO SalesTemp FROM Sales WHERE qty >= 20 GO SELECT stor_id,ord_num,title_id,qty FROM Sales WHERE stor_id+CONVERT(char(20),ord_num)+CONVERT(char(10),title_id) NOT IN (SELECT stor_id+CONVERT(char(20),ord_num)+CONVERT(char(10),title_id) FROM SalesTemp) GO SELECT stor_id,ord_num,title_id INTO #T FROM Sales DELETE #T FROM #T t, SalesTemp s WHERE t.stor_id = s.stor_id AND t.ord_num = s.ord_num AND t.title_id = s.title_id SELECT s.stor_id,s.ord_num,s.title_id FROM #T t, Sales s WHERE t.stor_id = s.stor_id AND t.ord_num = s.ord_num AND t.title_id = s.title_id GO