제 1강 : Update 되는 Top ~ Order by 뷰 및 CTE를 이용한 접속로그 테이블 관리

제 1강 : Update 되는 Top ~ Order by 뷰 및 CTE를 이용한 접속로그 테이블 관리

이름: 최석준 회사명: 조이온 근무부서: 거상2팀 직무: DBA

로그인 로그아웃 시간을 저장하는 방법은 많이 있다. 기능을 소개하기 위해서 간단한 접속로그 테이블의 예를 통해서 MSSQL2000에서는 실행될 수 없었지만 MSSQL2005에서 가능해진 기능을 소개하고자 한다. 유저의 로그인 로그아웃 시간을 저장하는 다음과 같은 구조의 접속로그 테이블이 있다. if object_id('ConnectLog','U') is not null drop table ConnectLog create table ConnectLog ( id varchar(10), login datetime, logout datetime ) 리스트 1 접속로그 테이블 만들기 이때 조건은 유저는 중간에 어플리케이션등의 오류로 인해 로그아웃이 기록되지 않은 채 로그아웃이 될 수도 있다. 가장 최근에 접속한 행을 업데이트 하는 문제이다. declare @i int set @i = 0 while @i < 365 begin insert into ConnectLog values('beatchoi',         dateadd(ss,rand()* 1440,dateadd(d,@i,'2006-01-01')),         case when @i = 364 then null                 when convert(int,rand() * 100) % 100 = 1 then null             else dateadd(ss,(rand()+10) * 1440,                     dateadd(d,@i,'2006-01-01'))         end) set @i = @i + 1 end 리스트 2 접속로그 테이블에 더미 데이터 입력 위와 같이 테이블에 1년간의 더미 데이터를 입력한다. 다음과 같이 많은 개발자들이 MSSQL2000 에서 정렬을 갖거나 행수 제한을 하기위한 뷰를 구현하기 위해서 Top ~ Order By 를 가진 정렬된 뷰를 생성을 할 수 있었다. create view vw_ConnectLog as select top 1 Id,Logout     from ConnectLog   order by Login desc 리스트 3 Top ~ Order By 를 가진 정렬된 뷰만들기 MSSQL2000에서는 Top ~ Order By 포함하는 가진 뷰를 생성할 수는 있지만 업데이트시 다음과 같은 4427 에러를 발생시키고 업데이트는 할 수 없다. update vw_ConnectLog    set Logout = getdate()   where Id = 'beatchoi' 서버: 메시지4427, 수준16, 상태1, 줄1 뷰또는함수'vw_ConnectLog' 정의에TOP 절이있어서업데이트할수없습니다. 리스트 4 Top ~ Order By를 가진 정렬된 뷰 업데이트 시 오류 UPDATE 가능한 MSSQL 2000 의 TOP ~ ORDER BY 를 포함하는 VIEW Microsoft Certified Trainer (MCT)인 Zoltan Kovacs가 발견한 업데이트 가능한 정렬된 뷰를 생성할 수 있도록 해 주는 백도어가 있다. 이 기법은 Openquery() 함수내의 Select 쿼리에 Order By 절을 지정하는 것이다. 백도어를 이용하는 방법은 다음과 같다. Openquery () 를 호출하여 자기 자신 서버에 대하여 쿼리를 실행하기 위해서는 우선 Data Access 서버 옵션을 설정하는 것이 필요하다. exec sp_serveroption '[원격서버명]','data access', true create view vw_ConnectLog_Backdoor as SELECT a FROM OPENROWSET('SQLOLEDB','[원격서버명]';'[로그인]';'[암호]'     'SELECT top 1 * FROM tempdb.dbo.ConnectLog ORDER BY login') AS a update vw_ConnectLog_Backdoor     set Logout = getdate()   where Id = 'beatchoi' 서버: 메시지7320, 수준16, 상태2, 줄1 OLE DB 공급자'SQLOLEDB'에대해쿼리를실행할수없습니다. 공급자가필요한행조회인터페이스를지원할수없습니다. 공급자가다른속성또는요구사항과의충돌이발생했음을나타냅니다. [OLE/DB provider returned message: 여러단계OLE DB 작업을하는동안오류가발생했습니다. 각OLE DB 상태값이있으면확인해보십시오. 완료된작업이없습니다.] OLE DB 오류추적[OLE/DB Provider 'SQLOLEDB' ICommandText::Execute returned 0x80040e21: SELECT top 1 * FROM tempdb.dbo.ConnectLog ORDER BY login[PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING]]. 리스트 5 MSSQL2000 백도어를 이용한 업데이트 되는 Top ~ Order By를 가진 뷰 업데이트를 실행하면 위와 같은 오류가 발생하는데, 이는 SQL Server OLE DB 공급자는 UPDATE 또는 DELETE 작업을 위한 기본 테이블에 고유한 색인을 필요로 하기 때문이다. 고유한 색인이 원격 테이블에 없을 경우 Update나 Delete를 시도하면 다음 오류가 발생하며, OpenQuery와 이름이 4부분으로 된 Update와 Delete작업 모두에 적용된다. 원격 테이블에 다음과 같이 고유한 색인을 추가하면 문제가 해결된다. --고유한 색인 추가 alter table ConnectLog add idx int identity primary key update vw_ConnectLog_Backdoor      set Logout = getdate()   where Id = 'beatchoi' (1개행적용됨) 리스트 6 MSSQL2000 백도어를 이용한 업데이트 되는 뷰를 위해 색인 추가 고유한 색인을 추가면 정렬도 되고 업데이트도 가능하지만 다음과 같은 단점이 있다. ANSI 호환이 아니라는 점 외에 로컬 쿼리에 비해 더 많은 비용이 드는 분산 쿼리를 사용한다는 점이다. 따라서 MSSQL2000에서 가장 최근의 행을 업데이트 하기 위해서는 다음과 같은 쿼리를 해야 했다. update a     set Logout = getdate()   from ConnectLog a     join ( select id, max( login ) as login               from ConnectLog           group by id) b      on a.id = b.id and a.login = b.login (1개행적용됨) 리스트 7 MSSQL2000에서 가장 최근의 행을 업데이트 하는 쿼리 Openquery를 이용한 View와 위의 쿼리의 예상실행계획을 보면, Openquery를 이용한 View가 99.15% 일반 쿼리가 0.85% 로 성능과 제약조건으로 인해 Openquery를 이용한 View 는 실제 서비스에서 쓰기는 어려워 보인다. 리스트 8 백도어를 이용한 뷰와 일반쿼리 실행계획 UPDATE 가능한 MSSQL 2005의 TOP ~ ORDER BY 를 포함하는 VIEW 하지만 MSSQL2005 에서는 Top ~ Order By 를 가진 뷰를 만들고 업데이트 할수 있다. create view vw_ConnectLog as select top 1 Id,Logout    from ConnectLog    order by Login desc update vw_ConnectLog     set Logout = getdate()   where Id = 'beatchoi' (1개행적용됨) 리스트 9 MSSQL2005에서 업데이트 되는 Top ~ Order By를 가진 뷰 다음은 MSSQL2000 방식과 MSSQL2005 두 쿼리의 예상 실행계획 이다. TOP ~ ORDER BY 포함하는 가진 뷰가 실행계획이 좀 더 간결하다. 리스트 10 MSSQL2005 업데이트 되는 Top ~ Order by를 가진 뷰와 일반쿼리 실행계획 MSSQL 2005의 공통테이블식 CTE (COMMON_TABLE_EXPRESSION) MSSQL2005에서 구현할수 있는 또 다른 방법은 공통테이블식 CTE (Common_Table_Expression) 를 이용하는 방법이다. CTE 구문 [ WITH <common_table_expression> [ ,...n ] ] <common_table_expression>::=           expression_name [ ( column_name [ ,...n ] ) ]        AS           ( CTE_query_definition ) 리스트 11 CTE 구문 일반적으로 Select 문과 재귀적인 쿼리문에만 CTE를 사용하는 경우가 있지만 CTE는 Update,Insert,Delete 모두 지원한다. 코드도 간결하며, 뷰 같은 물리적인 오브젝트를 관리하지 않아도 된다는 장점이 있으며 다음 실행에서 보면, 실행시간 및 I/O등에서도 별반 차이가 없다. 뷰를 사용한 업데이트 문을 CTE구문에 맞게 작성을 해보면 다음과 같다. ;with cte_ConnectLog (id,logout) as ( select top 1 id,logout     from ConnectLog   order by login desc ) update cte_ConnectLog     set Logout = getdate()   where Id = 'beatchoi' (1개행적용됨) 리스트 12 CTE를 이용한 가장 최근의 행을 업데이트 하는 쿼리 리스트 13 MSSQL2005 업데이트 되는 Top ~ Order by를 가진 뷰와 CTE실행계획 뷰를 사용한 업데이트 문을 CTE를 실행하여보면 둘의 실행계획은 정확하게 일치한다. 다음은 위의 세가지 식을 테스트한 결과이다. 테스트 결과를 보면 TOP ~ ORDER BY 뷰와 CTE는 차이가 없으며, 기존 MSSQL2000방식보다 미세하게 성능이 나은 것을 볼수 있다. SET STATISTICS IO ON SET STATISTICS TIME ON GO update a      set Logout = getdate()    from ConnectLog a      join ( select id,max(login) as login                from ConnectLog             group by id) b       on a.id = b.id and a.login = b.login SQL Server 구문분석및컴파일시간:       CPU 시간 = 16ms, 경과시간 = 16ms. 테이블'ConnectLog'. 검색수1, 논리적읽기수3, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0. SQL Server 실행시간:       CPU 시간 = 0ms, 경과시간 = 4ms. (1개행적용됨) update vw_ConnectLog       set logout = getdate()    where id = 'beatchoi' SQL Server 구문분석및컴파일시간:      CPU 시간 = 5ms, 경과시간 = 5ms. SQL Server 구문분석및컴파일시간:      CPU 시간 = 0ms, 경과시간 = 1ms. 테이블'ConnectLog'. 검색수1, 논리적읽기수3, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0. SQL Server 실행시간:      CPU 시간 = 0ms, 경과시간 = 2ms. (1개행적용됨) ;with cte_ConnectLog (id,Logout) as ( select top 1 id,Logout       from ConnectLog    order by login desc ) update cte_ConnectLog       set logout = getdate()    where id = 'beatchoi' SQL Server 구문분석및컴파일시간:      CPU 시간 = 0ms, 경과시간 = 4ms. 테이블'ConnectLog'. 검색수1, 논리적읽기수3, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0. SQL Server 실행시간:      CPU 시간 = 15ms, 경과시간 = 2ms. (1개행적용됨) 리스트 14 일반쿼리와 되는 Top ~ Order by를 가진 뷰와 CTE 실행통계

출처 : http://www.microsoft.com/korea/technet/expertlecture/MVP/seokjun.mspx