SQL Server 2000 Stored Procedure http mssql ce

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

저장프로시져 - 무엇? v v 서버에 컴파일 되어 저장된 SQL 문에 이름이 붙은 집합 모든 SQL 문장 사용 가능 n cf. VIEW 나 trigger는 일부 문장 혹은 SELECT 문 만 가능 n 예) 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)

저장프로시져 - 무엇? 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]

매개변수 create PROC p 1 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 p 1 @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

매개변수 2 alter PROC p 1 @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 p 1 . 5, '%cook'

매개변수 3 alter PROC p 1 @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 p 1. 5, '%cook', @r select @r output

RETURN alter PROC p 1 @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 = p 1 0. 25 , '%cook' select @rtn

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

반드시 컴파일 해야 할 때 use tempdb go DROP TABLE test go CREATE TABLE test ( id int identity , c 1 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 p. Test @id int AS SELECT * FROM test WHERE id < @id go exec p. Test 2 exec p. Test 1000 go

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

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

SP_EXECUTESQL v 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 v ODBC and OLEDB expose functionality via SQLExec. Direct and ICommand. With. Parameters

Prepare/Execute v v Similar to SP_EXECUTESQL n 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

Prepare/Execute v v ODBC and OLEDB expose functionality via SQLPrepare/SQLExecute and ICommand. Prepare Example: SQLPrepare(hstmt 1, (unsigned char *)"select OID from Orders where Cust. ID = ? ", SQL_NTS); … SQLBind. Parameter(hstmt 1, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(sz. Customer. ID), 0, &sz. Customer. ID, sizeof(sz. Customer. ID), NULL); … rc = SQLExecute(hstmt 1); …

Response Execute Handle Prepare … SQL Server 7. 0 Response Execute Handle Prepare Response Un. Prepare … Response Un. Prepare Execute … Response Execute Prepare/Execute Round-Trips SQL 서버 7. 0에서 User Code 7. 0 Client

Handle +Response Prepare+Execute … SQL Server 2000 New. Handle+Response Un. Prep+Exec Response … Response Un. Prepare Execute … Execute Prepare/Execute Round-Trips SQL 서버 2000에서 User Code 2000 Client

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

Plan Sharing Between Users v Example of implicit resolution: n n n 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 “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

Inappropriate Plan Sharing v 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 v Application takes responsibility for determining what to parameterize When using sp_executesql, prepare/execute, and/or stored procs

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

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

권고 사항 v v v 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 대신)

Debugging