통상적으로 테이블 로우수를 가지고 올때 아래와 같이 많이 사용 할 것이다.
select count(1) from 테이블명
테이블 로우수가 얼마되지 않은 자료를 가지고 올때는 위와같이 사용해도 특별히 문제점은 없다.
하지만 대용량으로 가면갈수록 전체 테이블에 로우수를 확인하는데도 초단위 이상에 시간이 소요가 된다.
덩치가 좀 되는 테이블 로우수는 어떻게 취하는게 효율적일까? 그 해답은 아래와 같다.
SELECT DISTINCT MAX(idx.rows)
FROM SYSINDEXES as idx INNER JOIN SYSOBJECTS AS obj
ON (idx.id = obj.id)
WHERE (obj.type = 'U') AND (obj.name = '테이블명')
쿼리를 보면 알겠지만 시스템 테이블을 활용하여 Row Count를 취하는 방법이다.
비교된 결과물이 있어야 화아악~ 와 닿겠죠잉~ ^^
제가 보유하고 있는 테이블 중에 Row Count가 54,602,856 인것을 예로 들면
먼저 흔히들 사용하는 select count(1) from 테이블명 쿼리로 하였을 때 결과다
결과값을 가지고 오는 4분 37초가 소요가 되었다.
반면
SELECT DISTINCT MAX(idx.rows)
FROM SYSINDEXES as idx INNER JOIN SYSOBJECTS AS obj
ON (idx.id = obj.id)
WHERE (obj.type = 'U') AND (obj.name = '테이블명')
서프라이즈~ ^^ 이 얼마나 놀라운가 시스템 테이블에 정보를 이용하여 실행과 함께 결과물이 나왔다.
이것을 실행결과로 비교해보면 100:0 에 실행 계획이 나온다.
나 역시 ms sql에서 테이블 로우수를 빨리 취하는법에 대해 알고는 있었지만 사용은 하지 않는다.
왜? 위에 쿼리보다 아래쿼리가 기니까~ ㅋ
하지만 대용량 서버에서 로우수를 취할 때는 유용하게 사용될 수 있을것이다.
더불어 해당 DB에 모든 테이블에 로우수를 가지고 오는 방법으로는 아래 쿼리를 사용하면 될것이다.
select obj.name, max(idx.rows)
from sysindexes as idx
inner join sysobjects as obj on (idx.id = obj.id)
where (obj.type = 'U')
group by obj.name
참고로 sysobject와 sysindexes layout 정보를 첨부해본다.
이런 된장~ 포스팅을 하고 검색을 좀 해보니 프로시저로 제공이 된다.. 뜨벌~
execute sp_tables_info_rowset '테이블명'
나 지금 모한거니? ㅠ,.ㅜ
[sysobject]
| 열 이름 | 데이터 형식 | 설명 | ||
|---|---|---|---|---|
name | sysname | 개체 이름 | ||
id | int | 개체 ID | ||
xtype | char(2) | 개체 유형입니다. 다음 개체 유형 중 하나일 수 있습니다. AF = 집계 함수(CLR) C = CHECK 제약 조건 D = 기본값 또는 DEFAULT 제약 조건 F = FOREIGN KEY 제약 조건 L = 로그 FN = 스칼라 함수 FS = 어셈블리(CLR) 스칼라 함수 FT = 어셈블리(CLR) 테이블 반환 함수 IF = 인라인 테이블 함수 IT = 내부 테이블 P = 저장 프로시저 PC = 어셈블리(CLR) 저장 프로시저 PK = PRIMARY KEY 제약 조건(K 유형) RF = 복제 필터 저장 프로시저 S = 시스템 테이블 SN = 동의어 SQ = 서비스 큐 TA = 어셈블리(CLR) DML 트리거 TF = 테이블 함수 TR = SQL DML 트리거 TT = 테이블 유형 U = 사용자 테이블 UQ = UNIQUE 제약 조건(K 유형) V = 뷰 X = 확장 저장 프로시저 | ||
uid | smallint | 개체 소유자의 스키마 ID입니다. 이전 버전의 SQL Server에서 업그레이드한 데이터베이스의 경우 스키마 ID는 소유자의 사용자 ID와 동일합니다.
사용자 및 역할 수가 32,767을 초과하는 경우 오버플로되거나 NULL을 반환합니다. 자세한 내용은 | ||
info | smallint | 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. | ||
status | int | 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. | ||
base_schema_ver | int | 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. | ||
replinfo | int | 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. | ||
parent_obj | int | 부모 개체의 개체 ID입니다. 예를 들어 트리거 또는 제약 조건인 경우 테이블의 ID입니다. | ||
crdate | datetime | 개체를 만든 날짜입니다. | ||
ftcatid | smallint | 전체 텍스트 인덱싱을 위해 등록된 모든 사용자 테이블의 경우 전체 텍스트 카탈로그 식별자이며, 등록되지 않은 모든 사용자 테이블의 경우 0입니다. | ||
schema_ver | int | 테이블의 스키마가 변경될 때마다 증가하는 버전 번호입니다. 항상 0을 반환합니다. | ||
stats_schema_ver | int | 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. | ||
type | char(2) | 개체 유형입니다. 다음 값 중 하나를 사용할 수 있습니다. AF = 집계 함수(CLR) C = CHECK 제약 조건 D = 기본값 또는 DEFAULT 제약 조건 F = FOREIGN KEY 제약 조건 FN = 스칼라 함수 FS = 어셈블리(CLR) 스칼라 함수 FT = 어셈블리(CLR) 테이블 반환 함수 IF = 인라인 테이블 함수 IT = 내부 테이블 K = PRIMARY KEY 또는 UNIQUE 제약 조건 L = 로그 P = 저장 프로시저 PC = 어셈블리(CLR) 저장 프로시저 R = 규칙 RF = 복제 필터 저장 프로시저 S = 시스템 테이블 SN = 동의어 SQ = 서비스 큐 TA = 어셈블리(CLR) DML 트리거 TF = 테이블 함수 TR = SQL DML 트리거 TT = 테이블 유형 U = 사용자 테이블 V = 뷰 X = 확장 저장 프로시저 | ||
userstat | smallint | 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. | ||
sysstat | smallint | 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. | ||
indexdel | smallint | 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. | ||
refdate | datetime | 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. | ||
version | int | 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. | ||
deltrig | int | 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. | ||
instrig | int | 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. | ||
updtrig | int | 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. | ||
seltrig | int | 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. | ||
category | int | 게시, 제약 조건 및 ID에 사용됩니다. | ||
cache | smallint | 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. |
중요:
[sysindexes]
| 열 이름 | 데이터 형식 | 설명 |
|---|---|---|
id | int | 인덱스가 속한 테이블의 ID입니다. |
status | int | 시스템 상태 정보입니다. 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. |
first | binary(6) | 첫 페이지 또는 루트 페이지에 대한 포인터입니다. indid가 0일 경우 사용되지 않습니다. NULL = indid가 1보다 큰 경우 인덱스가 분할됩니다. NULL = indid가 0 또는 1일 때 테이블이 분할됩니다. |
indid | smallint | 인덱스의 ID입니다. 0 = 힙 1 = 클러스터형 인덱스 >1 = 비클러스터형 인덱스 |
root | binary(6) | indid >= 1인 경우 root는 루트 페이지에 대한 포인터입니다. indid가 0일 경우 사용되지 않습니다. NULL = indid가 1보다 큰 경우 인덱스가 분할됩니다. NULL = indid가 0 또는 1일 때 테이블이 분할됩니다. |
minlen | smallint | 행의 최대 크기입니다. |
keycnt | smallint | 키 수입니다. |
groupid | smallint | 개체가 만들어진 파일 그룹의 ID입니다. NULL = indid가 1보다 큰 경우 인덱스가 분할됩니다. NULL = indid가 0 또는 1일 때 테이블이 분할됩니다. |
dpages | int | indid = 0 또는 indid = 1인 경우 dpages는 사용한 데이터 페이지의 수입니다. indid가 1보다 작은 경우 dpages는 사용한 인덱스 페이지의 수입니다. 0 = indid가 1보다 큰 경우 인덱스가 분할됩니다. 0 = indid가 0 또는 1인 경우 테이블이 분할됩니다. 행 오버플로가 발생할 경우 정확한 결과가 반환되지 않습니다. |
reserved | int | indid = 0 또는 indid = 1인 경우 reserved는 모든 인덱스 및 테이블 데이터에 할당된 페이지의 수입니다. indid가 1보다 작은 경우 reserved는 인덱스에 할당된 페이지의 수입니다. 0 = indid가 1보다 큰 경우 인덱스가 분할됩니다. 0 = indid가 0 또는 1인 경우 테이블이 분할됩니다. 행 오버플로가 발생할 경우 정확한 결과가 반환되지 않습니다. |
used | int | indid = 0 또는 indid = 1인 경우 used는 모든 인덱스 및 테이블 데이터용으로 사용된 총 페이지의 수입니다. indid가 1보다 작은 경우 used는 인덱스용으로 사용된 페이지의 수입니다. 0 = indid가 1보다 큰 경우 인덱스가 분할됩니다. 0 = indid가 0 또는 1인 경우 테이블이 분할됩니다. 행 오버플로가 발생할 경우 정확한 결과가 반환되지 않습니다. |
rowcnt | bigint | indid = 0 및 indid = 1을 기준으로 하는 데이터 수준 행 개수입니다. 0 = indid가 1보다 큰 경우 인덱스가 분할됩니다. 0 = indid가 0 또는 1인 경우 테이블이 분할됩니다. |
rowmodctr | int | 테이블에 대해 통계를 마지막으로 업데이트한 이후에 삽입, 삭제 또는 업데이트된 행의 총 수를 셉니다. 0 = indid가 1보다 큰 경우 인덱스가 분할됩니다. 0 = indid가 0 또는 1인 경우 테이블이 분할됩니다. SQL Server 2005 이상 버전에서 rowmodctr은 이전 버전과 완전히 호환되지는 않습니다. 자세한 내용은 주의를 참조하십시오. |
xmaxlen | smallint | 행의 최대 크기입니다. |
maxirow | smallint | 리프가 아닌 인덱스 행의 최대 크기입니다. SQL Server 2005 이상 버전에서 maxirow는 이전 버전과 완전히 호환되지는 않습니다. |
OrigFillFactor | tinyint | 인덱스가 만들어질 때 사용되는 원래 채우기 비율 값입니다. 이 값은 유지 관리되지 않지만 인덱스를 다시 만들어야 하거나 사용한 채우기 비율 값을 기억할 수 없는 경우에 유용합니다. |
StatVersion | tinyint | 0을 반환합니다. 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. |
reserved2 | int | 0을 반환합니다. 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. |
FirstIAM | binary(6) | NULL = 인덱스가 분할됩니다. 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. |
impid | smallint | 인덱스 구현 플래그입니다. 0을 반환합니다. 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. |
lockflags | smallint | 인덱스에 대해 고려된 잠금 세분성을 제약하는 데 사용합니다. 예를 들어 잠금 비용을 최소화하기 위해 일반적으로 읽기 전용인 조회 테이블을 테이블 수준의 잠금만 수행하도록 설정할 수 있습니다. |
pgmodctr | int | 0을 반환합니다. 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. |
keys | varbinary(816) | 인덱스 키를 구성하는 열의 열 ID 목록입니다. NULL을 반환합니다. 인덱스 키 열을 표시하려면 |
name | sysname | 인덱스 또는 통계의 이름입니다. indid가 0인 경우 NULL을 반환합니다. 응용 프로그램을 수정하여 NULL 힙 이름을 찾습니다. |
statblob | image | 통계 BLOB(Binary Large Object)입니다. NULL을 반환합니다. |
maxlen | int | 정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다. |
rows | int | indid = 0 및 indid = 1을 기준으로 하는 데이터 수준 행 개수이며 indid가 1보다 작은 경우에 값이 반복됩니다. |
[출처] ms-sql 테이블 레코드 수 빨리 취하기|작성자 땅쿠