Nayha

MSSQL 2005  >>   SQL Server business intelligence Development Studio

사용 하여 에이전트 작업 간편화

작업 목적  제휴사 관련 데이타 전달 작업

제휴사 관련 데이타 view 작업(필요한 데이타 만 추출 ) 뷰 제작후

디비 > 작업 > 데이타 내보내기

데이터 원본 (sql Native Client ) ( 실제 디비 선택)

대상 >>플랫 파일 대상 >> 파일이름 선택후 ( csv ) >> 데이타 내보내기 >>

패키지 저장 및 실행 옵션시 SSIS 패키지 선택 >>파일 시스템 선택  >> SSIS 로 패키지 제작

MSSQL 2005  >>   SQL Server business intelligence Development Studio  선택

새 프로젝트

비지니스 인텔리전스 프로젝트

탬플릿 선택

Integration Services 프로젝트

SSIS 패키지 선택  기존 패키지 불러오기 패키지 경로 ( 암호 alba)

불러와서

스케쥴 작업 

파일 시스템 ( 해당 파일 삭제후 )

데이터 흐름작업( 실제 디비에서 데이타 불러와서 csv파일 저장)

ftp 작업 제휴 관련 데이타 ftp로 전송

작업 완료

해당 작업시 해당 폴더에 sql 에이전트 사용자 폴더 수정,삭제 권한 추가 부여

완료 시 정상적으로 ftp 전송

작업완료 ;;

대충 정리 차후 제대로 정리가 필요함 ㅋㅋ;;

실행계획에 대해서 몇마디 적어보려고 합니다.

저희 팀에도 개발자가 몇 명 있습니다.

쿼리분석기로 쿼리를 작성하여, 이 쿼리가 어떻게 실행되는지 보기 위해 실행계획을 확인하는데, 중요한 부분을 놓치곤 합니다.

다음과 같은 쿼리가 있습니다.

select * from CE_ACCT where MANDT = '100' and PAOBJNR = '0000000607'

이 쿼리의 실행계획은,

이 테이블은 클러스터드 인덱스 한개만 있으므로 위 그림으로 보면 데이터 액세스 방식이 'clustered index seek'임을 알수 있습니다.

물론 마우스 포인터를  가져다 대면 'clustered index seek'라고 나오겠지요.

그리고는 '인덱스 잘 사용하고 있군'하고 넘어가는 경우가 종종 있습니다.

하지만, 사실 이 그림만 봐서는 방식은 인덱스 조회인데, 제대로 인덱스를 사용하고 있는지 알 수가 없습니다. 

좀더 자세한 내용을 알기 위해서는 꼭 실행 계획의 'Argument'를 확인하셔야 합니다.

인덱스 조회 노드에 마우스 포인터를 가져가 보면,

위 그림에서 '인수'라고 쓰인 부분이 바로 'Argument'입니다.

set statistics profile on 옵션을 준 후에 text plan을 보면, 결과창에 'Argument' 칼럼이 있습니다. 바로 이 값을 확인해 봐야 합니다.

OBJECT:([PRD].[dbo].[CE_ACCT].[cx]), SEEK:([CE_ACCT].[MANDT]=[@1]), 

WHERE:([CE_ACCT].[PAOBJNR]=[@2]) ORDERED FORWARD

Argument에는 크게 두가지 부분으로 나뉩니다.

SEEK 와 WHERE

SEEK 부분이 바로 쿼리 조건 중 인덱스 조회를 할 때 사용되어지는 칼럼 입니다.

WHERE 부분은 그렇게 인덱스를 통해 걸러진 결과집합을 가지고 필터로 걸러 내는 역할을 하는 칼럼입니다.

다시 예제 쿼리로 돌아가서,

우선 조회되는 테이블의 인덱스는 MANDT, AKTBO, PAOBJNR로 구성된 클러스터드 인덱스 입니다.

그리고 이 인덱스의 선택도를 보면

dbcc show_statistics (CE_ACCT,cx)

앞 두 칼럼 MANDT, AKTBO의 선택도는 낮고 제일 뒤에 있는 칼럼인 PAOBJNR 만 선택도가 굉장히 좋은 것을 알수 있습니다.

where 절의 조건 칼럼은 MANDT, PAOBJNR이고 이 두 칼럼 모두가 인덱스에 포함되어 있는데, 하나는 인덱스 조회용으로, 또 하나는 필터 용으로 사용되었을까요? 다시한번 엄밀히 말하면 PAOBJNR 칼럼의 조건은 인덱스에서 사용되지 않은 것입니다. 이 PAOBJNR칼럼의 선택도가 좋음에도 불구하고 왜 사용되지 못했을까요?

이유는, 인덱스의 칼럼 순서와 인덱스 구조 때문입니다.

인덱스 구조에 대한 설명은 여기서 하지 않고, 결론만 말씀 드리면, 인덱스 생성시 칼럼의 순서대로, 인덱스 칼럼이 사용될 수 있습니다.

다시 말하면, 위와 같은 순서의 인덱스에서는 PAOBJNR 칼럼이 인덱스 조회시 사용되려면, AKTBO 칼럼에 대한 조건이 where 절에 들어가 있어야 한다는 얘기지요.

그러므로 이 쿼리의 성능을 최적화 하기 위해서는 두가지 방법이 있습니다.

1. 쿼리에 AKTBO 조건이 포함되도록 한다.

2. 인덱스 칼럼의 순서를 변경한다. MANDT, AKTBO, PAOBJNR --> MANDT, PAOBJNR, AKTBO

물론 인덱스 칼럼의 순서를 변경하는 일은 극히 신중을 기해야 합니다.

그래서 AKTBO 칼럼에 대한 조건을 주고 쿼리를 실행해 보면

select * from CE_ACCT where MANDT = '100' and PAOBJNR = '0000000607' and AKTBO = 'X'

OBJECT:([PRD].[dbo].[CE_ACCT].[cx]), SEEK:([CE_ACCT].[MANDT]=[@1] AND [CE_ACCT].[AKTBO]=[@3] AND [CE_ACCT].[PAOBJNR]=[@2]) ORDERED FORWARD

실행계획의 Argument 부분에 SEEK 만 있고, WHERE 부분은 없어진 것을 알 수 있습니다.

그러면 두 쿼리의 비용과 I/O를 비교해 보겠습니다.

(1개 행 적용됨)

Table 'CE_ACCT'. Scan count 1, logical reads 395, physical reads 0, read-ahead reads 0.

(1개 행 적용됨)

Table 'CE_ACCT'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

AKTBO 조건이 유무에 따라서 비용이 98.93% 대 1.07% 그리고 I/O 차이는 395 대 3으로

엄청난 성능 향상을 가져왔습니다.

위 쿼리를 가지고 재미있는 실험을 하나 해보겠습니다.

아래 두 쿼리의 I/O를 비교하면 어떤차이가 있을까요? (쿼리1의 결과는 1행만을 반환 합니다. 그리고 쿼리2는 10000행을 반환 하고요.)

--쿼리1

select * from CE_ACCT where MANDT = '100' and PAOBJNR = '0000000607'

--쿼리2

select * from CE_ACCT where MANDT = '100'

결과는,

--쿼리1

(1개 행 적용됨)

Table 'CE_ACCT'. Scan count 1, logical reads 395, physical reads 0, read-ahead reads 0.

--쿼리2

(10000개 행 적용됨)

Table 'CE_ACCT'. Scan count 1, logical reads 395, physical reads 0, read-ahead reads 0.

놀랍게도, I/O는 같습니다.

이유는 두 쿼리의 실행계획의 Argument를 보면 이해할 수 있습니다.

--쿼리1

OBJECT:([PRD].[dbo].[CE_ACCT].[cx]), SEEK:([CE_ACCT].[MANDT]=[@1]),  WHERE:([CE_ACCT].[PAOBJNR]=[@2]) ORDERED FORWARD

--쿼리2

OBJECT:([PRD].[dbo].[CE_ACCT].[cx]), SEEK:([CE_ACCT].[MANDT]=[@1]) ORDERED FORWARD

쿼리1의 실행계획은 위에서 이미 설명 드렸습니다.

두 쿼리다 인덱스 조회 방식을 사용하고 있고 SEEK 부분이 같습니다. 물론 쿼리1은 WHERE 부분에 아주 선택도가 좋은 칼럼 PAOBJNR의 조건이 있기 때문에 결과는 1행만 반환하는것이고요.

결과가 1행만 반환했다고 해서 실제 디스크에서 읽는 I/O의 양이 꼭 적은 것은 아닙니다. 위 실험에서 보듯이...

그러므로 위 두 쿼리는 MANDT = '100' 조건만으로 인덱스를 사용하여 디스크(혹은 메모리)에서 읽은 다음, 첫번째 쿼리만 PAOBJNR 칼럼으로 필터링하여 결과를 보여주는 것입니다. 그러므로 동일한 I/O가 발생되는 것이지요.

그러면 이제 마무리를 지어볼까 합니다.

1. 먼저, 실행계획을 볼 때에는 꼭 'Argument'를 확인하라고 말씀드렸습니다. 추가로, graphical plan 보다 text plan을 추천합니다.

가끔 쿼리가 긴 경우, graphical plan 의 Argument의 뒷부분이 짤리기도 합니다. 또한 text plan은 보다 더 많은 유용한 정보를 보여 줍니다.

2. 인덱스 조회 방식일 때, Argument 에는 SEEK와 WHERE의 두부분으로 나뉜다고 말씀 드렸습니다. SEEK 부분에 있는 칼럼이 인덱스를 제대로 사용하는 칼럼이고, WHERE 부분의 칼럼은 다 읽어 놓은 결과에서 여러분 화면으로 데이터를 보내서 보여줄 때 필터 역할만 합니다.

3. 인덱스 칼럼의 순서대로 인덱스를 사용할 수 있습니다. 그러므로, 인덱스 칼럼의 순서와 맨 앞에 오는 칼럼을 결정하는 일은 너무나 중요합니다.

나름대로 중요하다고 생각되는 내용을 두서없이 적어 봤습니다. 이 글을 읽고 조금이나마 도움이 되셨기를 바랍니다.

<!--  Zip_Search.asp -->
<!--#include file="dbcon.inc"-->
<%
     response.charset = "euc-kr"

     set rs = server.CreateObject("adodb.recordset")
     sql = "select distinct sido from tb_zip order by sido "
     rs.open sql, db, 1
%>

<script>
 var ajax_gugun;

 function get_gugun_list(sido)
 {  
      ajax_gugun = new ActiveXObject("Microsoft.XMLHTTP");
      ajax_gugun.onreadystatechange = get_gugun_list_callback;
      ajax_gugun.open("GET", "Zip_GugunList.asp?sido=" + sido, true);

      ajax_gugun.send();
 }

 function get_gugun_list_callback()
 {
      if(ajax_gugun.readyState == 4){   
           if(ajax_gugun.status == 200){
                if(typeof(document.all.div_gugun) == "object"){
                    document.all.div_gugun.innerHTML = ajax_gugun.responseText;
                }
           }
      }
 }

 var ajax_addr;
 function get_addr_list(){
      var sido, gugun, dong;

      sido = document.frmZip.sido.value;
      gugun = document.frmZip.gugun.value;
      dong = document.frmZip.dong.value;

      if (dong.length < 2)            return false;
    

      ajax_addr = new ActiveXObject("Microsoft.XMLHTTP");

      ajax_addr.onreadystatechange = get_addr_list_callback;
      ajax_addr.open("GET", "Zip_AddrList.asp?sido=" + sido + "&gugun=" + gugun + "&dong=" + dong, true);     

      ajax_addr.send();
 }

 function get_addr_list_callback()
 {
      if(ajax_addr.readyState == 4){
          if(ajax_addr.status == 200){
              if(typeof(document.all.div_zip_list) == "object"){
                    if(ajax_addr.responseText.length > 0){
                         document.all.div_zip_list.style.display = "";
                         document.all.div_zip_list.innerHTML = ajax_addr.responseText;
                    }
                   else
                         document.all.div_zip_list.style.display = "none";     
               }
          }
     }
 }

</script>

<style>
      input, select, td {  font-family:tahoma;font-size:9pt; }
</style>

<table border="0" cellpadding="4" cellspacing="0" width="400">
<form name="frmZip">
 <tr>
  <td>
   <select name="sido" onChange="get_gugun_list(this.value);">
       <option value="">시도선택</option>
    <% Do Until rs.eof %>
          <option value="<%=rs("sido")%>" ><%=rs("sido")%></option>
    <%
               rs.movenext
          Loop

          rs.close
          Set rs = Nothing
    %>
   </select>
  </td>
  <td>
   <div id="div_gugun">

       <select name="gugun" >   
             <option value="">구/군선택</option>    
       </select>

   </div>
  </td>
  <td>
     동이름 : <input type="text" name="dong" style="width:50">
     <input type="button" value="검색" onClick="get_addr_list();" style="width:40;border-style:solid;border-width:1">
  </td>
 </tr>
 <tr>
  <td colspan="3" align="center">
       동이름을 입력하시오(2글자 이상)<br>
       (예 : 논현, 구의1동, .. )
  </td>
 </tr>
</form>
</table>

<div id="div_zip_list" style="display:none;overflow-y:scroll; height:300;width:400"></div>
 

<!--  Zip_GugunList.asp -->
<!-- #include file="dbcon.inc"-->

<%
     response.charset = "euc-kr"

     sido = request("sido")
 
     sql = "select distinct gugun from tb_zip where sido = '" & sido & "'   order by gugun"
     Set rs = server.CreateObject("adodb.recordset")
     rs.open sql, db, 1
%>

 <select name="gugun" >   
      <option value="">구/군선택</option>  

<% Do Until rs.eof  %>
      <option value="<%=rs("gugun")%>" ><%=rs("gugun")%></option>
<%
          rs.movenext
      Loop

      rs.close
      Set rs = Nothing
%>

 </select>

<!--  Zip_AddrList.asp -->
<!--#include file="dbcon.inc"-->
<%
     response.charset = "euc-kr"

     sido = request("sido")
     gugun = request("gugun")
     dong = request("dong")

     If sido = "" And gugun = "" Then
          sql = "select zipcode, sido, gugun, dong, ri, build, addr from tb_zip where dong like '%" & dong & "%'"
     ElseIf sido <> "" And gugun = "" Then
          sql = "select zipcode, sido, gugun, dong, ri, build,  addr from tb_zip where sido = '" & sido & "' and dong like '%" & dong & "%'" 
     ElseIf sido <> "" And gugun <> "" Then
          sql = "select zipcode, sido, gugun, dong, ri, build,  addr from tb_zip where sido = '" & sido & "' and gugun = '" & gugun & "' and dong like '%" & dong & "%'" 
     End If

     Set rs = server.CreateObject("adodb.recordset")
     rs.open sql, db, 1
%>

<table cellpadding="3" cellspacing="3" border="1" width="400">
<%
     Do Until rs.eof
         zip = "[" & Left(rs("zipcode"), 3) & "-" & Right(rs("zipcode"), 3) & "]"
%>
   <tr>
     <td>
         <a href="#"><%=zip%> </a>
    </td>
    <td>
         <%=rs("addr")%>
    </td>
   </tr>
<%  
       rs.movenext
    Loop
%>
</table>

    

<!---------------------->

<!-- ajaxTest.asp -->

<!---------------------->

<script>
    var ajax_test;

    function getHttpRequest(){
        var tt = document.forms[0].ttt.value;

        // 비동기 요청/응답에 사용할 객체 생성
       ajax_test = new ActiveXObject("Microsoft.XMLHTTP");  

   // 상태변경시마다 호출될 콜백함수
   function ajax_test_rev(){
        if(ajax_test.readyState == 4){  // 객체의 상태(0:비초기화, 1:로딩, 3:상호작용, 4: 완료)
        if(ajax_test.status == 200){ // http 상태(200:확인, 404:파일못찾음, 202:값없음)
        if(typeof(document.all["dd"]) == "object"){
                    // 응답된 텍스트를 원하는 레이어에 출력
                    // responseText에 응답된 내용이 저장되어 있음
                   document.getElementById("dd").innerHTML = ajax_test.responseText;
             }
        }
    }
 }

    var dragflag = false;
    var dragobj,dragfrm,x,y,sleft,sright;

   function move(e){
       if(dragflag){
             dragobj.style.left = sleft + event.clientX - x;
             dragobj.style.top = sright + event.clientY - y;
       }
       return false;
  }

  function drags(obj, e){
       dragobj = eval("document.all."+obj);
       

       dragflag = true;
       sleft  = parseInt(dragobj.style.left + 0);
       sright = parseInt(dragobj.style.top + 0);
       x = event.clientX;
       y = event.clientY;
  

       document.onmousemove = move;
       return false;
   }

   document.onmouseup = new Function("dragflag = false;");
</script>

<body>
   <form name="frm_dd">
        <input type="text" name="ttt"><br>
        <!-- 버튼 클릭 시 test.asp페이지 요청을 위해 getHttpRequest() 호출 -->
        <input type="button" onclick="document.all.dd.style.display='';getHttpRequest();" value="비동기 파일응답받기">
        <input type="button" onclick="document.all.dd.style.display='none';" value="감추기">
    </form>

    <!-- 요청한 페이지를 표시할 레이어 -->
    <div id="dd" style="position:absolute;display:none;left:100px;top:100px;width:200px;height=200px;"></div>

</body>

<HTML>
<HEAD>
<TITLE> Formatcurrency </TITLE>
</HEAD>

<BODY>
1. <%=formatcurrency(25000000)%> 결과값: \25,000,000 <br>
      '통화단위로 표시해준다.  \editplus에서는 원화표시가 안된다.<p>
2. <%="$" & formatNumber(25000000,0)%> $25,000,000.00<br>
      '컴마만 찍어준다. 따라서 달라는 내가 직접 표시해준다.<br>
   ,0을 넣어주면 소숫점값을 빼준다.<p>

3. <%=formatDateTime(now, 0)%>  결과값: 2005-05-17 오후 12:43:09 <br>
4. <%=formatDateTime(now, 1)%>  결과값:2005년 5월 17일 화요일 <br> 
5. <%=formatDateTime(now, 2)%>  결과값:2005-05-17 <br>
6. <%=formatDateTime(now, 3)%>  결과값:오후 12:43:09  <br>
7. <%=formatDateTime(now, 4)%>  결과값:12:43 <p>

8. <%=formatpercent(50/100)%> 결과값: 50.00%<p>
   <%=formatpercent(0.45)%> 결과값: 45.00%<p> 

<%
  str="Everybody Call me Great taeyo"
  response.Write Instr(str,"body") & "<br>"  '결과값: 6
  response.Write Lcase("SuperMan") & "<br>" '결과값:superman<소문자출력>
  response.Write Ucase("SuperMan")& "<br>"  '결과값:SUPERMAN<대문자출력>
%>

</BODY>
</HTML>

***  한페이지에서 처리

<!-- 인터페이스 가격체크 -->

<script language="JavaScript">
<!--
 function account(){

 var frm = document.frmReg;

  if ((frm.houseType[0].checked) == false && (frm.houseType[1].checked) == false)
  {
   alert('주택여부를 선택하여 주십시오');
   frm.houseType[0].focus();
   return;
  }

  if ((frm.goodType_code[0].checked) == false && (frm.goodType_code[1].checked) == false )
  {
   alert('거래종류를 선택하여 주십시오');
   frm.goodType_code[0].focus();
   return;
  }

   if(frm.price.value == ''){
    alert("거래금액을 입력하여 주십시오");
    frm.price.value = '';
    frm.price.focus();
    return;
   }else{
   if (!ChkNum(frm.price.value)) {
    alert("거래금액은 숫자만 입력 가능합니다!");
    frm.price.value = '';
    frm.price.focus();
    return;
   }
  }
  document.frmReg.action = 'Taxd.asp';
  document.frmReg.submit();
 }

 function ChkNum(Num) {
  var num ="0123456789";
  for (var i=0; i< Num.length; i++){
    if(-1 == num.indexOf(Num.charAt(i))){
     return false;
    }
  }
  return true;
 }

//-->
</script>

<!-- 변수 받아 계산처리 -->

<%
Dim houseType,houseTypeText
Dim goodType_code,goodTypeText '(1:매매,교환 2:임대, 전세)
Dim price,brokerage,brokerageText

houseType   = Request("houseType")
goodType_code = Request("goodType_code")
price     = Request("price")  * 10000

 Select Case houseType
  Case 1 : houseTypeText = "일반주택"
  Case 2 : houseTypeText = "주택외 비거주용 건물, 토지"
 End Select

 Select Case goodType_code
  Case 1 : goodTypeText = "매매, 교환"
  Case 2 : goodTypeText = "임대, 전세"
 End Select

 IF goodType_code = 1 Then '매매, 교환

  IF houseType  = 2 Then
   brokerage   = " ~ " & formatnumber(price * 0.009,0)
   brokerageText = "~ 0.9%"
  Else
   IF price < 50000000 Then
    brokerage   = price * 0.006
    brokerageText = "0.6%"
    IF brokerage > 250000 Then brokerage = 250000
   ElseIF price >= 50000000  and price <= 200000000 Then
    brokerage   = price * 0.005
    brokerageText = "0.5%"
    IF brokerage > 800000 Then brokerage = 800000
   Else
    brokerage   = price * 0.004
    brokerageText = "0.4%"
   End IF
    brokerage = formatnumber(brokerage,0)
  End IF

 Else '임대, 전세

  IF houseType  = 2 Then
   brokerage   = " ~ " & formatnumber(price * 0.009,0)
   brokerageText = "~ 0.9%"
  Else
   IF price < 50000000 Then
    brokerage   = price * 0.005
    brokerageText = "0.5%"
    IF brokerage > 200000 Then brokerage = 200000
   ElseIF price >= 50000000  and price <= 100000000 Then
    brokerage   = price * 0.004
    brokerageText = "0.4%"
    IF brokerage > 300000 Then brokerage = 300000
   Else
    brokerage   = price * 0.003
    brokerageText = "0.3%"
   End IF
    brokerage = formatnumber(brokerage,0)
  End IF

 End IF
%>

<!-- 체크값-->

<form name="frmReg" method="post">

                             <input type="radio" name="houseType" value=1 />
                              주택&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                              <input type="radio" name="houseType" value=2 />
                              주택 외 부동산 </td>

</form>

<a href="javascript:account();"><!-- 함수 호출 -->