저장프로시저 기초 정리

 저장프로시저의 특징 

저장 프로시저(Stored Procedure)란 무엇을 말하는 것일까요? 아마도 데이터베이스 쿼리를 의미하는 것일 수도 있고, 또는 데이터베이스 관리자(DBA)나 데이터베이스 소유자(DBO)들이 사용하는 것이라고 말할 수도 있을 것입니다. 하지만 제가 보기에 저장 프로시저에 대한 가장 적절한 의미는 "Transact - SQL(T-SQL) 언어로 쓰여지고 관계형 데이터베이스 범위 내에서 컴파일 및 실행된 명령어 세트" 라고 생각합니다.

그러면 다음 주제인 "왜 저장 프로시저를 사용하는 걸까요?" 라는 문제에 대해 살펴 보겠습니다. 앞에서 언급한 바와 같이 저장 프로시저들은 컴파일 과정을 거칩니다. 즉, 요청된 작업을 수행하기 위해 컴퓨터가 사용하는 명령어 세트는 작업이 수행되는 플랫폼에서 사용될 수 있는 가장 낮은 수준까지 분류됩니다. 속도를 위해, 요청이 있을 때마다 실행되는 쿼리와 데이터베이스 유지작업을 생성하도록 할 수도 있지만, 이럴 경우에는 SQL 서버에서 필요 이상의 많은 작업을 해야만 합니다.

저장 프로시저를 사용하고 생성하면 재사용이 가능한 작업을 중앙에서 관리할 수 있는 능력을 포함하여, 많은 혜택을 얻을 수 있습니다. Microsofta SQL Server 최신판에는 바로 이러한 시나리오를 위해 여러 개의 패키지화된 저장 프로시저가 포함되어 있습니다.

본 문서에서는 데이터베이스의 생성 및 관리에 중점을 두어 몇 가지 예를 토론해 보기로 하겠습니다. 또한, Query Analyzer(Microsoft SQL Server 7.0과 같이 제공됨) 내에 있는 패키지화된 저장 프로시저들을 사용하고, 이 순서들을 원격 호출하는 방법도 살펴보도록 하겠습니다.

 SQL Server에서 제공되는 프로그래밍기능이라고 할 수 있다. 

  어떠한 동작을 일괄처리하기 위한 용도로 사용된다. 어떤 특정 쿼리를 모듈화시켜 

  필요할 때 마다 호출하여 사용하는 것으로, 데이터베이스 개체에 속한다. 

 1) SQL Server 성능을 향상시킬 수 있다. 

  - 저장프로시저는 처음 시작되면 최적화, 컴파일 등의 과정을 거치고 그 결과가 

      캐시(메모리)에 저장된다. 그 후에는 캐시에 있는 것을 가져 사용하여 실행속도가 

      빨라진다.  반복되는 쿼리의 경우 저장프로시저를 사용하면 SQL Server의 

      성능을 향상시킬 수 있다. 




2) 모듈식 프로그래밍이 가능하다. 

  - 한번 저장으로 언제든지 실행 가능하고,  저장 되어진 쿼리의 관리가 수월해진다. 

      다른 모듈식 언어와 동일한 장점을 갖는다. 




 3) 보안을 강화할 수 있다. 

  - 사용자에게 테이블권한을 주지 않고 저장프로시저 접근권한을 줌으로써 보안을 

      강화할 수 있다. 




 4) 네트워크전송량을 감소시킬 수 있다. 

  - 긴 코드의 쿼리의 경우 서버로 쿼리의 텍스트가 모두 전송되어야한다. 하지만 

      이 쿼리를 서버에 저장프로시저로 생성해 놓았다면, 단지 저장프로시저이름,  

      매개변수 등 몇 글자만 전송되면 된다. 




※ 우리가 만들어쓰는 저장프로시저가 T-SQL 저장프로시저에 속하며, 생성 시 이름  

    앞에 usp_를 붙여 저장프로시저임을 구분 가능하도록 하자. 

※ 확장 저장프로시저라는 것이 있지만, 추후 없어질 것이므로, CLR 저장프로시저라는 

    강력한 기능을 가진 것을 사용하는 것이 좋다.(어려운 것이므로 추후나 공부) 




9. T-SQL과 저장프로시저 비교 

 1)T-SQL 과 저장프로시저의 작동방식 

  ① T-SQL 

    최초에구문분석에서실행까지의여러과정이실행되고, 메모리에 올라간다. 

    같은 구문을 재실행하면 작동시간이 아주 단축된다. 




  ② 저장프로시저 

    Create과정에서 실행하면 구문분석과 시스템테이블에 등록과정,  처음 실행하면 

    구분분석은 필요없고 나머지과정이 실시되고, 두 번째 실행부터 캐시에 것을 사용 

    하게 되어 속도가 빠르다. 




 ⇒ 즉,  두 개의 차이가 크게 없이 느껴질지도 모르나, 변수 즉 검색 등의 조건이 

    바뀌게 되면 T-SQL은 컴파일 과정을 전부 다시 실행하게 되지만, 저장프로시저 

    경우 바뀐 변수부분을 제외하고는 이미 컴파일이 이미 완료된 상태이다. 그러므로 

    실제 다양한 값이 입력되고 조건이 바뀌는 것이 사용되므로 저장프로시저가 효율적 

      이다. 

  


10. 저장프로시저의 단점 

 1) 단점 

  보통 인덱스를 사용하면 결과를 찾는 것이 빨라진다. 하지만, 가져올 데이터 건수가 

  많을 경우 인덱스를 사용하는 것이 오히려 성능을 나쁘게 한다. 

  그러므로 저장프로시저는 최초에 최적으로 한번만 컴파일이 되기 때문에 처음 작은 

  데이터를 사용하면 인덱스를 사용하게 되고, 후에 변수의 값이 커지게 되어도 

  인덱스를 사용하게 되어 성능에 좋지 않게 된다. 반대로도 마찬가지인데 처음에 

  많은 데이터를사용하고, 후에 적은 것을 쓰면 인덱스를 사용하지 않게 되어 

  성능에 좋지 않다. 




 2) 해결 방법 

  이러한 문제를 해결하기 위해 4가지방법을 생각할수있다. 

    ① 실행시에WITH RECOMPILE 를붙인다. 

        (기존에컴파일된상태를알아야한다) 

    ② EXEC sp_recompile 이름 

        EXEC 이름변수 

        (해댱하는 테이블이 이번 한번 다시 컴파일한다.) 

    ③ DBCC FREEPROCCACHE; 

        (캐시를 모두비운다.) 

        (다시 실행시 재컴파일하게 된다). 

    ④ 저장프로시저 사용시 마다 재컴파일되도록 설정한다. 

        (시작부분에 저장프로시저삭제부분을 기재)