Inline 쿼리 VS 저장 프로시저

저장프로시저가 좋다고 지랄하는 놈들한테 넌지시 말한번 꺼낼수 있는 좋은 아티클..^^; 이분한테 참 감사하고 싶다... 그래도, 나도 싫은 DB 에 잘 몰라서다... 우기기는 싫거든.... 참조 : http://resisa.tistory.com/55 "저장 프로시저 사용해보셨나요?" 어떤 분이 저에게 이렇게 묻는다면 저는 "아니요. "라고 대답을 할 것입니다. 안타깝게도(?) 프로젝트에서 전 저장 프로시저를 사용해보지 못했습니다. 그래서 전 저장프로시저에 대해서 잘모릅니다. 이번에는 진짜로 제가 받은 질문입니다. 개발시에 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쿼리와 저장 프로시저를 적절하게(?) 사용하는 것 아닐까요?