'Stored Procedure'에 해당되는 글 3건

  1. 2017.06.04 .NET IsolationLevel에 대한 고찰 5
  2. 2010.07.25 TransactionScope 그리고 Stored Procedure 4
  3. 2009.05.08 Inline 쿼리 VS 저장 프로시저 4

또?! 오랜만입니다 ㅎㅎ 지인이 애드센스 등록을 하셔서 저도 시도를 해보았더니 콘텐츠 부족으로 실패하여 이렇게 새로운 글을 쓰고 있습니다.

어떤 내용을 쓸까 고민을 하다가 블로그를 정리하면서 예전에 썼던 글 중에 그 당시 궁금했던 내용에 대한 답변을 해보고자 합니다.
(벌써 7년이 지났다니 시간이 참 빠릅니다;;)

TransactionScope 그리고 Stored Procedure (2010. 07. 25)
출처: http://resisa.tistory.com/110 [심플하게~]

(중략)...

1. 한 가지 의문점은 SP안에서는 트랜잭션의 Scope 범위(TransactionScopeOption) 지정하는 부분은 없어보이지만 IsolationLevel 대해서는 지정을   있습니다또한 TransactionScope에서도 IsolationLevel 대해서 지정할  있습니다그래서 IsolationLevel 따른 LTM SQLTransaction 관계가 어떻게 될지도 살짝 궁금해집니다. 하지만 실질적으로 이러한 것을 프로젝트에서 정확하게 알고 사용하는 개발자가 얼마나 될지도 의문이며 IsolationLevel 트랜잭션 사이의 잠금(lock) 대한 것이기 때문에 2. 성능보다는 안전성을 중요시하는 우리 나라의 대부분의 프로젝트에서는 ReadCommitted 사용하는 것이 개발자의 정신건강에 이로워 보입니다. ^^

예전에 썼던 글에 은근히 잘못된 내용이 있습니다;;  이유는 이해부족 입니다;;
저는 새로운 기술을 습득하는 과정에서 '정의'를 자신의 방식으로 표현하고 증명하는 과정을 반복합니다. '정의'와 '증명'을 잘못하면 위와 같이 잘못된 글이 나오기도 하지만 경험이 붙으면 증명하는 시간이 짧아지고 단순 실수도 줄어 이해하는 시간이 짧아지고 자신의 표현 방식으로 인하여 오랫동안 기억되는 장점이 있는 방식이라고 생각됩니다. 그럼 잘못된 부분부터 수정해보겠습니다.

=> 1번에서는 개념에 대해서 잘못 설명하고 있습니다. TransactionScopeOption은 TransactionScope클래스의 파라미터로 DB(SP)에는 없는 속성입니다.

=> 2번 대부분의 프로젝트에서 ReadCommitted로 설정할 것이라는 이유는 MSSQL의 기본 IsolationLevel이 ReadCommitted이기 때문입니다. 이 당시에는 IsolationLevel에 대한 이론적인 것만 알고 있었기 때문에 이런 결론을 냈었는데 업그레이드된?! 지금은 대부분의 프로젝트에서는 ReadUncommitted를 사용할 것이라고 생각합니다. 왜냐하면 ReadCommitted로 설정을 하면 DeadLock이 발생하는 구간이 많이 있을 수 있습니다. 그렇기 때문에 직접적으로 ReadUncommitted를 지정하지 않았더라도 이와 동일한 WITH(NOLOCK) 힌트를 많이 사용했을 것이라고 추측됩니다.

그럼 본론으로 들어가서 노란색 부분에 대한 이야기를 풀어보겠습니다.

먼저 트랜잭션을 아래와 같이 2가지 형태로 구분합니다.
1. 로컬 트랜잭션(SQL Transaction)
2. 분산 트랜잭션(DTC Transaction)

1. 로컬 트랜잭션을 세부화 하면 아래와 같이 나눌 수 있습니다.
1.1. TM(Transaction Manager)이 관여하는 SQL Transaction
1.2 TM이 관여하지 않는 SQL Transaction

개념적인 측면에서는 TM의 관여 여부로 나눌 수 있으며 개발적인 측면에서는 프로그램에서 트랜잭션을 관리하는지 DB에서 즉 SP에서 트랜잭션을 관리 여부로 나눌 수 있습니다.

다시 노란색 부분을 보면 SP에서 트랜잭션을 관리하는 부분들이 프로그램에서 트랜잭션을 관리하는 것과 합쳐졌을 경우에 트랜잭션 관리(Connection, IsolationLevel)에 대한 궁금증이였습니다.

프로그램에서 트랜잭션 관리를 하는 방법은 아래와 같습니다.
1. ADO.NET(System.Data.SqlClient.SqlConnection)를 사용하여 직접 BeginTransaction()를 호출하여 사용
2. System.Transactions.TransactionScope를 사용

트랜잭션을 관리하는 1번, 2번 방법의 공통점은 프로그램에서 트랜잭션을 관리하기 때문에 TM이 트랜잭션을  관리한다는 점입니다. 차이점이 바로 이 글의 핵심이 되는 답변으로 IsolationLevel과 관련되어 있습니다. TransactionScope에 IsolationLevel을 지정하여 사용할 때 SP의 IsolationLevel을 변경할 수 없다는 것입니다. IsolationLevel에 대한 위의 네임스페이스를 살펴보면 아래와 같습니다.

1. System.Data.SqlClient.SqlConnection - System.Data.IsolationLevel
2. System.Transactions.TransactionScope - System.Transactions.IsolationLevel

조심스럽게 예측해보면 TransactionScope의 트랜잭션은 DB 트랜잭션이라기보다는 프로그램에서 발생하는 트랜잭션(여기서는 DB 트랜잭션을 포함하는 상위의 트랜잭션의 개념으로 이해)이라고 할 수 있고 IsolationLevel은 트랜잭션 사이의 격리성이기 때문에 프로그램에서 발생하는 트랜잭션에 대한 격리성이지 DB 트랜잭션에 대한 격리성이 아니기 때문에 DB(SP) IsolationLevel을 변경할 수 없는 것입니다.

결론을 맺으면 프로그램에서 DB(SP) 트랜잭션 관리(IsolationLevel)를 하고 싶으면 SqlConnection을 사용하여 직접 트랜잭션을 발생(BeginTransaction())시켜야하며 TransactionScope을 통해서는 DB(SP) IsolationLevel까지는 관리할 수 없다는 것입니다.

Posted by resisa
,


드디어 Strored Procedure(이하 SP)를 사용하는 프로젝트에 참여하고 있습니다. 아쉽게(?) 실질적으로 SP를 호출하고 결과를 받아서 UI에 보여주는 작업은 아닙니다.

SP로 실질적인 프로젝트를 하지 않아서 전부터 궁금하던 사실이 있었습니다. 'SP를 사용하면 굳이 서버쪽에 Layer가 필요한 것일까?' 란 의문이였습니다. 프로젝트의 규모가 작고 하나의 SP에서 대부분의 로직을 처리할 수 있다면 굳이 필요하지 않아보입니다만 또 하나의 의문은 SP로 작성된 구문 사이의 트랜잭션의 범위에 대한 의문이였습니다. 일반적으로 Biz Layer에서 트랜잭션을 발생시켜 주고 Dac Layer에서 여러 SP를 호출해줄 경우 Biz에서 발생하는 트랜잭션과 SP내의 트랜재션과의 관계와 그 결과에 대한 의문이였습니다. 

조금 더 이해를 돕기 위해서 Biz Layer에서는 TransactionScope 클래스를 사용하였다고 가정하고 SP내에서는 ADO.NET 트랜잭션 메소드(BeginTransaction/Commit/Rollback)를 사용하였다는 가정입니다. TransactionScope는 System.Transaction 네임스페이스 하위의 클래스이며 System.Transaction 네임스페이스에서 관리하는 트랜잭션은 항상 LTM(Lightweight Transaction Manager)을 통해서 관리된다고 합니다. 따라서 해당 테스트는 LTM과 ADO.NET 트랜잭션 메소드 사이의 관계에 대해서 설명하자고 하는 글입니다.

LTM에 대해서는 아래의 사이트를 참고하세요.

http://www.simpleisbest.net/articles/996.aspx


그럼 이제 테스트를 해보도록 하겠습니다. (참고 : 테스트 DB는 SQL Server 2008(R2는 아님)입니다.)

DB 데이터 베이스는 ID(identity), Name 가지고 있는 테이블입니다.

이제 SP를 DB에 만들어 보겠습니다. SP의 내용은 아래와 같습니다.

CREATE PROCEDURE EFSP

 

@name nvarchar(Max),

@txOutput int OUTPUT

 

AS

 

BEGIN TRAN

 

insert [dbo].[Entity1Set]([Name])

values (@name)

select [Id]

from [dbo].[Entity1Set]

where @@ROWCOUNT > 0 and [Id] = scope_identity()

 

-- 위의 insert 구문 2 추가

 

SET @txOutput = @@TRANCOUNT


COMMIT TRAN

GO 

Entity Framework에서 해당 SP Function으로 Import 있습니다. System.Data.SqlClient 사용하셔도 됩니다. 여기서 @@TRANCOUNT로 발생하는 트랜잭션의 개수를 SP의 output 파라미터에 대입해주는 것을 확인하실 있습니다. ADO.NET 트랜잭션 메소드는 SQL Profiler SQLTransaction으로 표현이 되는데 SQLTransaction 해당 테스트와 상관없이 너무나 자주 발생하여 트랜잭션이 발생하는 개수를 파악하기 위한 방법으로 사용되었습니다.

그러면 이제 단순히 SP 실행해보도록 하겠습니다.


=> 위에 말했던 대로 SP내부에서 사용한 ADO.NET 트랜잭션 메소드는 SQLTransaction으로 표시가 되며 EventSubClass 열로 트랜잭션이 어느 부분부터 시작되며 어느 부분에서 종료가 되는지 아실 있습니다. ObjectName 통해서는 사용자가 발생한 트랜잭션인지 SQL Server내부에서 발생하는 트랜잭션인지를 구분할 있습니다.

 

그러면 이제 TransactionScope 클래스와 함께 SP 실행해보도록 하겠습니다.


=> TransactionScope 클래스를 사용하였기 때문에 TM(LTM) 관여하는 것을 보실 있으며 DBM_INIT이라는 내부 Object 의해서 무엇인지는 정확하게 없지만 SQLTransaction 발생한 것도 있습니다. 여기서 중요한 것은 바로 sp_reset_connection 함수입니다. 함수는 커넥션 풀링이 되었다는 의미입니다. 이상합니다. 하나의 SP만을 실행준 것인데 커넥션 풀링이 필요한 것일까요?

 

TransactionScope 클래스를 사용한 구문을 보도록 하겠습니다.

using (TransactionScope scope = new TransactionScope())

{

    // 하나의 SP실행

 

    scope.Complete();

} 

=> 앞에서 말했듯이 System.Transaction 네임스페이스의 트랜잭션은 TM 관리하였습니다. 단순히 SP만을 실행해줄 경우에는 SQLTransaction에서 commit 이후에 커넥션이 닫혔습니다. 그러나 TransactionScope안에서 SP 실행할 경우에 SQLTransaction commit되기 전에 커넥션이 먼저 닫히게 됩니다. 이것으로 보아 TransactionScope 사용하게 되면서 ADO.NET 트랜잭션 메소드가 TM 관리하는 트랜잭션으로 관리된다는 것을 있습니다. TM Commit 되기 전에 커넥션이 닫혔기 때문에 TM에서는 커넥션 풀링을 통해서 다시 커넥션을 맺고 내부의 SQLTransaction 발생한 이후에 SQLTransaction commit해주며 이후에 TM Commit 되고 마지막으로 커넥션을 닫아주는 것입니다.

가지 테스트를 통해서 TransactonScope 옵션(TransactionScopeOption) 따라서 ADO.NET 트랜잭션 메소드가 참여하게 된다는 사실을 확인할 있었습니다.

이렇게 하여 LTM SQLTransaction 관계에 대해서 대부분(?!) 파악이 되었습니다. 가지 의문점은 SP안에서는 트랜잭션의 Scope 범위(TransactionScopeOption) 지정하는 부분은 없어보이지만 IsolationLevel 대해서는 지정을 있습니다. 또한 TransactionScope에서도 IsolationLevel 대해서 지정할 있습니다. 그래서 IsolationLevel 따른 LTM SQLTransaction 관계가 어떻게 될지도 살짝 궁금해집니다. 하지만 실질적으로 이러한 것을 프로젝트에서 정확하게 알고 사용하는 개발자가 얼마나 될지도 의문이며 IsolationLevel 트랜잭션 사이의 잠금(lock) 대한 것이기 때문에 성능보다는 안전성을 중요시하는 우리 나라의 대부분의 프로젝트에서는 ReadCommitted 사용하는 것이 개발자의 정신건강에 이로워 보입니다. ^^

Posted by resisa
,

"저장 프로시저 사용해보셨나요?" 어떤 분이 저에게 이렇게 묻는다면 저는 "아니요. "라고 대답을 할 것입니다. 안타깝게도(?) 프로젝트에서 전 저장 프로시저를 사용해보지 못했습니다. 그래서 전 저장프로시저에 대해서 잘모릅니다. 이번에는 진짜로 제가 받은 질문입니다. 개발시에 Inline쿼리와 저장 프로시저중에 선택을 해야 한다면 어떤 것을 선택할 것이냐는 질문입니다. 개발인력이 많다면 전 Inline쿼리를 사용할 것이라고 대답했습니다. 개발인력이 많다는 것은 제 개인적인 생각이지만 저처럼 저장 프로시저를 사용해보지 못한 사람들도 많을 것이라는 생각때문이였고 저장 프로시저에 대해서 제 자신이 잘 모르기 때문입니다. 그 분이 다시 저에게 이런 말을 했습니다. 저장 프로시저는 컴파일이 된 이후에 다음에 사용할 경우에는 캐쉬된 상태로 사용되기 때문에 당연히 저장 프로시저를 사용해야 된다는 말이였습니다. 그래서 제가 정적SQL인지 동적SQL인지 명칭이 확실하지는 않지만 파라미터를 사용하는 쿼리는(MSSQL은 @문자로 오라클은 :문자로 받을 경우) 컴파일을 한번만 한다고 말했지만 그 분은 아니라고 하셨습니다. 그럼 한 번 확인해볼까요?

테스트 환경은 운영체제는 Vista, DB는 MSSQL2005이며 System.Data.SqlClient를 사용해서 확인해보도록 하겠습니다. 코드는 아래와 같습니다.
1. 파라미터 없는 SQL

string ConnStr = "Data Source=(local);Initial Catalog=TestDB;Persist Security Info=True;User ID=sa;Password=dsdvp";

string query = " SELECT * FROM Student WHERE ID = '2' "; 

SqlConnection conn = new SqlConnection(ConnStr);

SqlCommand cmd = new SqlCommand(query, conn); 

conn.Open();

cmd.ExecuteNonQuery(); 

conn.Close();


2. 파라미터 있는 SQL
string ConnStr = "Data Source=(local);Initial Catalog=TestDB;Persist Security Info=True;User ID=sa;Password=dsdvp";

string query = " SELECT * FROM Student WHERE ID = @ID "; 

SqlConnection conn = new SqlConnection(ConnStr);

SqlCommand cmd = new SqlCommand(query, conn); 

SqlParameter param = new SqlParameter("ID", "2");

cmd.Parameters.Add(param); 

conn.Open();

cmd.ExecuteNonQuery(); 

conn.Close();


3. 저장 프로시저
string ConnStr = "Data Source=(local);Initial Catalog=TestDB;Persist Security Info=True;User ID=sa;Password=dsdvp"; 

SqlConnection conn = new SqlConnection(ConnStr);

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "TestProc"; 

conn.Open();

cmd.ExecuteNonQuery(); 

conn.Close();

=> Inline쿼리나 저장 프로시저가 컴파일되는지의 여부를 알기 위해서 이전에 SQL 튜닝교육을 받았던 문서를 보다가 운영체제에서 제공해주는 XP에서는 관리도구-성능 Vista에서는 관리도구-안정성 및 성능모니터를 사용하였습니다. 성능모니터를 하기 위해서는 카운터를 추가해주어야 합니다. SQL Server:SQL Statstics에서 SQL Compilations/sec과 SQL Re-Complilations/sec를 추가해주고 배율을 좀 더 확인하기 쉽도록 1에서 10으로 변경해준 후에 위의 구문 3개를 실행해보았습니다.
먼저 1, 2, 3번을 차례대로 실행했을 경우의 화면입니다.
=> 파라미터가 없는 SQL이 가장 높게 나타나는 것을 볼 수 있고 파라미터가 있는 SQL과 저장 프로시저를 동일하게 나타납니다. 이후의 1, 2, 3번 어떤 것을 계속 실행하여도 다시 컴파일 되는 것을 볼 수 없습니다. 혹시나 1, 2, 3번을 실행한 순서에 관계가 있나 싶어 서비스에서 SQL를 내렸다 다시 올려 번갈아 가면서 실행해 보았지만 순서만 달라질 뿐 위의 결과와 똑같은 것을 확인할 수 있었습니다. 여기서 Inline쿼리도 저장 프로시저와 마찬가지로 한번만 컴파일 된다는 것을 알 수 있습니다. 저는 2, 3번만 컴파일이 한번 될 줄 알았는데 1번도 컴파일은 한번만 됩니다. 그런데 왜 1번만 컴파일 과정에서 높게 나타날까요? SQL Server Profiler로 알아보도록 하겠습니다.

새추적를 누르고 '모든 이벤트 표시'를 체크해준 후에 Stored Procedures와 TSQL의 모든 이벤트를 체크해줍니다. 그리고 1, 2, 3번을 실행시킨 화면입니다.

1. 파라미터 없는 SQL

2. 파라미터 있는 SQL
3. 저장 프로시저
=> 차이가 보이시나요? 1번에서는 SP:CacheInsert가 두 번 되는 것을 확인하실 수 있습니다. 그래서 2번이나 3번보다 컴파일시에 더 높게 나타나는 것입니다. 다음은 컴파일 된 이후에 1번을 실행시킨 것을 추적한 화면입니다.

=> SP:CacheHit가 일어나는 것으로 보아 그냥 날리는 Inline의 쿼리의 경우에도 캐쉬가 되는 것을 알 수있습니다. 더군다나 파라미터가 있는 SQL의 경우에는 마치 저장 프로시저처럼 실행이 되는 것도 알 수 있었습니다. 컴파일도 캐쉬도 Inline쿼리가 저장 프로시저보다 사용하기에 나쁜 이유는 되지 않는 것 같습니다.

다음은 제가 SQL 튜닝 교육을 받았던 강사님의 홈페이지에 대한 저장 프로시저에 대한 내용입니다.
http://www.sqlworld.pe.kr/
1. 저장 프로시져에 사용된 모든 구문이 미리 분석되어 최적화된 후 처음 수행시 메모리에 올려져 이후에 사용 될때는 메모리에 올려진 내용이 수행되므로 속도가 월등히 빠릅니다.
2. 복잡한 퀴리문을 네트워크를 통하여 서버로 보낼 필요가 없이, 단지 저장 프로시져를 호출하는 간단한 내용만 서버로 전달되므로 네트워크 트래픽이 감소됩니다.
3. 특정 테이블에 대한 권한이 없는 사용자 계정에 저장 프로시져를 수행 할 수 있는 권한을 주어 필요한 작업을 할 수있게 할 수 있으므로 보안성을 높일 수 있습니다.
4. 특정 기능을 수행하는 저장 프로시져를 만들어 두면 여러 응용프로그램에서 이를 활용 할 수 있습니다. 즉, 특정처리를 위한 모듈화작업이 가능합니다. 모듈화가 되어 있으므로 응용프로그램 전체의 수정없이 해당 저장 프로시져만을 수정하여 원하는 기능 구현을 할수 있습니다.
=> 1번의 경우 월등히 빠르다는 위에서 제가 테스트한 결과와 조금은 다른 내용으로 저에게 질문을 하신 분과 같은 의견입니다. 2번의 경우가 제가 표현을 못했던 가장 중요한 이유입니다. 복잡한 쿼리문을 네트워크로 전송하는 것보다는 저장 프로시저의 이름만 넘기는 것이 당연히 네트워크의 부하를 주지 않을 것입니다. 하지만 이 경우도 일반적인 응용 프로그램이 아닌 웹 프로그램이면서 웹서버와 DB가 같이 돌고 있는 경우라면 그렇게 큰 차이가 있을까요? 3번의 경우에는 응용 프로그램 자체에서 권한 관리를 한다면 굳이 상관없어 보입니다. 4번이 인라인과 저장 프로시저를 어떤 것을 선택해야 하는지의 가장 중요한 지표가 되는 것 같습니다. 쿼리 부분을 프로그램에서 관리할 것인지 DB안에 저장 프로시저로 관리할 것인지의 여부입니다.

일반적으로 비지니스 로직 레이어에서 트랜잭션을 발생시킵니다. 저장 프로시저를 사용하면 저장 프로시저안에서 트랜잭션 처리를 하게 되고 실질적으로 비지니스 로직 레이어가 의미가 없어져 버립니다. 하지만 이 트랜잭션이라는 것이 단순히 DB에 대한 트랜잭션만 있어야 하는 것일까요? 파일이나 레지스트리에 대한 모든 것에 트랜잭션으로 한꺼번에 처리하고 싶을 경우에도 저장 프로시저로 모든 것이 가능할까란 생각이 제일 먼저 듭니다. 또한 비지니스 로직에 계산 로직 등 복잡한 로직이 들어간 상태에서 트랜잭션 처리를 해야 한다면 저장 프로시저에서는 어떻게 처리할까요? 다시 한번 말씀드리지만 저는 저장 프로시저를 싫어하는 사람이 아니라 저장 프로시저에 대해서 잘 모르는 사람입니다. 아마 정답은 상황에 맞게 Inline쿼리와 저장 프로시저를 적절하게(?) 사용하는 것 아닐까요?

Posted by resisa
,