'저장프로시저'에 해당되는 글 2건

  1. 2009.08.11 좋은 저장프로시저를 위한 21가지 팁 (2)
  2. 2007.10.11 [MSSQL] 엔터프라이즈 메니져의 접속시간을 줄여보자 (2)
컴퓨터2009.08.11 18:00



좋은 저장프로시저를 위한 21가지 팁

  1. 키워드
    SQL 명령어는 대문자로 알아보기 쉽게 표시한다.

  2. SQL-92
    항상 ANSI 92 를 사용하도록 노력해야 합니다.

  3. 변수
    가능한 최소한으로 사용하여 캐쉬 공간에 여유를 주어야합니다.

  4. 동적 쿼리
    최소한으로 사용하여야한다. 동적쿼리에 따라 재컴파일이 된다.

  5. 친숙한 전체 이름 사용
    database_name.schema_name.table_name 으로 사용을 해야 하며, CREATE PROCEDURE dbo.Your_Proc_name 처럼 사용해여 합니다.

  6. SET NOCOUNT OFF
    실행 결과 행은 네트워크 트래픽을 사용하게 되므로 주의해여 한다.

  7. sp_ 접두어를 사용하지 않는다
    시스템데이터베이스(MASTER) 와 사용자 데이터베이스에 같은 이름의 저장프로시져가 있게 되면 사용자 데이터베이스는 결코 실행되지 않는다.

  8. sp_executeSQL 과 KEEPFIXED PLAN 옵션
    sp_executeSQL 과 KEEPFIXED PLAN 옵션은 저장프로시저를 재컴파일합니다. 만약 동적 SQL 을 사용하려면 EXEC(proc_name) 대신에 sp_eecutesql 을 사용해야 합니다.

  9. SELECT 와 SET
    SELECT는 서로 다른 여러 변수를 사용할수 있습니다. SET 은 하나의 변수만을 사용합니다.

    SELECT @var1 = @var1 + 1, @var2 = @var2 - 1

    SET @var1 = @var1 + 1
    SET @var2 = @var2 - 1

  10. WHERE 연산자
    =, >, <, >=, <=, <>, !=, !>, ! 의 사용을 줄여야 합니다.
    SQL Server 2008 Books Online (July 2009) Operator Precedence (Transact-SQL)

  11. WHERE 연산자 추가
    쉽게 LOWER 등의 연산자를 이용해서 대소문자를 구별하곤 하는데, OR 명령을 이용하는 편이 보다 빠른 처리를 할수 있습니다.


    SELECT emp_name FROM table_name WHERE LOWER(emp_name) = 'edu'


    SELECT emp_name FROM table_name WHERE emp_name = 'EDU' OR emp_name = 'edu'


    또한 EXISTS  대신에 IN 연산자를 사용하면 보다 빠른 처리가 가능합니다


    SELECT * FROM employee WHERE emp_no NOT IN (SELECT emp_no from emp_detail)
    SELECT * FROM employee WHERE NOT EXISTS (SELECT emp_no FROM emp_detail)

  12. CAST 와 CONVERT
    CONVERT 보다 CAST 를 쓰도록 합니다. CAST 는 ANSI-92 지만 CONVERT 는 MSSQL 에서만 사용이 가능합니다. 다만 DATETIME 데이타타입의 변형에는 CONVERT 를 사용해야 합니다. CAST 는 안되거든요.

  13. DISTINCT 와 ORDER BY
    추가적인 데이터베이스작업이 요구되기 때문에 굳이 필요하지 않다면 사용하지 않는 것이 좋습니다. 성능상 좋지 않습니다.

  14. 커서 사용
    되도록 가상테이블이나 테이블을 이용하여야 합니다.

  15. SELECT 구문
    컬럼을 지정해야 합니다. 그렇지 않으면 모든 컬럼을 가져오게 되어서 레코드셋이 불필요하게 가득차게 됩니다.

  16. 서브쿼리 와 조인
    서비쿼리와 조인은 거의 비슷합니다. 추가설명 필요;;

  17. CREATE TABLE 와 SELECT INTO
    SELECT * INTO 는 작은 테이블을 만들기에는 좋지만 큰 레코드가 있거나, 오래걸리는 쿼리 테이블에는 많은 시간이 걸립니다.

  18. 임시테이블 대신에 사용자 테이블변수를 사용하라
    임시 테이블은 저장프로시저를 재컴파일하는 원인이 됩니다. 하지만 테이블 변수는 실행하는 동안 저장프로시저를 다시 설계합니다.
    추가 설명 필요;;

  19. 인덱스의 사용
    인덱스를 사용하면 보다 빠른 결과를 얻을수 있고, 작은 테이블보다는 큰 테이블에 효과적이다.

  20. 프로파일러를 사용하라

원문 : Twenty tips to write a good stored procedure


Posted by Zasfe
컴퓨터2007.10.11 12:00
데이터베이스 접속량이 많아지고 빠른 응답속도를 보이는 저장프로시져보다 직접 서버에서 쿼리를 보내고 응답을 받는횟수가 증가함에 따라 엔터프라이즈 메니져(Enterprise Manager. 이하 EM)로 접속하는 사용자로부터 응답속도가 느리고 때로는 접속은 가능하지만 데이터베이스 목록부분이 안보인다는 내용을 전달받았다.

가장 좋은 방법은 서버사양을 올리면 되는 것이지만, 돈이 든다는 가장 큰 단점이 있다.


그래서 EM의 접속시 가장 문제가 되는 데이터베이스 목록이 안보이는 현상을 중심으로 접속속도 증가를 위한 방법을 찾아보기로 하였다.

우선 프로필러로 EM 접속시 발생하는 쿼리를 캡춰하고, 하나하나 쿼리의 내용을 확인하였다.

상세한정보를 확인가능하지만 쿼리를 알고 있어야 사용이 가능한 쿼리분석기(query analyzer, 이하 QA) 를 이용해서 클라이언트측 응답속도의 기준이 되는 클라이언트통계를 바탕으로 쿼리 처리시간 을 체크하였다.

가장 많은 처리시간을 보이는 쿼리는 사용자가 접근가능한 모든 데이터베이스 목록을 가져오는 프로시져(sp_MShasdbaccess) 였다.
Microsoft 기술문서 KB88969 에 의하면 이 프로시져를 수정함으로써 많은 효과를 볼수 있다고 하며,
SQLER.PE.KR 에서 "데이터베이스의 권한이 있는 데이터베이스만 보기" 라는 이름으로 수정방법이 공개되어 있는 프로시져였다.

그래서 이 sp_MShasdbaccess 프로시져 를 수정하고 재차 테스트를 진행하였다.

하지만 의외로 접속 속도는 크게 줄지 않았다.

그러면서 알게된것이지만 EM은 화면에 표시하는게 많아서인지 일반 사용자에게는 쓸모없는 데이터베이스 복제 관련 정보도 가져오고 있는 부분이 상당수가 있었다.
물론 보안강화를 위해 일반사용자의 권한을 제한하고 있기때문에 해당쿼리의 응답은 항상 같은 코드(권한 없음에 해당하는 반환코드)를 응답하고 있었다.

단독으로 사용되는 서버이기때문에 복제부분의 응답코드를 하드코딩하기로 하였다.

결국 해당 부분을 수정하는것으로 실서비스에는 아무런 영향도 주지않았으면서 EM 의 접속속도가 1/3 로 줄어들었으며, 이후 EM 접속시 느리다다거나 목록부분이 안보인다거나 하는 문의가 줄어들었다.

역시 사람은 문제가 생겨야 생각을 하는것 같다.
Posted by Zasfe