패턴... 참 좋은 단어 입니다. 말 그대로 주어진 틀에 맞춘다는 것이지요. Microsoft 가 자사의 기술을 이용하는데 적용하라고 제시하는 Pattern & Practice 씨리즈의 글들 역시 일정 패턴에 맞추어 어플리케이션을 설계하고 구현하고 배포하라는 것을 가이드 하고 있습니다. 객체 지향 설계/구현에서 많이 인용되며 사용되는 디자인 패턴 역시 그러합니다.
Microsoft 기술 기반의 어플리케이션에서 데이터베이스를 액세스할 때 작성하는 코드 역시 권장되는 패턴이 있습니다. 이는 VBScript, VB 6.0 등의 Unmanaged Code에서 사용되는 ADO에도 적용되며, 닷넷 기반의 코드를 작성할 때도 역시 적용되는 코딩 패턴입니다. 하지만 많은 개발자들이 이것을 간과하는 경우가 많습니다. 물론, 코딩 패턴은 말 그대로 패턴일 뿐이지 절대적인 진리는 아닙니다. 단순히 코드 패턴을 암기해서 기계적으로 코드를 작성하는 것보다 왜 그런 패턴을 권장하는지 아는 것이 중요하다고 할 수 있지요. 그래야만 해당 코딩 패턴을 적용해야 할지, 아니면 그것을 응용한 다른 코드를 작성해야 할지, 패턴을 무시해야 할지를 결정할 수 있으니까요.
필자가 걱정하는 것은 이 글로 인해, 무조건 이렇게 코딩을 해야 한다든가 이것이 진리라든가 등등 독자들이 잘못된 인식을 갖을까 두렵습니다만, 잘못된 아니 잘못되었다기보다는 효율적이지 않은, 성능에 도움되지 않는 데이터 액세스 코드를 작성하는 일들이 줄어 들었으면 하는 바램에서 ADO.NET 코딩 패턴 씨리즈의 포스트를 작성해 보려고 합니다.
이 글에서 비효율적인 데이터 액세스 코드들을 지적할 것이고 왜 비효율적인가를 설명할 겁니다. 그리고 권장되는 데이터 액세스 코드 패턴을 제시할 겁니다. 이 권장 사항은 필자의 권장 사항임을 강조합니다. 물론 이들 중에는 마이크로소프트의 권장 사항이기도 한 것도 있습니다만 구체적으로 어느 사이트 혹은 문서에서 마이크로소프트가 권장하고 있는가를 모두 구별해 드릴 수는 없습니다(귀차니즘이 물밀듯이 밀려와서... 언젠가 저는 이 게으름으로 망할 듯...). 필자가 권고하는 사항을 받아들이는 것은 순전히 독자의 몫입니다. 다만 제가 얼토당토 않은 제안을 하지 않는다는 점과 여러 자료, 예제들을 기반으로 필자의 경험과 테스트를 바탕으로 심사숙고(?)해서 드리는 제안이라는 점만을 알아 주시면 감사하겠습니다(책임 회피의 냄새가 풀풀 나죠? -_-;). 생산적이고 건설적인 토론이나 필자의 잘못된 지식을 지적해 주시는 것은 항상 환영하지만, 소모적이고 우격다짐식의 비판이나 논쟁은 사양합니다.
참고로, 이 씨리즈의 글들은 모두 ADO.NET 기반, 즉 닷넷 기반에서의 코딩 패턴에 대해 다룹니다. 닷넷 이전 unmanaged 코드에 대한 코딩 패턴은 필자가 2001년 9월 월간 마이크로소프트에 기고했었던 "제대로 알아보는 ADO 코딩 패턴" 이란 글을 참조해 주십시오 (사이트에서 원문을 보실 수 없으며 PDF를 다운로드 해야 합니다. 500원이라는 돈이 들죠... -_-;; 오해는 하지 마시길...).
Use Parameterized Query
데이터베이스 기반의 어플리케이션이 성능이 기대보다 못 미치는 경우가 많이 발생하곤 한다. 대부분 데이터베이스 설계가 잘 못되었거나 인덱스의 부적절한 사용, 데이터베이스 튜닝 부족 등을 이유로 꼽을 수 있다. 이러한 요인 외에도 잘못된, 아니 잘못되었다기보다는 효율적이지 못한 데이터 액세스 코드로 인해 데이터베이스의 부하가 증가하는 경우가 많다. 데이터베이스에 부하가 증가한다는 것은 곧 성능적인 손해가 올 수 있음을 의미한다.
Non-parameterized Query
대표적으로 비효율적인 데이터 액세스 코드는 매개변수를 사용하지 않는 쿼리 문장을 사용하는 것이다. 예를 들어 설명하는 것이 가장 빠를 것 같다. 다음 코드가 필자가 말하는 '비효율적인 코드'가 되겠다.
private const string _ConnectionString = "SERVER=(local);UID=yourid;PWD=yourpwd;Database=Northwind";
private static DataSet NonParameterizedQuery1(string customerID, DateTime beginDate, DateTime endDate)
{
// 문자열 연산으로 SQL 문장을 만든다. 좋지 못하다.
string query = "SELECT * FROM Orders WHERE customerID = '" + customerID +
"' AND OrderDate >= '" + beginDate.ToString("yyyy-MM-dd") +
"' AND OrderDate <= '" + endDate.ToString("yyyy-MM-dd") + "'";
SqlDataAdapter adapter = new SqlDataAdapter(query, _ConnectionString);
DataSet ds = new DataSet();
adapter.Fill(ds);
return ds;
}
리스트1. 매개변수를 사용하지 않는 데이터 액세스 예제1
리스트1은 Northwind 데이터베이스의 Orders 테이블에서 주어진 주문자(customerID)에 대해 주어진 기간(beginDate와 endDate 사이)에서 주문한 내역을 조회하는 코드이다. 얼핏 보면 아무런 문제가 없어 보이는 코드이다. 게다가 매우 잘 작동한다. 하지만 이 코드는 비효율적으로 데이터 액세스를 수행하고 있다. 필자가 구라를 치지 않는다는 증거를 이제 살펴보자.
SQL Compilation & Query Plan
SQL Server(다른 데이터베이스도 마찬가지라고 생각한다)는 SQL 쿼리 문장이 주어지면 이것을 파싱하고 컴파일하여 실행계획(query plan)을 생성한다. 그리고 이 실행 계획을 다음에 재사용하기 위해 캐시하여 다음에 동일한 쿼리를 수행할 때 SQL 문장을 다시 컴파일하고 실행 계획을 세우는 일을 줄인다. 뭐... 당연히 그래야 할 것이다. 그런데... SQL Server가 캐시된 실행 계획을 찾는데 있어서 매우 똑똑하지 못하다는 것이다 (사실 상당히 똑똑한 편인데도 불구하고 사람의 관점에서 보면 똑똑하지 않은 것 처럼 보인다). 리스트1의 NonParameterizedQuery1 메쏘드를 다음과 같이 2회 호출한다고 가정해 보자.
string customerID = "VINET";
DateTime beginDate = new DateTime(1996, 1, 1);
DateTime endDate = new DateTime(1996, 1, 31);
DataSet ds = NonParameterizedQuery1(customerID, beginDate, endDate);
customerID = "VINET";
beginDate = new DateTime(1996, 2, 1);
endDate = new DateTime(1996, 2, 29);
ds = NonParameterizedQuery1(customerID, beginDate, endDate);
위 두 호출은 결과적으로 SQL Server에 대해 다음 두 문장을 수행하는 결과를 낳는다. 뭐 코드가 뻔하니깐 당연하겠지...
SELECT * FROM Orders WHERE customerID = 'VINET' AND OrderDate >= '1996-01-01'
AND OrderDate <= '1996-01-31'
SELECT * FROM Orders WHERE customerID = 'VINET' AND OrderDate >= '1996-02-01'
AND OrderDate <= '1996-02-29'
우리가 보기엔 두 쿼리는 전혀 다르지 않아 보인다. WHERE 절에 조회 조건으로 주어진 조회 기간 '매개변수'의 값만이 다른 것이 아닌가? 우리에겐 같아 보이는 두 쿼리가 SQL Server에겐 다르게 보인다는 것이다. 그래서 SQL Server는 이 두 쿼리를 다른 것으로 간주하고 SQL 컴파일과 실행 계획을 매번 세우게 된다. 정말로 SQL Server가 컴파일을 매번 수행하는지 알아보고 싶다면 리스트1의 NonParameterizedQuery1 메쏘드를 매개변수를 매번 다르게 하여 호출하면서 성능 모니터를 통해 SQL Server의 SQL Compilation/sec 성능 카운터 항목을 살펴보기 바란다. 매개변수를 쓰지 않는 리스트1의 코드는 높은 SQL 컴파일 회수를 나타낼 것이다.
이제 왜 매개변수를 쓰지 않는 쿼리를 사용하면 효율이 떨어지는지에 대한 첫 번째 해답이 될 것이다.
String Concatenation
리스트1의 코드에서 원하는 SQL 쿼리 문장을 만들기 위해서 문자열들을 더하는(concatenate) 연산을 하고 있음을 알 수 있다. 문자열을 더하는 것이 좋지 않다는 말을 어디서 들어보지 않았는가? 그렇다. 문자열 연산은 좋지 못하다. 피할 수 있다면 피하는 것이 좋다. 그래서 문자열 연산 대신 StringBuilder를 사용하여 다음과 같은 코드를 생각해 볼 수 있겠다.
private static DataSet NonParameterizedQuery2(string customerID, DateTime beginDate, DateTime endDate)
{
// StringBuilder로 SQL 문장을 만든다. 가장 좋지 못하다.
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM Orders WHERE customerID = '");
sb.Append(customerID);
sb.Append("' AND OrderDate >= '");
sb.Append(beginDate.ToString("yyyy-MM-dd"));
sb.Append("' AND OrderDate <= '");
sb.Append(endDate.ToString("yyyy-MM-dd"));
sb.Append("'");
SqlDataAdapter adapter = new SqlDataAdapter(sb.ToString(), _ConnectionString);
DataSet ds = new DataSet();
adapter.Fill(ds);
return ds;
}
리스트2. 매개변수를 사용하지 않으면서 StringBuilder까지 쓰는 예제 코드. 대략 가장 좋지 않다.
뭐 좋은 의도로 StringBuilder를 썼겠지만, 결과적으로는 더욱 좋지 못하다. 왜 StringBuilder를 쓰면 더 안 좋아지는 가에 대해서는 필자의 "StringBuilder에 대한 진실 혹은 거짓말" 씨리즈 글들을 살펴보는 것이 좋겠다.
리스트1과 같이 문자열 연산을 쓰거나 리스트2와 같이 StringBuilder를 사용하는 것은 데이터베이스 서버에 영향을 준다기 보다는 웹 서버나 COM+가 수행되는 어플리케이션 서버의 가비지 컬렉션 효율을 떨어뜨리는 원인이 되겠다. 암튼 비효율은 비효율이니까....
Recommendation
Use Parameterized Query
잔뜩 코드들을 비판했으면 대안을 제시해야 할 것 아닌가? 대안은 매개변수를 사용하는 쿼리를 사용하는 것이다. 즉, 쿼리에 변하는 변수 부분을 매개변수로 처리하면 된다. 말로 설명하기 영 까칠하므로 예제 코드를 보자.
private static DataSet ParameterizedQuery(string customerID, DateTime beginDate, DateTime endDate)
{
// 문자열 상수만을 이용하고 있다. 가장 좋다.
string query = "SELECT * FROM Orders WHERE customerID = @customerID " +
"AND OrderDate >= @beginDate " +
"AND OrderDate <= @endDate";
SqlConnection conn = new SqlConnection(_ConnectionString);
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
cmd.Parameters.Add("@customerID", SqlDbType.NChar, 5).Value = customerID;
cmd.Parameters.Add("@beginDate", SqlDbType.DateTime).Value = beginDate;
cmd.Parameters.Add("@endDate", SqlDbType.DateTime).Value = endDate;
adapter.Fill(ds);
return ds;
}
리스트3. 매개변수를 사용하는 데이터 액세스 코드. 대략 훌륭하다... (자뻑?)
리스트3 코드는 쿼리 내에 @customerID, @beginDate, @endDate 등의 매개변수를 사용하고 Command 객체의 Parameters 컬렉션을 사용하여 매개변수 값을 설정하고 있다. 이 코드가 좋은 이유는 SQL Server가 매개변수가 변경되더라도 이것을 하나의 SQL 문장으로 간주하고 캐시된 실행 계획을 계속 재사용할 수 있도록 해준다는 것이다. 정말로 SQL Server가 SQL 컴파일을 1회만 수행하고 캐시에 저장된 실행 계획을 재사용하는가를 알아보기 위해 리스트1의 코드를 12회 반복 호출하고 리스트3의 코드를 12회 반복 호출하는 코드를 수행 시키면서 성능 모니터에서 SQL 컴파일 회수를 살펴보았다. 결과는 화면1과 같다. 첫 번째 높이 솟아있는 것이 매개변수를 사용하지 않는 리스트1이 수행되었을 때의 초당 SQL 컴파일 회수이며 두 번째 낮게 깔린 봉우리가 매개변수를 사용하는 리스트3이 수행되었을 때의 초당 SQL 컴파일 회수이다. 차이를 느낄 수 있을 것이다.
화면1. 매개변수를 사용한 경우와 그렇지 않은 경우 SQL 컴파일 회수 비교
리스트3의 코드가 좋은 코드인 이유는 문자열 연산을 사용하지 않는다는 점이다. 잉? 필자가 말도 안되는 사기를 친다고? 코드를 보면 분명 + 연산자로 문자열을 더하고 있지 않은가? 리스트3의 코드는 보기 좋게 하기 위해 문자열을 쪼갰을 뿐 실제 컴파일된 코드는 단일 문자열을 사용한다. 즉, 문자열 연산이 없다는 말이다. 상세한 내용은 "StringBuilder에 대한 진실 혹은 거짓말" 씨리즈 글을 살펴보기 바란다.
마지막으로 리스트3의 코드가 좋은 이유로는 매개변수의 타입(type)을 nchar 니 DateTime 등으로 명시하고 있다는 것이다. 이 글에서 든 예제는 그렇지 않지만 매개변수를 사용하지 않는 경우나, 매개변수를 사용하더라도 매개변수의 타입을 주지 않는 경우, SQL Server가 형변환을 해야 하는 경우가 발생하곤 한다. 이때 데이터베이스 서버에 부하가 증가하여 CPU 사용율이 올라가게 된다. 가급적 명시적으로 매개변수의 타입을 주는 것이 데이터베이스의 부하를 줄이는 좋은 코딩 습관이 되겠다.
Use Stored Procedure
이야기가 좀 빗나가겠지만, 쿼리를 직접 구사하는 것 보다는 저장 프로시저(Stored Procedure)를 사용하는 것도 좋은 방안이다. 저장 프로시저는 생성과 동시에 컴파일되고 실행 계획이 저장(캐시가 아니다!)되므로 더욱 더 나은 효율을 나타낼 수도 있다. 게가다 저장 프로시저에게 값을 넘겨 주기 위해서는 반드시 매개변수를 써야할 것이며, 저장 프로시저는 달랑 이름만 주게되므로 문자열 연산이니 StringBuilder니 하는 복잡함도 없을 것 아닌가?
저장 프로시저를 쓰는 것에는 이견이 분분하다. 마이크로소프트는 성능 등의 관점에서 저장 프로시저의 사용을 권장하지만, 일선 SI 프로젝트에서 저장 프로시저를 기피하는 경우가 많다. 이유는 관리가 어렵다는 것인데 사실 무분별하게 저장 프로시저를 사용하면 데이터베이스는 온통 저장 프로시저들이 쌓여 있게 되고 어떤 것이 실제 사용 중이고 어떤 것이 수정되면 어떤 영향을 주는지 추적이 안 되는 경우가 많이 발생하곤 하기 때문이다. 필자의 의견은 저장 프로시저가 무분별하게 사용되고 있다는 것은 그만큼 설계가 명확하고 정확히 안되 있다는 말과 동등하다. 설계가 제대로 되어 있고, 문서화만 제대로 되어 있다면 추적이 안 되거나 무분별하게 남용되는 저장 프로시저는 없다고 본다. 사실 우리나라 SI 환경에서 설계를 제대로 하기란 매우 어렵다. 쓰봉... 뻑하면 요구사항이 변경되고(요거 존나 열받는 시츄에이션이다) 화면들이 매우 복잡하기 때문이기도 하지만 설계를 등한시 하는 풍토 때문이기도 하다.
어찌 되었건 저장 프로시저의 사용 여부는 해당 프로젝트에서 성능, 관리 측면에서 충분히 검토하고 신중이 결정하면 될 것이다(오호... 또 슬그머니 빠져나가는... -_-; ).
What about Oracle ?
지금까지 필자가 이야기 한 것은 모두 SQL Server에 대한 내용 이였다. 오라클은 어떨까? 뻑 하면 입버릇 처럼 필자가 이야기 하는 바 대로 필자는 오라클에 대해 조또 아는 것이 없다. 하지만 상식적으로 생각해 봤을 때 둘은 비슷하지 않을까 한다. 고로... 오라클에서도 매개변수를 사용하는 쿼리를 써야 하지 않을까가 필자의 생각이다. 오라클에 대하 잘 아는 사람 있으면 필자에게도 알려주는 은혜를 베풀기 바란다.
Conclusion
데이터 액세스 코드를 작성할 때, SQL 문장을 문자열 연산이나 StringBuilder를 사용하여 작성하는 것은 비효율적인 방법이므로 지양해야 한다. 대신 적극적으로 매개변수를 사용하는 쿼리 문장을 작성하여 사용하는 것이 좋다. 매개변수를 사용하는 쿼리 문장은 SQL 컴파일 회수를 줄여주고 캐시 된 실행 계획을 재사용할 확율을 크게 높여주기 때문이다.
매개변수를 사용하지 않는 쿼리를 쓰면 큰 문제가 생길까? 그에 대한 답은 "글쎄요" 이다. 사실 매개변수를 사용하지 않는 쿼리를 쓰더라도 어플리케이션이 기대하는 만큼의 충분한 성능을 낸다든가, 그렇게 사용하더라도 데이터베이스에 큰 부하가 없이 탱탱거리고 노는 서버라면 굳이 매개변수를 쓰는 쿼리를 반드시 써야만 하는 것은 아니다. 잘 작동하는데 뭐... 글타... 잘 작동하면 장땡인 것이다. 굳이 잘 도는 코드를 바꾸려고 드는 것은 힘이 남아 돌아 주체를 못할 때 해보는 것이 맞는 것이다.
하지만... 버릇이라는 것이 무서운 것이, 다른 코드를 작성할 때 버릇처럼 이전 코드와 동일하게 작성했는데 또 성능상에 문제가 없으리란 법이 없는 것이다. 그래서... 처음부터 권장되는 코딩 패턴으로 코드를 작성하는 버릇을 들여 놓으면 다음에도 그 다음에도 권장되는 코드들을 작성해 나갈 것이 아닌가? 뭐 필자의 말에 동의한다면 이제부터라도 데이터 액세스 코드를 작성할 때 매개변수를 사용하는 코드를 작성하기길 바라며 졸필을 마친다. (웬지 머쪄 보인당... 헤벌레~)
Appendix. Auto-Parameterize
SQL Server 2000에는 자동 매개변수화(Auto-Parameterize) 기능이란 것이 있다. 이 기능은 매개변수를 쓰지 않은 쿼리가 사용되더라도 자동으로 매개변수 사용된 것처럼 실행 계획을 세워두는 것을 말한다. 예를 들어 다음과 같은 쿼리가 수행되면,
SELECT * FROM Northwind.dbo.Products WHERE CategoryID = 1
마지막 WHERE 절에 매개변수가 사용된 것 처럼(WHERE CategoryID = @param1) 실행 계획을 작성해 둔다는 것이다. 그래서 나중에 동일하지만 값이 다른 다음과 같은 쿼리가 수행되더라도 실행 계획을 재사용하도록 하는 기능이다.
SELECT * FROM Northwind.dbo.Products WHERE CategoryID = 4
필자가 본문에서 SQL Server가 상당히 영리하다고 언급한 이유가 바로 여기에 있다. 리스트1의 코드를 매개변수를 달리하여 반복적으로 수행하면 점점 SQL 컴파일 회수가 줄어드는 것을 성능 모니터에서 발견할 수 있다. 컴파일 회수가 줄어드는 이유가 바로 이 자동 매개변수화 기능 덕분인 것이다.
하지만 자동 매개변수화는 항상 성공하는 것은 아니다. 복잡한 쿼리가 사용되는 경우, SQL Server는 자동 매개변수화에 실패하는 경우가 많이 발생되곤 하기 때문이다. 이 때문에 우리는 이 자동 매개변수화에 의존해서는 안 된다. 될 때도 있고 안될 때도 있는 자동 매개변수화에 의존하는 것보단 아쌀하게 매개변수를 명시적으로 사용하는 쿼리를 사용함으로써 안정적인 코드를 유지하는 것이 좋다.