반응형
OS : Microsoft Windows 2000 Server / Advanced Server SQL : Microsoft SQL Server 2000 Standard / Enterprise Edition 동적 SQL 에 대해서 50프로만 아는 개발자가 실수 하는 부분을 알아 보겠습니다. select top 10 productid, quantity, unitprice from dbo.[order details] where orderid= 1234 그러면 페이징 쿼리가 들어가게 되면 10, 20, 30 등이 많이 쓰이겠죠 실제 쿼리에서는 더 복잡한 구문으로 조인도 들어가고 서브 쿼리도 들어가겠죠 바뀌는 구문마다 select * from master.dbo.syscacheobjects 에서 살펴보면 Compiled Plan 을 하나 만들고 여러번 실행되고 실행될때 마다 execution plan 을 재사용 한다는 sp_executesql 을 구성해서 만들었습니다. 프로그램을 오래 하다보니 또 디비를 좀 하다 보니.....어디서 좋다는 말은 많이 들었습니다. 문제는 사람들이 요까지만 안다는 것이죠 !!!! -_-/ 그럼 쪼메 더 아는 컨설턴트들이 와서 살짝 일하고 수백만원 받아 갑니다. 그리고 서버는 조용해 졌으니 우아~~~~ 하고 입만 벌리고 있다는 거죠 -_-/ 그럼 쪼메더 일하는 것에 대해서 설명해 드리겠습니다. 일단 sql2000 의 쿼리분석기에서 dbcc memorystatus 라는 문서화 되지 않은 프로시져가 있습니다. 그것의 결과값 중 관심 있는 부분인 Dynamic Memory Manager 파트중 QueryPlan 의 값을 계산해 봅니다. 숫자 * 8 KB 로 계산되어 메모리를 차지하고 있습니다. 머 이까지도 대충 여러번 어디서 주서 들으면 알고 있습니다. 그런데 이값을 계산해 보니 서버에서 1.4GB 나 차지하고 있습니다. SQL 2000 스텐다드 에디션 이라면 끝장 난 거라고 볼 수 있겠죠 ^.^ 먼저 위 구문에서 두가지 변수가 있습니다. 1. top 다음에 오는 상수가 변수 값이 됩니다. 2. ORDERID 다음에 오는 값이 변수가 됩니다. 여기에서 (2) 번을 변수라고 생각하지 못하고 있다는 것입니다. 사실 TOP 의 10 ,20 ,30 등 다양한 TOP 에 대해서 실행계획이 세워지는 것은 어쩔수 없습니다. 사실 TOP 에 1 이 올때와 10000 이 올때 같은 실행 계획을 가지고 실행하게 만드는 것도 바보 짓이니까요 ^.^ 이제 (2) 도 변수라는 것을 알았으면 어떻게 해야 할까요? 바로 파라메터가 있는 동적 SQL 을 만들어 주면 되겠죠? [잘못된 방법] use northwind declare @stmt as nvarchar(100) declare @params as nvarchar(100) declare @cnt as nvarchar(10) DECLARE @ORDERID AS INT SET @ORDERID = 1234 set @cnt = '10' set @stmt = 'select top '+@cnt+' productid, quantity, unitprice from ' set @stmt = @stmt + 'dbo.[order details] where orderid ='+ @orderid exec sp_executesql @stmt 이렇게 만들면 @ORDERID 가 바뀔때 마다 신나게 컴파일 플랜 만들고 익스큐션 플랜 만들게 되겠죠 ORDERID 가 몇개 안되면 문제가 안되는데 이게 뭐 100만개 된다. 그야로 재앙 입니다. @CNT 가 바뀔때 마다도 생기지 않느냐? 맞습니다. 생깁니다. 하지만 대부분의 사용자는 10,20,30 그 후로 잘 안보기 때문에 사실 TOP CNT 에 의해 생기는 것보다 ORDERID 값에 의해 플랜이 바뀐는 것이 100만개라는 거죠........ [수정된 쿼리문] use northwind declare @stmt as nvarchar(100) declare @params as nvarchar(100) declare @cnt as nvarchar(10) set @cnt = '10' set @stmt = 'select top '+@cnt+' productid, quantity, unitprice from ' set @stmt = @stmt + 'dbo.[order details] where orderid=@orderid' set @params = '@orderid int' exec sp_executesql @stmt, @params, @orderid=10263 이넘은 최소한 TOP 10 을 하는 어떠한 @ORDERID 에 대해서도 같은 플랜을 공유하게 되겠죠... 이정도만 만들어 줘도 SQL 은 미치지 않는답니다. ^.^ 실제로 위와 같이 간단한 sql 은 set rowcount 10 이렇게 적어서 하시면 되겠죠......^.^ 자자 실수 하신분은 빨리 고치러 가시고. 다 알고 있었던 분은 야가 또 실없는 소리 하네 하고 빨리 하던일 하시길 바랍니다. ^.^ 오늘 하루도 좋은 하루 되시길 바랍니다.
출처 : http://sqler.pe.kr/web_board/view_list.asp?id=1035&read=3589&pagec=1&gotopage=1&block=0&part=myboard7&tip=ok
반응형