1 / 30

SQL Server 2000 세미나 Stored Procedure

SQL Server 2000 세미나 Stored Procedure. 강사: 정원혁 http://mssql.ce.ro. 차 례. 무엇 ? 매개 변수 / Return 일반 SQL 문장과 프로시저의 차이 컴파일이 성능에 미치는 영향 컴파일 해야만 하는 경우 exec/ sp_executesql / sp_prepare / sp_execute 시스템 프로시저 내가 만들기 디버깅. 저장프로시져 - 무엇 ?. 서버에 컴파일 되어 저장된 SQL 문에 이름이 붙은 집합 모든 SQL 문장 사용 가능

sally
Download Presentation

SQL Server 2000 세미나 Stored Procedure

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server 2000 세미나Stored Procedure 강사: 정원혁 http://mssql.ce.ro

  2. 차 례 • 무엇? • 매개 변수 / Return • 일반 SQL 문장과 프로시저의 차이 • 컴파일이 성능에 미치는 영향 • 컴파일 해야만 하는 경우 • exec/ sp_executesql / sp_prepare / sp_execute • 시스템 프로시저 내가 만들기 • 디버깅

  3. 저장프로시져 - 무엇? • 서버에 컴파일 되어 저장된 SQL 문에 이름이 붙은 집합 • 모든 SQL 문장 사용 가능 • cf. VIEW 나 trigger는 일부 문장 혹은 SELECT 문만 가능 • 예) SELECT pub_id, title_id, price, pubdate FROM titles where price is NOT NULL order by pub_id COMPUTE avg(price) BY pub_id COMPUTE avg(price)

  4. 저장프로시져 - 무엇? CREATE PROC[EDURE] procedure_name [;number] [ {@parameter data_type} [VARYING] [= default] [OUTPUT] ] [,...n] [WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [FOR REPLICATION] AS sql_statement [...n] ------------------------------------ CREATE PROC[EDURE] DROP PROC[EDURE]

  5. 매개변수 create PROC p1 as set nocount on SELECT TOP 1 title_id,price FROM titles UPDATE titles set price = price *2 SELECT TOP 1 title_id,price FROM titles set nocount off Go ----------------------------------------------------------------- alter PROC p1 @a numeric(10, 3) as set nocount on SELECT TOP 1 title_id,price FROM titles UPDATE titles set price = price *@a SELECT TOP 1 title_id,price FROM titles set nocount off go

  6. 매개변수2 alter PROC p1 @a numeric(10, 5) = 2 , @b varchar(2000) as SET NOCOUNT ON SELECT TOP 5 title_id, type, price FROM titles where type like @b UPDATE titles set price = price * @a where type like @b SELECT TOP 5 title_id, type, price FROM titles where type like @b Go ----------------------------------------------------------------- Exec p1 .5, '%cook'

  7. 매개변수3 alter PROC p1 @a numeric(10, 5) = 2 , @b varchar(2000) , @c int output as SET NOCOUNT ON SELECT TOP 5 title_id, type, price FROM titles where type like @b UPDATE titles set price = price * @a where type like @b select @c = @@rowcount SELECT TOP 5 title_id, type, price FROM titles where type like @b ----------------------------------------------------- declare @r int exec p1 .5, '%cook', @r output select @r

  8. alter PROC p1 @a numeric(10, 5) = 2 , @b varchar(2000) as SET NOCOUNT ON SELECT TOP 1 * FROM titles where type like @b UPDATE titles set price = price * @a where type like @b if @@error <> 0 return(-1) else begin SELECT TOP 1 * FROM titles where type like @b SET NOCOUNT OFF return(0) end declare @rtn int EXEC @rtn = p1 0.25 , '%cook' select @rtn RETURN

  9. 1. Parsing 2. 이름확인 3. 보안점검 4. 최적화(Optimize) 5. Compile 재 실행 시 ? 같은 context 라면 그대로 사용 (극히 제한적인 경우) 그렇지 않다면 1에서 5의 과정 반복 어떻게 수행되나 (일반 쿼리)

  10. 만들 때 1. Parsing 2. 이름확인 보안 점검 (결과저장) 첫 실행 4. 최적화(Optimize) 5. Compile 재 실행 시 ? 그대로 사용 메모리에 없을때 혹은 재 컴파일이 필요할때만 4, 5의 과정 반복 어떻게 수행되나 (SP)

  11. 저장 프로시저 장점 • 편의성 :매개변수/ return / result sets • 보안 • 네트웍 트래픽의 감소 • 재사용 • 모듈화 • 성능

  12. compile 과 성능 dbcc freeproccache go use pubs go exec sp_help sales --첫번째 exec sp_help sales --두번째 exec sp_help sales --세번째 exec sp_help sales with recompile --강제 recompile use master go sp_helptext sp_help

  13. use tempdb go DROP TABLE test go CREATE TABLE test ( id int identity , c1 char(100) default 'a' ) go SET NOCOUNT ON declare @i int set @i = 0 while @i < 5000 begin insert test default values select @i = @i + 1 --select @i end oo CREATE NONCLUSTERED INDEX NC_test ON test (id) go sp_helpindex test CREATE PROC pTest @id int AS SELECT * FROM test WHERE id < @id go exec pTest 2 exec pTest 10 exec pTest 1000 go 반드시 컴파일 해야 할 때

  14. 반드시 컴파일 해야 할 때 • 넌클러스터 인덱스가 있을 때(heap) • 데이터 분포가 균일하지 않을 때 • 매개변수로 인해 처리하는 데이터 양이 틀려질 때

  15. CREATE PROC pTest @id int AS DECLARE @sql varchar(200) …… SET @sql = ‘SELECT * FROM test WHERE id < ’ + cast (@id as varchar(20))EXEC (@sql)... !주의 : 권한 문제! Solution: In Line EXEC

  16. SP_EXECUTESQL • Similar to stored procedures, but… • Requires identification of parameters by application • Does not require persistent object management • Syntax: sp_executesql [@stmt =] stmt[     {, [@params =] N'@parameter_name  data_type [,...n]' }     {, [@param1 =] 'value1' [,...n] }]  Repeated calls with the same @stmt use cached plan (with the new parameter values specified)

  17. SP_EXECUTESQL • Example: Q#1: sp_executesql N'insert mytable values(@p)', N'@p float', 1.0 Q#2: sp_executesql N'insert mytable values(@p)', N'@p float’, 2.0 Q#3: sp_executesql N'insert mytable values(@p)', N'@p float', 1.0  Q#2 and Q#3 use same cached plan as Q#1 • ODBC and OLEDB expose functionality via SQLExecDirect and ICommandWithParameters

  18. Prepare/Execute • Similar to SP_EXECUTESQL • Parameters are identified by the application • Does not require the full text of the batch to be sent on each execution. • Handle returned on prepare used to invoke batch at execution time • Also usable via ODBC and OLEDB when cursors are involved

  19. Prepare/Execute • ODBC and OLEDB expose functionality via SQLPrepare/SQLExecute and ICommandPrepare • Example: SQLPrepare(hstmt1, (unsigned char *)"select OID from Orders where CustID = ?", SQL_NTS); … SQLBindParameter(hstmt1, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(szCustomerID), 0, &szCustomerID, sizeof(szCustomerID), NULL); … rc = SQLExecute(hstmt1); …

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

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

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

  23. Plan Sharing Between Users • Maximizes effectiveness of caching mechanisms • Sharing rules: • Avoid changing environment (SET or database settings) in middle of application or connection • Insure that batches/procedures don't require implicit resolution  Except when absolutely necessary

  24. Plan Sharing Between Users • Example of implicit resolution: • Mary and Jane are two users in database DB • Both have objects named FOO • For Mary, “select * from DB..FOO” means “select * from DB.Mary.FOO” • For Jane, it means “select * from DB.Jane.FOO • If Jane is executing the query the query “select * from DB..FOO” an implicit resolution is required  A batch/procedure compiled by Mary with this query could not be used by anyone except Mary

  25. Inappropriate Plan Sharing • If optimal plan for a parameter value is not the same as the cached plan, optimal execution time will not be achieved  This is why the server is "conservative" about auto-parameterization • Application takes responsibility for determining what to parameterize  When using sp_executesql, prepare/execute, and/or stored procs

  26. 그럼 뭘 써야하지? • Stored Procedures • Multiple applications are executing batches • Parameters are known • Prepare/Execute • Multiple applications are executing batches • Parameters are known • Single users will use batches again • SP_EXECUTESQL or EXEC • Parameters are known • Single users may use batches again

  27. 그럼 뭘 써야하지? • Auto-parameterization • Don’t design new applications to use • Beneficial to existing applications which cannot be modified • Ad-hoc caching • Don’t design new applications to use • Provides benefits in limited scenarios

  28. 권고 사항 • Insure all users execute in the same environment • Avoid implicit resolutions • Take advantage of plan sharing • Do not parameterize constants whose range of values drastically affect optimization • 최대한 저장 프로시저를 사용한다 • Prepare/Execute를 사용한다. (SQLExecute 대신)

  29. system procedure 만들기 • Master 에 저장 + sp_ 로 시작 어디서든 실행 가능 • sp_lock / sp_who2 등을 개선시켜보자 blocking 관계에 있는 것만 보여주기 • 모든 table 의 행/ 컬럼 수 세기, 크기 구하기 • table의 모든 index 나열

  30. Debugging

More Related