database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
ㆍMS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MS-SQL Q&A 6702 게시물 읽기
No. 6702
쿼리문 질문입니다.
작성자
김민식(mspower)
작성일
2013-07-24 17:25ⓒ
2013-07-25 22:17ⓜ
조회수
9,041

판매 테이블 구조

 

고유번호 이름 값 날짜

1 홍길동 100 2013-07-11

2 김철수 110 2013-07-11

3 홍길동 200 2013-07-20

4 김철수 300 2013-07-20

.....

 

위 테이블에서 모든 회원의 마지막 거래 내역을 뽑고자 합니다.

아래 결과를 뽑아야 하는데...

GROUP BY 를 사용하니 모든 데이터(인덱스파일)를 퍼올리네요...

회원테이블은 따로 존재 합니다.

도움 부탁드립니다.

 

고유번호 이름 값 날짜

3 홍길동 200 2013-07-20

4 김철수 300 2013-07-20

 

set statistics profile on

로 결과를 보면 Rows 값이 전체 줄수로 나옵니다. 물론 Index Scan 이구요.

---------------------

 

select max(고유번호) from 판매테이블 group by 이름

 

하면 원하는 결과가 나오네요. 이때 위 질문처럼 set statistics profile on 사용시

Rows 값이 전체 줄수인데..

회윈이 위처럼 두 사람이라면, 대충 두줄정도가 나오게 할순 없나요?

 

 

이 글에 대한 댓글이 총 7건 있습니다.

무슨 인덱스가 어떻게 있는 지를 알아야....

우욱님이 2013-07-25 07:57에 작성한 댓글입니다. Edit

고유번호, 이름, 날짜에 인덱스가 있습니다. 필드수는 더 있는데, 생략하였구요.

데이터 건수는 천만, 억 ... 단위 입니다.

회원수가 30 명이고, GROUP BY 로 실행시 몇십초가 걸립니다.

김민식(mspower)님이 2013-07-25 16:26에 작성한 댓글입니다.
이 댓글은 2013-07-25 22:18에 마지막으로 수정되었습니다.

-- (이름 + 고유번호) 결합 인덱스가 있는 상태에서 다음과 같이
SELECT a.이름
     , (SELECT TOP 1 고유번호
          FROM 판매 b
         WHERE b.이름 = a.이름
         ORDER BY 고유번호 DESC
        ) 고유번호
  FROM 회원 a
;

마농(manon94)님이 2013-07-26 09:52에 작성한 댓글입니다.

마농님의 답변에서와 같이 

(이름, 고유번호)

인덱스가 있다면 그리 오래 걸리지 않을텐데요....

 

그래도 느리시고 운영 서버와 별도의 테스트 서버가 있으시다면

!!운영 중인 서버에서는 DBCC DROPCLEANBUFFERS;를 실행하지 마세요!!

SET STATISTICS IO ON
;
DBCC DROPCLEANBUFFERS
;
SELECT 이름, MAX(고유번호)
FROM 판매테이블
GROUP BY 이름
;
을 실행하신 후에 Messages 탭에 나오는 내용을 한 번 보여주세요.

 

우욱님이 2013-07-26 21:44에 작성한 댓글입니다. Edit

실제 데이터베이스 테이블명을 적어봅니다.

 

회원테이블 ---> RunData.dbo.Vehicle 여기서 회원이름에 해당하는 필드는 VehicleNumber 입니다. 이 필드에 고유키 인덱스가 잡혀있습니다. 이 테이블의 레코드 수는 30 입니다.

판매테이블 ---> PlayBack.dbo.pVehicle 마찬가지 여기에 VehicleNumber 이 있고, 고유번호 _Seq 가 있습니다. PK 는 RegTime(등록시각)+_Seq 로 잡혀있고, 별도로 _Seq 에도 고유인덱스가 잡혀 있습니다. 물론 VehicleNumber 에도 인덱스가 잡혀 있습니다. 이 테이블의 레코드수는 19,998,734 이네요.

 

마농님의 쿼리를 아래 처럼 작성하여

 

select a.VehicleNumber

,

(

select top 1 b._Seq

from PlayBack.dbo.pVehicle as b

where b.VehicleNumber = a.VehicleNumber

order by b._Seq DESC

) AS _Seq

from

RunData.dbo.Vehicle AS a

 

실행한 결과..ㅠ.ㅠ .... 언제 결과가 나오는지 뎃글 작성중에도 실행중이네요.

 

우욱님 말씀대로 아래와 같이 실행하니

 

set statistics io on

DBCC DROPCLEANBUFFERS

SELECT VehicleNumber, MAX(_Seq)

FROM PlayBack.dbo.pVehicle

GROUP BY VehicleNumber

 

다음과 같은 메시지가 나왔습니다.

 

DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

 

(12개 행이 영향을 받음)

테이블 'pVehicle'. 검색 수 9, 논리적 읽기 수 54879, 물리적 읽기 수 1, 미리 읽기 수 54613, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 

=================================================================

 

30 1 select a.VehicleNumber

 

,

(

select top 1 b._Seq

from PlayBack.dbo.pVehicle as b

where b.VehicleNumber = a.VehicleNumber

order by b._Seq DESC

) AS _Seq

 

from

RunData.dbo.Vehicle AS a 1 1 0 NULL NULL NULL NULL 30 NULL NULL NULL 1.312771 NULL NULL SELECT 0 NULL

 

0 0 |--Compute Scalar(DEFINE:([Expr1005]=[Expr1004])) 1 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1005]=[Expr1004]) [Expr1005]=[Expr1004] 30 0 3E-06 15 1.312771 [a].[VehicleNumber], [Expr1005] NULL PLAN_ROW 0 1

 

30 1 |--Nested Loops(Left Outer Join, OUTER REFERENCES:([a].[VehicleNumber])) 1 3 2 Nested Loops Left Outer Join OUTER REFERENCES:([a].[VehicleNumber]) NULL 30 0 0.0001254 15 1.312768 [a].[VehicleNumber], [Expr1004] NULL PLAN_ROW 0 1

 

30 1 |--Clustered Index Scan(OBJECT:([RunData].[dbo].[Vehicle].[PK_Vehicle] AS [a])) 1 4 3 Clustered Index Scan Clustered Index Scan OBJECT:([RunData].[dbo].[Vehicle].[PK_Vehicle] AS [a]) [a].[VehicleNumber] 30 0.003865741 0.00019 11 0.004055741 [a].[VehicleNumber] NULL PLAN_ROW 0 1

 

12 30 |--Top(TOP EXPRESSION:((1))) 1 5 3 Top Top TOP EXPRESSION:((1)) NULL 1 0 1E-07 11 1.308587 [Expr1004] NULL PLAN_ROW 0 30

 

0 0 |--Compute Scalar(DEFINE:([Expr1004]=[PlayBack].[dbo].[pVehicle].[_Seq] as [b].[_Seq])) 1 6 5 Compute Scalar Compute Scalar DEFINE:([Expr1004]=[PlayBack].[dbo].[pVehicle].[_Seq] as [b].[_Seq]) [Expr1004]=[PlayBack].[dbo].[pVehicle].[_Seq] as [b].[_Seq] 1 0 0.164012 15 1.308584 [b].[_Seq], [Expr1004] NULL PLAN_ROW 0 30

 

12 30 |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[RegTime], [b].[_Seq], [Expr1006]) WITH ORDERED PREFETCH) 1 7 6 Nested Loops Inner Join OUTER REFERENCES:([b].[RegTime], [b].[_Seq], [Expr1006]) WITH ORDERED PREFETCH NULL 12.19346 0 83.59471 15 1.308405 [b].[_Seq] NULL PLAN_ROW 0 30

 

368930815 30 |--Index Scan(OBJECT:([PlayBack].[dbo].[pVehicle].[XAK1pVehicle] AS [b]), ORDERED BACKWARD) 1 9 7 Index Scan Index Scan OBJECT:([PlayBack].[dbo].[pVehicle].[XAK1pVehicle] AS [b]), ORDERED BACKWARD [b].[RegTime], [b].[_Seq] 12.19346 33.03572 21.99876 19 0.008255873 [b].[RegTime], [b].[_Seq] NULL PLAN_ROW 0 30

 

12 368930815 |--Clustered Index Seek(OBJECT:([PlayBack].[dbo].[pVehicle].[PK_pVehicle] AS [b]), SEEK:([b].[RegTime]=[PlayBack].[dbo].[pVehicle].[RegTime] as [b].[RegTime] AND [b].[_Seq]=[PlayBack].[dbo].[pVehicle].[_Seq] as [b].[_Seq]), WHERE:([PlayBack].[dbo].[pVehicle].[VehicleNumber] as [b].[VehicleNumber]=[RunData].[dbo].[Vehicle].[VehicleNumber] as [a].[VehicleNumber]) LOOKUP ORDERED FORWARD) 1 11 7 Clustered Index Seek Clustered Index Seek OBJECT:([PlayBack].[dbo].[pVehicle].[PK_pVehicle] AS [b]), SEEK:([b].[RegTime]=[PlayBack].[dbo].[pVehicle].[RegTime] as [b].[RegTime] AND [b].[_Seq]=[PlayBack].[dbo].[pVehicle].[_Seq] as [b].[_Seq]), WHERE:([PlayBack].[dbo].[pVehicle].[VehicleNumber] as [b].[VehicleNumber]=[RunData].[dbo].[Vehicle].[VehicleNumber] as [a].[VehicleNumber]) LOOKUP ORDERED FORWARD NULL 1 0.003125 0.0001581 11 1.312771 NULL NULL PLAN_ROW 0 720.4141

김민식(mspower)님이 2013-07-27 12:12에 작성한 댓글입니다.
이 댓글은 2013-07-27 12:30에 마지막으로 수정되었습니다.

다음과 같이 VehicleNumber , _Seq 의 순서대로 복합 인덱스를 생성하시면 문제가 해결될 것으로 보입니다.

-- nc_pVehicle_VN_Seq 는 제가 임의로 지은 이름이니 정책에 맞도록 수정하세요. 
 
CREATE INDEX nc_pVehicle_VN_Seq ON PlayBack.dbo.pVehicle ( VehicleNumber , _Seq )
;
 
그런데 DBCC DROPCLEANBUFFERS를 수행한 후의 결과가 아무리 생각해봐도 물리적 읽기가 1인 것은 잘 이해는 되지 않습니다만.. --_--;;
우욱님이 2013-07-27 18:42에 작성한 댓글입니다.
이 댓글은 2013-07-27 18:44에 마지막으로 수정되었습니다. Edit

감사합니다.

 

이론상 알고는 있었지만, 복합인덱스의 효과가 이렇게 클지 몰랐네요.

 

말씀대로 VehicleNumber, _Seq 에 복합인덱스를 생성하니 결과가 바로 나옵니다.

 

다시한번 도움 말씀에 깊히 감사드립니다.

 

----------------------

 

이런.... 마농님이 벌써 말씀을 하셨었네요..

 

"-- (이름 + 고유번호) 결합 인덱스가 있는 상태에서 다음과 같이..."

 

애구... 죄송하고 감사합니다.

 

 

 

김민식(mspower)님이 2013-07-29 00:40에 작성한 댓글입니다.
이 댓글은 2013-07-29 00:42에 마지막으로 수정되었습니다.
[Top]
No.
제목
작성자
작성일
조회
6705사용중인 프로그램 조인할수 있나요? [4]
김우성
2013-08-07
6961
6704안녕하세요 [6]
초보입니다.
2013-08-06
7534
6703같은테이블 조회 및 계산 방법. [2]
구글어스
2013-07-25
7204
6702쿼리문 질문입니다. [7]
김민식
2013-07-24
9041
6701다음 oracle sql 을 ms-sql 에서는 어떻게 하나여 [1]
최종길
2013-07-24
6898
6700transaction log 축소의 효과? [1]
조중규
2013-07-22
7345
6699엑셀문서를 DB에 insert [1]
DBDBDB
2013-07-22
7292
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.050초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다