[TSQL]"엄청 빠른 ROW COUNT" COUNT(*) 는 너무 느려 SYS.DM_DB_PARTITION_STATS 을 써보자

DB에서는 쿼리 플랜을 위해 테이블의 ROWCOUNT 정보를 가지고 있습니다.

그걸 엿볼수 있는게 SYS.DM_DB_PARTITION_STATS  catalog view 입니다.

 

--전체 테이블 카운트

SELECT T.NAME AS TBLNAME, SUM(P.ROW_COUNT) T_COUNT
FROM SYS.TABLES T JOIN SYS.DM_DB_PARTITION_STATS P ON T.OBJECT_ID = P.OBJECT_ID AND T.TYPE_DESC = 'USER_TABLE'
AND P.INDEX_ID IN (0,1) 
--WHERE T.NAME IN ('TBL_NAMES')
GROUP BY T.SCHEMA_ID, T.NAME
ORDER BY T.NAME

 

SELECT T.NAME AS TBLNAME, SUM(P.ROW_COUNT) T_COUNT
FROM SYS.TABLES T JOIN SYS.DM_DB_PARTITION_STATS P ON T.OBJECT_ID = P.OBJECT_ID AND T.TYPE_DESC = 'USER_TABLE'
AND P.INDEX_ID IN (0,1) 
--WHERE T.NAME IN ('TBL_NAMES')
GROUP BY T.SCHEMA_ID, T.NAME
ORDER BY T.NAME

--두개 인스턴스의 특정 테이블 카운트를 비교해 볼까요?

SELECT *
FROM (
SELECT T.NAME AS TBLNAME, SUM(P.ROW_COUNT) T_COUNT
FROM DB1.SYS.TABLES T JOIN DB1.SYS.DM_DB_PARTITION_STATS P ON T.OBJECT_ID = P.OBJECT_ID AND T.TYPE_DESC = 'USER_TABLE'
AND P.INDEX_ID IN (0,1) 
WHERE T.NAME IN ('TBL1',TBL2',TBL3',TBL4')
GROUP BY T.SCHEMA_ID, T.NAME ) A
CROSS APPLY ( SELECT B.T_COUNT AS T_COUNT_NEW
FROM (
SELECT T.NAME AS TBLNAME, SUM(P.ROW_COUNT) T_COUNT
FROM DB2.SYS.TABLES T JOIN DB2.SYS.DM_DB_PARTITION_STATS P ON T.OBJECT_ID = P.OBJECT_ID AND T.TYPE_DESC = 'USER_TABLE'
AND P.INDEX_ID IN (0,1) 
WHERE T.NAME IN ('TBL1',TBL2',TBL3',TBL4')
GROUP BY T.SCHEMA_ID, T.NAME ) B
WHERE A.TBLNAME = B.TBLNAME ) B 
ORDER BY A.TBLNAME

SELECT *
FROM (
SELECT T.NAME AS TBLNAME, SUM(P.ROW_COUNT) T_COUNT
FROM DB1.SYS.TABLES T JOIN DB1.SYS.DM_DB_PARTITION_STATS P ON T.OBJECT_ID = P.OBJECT_ID AND T.TYPE_DESC = 'USER_TABLE'
AND P.INDEX_ID IN (0,1) 
WHERE T.NAME IN ('TBL1',TBL2',TBL3',TBL4')
GROUP BY T.SCHEMA_ID, T.NAME ) A
CROSS APPLY ( SELECT B.T_COUNT AS T_COUNT_NEW
FROM (
SELECT T.NAME AS TBLNAME, SUM(P.ROW_COUNT) T_COUNT
FROM DB2.SYS.TABLES T JOIN DB2.SYS.DM_DB_PARTITION_STATS P ON T.OBJECT_ID = P.OBJECT_ID AND T.TYPE_DESC = 'USER_TABLE'
AND P.INDEX_ID IN (0,1) 
WHERE T.NAME IN ('TBL1',TBL2',TBL3',TBL4')
GROUP BY T.SCHEMA_ID, T.NAME ) B
WHERE A.TBLNAME = B.TBLNAME ) B 
ORDER BY A.TBLNAME

There is fastest way for counting your table in mssql.
It can be by using catalog view.
Here is some example for you.
I hope it will be help you.