'mssql'에 해당되는 글 6건

--첫번째 줄 일자는 시작 일자

--마지막 줄 일자는 시작일자와 종료일자

SELECT CONVERT(char(10),DATEADD(d,number,'2018-01-01'),120) DT, CONVERT(char(8),DATEADD(d,number,'2017-12-01'),112) DT2
FROM master..spt_values
WHERE type = 'P' and number <= DATEDIFF(d,'2018-01-01','2018-12-31')

use db명

go

declare @sql as nvarchar(100);
declare @tbname as nvarchar(100);
declare c cursor fast_forward for
select [name] from sysobjects where xtype ='u' or xtype ='p' order by name;
open c;
fetch next from c into @tbname;

while @@fetch_status=0
begin
 set @sql='alter schema dbo transfer 스키마명.'+@tbname;
 exec(@sql);
 fetch next from c into @tbname;
end
close c;
deallocate c;

1. 유명한 NOT IN 기법.

초반 페이지는 큰 문제가 없으나, 뒤쪽으로 갈수록 많은 데이터를 NOT IN 처리 해야 하기때문에 문제가 많아진다.

SELECT TOP (@PAGESIZE) SEQ, THREAD, DEPTH, WRITER, TITLE, READCOUNT, WRITEDATE FROM BLUE_BOARD
WHERE THREAD NOT IN
(SELECT TOP (@PAGESIZE * (@PAGE-1)) THREAD FROM BLUE_BOARD ORDER BY THREAD DESC)
ORDER BY THREAD DESC

기본형태

첫페이지 끝페이지(50000)

4 ????(엄청나게 느림)

검색과 정렬 추가시

첫페이지 끝페이지(50000)

50 35000

정적으로 검색,정렬 하였을때

첫페이지 끝페이지

10 8000 (검색실패시 3000)

글을 수정하였다 역시 잘못되었다.

무지무지무지 느리다.

----------> 해당 쿼리를 동적. 쿼리로 제작하였을 경우 마지막 페이지가 1초가 걸리는 괴?상한 현상이 계속 목격되고 있다.

----------> 검색시 4초 가량이 소요되었고 검색 실패시에도 2초 가량이 소요되었다 -----> 동적쿼리는 일반적으론 나쁘다고 하는데(물론 좋은 경우도 있겠지만) 100만건 기준으로 정말로 1~2초라면.... 사용해도 되지 않을까

2. IN 기법.

NOT IN의 문제를 개선했다고 한다.

하지만 역시나 후반페이지에 문제가 많다고 한다. 테스트 해보진 않았다.

3. MIN 기법

MIN을 이용한 기법.

SELECT TOP (@PAGESIZE) SEQ, THREAD, DEPTH, WRITER, TITLE, READCOUNT, WRITEDATE
FROM BLUE_BOARD
WHERE THREAD <=
(
SELECT MIN(THREAD)
FROM
(
SELECT TOP (((@page-1)*20)+1) THREAD
FROM BLUE_BOARD
ORDER BY THREAD DESC
) AS A
)
ORDER BY THREAD DESC

기본형태

첫페이지 끝페이지(50000)

5 200

검색과 정렬 추가시엔 테스트를 해보지 않았다.

4. 인덱스를 이용한 기법

빠르다고 한다. 그러나 페이징의 헛점이 많다고 한다.

5. ROW_NUMBER()를 이용한 기법중. 하나

2005부터 사용 가능하다.

특이사항으로는 총 카운트 T2.CNT 와, 라스트페이지를 CEILING(T2.CNT/@PAGESIZE) AS 'LASTPAGE'

테이블에 같이 집어넣는다는것이다. 좀더 개선할수 있을듯 하다.

속도는 좋을것같아 보인다.

SELECT * FROM
(
SELECT
(
ROW_NUMBER() OVER(ORDER BY THREAD DESC)
) AS ROWNUM,*
FROM BLUE_BOARD
) AS A
WHERE A.ROWNUM BETWEEN ((@PAGE-1)*@PAGESIZE)+1 AND @PAGE*@PAGESIZE

기본형태

첫페이지 끝페이지

5 1400

검색과 정렬 추가시

첫페이지 끝페이지

80000 80000

유명한 ROWNUM 기법 치고는 생각보다 느리다. 어째성?;;;;

뭐지? 내 테스트 쿼리가 잘못된것인가? ㅋㅋㅋㅋㅋ

정적으로 검색,정렬 하였을때

첫페이지 끝페이지

8000 12000 (검색 실패시 3000)

정도가 소요되었다.

생각보다 안좋다-_-;

6. MAX,MIN 조합기법

위의 3번 쿼리인 MIN쿼리와 흡사하다

해당 방법은 쿼리가 길다는 문제이다.(쿼리가 복잡해지면 아래 쿼리도 그만큼 복잡해진다)

DECLARE @MAXCNT INT
,@MINCNT INT

SELECT @MINCNT = (ISNULL(MIN(THREAD),0)),
@MAXCNT = (ISNULL(MAX(THREAD),0))
FROM
(
SELECT TOP(@PAGE * @PAGESIZE) THREAD
FROM BLUE_BOARD
ORDER BY THREAD DESC
) T

SELECT @MAXCNT = MIN(THREAD)
FROM
(
SELECT TOP(((@PAGE-1)*@PAGESIZE)+1) THREAD
FROM BLUE_BOARD
WHERE THREAD BETWEEN @MINCNT AND @MAXCNT
ORDER BY THREAD DESC
) T

SELECT TOP(@PAGESIZE) * FROM BLUE_BOARD
WHERE THREAD BETWEEN @MINCNT AND @MAXCNT
ORDER BY THREAD DESC

기본형태

첫페이지 끝페이지(50000)

10 290

꽤나 빠른데? 하지만 검색과 정렬을 넣는다면 좀 복잡해지는 단점이....

7. SELECT TOP 11 * BLUE_BOARD WHERE ID <= 마지막 번호 (ORDER BY DESC)

페이징이라고 하기엔 약간 꼼수?틱한 방법인데

TOP 11은 사실 글을 10개만 보여주는것이다 그리고 1은 다음 페이지의 첫 결과가 되는것이다

계속 11개씩 불러서 10개만 페이지에 뿌려주는 ㅎㅎㅎ

SELECT TOP 11 * FROM BLUE_BOARD ORDER BY THREAD DESC
SELECT TOP 11 * FROM BLUE_BOARD WHERE SEQ <= 11 ORDER BY THREAD DESC

동접사수 많고 글수가 어마어마 하다면 이방법이 제일일듯 하다.

그러나 이전페이지는... 어떻게 구현을 해야할지

8. CTE

이녀석도 결국은 ROW_NUMBER를 의존하는데

일종의 뷰? 같은것을 생성하여 그것으로 페이징한다고 생각하면 될듯하다.(물론 이게 아니지만...)

Set nocount on을 빼먹으면 아예 작동조차 하지 않는다 와이?

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH PAGELIST AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY THREAD DESC) AS ROWNUM
FROM BLUE_BOARD
)
SELECT * FROM PAGELIST
WHERE ROWNUM BETWEEN ((@PAGE-1)*@PAGESIZE)+1 AND @PAGE*@PAGESIZE

기본형태

첫페이지 끝페이지

2 1300

검색과 정렬 추가시

첫페이지 끝페이지

63000 ????

정적 검색과 정렬 추가시

첫페이지 끝페이지

1000 12000

별다른 조건이 없었을때는 제법 나쁘지 않은듯 싶다

저장 프로시저 sp_dbcmptlevel을 사용하여 현재 데이터베이스의 호환성 수준을 80 이하로 설정하십시오.

호환성 수준 확인
sp_dbcmptlevel 'db이름'

호환성을 80버전 수정
sp_dbcmptlevel 'db이름', 80