SQL 쿼리 성능 개선의 팁 21개와 23개

SQL 쿼리 성능 개선의 팁

실천 중이거나, 개선이 필요한 사항.

1. 가능하면 커서(Cursor)를 피하라. 

2. 커서를 피할 수 없다면, 임시 테이블(temp table)을 사용하라 

3. 임시 테이블을 현명하게 사용하라 

7. 스칼라(Scalar) 대신 테이블 반환 함수(Table-Valued Functions)를 사용하라

14. 커다란 트랜잭션은 작은 트랜잭션 여러 개로 쪼개라 

15. 트리거(Trigger) 사용을 자제하라

17. 테이블에 있는 모든 것을 카운트(Count) 하지 말라 

18. 행을 카운트하려면 시스템 테이블(System Table)을 사용하라 

19. 필요한 수의 열만 끌어오라 

20. 네거티브 검색(Negative Search)를 피하기 위해 쿼리를 재 작성하라 

23. Avoid using the keyword DISTINCT 

 

원출처: https://www.javaworld.com/article/3209906/21-rules-for-faster-sql-queries.html

번역 출처: http://www.itworld.co.kr/print/105792 

제목: 더 빠른 SQL 쿼리를 위한 21가지 데이터베이스 튜닝 규칙

 

√1. 가능하면 커서(Cursor)를 피하라.
커서는 일련의 데이터에 순차적으로 액세스 할 때 검색 및 현재 위치를 포함하는 데이터 요소를 말한다. 커서를 피하는 것은 아주 쉬운 결정이다. 커서는 속도 문제를 겪을 뿐 아니라, 다른 작업을 필요 이상 지연시킬 정도로 하나의 작업을 블록(Block)시킬 수도 있다. 이는 시스템의 동시성을 크게 저하시킨다.

√2. 커서를 피할 수 없다면, 임시 테이블(temp table)을 사용하라
커서를 사용해야만 할 때가 있다. 그런 경우, 라이브 테이블(Live Table)보다는 임시 테이블에 대한 커서 작업을 수행하는 것이 더 낫다. 훨씬 더 작은 라이브 테이블에 대한 하나의 UPDATE 문이 있을 수 있다. 짧은 시간 동안에만 잠금(Lock)을 유지하게 되어 동시성을 크게 증진시켜 준다.

 

√3. 임시 테이블을 현명하게 사용하라
다른 여러 가지 상황에서도 임시 테이블을 사용할 수 있다. 예를 들어, 어떤 테이블을 더 큰 테이블에 조인(Join) 시켜야만 한다면, 더 큰 테이블에서 필요한 일부 데이터만 임시 테이블로 끌어(Pull) 와서 대신 그것과 조인시킴으로써 성능을 개선할 수 있다. 이는 필요한 처리 능력을 크게 줄여주며, 프로시저에 같은 테이블에 대해 유사한 조인을 해야만 하는 여러 개의 쿼리가 있는 경우 유용하다.

4. 데이터를 미리 준비하라
흔히 간과되는 예전의 기법이다. 커다란 테이블에 대해 비슷한 조인 작업을 할 보고서(Report)나 프로시저가 있다면, 미리 테이블을 조인시키고 테이블들을 하나의 테이블에 영속화(Persisted)시킴으로써 데이터를 사전 준비하라. 그렇게 하면, 사전 준비된 해당 테이블에 대한 보고서 작업을 실행할 수 있어서, 대규모 조인 작업을 피할 수 있다.

항상 이 기법을 사용할 수는 없지만, 대부분의 환경에는 늘 조인되는 인기 테이블이 있기 마련이다. 이런 테이블들을 사전에 준비하지 못할 이유가 전혀 없으며, 서버 자원을 절약하기 위한 훌륭한 방법이다.

5. 복합 뷰(Nested View)를 최소화하라
뷰는 엄청난 쿼리를 사용자들로부터 가리는데 훌륭하지만, 하나의 뷰 안에 또 다른 뷰와 내부에 있는 다른 뷰를 (계속해서) 중첩시키다 보면 심각한 성능 저하를 유발할 수 있다. 너무 많은 수의 복합 뷰는 모든 쿼리에 대해 엄청난 양의 데이터가 반환(Return) 되는 결과를 초래해서, 데이터베이스 성능을 말 그대로 기어 다니게 만들 수 있다. 혹은, 더 나가서, 쿼리 최적 화기(Optimizer)가 포기해서 아무것도 반환되지 않을 수도 있다.

복합 뷰를 풀어내는 것으로 쿼리 응답 시간을 몇 분에서 몇 초로 줄일 수 있다.

√6. UPDATE 문 대신 CASE 문을 사용하라
다음 시나리오를 살펴보자. 임시 테이블에 데이터를 삽입하고 있으며 다른 값이 존재할 경우 해당 데이터가 특정 값을 표시하도록 해야 한다. Customer 테이블에서 데이터를 끌어오고 있으며 주문 액수가 100,000달러 이상인 고객에 대해서 “우대”라는 라벨을 붙이고 싶어한다고 하자. 그래서, 100,000달러 이상의 주문 금액을 보유하고 있는 모든 고객에 대해서 CustomerRank 열에 “우대”라고 설정하기 위해 테이블에 데이터를 삽입하고 UPDATE 문을 실행한다.

논리적으로 보인다, 그렇지 않은가? 문제는 UPDATE 문이 로그된다는 것이다 즉, 테이블에 대한 모든 한 번의 쓰기 작업 당 두 번의 쓰기 작업이 일어난다는 의미이다. 물론, 이 문제를 피하는 방법은 SQL 쿼리 자체에서 인라인(Inline) CASE 문을 사용하는 것이다. 이는 모든 행에 대해 주문량 조건을 확인하고 테이블에 쓰기 전에 “우대” 라벨을 설정한다. 성능 증가는 깜짝 놀랄 정도이다.

√7. 스칼라(Scalar) 대신 테이블 반환 함수(Table-Valued Functions)를 사용하라

Convert scalar functions into table-valued functions

전문가들이 사용하는 팁이 있다. 쿼리의 SELECT 목록에서 스칼라 함수를 사용할 경우, 그 대신에 쿼리에서 테이블 반환 함수를 사용하고 CROSS APPLY 문을 사용하면 성능을 개선할 수 있다. 이는 쿼리 시간을 절반으로 대폭 줄여줄 수 있다.


√8. SQL 서버에서 분할(Partition)을 활용하라
SQL 서버 엔터프라이즈 사용자들은 성능을 가속화하기 위해 데이터 엔진의 자동 분할 기능을 활용할 수 있다. SQL 서버에서는 간단한 테이블조차도 하나의 분할로 생성되며, 사용자는 나중에 그것을 필요에 따라 여러 개의 분할로 쪼갤 수 있다. 테이블 간에 많은 양의 데이터를 옮겨야 할 경우, INSERT와 DELETE 문 대신에 SWITCH 명령을 사용할 수 있다. 테이블 간에 많은 양의 데이터를 삭제하고 삽입하는 대신, 단일 테이블에 대한 메타데이터만 변경하는 것이기 때문에, 실행하는데 몇 초 밖에 걸리지 않는다.

9. 배치 모드로 삭제(Delete)와 갱신(Update) 작업을 하라
거대한 테이블에서 많은 양의 데이터를 삭제하거나 업데이트하는 작업은 악몽일 수 있다. 문제는 이 두 가지 명령문 모두가 하나의 트랜잭션으로 실행되는 것이며, 프로세스를 중지시켜야 한다거나 작업 도중에 어떤 일이 일어진다면, 시스템은 전체 트랜잭션을 복원(Roll Back)시켜야만 한다. 이 작업은 진행 중인 다른 트랜잭션들을 블록 시킬 뿐 아니라, 많은 시간이 걸릴 수 있어서, 기본적으로 시스템 병목을 일으킨다.

해결책은 작은 배치 단위로 삭제나 업데이트 작업을 하는 것이다. 트랜잭션이 중지돼도, 소수의 행만 복원하면 되므로, 데이터베이스는 훨씬 더 빨리 온라인으로 돌아온다. 그리고 더 작은 배치작업들이 디스크에 커밋(Commit)하는 동안, 다른 작업들이 끼어들어서 어느 정도의 작업을 할 수 있어서 동시성이 크게 개선된다.

10. 서두르지 말고 천천히 하라
일부 개발자들은 이런 삭제와 업데이트 작업이 같은 날 완료되어야만 한다는 사실을 머릿속에 새겨놓고 있다. 늘 그런 것은 아니다. 특히, 아카이빙 작업은 더욱 그렇지 않다. 이 작업은 필요한 만큼 늘일 수 있으며, 이 작업을 완료하는 데는 더 작은 배치작업들이 도움이 된다. 이런 집약적인 작업을 더 천천히 할 수 있다면, 여분의 시간을 시스템이 다운되지 않도록 하는 데 투여하기 바란다.

11. ORM을 피하라
ORM(Object-relational Mapper: 객체 관계형 매퍼)는 지구상에서 최악의 코드를 만들어 내고 있으며, 개발자가 직면할 가능성이 있는 대부분의 성능 문제에 책임이 있다. 그렇지만, ORM을 피할 수 없다면, 스스로 자체적인 저장 프로시저를 작성하고 ORM이 자체 쿼리를 작성하는 대신 사용자가 작성한 쿼리를 호출하게 함으로써 부정적인 측면을 최소화할 수 있다.

12. 가능한 경우, 저장 프로시저(Stored Procedure)를 사용하라
더 훌륭한 코드로 이끄는 것 외에, 저장 프로시저는 다른 많은 장점도 가지고 있다. 저장 프로시저는 호출이 더 짧을 것이기 때문에, 트래픽을 크게 줄여준다. 프로파일러(Profiler) 같은 도구를 사용해서 추적하기가 더 쉬워서 사용자가 성능 통계치를 확보하고 잠재적인 문제를 더 빨리 규명할 수 있게 해 준다. 더욱 일관성 있는 방식으로 정의할 수 있으며, 이는 실행 계획(Execution Plan)을 재사용할 가능성이 더 높으며, 임의 쿼리에 비해 에지 케이스(Edge Case)와 감사용으로 사용하기가 더 쉽다는 의미이다.

많은 닷넷 코더들은 비즈니스 로직이 데이터베이스가 아닌 애플리케이션의 프론트 엔드에 속한다고 믿고 있다. 그렇지만, 그들은 틀렸다(대부분의 경우).

13. 더블 디핑(Double-Dipping: 중복 처리)을 피하라
저장 프로시저 사용은 때로 “더블 디핑”으로 이어질 수 있다. 대규모 테이블에 대해 별개의 쿼리를 여러 개 실행하고, 그것들을 임시 테이블에 넣은 다음에, 테이블들을 다시 조인하는 것이다. 이는 성능에 커다란 방해물이 될 수 있다. 가능한 한 대규모 테이블을 한 번만 쿼리 하는 것이 훨씬 더 낫다.

조금 다른 시나리오는 한 프로세스의 몇 가지 단계에서 커다란 테이블의 일부가 필요한 경우로, 이는 매 번 커다란 테이블에 대한 쿼리를 유발한다. 일부에 대한 쿼리를 실행하고 그것을 다른 곳에 영속화 시킨 다음에, 후속 단계를 영속화된 더 작은 데이터 세트로 유도하라.

14. 커다란 트랜잭션은 작은 트랜잭션 여러 개로 쪼개라
단일 트랜잭션에서 여러 개의 테이블을 처리하는 작업은 해당 트랜잭션이 끝날 때까지 모든 테이블을 잠글 수 있기 때문에, 다수의 블로킹으로 이어진다. 해결책은 이 트랜잭션을 각각이 개별적으로 단일 테이블에 대한 작업을 하는 여러 개의 루틴(Routines)으로 쪼개는 것이다. 이는 블로킹 횟수를 줄여주고 다른 작업들이 계속해서 이루어질 수 있도록 다른 테이블들을 풀어준다.

15. 트리거(Trigger) 사용을 자제하라
하려고 하는 작업이 무엇이든, 원래 작업의 동일한 트랜잭션에서 수행될 것이기 때문에 트리거 사용도 비슷한 문제로 이어질 수 있다. 이는 트리거가 완료될 때까지 여러 개의 테이블을 잠그는 결과를 초래할 수 있다는 의미이다. 이런 트리거를 별개의 트랜잭션들로 쪼개면 더 적은 수의 자원을 잠그게 돼서 필요한 경우 변경사항 복원을 쉽게 만들어준다. 가능하면 트리거를 피하라.


16. GUID에 대한 클러스터링을 피하라
테이블 데이터 정렬을 위해 GUID(Globally Unique Identifier: 범용 고유 식별자)를 사용하지 말라. 임의로 생성되는 이런 16비트 숫자는 사용자의 테이블을 훨씬 더 빨리 파편화한다. DATE나 IDENTIFY 같은 값을 점진적으로 증가시켜서 데이터를 정렬하는 것이 훨씬 낫다. 휘발성 있는 모든 열에 대해서도 갖은 규칙이 적용된다. 단 몇 분 만에 극적으로 테이블들이 파편화될 수도 있다.

17. 테이블에 있는 모든 것을 카운트(Count)하지 말라
테이블에 데이터가 존재하거나 어떤 고객에 대한 데이터가 존재하는 지를 확인할 필요가 있으며, 확인 결과에 따라, 어떤 조치를 취해야 한다고 가정하자.

필자는 그런 데이터의 존재를 확인하기 위해 누군가가 SELECT COUNT(*) FROM dbo.T1 명령을 실행하는 것을 자주 보았다.

SET @CT = (SELECT COUNT(*) FROM dbo.T1);
If @CT > 0
BEGIN 
END

전혀 불필요한 명령이다. 존재 여부를 확인하고 싶다면, 다음과 같이 하라:

If EXISTS (SELECT 1 FROM dbo.T1)
BEGIN

END

다른 말로 하면, 테이블에 있는 모든 것을 카운트하지 말라는 것이다. 첫 번째 행으로 돌아가면 찾을 수 있다. SQL 서버는 EXIST 문을 제대로 사용할 수 있을 정도로 똑똑하며, 두 번째 블록의 코드는 아주 빠르게 결과를 돌려준다. 테이블이 크면 클수록, 더 많은 차이를 낼 것이다.

18. 행을 카운트하려면 시스템 테이블(System Table)을 사용하라
커다란 테이블의 행을 정말로 카운트할 필요가 있다면, 시스템 테이블에서 끌어 올 수 있다. ‘SELECT rows from sysindex’ 명령문은 모든 인덱스에 대한 열의 수를 알려줄 것이다.

그리고 클러스터된 인덱스가 데이터 자체를 나타내기 때문에, ‘WHERE indid = 1’을 추가하면 테이블 행을 얻을 수 있다. 그다음에는 그냥 테이블 이름을 추가하기만 하면 만사형통이다. 이렇게 하면, 최종 쿼리는 다음과 같다

SELECT rows FROM sysindexes WHERE object_name(id) = ‘T1’ AND indexid = 1

19. 필요한 수의 열만 끌어오라
열을 개별적으로 나열하는 대신 모든 쿼리를 SELECT * 명령문으로만 코딩한다면 너무 쉬울 것이다. 또 다시 문제는 필요한 것보다 더 많은 데이터를 끌어 온다는 것이다. 개발자가 120개의 열과 수 백만 개의 행을 가지고 있는 테이블을 대상으로 SELECT *를 실행하고는, 겨우 3~5개만 사용하고 말았다. 그 시점에, 개발자는 필요한 것보다 훨씬 더 많은 데이터를 처리시켰을 뿐만 아니라 다른 프로세스들로부터 자원을 뺏어가기도 한 것이다.

20. 네거티브 검색(Negative Search)를 피하기 위해 쿼리를 재 작성하라
인덱스를 사용할 수 없는 쿼리를 사용해서 데이터를 행 별로 비교할 필요가 있을 때, 예를 들어 FROM Customers WHERE RegionID <> 3 같은 경우는 인덱스를 사용할 수 있도록 쿼리를 재작성하는 것이 더 낫다.

SELECT * FROM Customers WHERE RegionID < 3 UNION ALL SELECT * FROM Customers WHERE RegionID

데이터 세트가 큰 경우, 인덱스를 사용하는 것이 테이블 스캔 버전을 크게 능가하는 결과를 내놓을 수도 있다. 물론, 더 열악한 결과를 낼 수도 있으니 구현에 앞서 시험해보라.

필자는 이 쿼리가 팁 13번(중복 처리를 피하라)을 어긴다는 것을 알았지만, 융통성 없는 규칙은 없다는 것을 보여주는 것이기도 하다. 여기서는 중복 처리를 했지만, 대가가 큰 테이블 스캔을 피하기 위해서이다.

21. 맹목적으로 코드를 재사용하지 말라
필요한 데이터를 끌어온다는 것을 알기 때문에 다른 누군가의 코드를 복사하기가 십상이다. 문제는 종종 필요한 것보다 훨씬 더 많은 데이터를 끌어오고 있으며, 개발자들이 양을 줄이려 하는 경우는 거의 없어서, 거대한 데이터 상위 집합에 이르고 만다. 이는 대개 추가적인 외부 조인(Outer Join)이나 WHERE 문에서 추가 조건 형태로 나타난다. 재사용된 코드를 꼭 필요한 수준으로 줄일 수 있다면 커다란 성능 이득을 볼 수 있다.

이런 기법들 모두가 모든 상황에서 작동하지는 않는다는 것만 명심하라. 어떤 기법이 가장 잘 동작하는지를 알기 위해 실험을 해야만 할 것이다. 그렇지만, 일반적으로 언급한 SQL 팁들을 잘 사용하면 동시성을 증가시키고, 성능을 가속화시키며, DBA부터 최종 사용자들까지, 모든 사람의 삶을 훨씬 더 쉽게 만들어 줄 것이다.

유사 영문자료 ; https://www.freelancer.com/articles/web-development/how-to-make-your-sql-queries-faster

유사 영문자료 제목 : Below are 23 rules to make your SQL faster and more efficient

Below are 23 rules to make your SQL faster and more efficient
1. Batch data deletion and updates
When you are deleting or updating your data, use as small batches as you possibly can. This will avoid loss or killing of your data in case there is a rollback. Working on smaller batches also enhances concurrency, as data other than the part you are deleting or updating can continue doing other work.

2. Use automatic partitioning SQL server features
Features of the data engine’s automatic partitioning is a big advantage to SQL Server Enterprise users. Split single partitions into multiples to move larger data amounts, by using SWITCH instead of DELETE and INSERT. This is because instead of inserting and deleting large data amounts, you are only changing the metadata. This takes a very short time.

3. Convert scalar functions into table-valued functions
Convert your scalar function to a table-valued function in order to make the performance faster, and reduce the time used.

4. Instead of UPDATE, use CASE
In the SQL query, an UPDATE statement writes longer to a table than a CASE statement, because of its logging. An inline CASE statement chooses what is preferred before writing it on the table, thus increasing the speeds.

5. Reduce nested views to reduce lags
Performance lags come about due to nesting views that are inside other views, which are also inside other views and so on. This nesting causes too many data returns for every single query, which either makes the database crawl, or completely give up and give no returns. Minimizing nesting will significantly improve speeds.

6. Data pre-staging
Data pre-staging means you can have your reports faster. Join data tables ahead of reporting, presentation or writing time to avoid joining them together at once.

7. Use temp tables
Temporary tables come in handy in several situations, especially when you are joining a small table to a larger one. Improve data performance by taking any data needed out of the large table, transferring it to a temp table and join with that. This reduces the power required in processing.

임시 테이블은 여러 상황에서 특히 작은 테이블을 더 큰 테이블에 조인할 때 유용합니다. 큰 테이블에서 필요한 데이터를 가져와서 임시 테이블로 전송하여 조인하여 데이터 성능을 향상합니다. 이는 처리에 필요한 전력을 줄입니다.

8. Avoid using re-use code
When you use another person’s code, chances are you might pull more data than you really need. Cutting this data down may be hard, but if you trim a re-used code to your needs, there is no reason for ending up with huge data clusters.

9. Avoid negative searches

Nothing slows data as much as carrying out negative searches. To avoid this, rewrite your queries with better indexes, especially for large amounts of data.

10. Avoid cursors
It is always best to avoid cursors, because they can slow you down. If you can’t avoid them make use of temp tables to help save time and increase speed.

11. Use only the correct number of columns you need
When you code all queries with SELECT, you pull off more data than you need. Before doing a SELECT, make sure you have the correct number of columns against as many rows as you want. This will speed up your processes.

Find out how to make your website faster

12. Count your rows using the system table
If you need to count your rows, make it simple by selecting your rows from sysindexes.  Below is the best way to add rows to your table.

“SELECT rows FROM sysindexes WHERE object_name (id) = â€˜T1’ AND indexid = 1”

13. Don’t count everything in the table
If you need to check that some data exists, you will need to carry out an action. People often try this:

SET @CT = (SELECT COUNT (*) FROM dbo.T1);If @CT > 0BEGIN END

This is unnecessary and time wasting. The best action to take is:

If EXISTS (SELECT 1 FROM dbo.T1)BEGINEND

This helps you to avoid counting every item on the table. When you use EXIST, the SQL server recognizes it and acts, making faster returns.

If this still seems too hard to understand, do not hesitate to hire a professional to help you.

14. Do not use Globally Unique Identifiers (GUIDs)
To order table data, avoid using GUIDs as much as possible because they can easily cause very fast break off your table. Use IDENTITY or DATE for dramatic break off that will take only a couple of minutes.

 

15. Avoid triggers
It is not necessary to use triggers, as whatever you plan on doing to your data will go through the same transactions as the previous operations. If you go ahead and use triggers, you could lock several tables until the trigger completes its cycle. Split the data into different transactions to lock up just a few resources, making the transactions go faster.

16. Separate large and small transactions
If you handle several tables in one transaction, you might lock them all until your transaction is complete. Avoid blocking off transactions by breaking them into several routines, with each routine operating singularly at a time. This will reduce the amount and number of blocks, and will free up tables for operations to continue taking place.

17. Do not double dip
Double dipping is running different queries on tables and later putting the queries on temp tables, then joining the large tables and temp tables together. This takes a huge toll on performance. The best thing to do would be to query only the large tables once.

18. Whenever you can, use stored procedures
Stored procedures have so many advantages that make your work easier, and writing queries faster. They slow down traffic because with stored procedures, calls become shorter. If you use profiler, and other tools that allow you to identify statistics concerning performance, it gets easier to trace. When you use stored procedures, you can use your plans of execution repeatedly.

19. Avoid ORM
Object-Relational Mappers (ORMs) give the worst codes in the world of technology today. These poor codes are responsible for many bad performances you come across in your daily encounters. If you are not able to completely avoid them, the best you can do is minimize them by writing stored procedures that are completely your own, and have ORM use yours instead of those it creates.

20. Go slow
Do not ever assume you have to complete every task of updating and deleting in one single day. This is wrong, especially archiving data. Take your time and work on the operation for as long as you need, while making use of the small batches. Working too quickly to finish the work only slows down your queries, and this might bring down your systems.

21. Use cursors less
Cursors cause many problems, especially to speed. Besides low speed, they can also cause blockages where one operation leads to the blockage of other operations. This can last for longer than expected and it affects your systems concurrency, slowing everything down.

22. Use AWR and ADDM
As queries get older from too much use, their performance tends to worsen. These uses are from upgrades, structural changes, database changes and applications. To understand these changes better and learn about the plan of execution, use Automatic Database Diagnostic Monitor (ADDM) and automatic workload repository (AWR). This will help to increase the speed of the queries over a period.

23. Avoid using the keyword DISTINCT
If you can reach all your objectives in other ways, avoid using the keyword DISTINCT as much as possible. When you use DISTINCT you incur an extra operation, which slows all the queries down and makes it almost impossible to get what you need.

다른 방법으로 모든 목표를 달성 할 수 있으면 DISTINCT 키워드를 최대한 사용하지 마십시오. DISTINCT를 사용하면 추가 작업이 발생하여 모든 쿼리 속도가 느려지고 필요한 것을 얻는 것이 거의 불가능합니다.