SQL Server 저장 프로시저를 작성하고 최적화하는 방법

[소개] 데이터베이스 개발 과정에서 복잡한 비즈니스 로직과 데이터베이스 작업이 자주 발생합니다. 이때 SP는 데이터베이스 작업을 캡슐화하는 데 사용됩니다. 프로젝트에 SP가 많고, 작성에 대한 일정한 기준이 없으면 향후 시스템 유지가 어려워지고, 대규모 SP의 논리를 이해하기 어렵게 됩니다. >

데이터베이스 개발 과정에서 복잡한 문제가 자주 발생합니다. 비즈니스 로직 및 데이터베이스 운영의 경우 이때 데이터베이스 운영을 캡슐화하는 데 SP가 사용됩니다. 프로젝트에 SP가 많고 작성이 표준화되지 않은 경우 향후 시스템 유지 관리가 어렵고 대규모 SP의 논리를 이해하기 어렵게 됩니다. 프로젝트 규모가 크거나 SP에 대한 높은 성능 요구 사항이 있는 경우 최적화 문제가 발생합니다. 그렇지 않으면 속도가 매우 느려질 수 있습니다. 개인적인 경험에 따르면 최적화된 SP는 성능이 낮은 SP보다 수백 배 더 효율적입니다. .

세부정보:

1. 개발자가 다른 라이브러리의 테이블이나 뷰를 사용하는 경우 라이브러리 간 작업을 구현하려면 현재 라이브러리에 뷰를 생성해야 합니다. "databse.dbo.table_name"입니다. 왜냐하면 sp_dependents는 SP에서 사용하는 데이터베이스 간 테이블이나 뷰를 표시할 수 없기 때문에 확인이 불편하기 때문입니다.

2. 개발자는 SP를 제출하기 전에 set showplan을 사용하여 쿼리 계획을 분석하고 자체 쿼리 최적화 검사를 수행해야 합니다.

3. 프로그램 운영 효율을 높이고 애플리케이션을 최적화하려면 SP 작성 과정에서 다음 사항에 주의해야 합니다.

(a) SQL 사용 사양 :

i. 대규모 트랜잭션 작업을 피하고 시스템 동시성을 향상시키기 위해 주의해서 Holdlock 절을 사용하십시오.

ii. 동일한 테이블, 특히 대량의 데이터가 포함된 테이블에 반복적으로 액세스하지 않도록 하세요. 먼저 조건에 따라 데이터를 임시 테이블로 추출한 후 연결하는 것을 고려해 보세요.

iii. 커서로 작동하는 데이터가 10,000행을 초과하는 경우 커서를 사용하면 커서 루프가 발생하지 않도록 하세요. 그런 다음 테이블 조인 작업을 수행합니다.

iv. where 단어 작성에 주의하세요. 조건절의 순서는 인덱스 순서와 범위 크기에 따라 결정되어야 합니다. 인덱스 순서 및 범위와 일치하는 순서입니다.

v. where 절의 "=" 왼쪽에서는 함수, 산술 연산 또는 기타 표현식 연산을 수행하지 마십시오. 그렇지 않으면 시스템이 인덱스를 올바르게 사용하지 못할 수 있습니다.

vi. 레코드가 존재하는지 확인하려면 select count(1) 대신 presents를 사용해 보십시오. count 함수는 테이블의 모든 행을 계산할 때만 사용되며 count(1)는 더 많습니다. count(*)보다 편리합니다.

vii. ">" 대신 ">="를 사용해 보세요.

viii. 일부 또는 절과 결합절 사이의 교체에 주의하세요.

ix. 테이블 간 연결의 데이터 유형에 주의하고 유형 간 차이를 피하세요. 데이터의.

x. 저장 프로시저에서 매개변수와 데이터 유형 간의 관계에 주의하세요.

xi. 다른 애플리케이션과의 충돌을 방지하려면 삽입 및 업데이트 작업의 데이터 양에 주의하세요. 데이터 양이 200 데이터 페이지(400k)를 초과하는 경우 시스템은 잠금을 업그레이드하고 페이지 수준 잠금은 테이블 수준 잠금으로 업그레이드됩니다.

(b) 인덱스 사용 사양:

i. 인덱스 생성은 애플리케이션과 함께 고려하는 것이 좋습니다. 대규모 OLTP 테이블은 6개의 인덱스를 초과해서는 안 됩니다.

ii. 인덱스 필드, 특히 클러스터형 인덱스를 사용하여 인덱스를 강제로 지정할 수 있습니다.

iii. 페어링을 피하세요. 대규모 테이블을 쿼리할 때는 테이블 스캔을 수행하고, 필요한 경우 새 인덱스 생성을 고려하세요.

iv. 인덱스 필드를 조건으로 사용하는 경우 인덱스가 결합 인덱스인 경우 시스템이 인덱스를 사용하는지 확인하기 위해 인덱스의 첫 번째 필드를 조건으로 사용해야 합니다. 그렇지 않으면 인덱스가 사용되지 않습니다.

v. 인덱스 유지 관리에 주의하고, 정기적으로 인덱스를 재구축하고 저장 프로시저를 다시 컴파일하십시오.

(c) Tempdb 사용 사양:

i. , 이러한 문은 tempdb에 대한 부담을 증가시키기 때문입니다.

ii. 임시 테이블을 자주 생성하고 삭제하지 말고 시스템 테이블 리소스 소비를 줄이세요.

iii. 임시 테이블을 생성할 때 한 번에 삽입되는 데이터의 양이 많은 경우 테이블을 생성하는 대신 선택을 사용하여 데이터 양이 많은 경우 로그를 방지할 수 있습니다. 규모가 크지 않으므로 시스템을 쉽게 하기 위해 테이블 ​​리소스의 경우 먼저 테이블을 생성한 후 삽입하는 것이 좋습니다.

iv. 임시 테이블에 데이터 양이 많아 인덱싱이 필요한 경우 임시 테이블 생성 및 인덱싱 과정을 별도의 하위 저장 프로시저에 배치하여 시스템에서는 임시 테이블의 인덱스를 사용하는 것이 더 좋습니다.

v. 임시 테이블을 사용하는 경우 저장 프로시저가 끝날 때 모든 임시 테이블을 명시적으로 삭제해야 합니다. 이렇게 하면 테이블이 장기간 삭제되는 것을 방지할 수 있습니다. 시스템 테이블 잠금.

vi. 이 작업은 하나의 명령문에서 tempdb 시스템 테이블을 여러 번 사용하므로 시스템 테이블의 부담을 줄이기 위해 대규모 임시 테이블과 다른 대규모 테이블 간에 쿼리 및 수정을 연결할 때 주의해야 합니다.

(d) 합리적인 알고리즘 사용:

위에서 언급한 SQL 최적화 기술과 ASE Tuning 매뉴얼의 SQL 최적화 내용을 바탕으로 실제 적용과 결합하여 여러 알고리즘을 비교하여 자원을 가장 적게 소모하고 가장 효율적인 방법을 구합니다. . 특정 ASE 조정 명령을 사용할 수 있습니다: set Statistics io on, set Statistics time on, set showplan on 등.

위 내용은 SQL Server 저장 프로시저를 작성하고 최적화하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!