대규모 테이블에서 SQL Server 쿼리 성능 향상
비교적 큰 테이블(현재 200만 개의 레코드)을 가지고 있는데, 임시 쿼리의 성능을 향상시킬 수 있는지 알고 싶습니다.여기서 애드혹이라는 단어가 핵심입니다.인덱스를 추가하는 것은 옵션이 아닙니다(가장 일반적으로 쿼리되는 열에 이미 인덱스가 있음).
간단한 쿼리를 실행하여 가장 최근에 업데이트된 레코드 100개를 반환합니다.
select top 100 * from ER101_ACCT_ORDER_DTL order by er101_upd_date_iso desc
몇 분 걸립니다.아래 실행 계획 참조:
테이블 스캔의 추가 세부 정보:
SQL Server Execution Times:
CPU time = 3945 ms, elapsed time = 148524 ms.
sql server 2008 r2 x64를 실행하는 서버는 매우 강력합니다(메모리 48GB RAM, 코어 프로세서 24개).
갱신하다
저는 이 코드를 찾아 1,000,000개의 레코드가 있는 테이블을 만들었습니다.그러면 도망갈 수 있을 것 같아서요SELECT TOP 100 * FROM testEnvironment ORDER BY mailAddress DESC몇 개의 다른 서버에서 디스크 액세스 속도가 서버에서 좋지 않은지 확인합니다.
WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1),
t2(N) AS (SELECT 1 FROM t1 x, t1 y),
t3(N) AS (SELECT 1 FROM t2 x, t2 y),
Tally(N) AS (SELECT TOP 98 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM t3 x, t3 y),
Tally2(N) AS (SELECT TOP 5 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM t3 x, t3 y),
Combinations(N) AS (SELECT DISTINCT LTRIM(RTRIM(RTRIM(SUBSTRING(poss,a.N,2)) + SUBSTRING(vowels,b.N,1)))
FROM Tally a
CROSS JOIN Tally2 b
CROSS APPLY (SELECT 'B C D F G H J K L M N P R S T V W Z SCSKKNSNSPSTBLCLFLGLPLSLBRCRDRFRGRPRTRVRSHSMGHCHPHRHWHBWCWSWTW') d(poss)
CROSS APPLY (SELECT 'AEIOU') e(vowels))
SELECT IDENTITY(INT,1,1) AS ID, a.N + b.N AS N
INTO #testNames
FROM Combinations a
CROSS JOIN Combinations b;
SELECT IDENTITY(INT,1,1) AS ID, firstName, secondName
INTO #testNames2
FROM (SELECT firstName, secondName
FROM (SELECT TOP 1000 --1000 * 1000 = 1,000,000 rows
N AS firstName
FROM #testNames
ORDER BY NEWID()) a
CROSS JOIN (SELECT TOP 1000 --1000 * 1000 = 1,000,000 rows
N AS secondName
FROM #testNames
ORDER BY NEWID()) b) innerQ;
SELECT firstName, secondName,
firstName + '.' + secondName + '@fake.com' AS eMail,
CAST((ABS(CHECKSUM(NEWID())) % 250) + 1 AS VARCHAR(3)) + ' ' AS mailAddress,
(ABS(CHECKSUM(NEWID())) % 152100) + 1 AS jID,
IDENTITY(INT,1,1) AS ID
INTO #testNames3
FROM #testNames2
SELECT IDENTITY(INT,1,1) AS ID, firstName, secondName, eMail,
mailAddress + b.N + b.N AS mailAddress
INTO testEnvironment
FROM #testNames3 a
INNER JOIN #testNames b ON a.jID = b.ID;
--CLEAN UP USELESS TABLES
DROP TABLE #testNames;
DROP TABLE #testNames2;
DROP TABLE #testNames3;
그러나 세 개의 테스트 서버에서 쿼리가 거의 즉시 실행되었습니다.누가 설명해 줄 수 있나요?
업데이트 2
의견 감사합니다. 계속해서...이를 통해 기본 키 인덱스를 비클러스터형에서 클러스터형으로 변경하여 흥미로운(예상치 못한) 결과를 얻을 수 있었습니다.
비클러스터:
SQL Server Execution Times:
CPU time = 3634 ms, elapsed time = 154179 ms.
클러스터:
SQL Server Execution Times:
CPU time = 2650 ms, elapsed time = 52177 ms.
이것이 어떻게 가능한 걸까요?er101_upd_date_iso 열에 인덱스가 없으면 클러스터된 인덱스를 어떻게 사용할 수 있습니까?
업데이트 3
요청한 대로 테이블 만들기 스크립트입니다.
CREATE TABLE [dbo].[ER101_ACCT_ORDER_DTL](
[ER101_ORG_CODE] [varchar](2) NOT NULL,
[ER101_ORD_NBR] [int] NOT NULL,
[ER101_ORD_LINE] [int] NOT NULL,
[ER101_EVT_ID] [int] NULL,
[ER101_FUNC_ID] [int] NULL,
[ER101_STATUS_CDE] [varchar](2) NULL,
[ER101_SETUP_ID] [varchar](8) NULL,
[ER101_DEPT] [varchar](6) NULL,
[ER101_ORD_TYPE] [varchar](2) NULL,
[ER101_STATUS] [char](1) NULL,
[ER101_PRT_STS] [char](1) NULL,
[ER101_STS_AT_PRT] [char](1) NULL,
[ER101_CHG_COMMENT] [varchar](255) NULL,
[ER101_ENT_DATE_ISO] [datetime] NULL,
[ER101_ENT_USER_ID] [varchar](10) NULL,
[ER101_UPD_DATE_ISO] [datetime] NULL,
[ER101_UPD_USER_ID] [varchar](10) NULL,
[ER101_LIN_NBR] [int] NULL,
[ER101_PHASE] [char](1) NULL,
[ER101_RES_CLASS] [char](1) NULL,
[ER101_NEW_RES_TYPE] [varchar](6) NULL,
[ER101_RES_CODE] [varchar](12) NULL,
[ER101_RES_QTY] [numeric](11, 2) NULL,
[ER101_UNIT_CHRG] [numeric](13, 4) NULL,
[ER101_UNIT_COST] [numeric](13, 4) NULL,
[ER101_EXT_COST] [numeric](11, 2) NULL,
[ER101_EXT_CHRG] [numeric](11, 2) NULL,
[ER101_UOM] [varchar](3) NULL,
[ER101_MIN_CHRG] [numeric](11, 2) NULL,
[ER101_PER_UOM] [varchar](3) NULL,
[ER101_MAX_CHRG] [numeric](11, 2) NULL,
[ER101_BILLABLE] [char](1) NULL,
[ER101_OVERRIDE_FLAG] [char](1) NULL,
[ER101_RES_TEXT_YN] [char](1) NULL,
[ER101_DB_CR_FLAG] [char](1) NULL,
[ER101_INTERNAL] [char](1) NULL,
[ER101_REF_FIELD] [varchar](255) NULL,
[ER101_SERIAL_NBR] [varchar](50) NULL,
[ER101_RES_PER_UNITS] [int] NULL,
[ER101_SETUP_BILLABLE] [char](1) NULL,
[ER101_START_DATE_ISO] [datetime] NULL,
[ER101_END_DATE_ISO] [datetime] NULL,
[ER101_START_TIME_ISO] [datetime] NULL,
[ER101_END_TIME_ISO] [datetime] NULL,
[ER101_COMPL_STS] [char](1) NULL,
[ER101_CANCEL_DATE_ISO] [datetime] NULL,
[ER101_BLOCK_CODE] [varchar](6) NULL,
[ER101_PROP_CODE] [varchar](8) NULL,
[ER101_RM_TYPE] [varchar](12) NULL,
[ER101_WO_COMPL_DATE] [datetime] NULL,
[ER101_WO_BATCH_ID] [varchar](10) NULL,
[ER101_WO_SCHED_DATE_ISO] [datetime] NULL,
[ER101_GL_REF_TRANS] [char](1) NULL,
[ER101_GL_COS_TRANS] [char](1) NULL,
[ER101_INVOICE_NBR] [int] NULL,
[ER101_RES_CLOSED] [char](1) NULL,
[ER101_LEAD_DAYS] [int] NULL,
[ER101_LEAD_HHMM] [int] NULL,
[ER101_STRIKE_DAYS] [int] NULL,
[ER101_STRIKE_HHMM] [int] NULL,
[ER101_LEAD_FLAG] [char](1) NULL,
[ER101_STRIKE_FLAG] [char](1) NULL,
[ER101_RANGE_FLAG] [char](1) NULL,
[ER101_REQ_LEAD_STDATE] [datetime] NULL,
[ER101_REQ_LEAD_ENDATE] [datetime] NULL,
[ER101_REQ_STRK_STDATE] [datetime] NULL,
[ER101_REQ_STRK_ENDATE] [datetime] NULL,
[ER101_LEAD_STDATE] [datetime] NULL,
[ER101_LEAD_ENDATE] [datetime] NULL,
[ER101_STRK_STDATE] [datetime] NULL,
[ER101_STRK_ENDATE] [datetime] NULL,
[ER101_DEL_MARK] [char](1) NULL,
[ER101_USER_FLD1_02X] [varchar](2) NULL,
[ER101_USER_FLD1_04X] [varchar](4) NULL,
[ER101_USER_FLD1_06X] [varchar](6) NULL,
[ER101_USER_NBR_060P] [int] NULL,
[ER101_USER_NBR_092P] [numeric](9, 2) NULL,
[ER101_PR_LIST_DTL] [numeric](11, 2) NULL,
[ER101_EXT_ACCT_CODE] [varchar](8) NULL,
[ER101_AO_STS_1] [char](1) NULL,
[ER101_PLAN_PHASE] [char](1) NULL,
[ER101_PLAN_SEQ] [int] NULL,
[ER101_ACT_PHASE] [char](1) NULL,
[ER101_ACT_SEQ] [int] NULL,
[ER101_REV_PHASE] [char](1) NULL,
[ER101_REV_SEQ] [int] NULL,
[ER101_FORE_PHASE] [char](1) NULL,
[ER101_FORE_SEQ] [int] NULL,
[ER101_EXTRA1_PHASE] [char](1) NULL,
[ER101_EXTRA1_SEQ] [int] NULL,
[ER101_EXTRA2_PHASE] [char](1) NULL,
[ER101_EXTRA2_SEQ] [int] NULL,
[ER101_SETUP_MSTR_SEQ] [int] NULL,
[ER101_SETUP_ALTERED] [char](1) NULL,
[ER101_RES_LOCKED] [char](1) NULL,
[ER101_PRICE_LIST] [varchar](10) NULL,
[ER101_SO_SEARCH] [varchar](9) NULL,
[ER101_SSB_NBR] [int] NULL,
[ER101_MIN_QTY] [numeric](11, 2) NULL,
[ER101_MAX_QTY] [numeric](11, 2) NULL,
[ER101_START_SIGN] [char](1) NULL,
[ER101_END_SIGN] [char](1) NULL,
[ER101_START_DAYS] [int] NULL,
[ER101_END_DAYS] [int] NULL,
[ER101_TEMPLATE] [char](1) NULL,
[ER101_TIME_OFFSET] [char](1) NULL,
[ER101_ASSIGN_CODE] [varchar](10) NULL,
[ER101_FC_UNIT_CHRG] [numeric](13, 4) NULL,
[ER101_FC_EXT_CHRG] [numeric](11, 2) NULL,
[ER101_CURRENCY] [varchar](3) NULL,
[ER101_FC_RATE] [numeric](12, 5) NULL,
[ER101_FC_DATE] [datetime] NULL,
[ER101_FC_MIN_CHRG] [numeric](11, 2) NULL,
[ER101_FC_MAX_CHRG] [numeric](11, 2) NULL,
[ER101_FC_FOREIGN] [numeric](12, 5) NULL,
[ER101_STAT_ORD_NBR] [int] NULL,
[ER101_STAT_ORD_LINE] [int] NULL,
[ER101_DESC] [varchar](255) NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PRT_SEQ_1] [varchar](12) NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PRT_SEQ_2] [varchar](120) NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TAX_BASIS] [char](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_RES_CATEGORY] [char](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_DECIMALS] [char](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TAX_SEQ] [varchar](7) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MANUAL] [char](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TR_LC_RATE] [numeric](12, 5) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TR_FC_RATE] [numeric](12, 5) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TR_PL_RATE] [numeric](12, 5) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TR_DIFF] [char](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TR_UNIT_CHRG] [numeric](13, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TR_EXT_CHRG] [numeric](13, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TR_MIN_CHRG] [numeric](13, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TR_MAX_CHRG] [numeric](13, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PL_UNIT_CHRG] [numeric](13, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PL_EXT_CHRG] [numeric](13, 2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PL_MIN_CHRG] [numeric](13, 2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PL_MAX_CHRG] [numeric](13, 2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TAX_RATE_TYPE] [char](1) NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ORDER_FORM] [varchar](2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_FACTOR] [int] NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MGMT_RPT_CODE] [varchar](6) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ROUND_CHRG] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_WHOLE_QTY] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SET_QTY] [numeric](15, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SET_UNITS] [numeric](15, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SET_ROUNDING] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SET_SUB] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TIME_QTY] [numeric](13, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_GL_DISTR_PCT] [numeric](7, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_REG_SEQ] [int] NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ALT_DESC] [varchar](255) NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_REG_ACCT] [varchar](8) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_DAILY] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_AVG_UNIT_CHRG] [varchar](1) NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ALT_DESC2] [varchar](255) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_CONTRACT_SEQ] [int] NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ORIG_RATE] [numeric](13, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_DISC_PCT] [decimal](17, 10) NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_DTL_EXIST] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ORDERED_ONLY] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SHOW_STDATE] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SHOW_STTIME] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SHOW_ENDATE] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SHOW_ENTIME] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SHOW_RATE] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SHOW_UNITS] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_BASE_RATE] [numeric](13, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_COMMIT_QTY] [numeric](11, 2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MM_QTY_USED] [varchar](2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MM_CHRG_USED] [varchar](2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_TEXT_1] [varchar](50) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_NBR_1] [numeric](13, 3) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_NBR_2] [numeric](13, 3) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_NBR_3] [numeric](13, 3) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PL_BASE_RATE] [numeric](13, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_REV_DIST] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_COVER] [int] NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_RATE_TYPE] [varchar](2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_USE_SEASONAL] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TAX_EI] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TAXES] [numeric](13, 2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_FC_TAXES] [numeric](13, 2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PL_TAXES] [numeric](13, 2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_FC_QTY] [numeric](13, 2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_LEAD_HRS] [numeric](6, 2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_STRIKE_HRS] [numeric](6, 2) NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_CANCEL_USER_ID] [varchar](10) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ST_OFFSET_HRS] [numeric](7, 2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_EN_OFFSET_HRS] [numeric](7, 2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MEMO_FLAG] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MEMO_EXT_CHRG] [numeric](13, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MEMO_EXT_CHRG_PL] [numeric](13, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MEMO_EXT_CHRG_TR] [numeric](13, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MEMO_EXT_CHRG_FC] [numeric](13, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TIME_QTY_EDIT] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SURCHARGE_PCT] [decimal](17, 10) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_INCL_EXT_CHRG] [numeric](13, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_INCL_EXT_CHRG_FC] [numeric](13, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_CARRIER] [varchar](6) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SETUP_ID2] [varchar](8) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SHIPPABLE] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_CHARGEABLE] [varchar](2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_NBR_ALLOW] [varchar](2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_NBR_START] [int] NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_NBR_END] [int] NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_SUPPLIER] [varchar](8) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TRACK_ID] [varchar](40) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_REF_INV_NBR] [int] NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_NEW_ITEM_STS] [varchar](2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MSTR_REG_ACCT_CODE] [varchar](8) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ALT_DESC3] [varchar](255) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ALT_DESC4] [varchar](255) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ALT_DESC5] [varchar](255) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SETUP_ROLLUP] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MM_COST_USED] [varchar](2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_AUTO_SHIP_RCD] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_FIXED] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_EST_TBD] [varchar](3) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ROLLUP_PL_UNIT_CHRG] [numeric](13, 4) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ROLLUP_PL_EXT_CHRG] [numeric](13, 2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_GL_ORD_REV_TRANS] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_DISCOUNT_FLAG] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SETUP_RES_TYPE] [varchar](6) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SETUP_RES_CODE] [varchar](12) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PERS_SCHED_FLAG] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PRINT_STAMP] [datetime] NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SHOW_EXT_CHRG] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PRINT_SEQ_NBR] [int] NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PAY_LOCATION] [varchar](3) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MAX_RM_NIGHTS] [int] NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_USE_TIER_COST] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_UNITS_SCHEME_CODE] [varchar](6) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ROUND_TIME] [varchar](2) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_LEVEL] [int] NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SETUP_PARENT_ORD_LINE] [int] NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_BADGE_PRT_STS] [varchar](1) NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_EVT_PROMO_SEQ] [int] NULL
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_REG_TYPE] [varchar](12) NULL
/****** Object: Index [PK__ER101_ACCT_ORDER] Script Date: 04/15/2012 20:24:37 ******/
ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD CONSTRAINT [PK__ER101_ACCT_ORDER] PRIMARY KEY CLUSTERED
(
[ER101_ORD_NBR] ASC,
[ER101_ORD_LINE] ASC,
[ER101_ORG_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 50) ON [PRIMARY]
표의 크기는 2.8GB이고 인덱스 크기는 3.9GB입니다.
간단한 대답: 아니요.클러스터된 인덱스의 채우기 비율이 50%인 238 열 테이블에서는 임시 쿼리를 지원할 수 없습니다.
상세 답변:
제가 이 주제에 대한 다른 답변에서 언급했듯이 인덱스 디자인은 예술과 과학 모두이며 고려해야 할 요소가 너무 많아서 어렵고 빠른 규칙이 거의 없습니다.고려해야 할 것은 DML 작업 볼륨 대 SELECT, 디스크 하위 시스템, 테이블의 다른 인덱스/트리거, 테이블 내 데이터 배포, SARGable WHERE 조건을 사용한 쿼리 및 현재 기억조차 나지 않는 몇 가지입니다.
표 자체, 색인, 트리거 등에 대한 이해 없이는 이 주제에 대한 질문에 도움을 줄 수 없습니다.이제 테이블 정의를 게시했습니다(인덱스 대기 중이지만 테이블 정의만으로 문제의 99%를 가리킴).제가 몇 가지 제안을 할 수 있습니다.
첫째, 표 정의가 정확한 경우(238개 열, 채우기 비율 50%) 나머지 답변/권고는 거의 무시할 수 있습니다;-).여기서 정치적이지 못한 것은 미안하지만, 정말로, 그것은 구체적인 것을 알지 못하는 기러기 사냥입니다.이제 테이블 정의를 보면 테스트 쿼리(업데이트 #1)가 그렇게 빨리 실행된 경우에도 단순 쿼리가 왜 그렇게 오래 걸리는지 알 수 있습니다.
여기서 (그리고 많은 성능 저하 상황에서) 주요 문제는 잘못된 데이터 모델링입니다.인덱스가 999개인 것이 금지되지 않는 것처럼 238개의 열이 금지되지는 않지만 일반적으로 매우 현명하지도 않습니다.
권장 사항:
- 우선, 이 테이블은 정말 리모델링이 필요합니다.이 테이블이 데이터 웨어하우스 테이블인 경우에는 가능하지만 그렇지 않은 경우에는 이러한 필드를 여러 테이블로 분할하여 모두 동일한 PK를 가질 수 있어야 합니다.마스터 레코드 테이블이 있고 하위 테이블은 일반적으로 연결된 특성에 기반한 종속 정보일 뿐이며 이러한 테이블의 PK는 마스터 테이블의 PK와 동일하므로 마스터 테이블에 대한 FK도 마찬가지입니다.마스터 테이블과 모든 하위 테이블 사이에는 1대 1의 관계가 있습니다.
- 의
ANSI_PADDING OFF시간이 지남에 따라 다양한 열 추가로 인해 표 내에서 일관성이 없는 것은 말할 것도 없습니다.당신이 지금 그것을 고칠 수 있을지 확신할 수 없지만, 이상적으로 당신은 항상 가지고 있을 것입니다.ANSI_PADDING ON아니면 적어도 전체적으로 동일한 설정을 가지고 있습니다.ALTER TABLE진술들. - 두개의 그룹을 하는 것을 .테이블 및 인덱스.당신의 물건을 넣지 않는 것이 최선입니다.
PRIMARYSQL SERVER가 객체에 대한 모든 데이터와 메타데이터를 저장하는 곳입니다.및 인덱스에 대한 는 테이생테를성다(테이블에 데이터)에 합니다.[Tables]모든 는 " " "에 있습니다.[Indexes] - 채우기 비율을 50%에서 증가시킵니다.이 낮은 숫자는 인덱스 공간이 데이터 공간보다 큰 이유일 수 있습니다.인덱스 재구성을 수행하면 데이터에 사용된 최대 4k(총 8k 페이지 크기 중)의 데이터 페이지가 재생성되어 테이블이 넓은 영역에 분산됩니다.
- 가 "ER101_ORG_CODE"에 "ER101_ORG_CODE가 있는
WHERE조건을 클러스터된 인덱스의 맨 앞 열로 이동하는 것을 고려하십시오."보다 더 합니다."ER101_ORD_NBR"보다 더 자주 사용된다고 가정합니다.이 더 되는 경우하십시오."ER101_ORD_NBR"이 더 자주 사용되는 경우에는 이를 유지합니다.필드 이름이 "OrganizationCode" 및 "OrderNumber"를 의미한다고 가정하면 "OrgCode"가 여러 개의 "OrderNumber"를 포함할 수 있는 더 나은 그룹으로 간주됩니다. - 이지만 "ER101_ORG_CODE에는 "ER101_ORG_CODE"를 합니다.
CHAR(2)에VARCHAR(2)가변 너비 크기를 추적하고 수백만 개의 행을 추가하는 행 헤더에 바이트를 저장합니다. - 에 있는 다른 했듯이, 기있다사언것이처럼급한사용람들여른는,, 용:사를 사용하면 다음과 .
SELECT *성능이 저하됩니다.인덱스에 검색을 만 아니라 하여 SQL Server를 하는 데에도 .*모든 열 이름으로 이동합니다.모든 238개 열 이름을 지정하는 것이 약간 더 빠를 것입니다.SELECT목록을 표시하면 스캔 문제에 도움이 되지 않습니다.하지만 238개의 모든 칼럼이 동시에 필요한 적이 있습니까?
행운을 빕니다.
UPDATE
"임의 쿼리를 위한 대규모 테이블의 성능을 향상시키는 방법"이라는 질문에 대한 완전성을 위해 SQL Server 2012(또는 그 시점에 최신 버전)를 사용하고 있고 테이블이 업데이트되지 않는 경우 Columnstore Index를 사용하는 것이 이 특정 사례에는 도움이 되지 않습니다.이 새로운 기능에 대한 자세한 내용은 http://msdn.microsoft.com/en-us/library/gg492088.aspx 을 참조하십시오(SQL Server 2014부터 업데이트할 수 있도록 제작된 것으로 생각됨).
UPDATE 2 2
추가적으로 고려해야 할 사항은 다음과 같습니다.
- 클러스터된 인덱스에서 압축을 사용합니다.이 옵션은 SQL Server 2008에서 사용할 수 있게 되었지만 Enterprise Edition 전용 기능으로 사용할 수 있습니다.하지만 SQL Server 2016 SP1부터는 모든 에디션에서 데이터 압축을 사용할 수 있게 되었습니다!행 및 페이지 압축에 대한 자세한 내용은 데이터 압축 MSDN 페이지를 참조하십시오.
- 할 수 테이블에 큰:
INT,BIGINT,TINYINT,SMALLINT,CHAR,NCHAR,BINARY,DATETIME,SMALLDATETIME,MONEY넘는 것은 ① ② ③ ④ ③ ④ ④ ③NULL그런 다음 사용하도록 설정하는 것을 고려합니다.SPARSESQL Server 2008에서 사용할 수 있게 된 옵션입니다.자세한 내용은 Sparse Column 사용의 MSDN 페이지를 참조하십시오.
이 쿼리에는 몇 가지 문제가 있으며 모든 쿼리에 적용됩니다.
지수부족
에대인부족스에 대한 .er101_upd_date_iso칼럼은 오데드가 이미 언급했듯이 가장 중요한 것입니다.
일치하는 인덱스가 없으면(이 부족하면 테이블 검색이 발생할 수 있음) 큰 테이블에서 빠른 쿼리를 실행할 수 없습니다.
인덱스를 추가할 수 없는 경우(단 하나의 애드혹 쿼리에 대한 인덱스를 만드는 것이 의미가 없는 경우 등) 몇 가지 해결 방법을 제안합니다(애드혹 쿼리에 사용할 수 있음).
임시 테이블 사용
관심 있는 데이터의 부분 집합(행 및 열)에 임시 테이블을 만듭니다.임시 테이블은 원래 원본 테이블보다 훨씬 작아야 하며 필요한 경우 쉽게 인덱싱할 수 있으며 관심 있는 데이터의 하위 집합을 캐시할 수 있습니다.
임시 테이블을 만들려면 다음과 같은 코드(테스트되지 않음)를 사용할 수 있습니다.
-- copy records from last month to temporary table
INSERT INTO
#my_temporary_table
SELECT
*
FROM
er101_acct_order_dtl WITH (NOLOCK)
WHERE
er101_upd_date_iso > DATEADD(month, -1, GETDATE())
-- you can add any index you need on temp table
CREATE INDEX idx_er101_upd_date_iso ON #my_temporary_table(er101_upd_date_iso)
-- run other queries on temporary table (which can be indexed)
SELECT TOP 100
*
FROM
#my_temporary_table
ORDER BY
er101_upd_date_iso DESC
찬성:
- 모든 데이터 하위 집합에 대해 쉽게 수행할 수 있습니다.
- 관리가 용이합니다. 임시적이고 테이블 형태입니다.
- 다음과 같은 전반적인 시스템 성능에 영향을 미치지 않습니다.
view. - 임시 테이블을 인덱싱할 수 있습니다.
- 당신은 그것에 대해 신경 쓸 필요가 없습니다. 그것은 일시적인 것입니다 :).
단점:
- 데이터 스냅샷이지만 대부분의 임시 쿼리에는 이 정도면 충분합니다.
공통 테이블 표현식 - CTE
개인적으로 저는 CTE를 애드혹 쿼리와 함께 많이 사용합니다. CTE는 쿼리를 하나하나 구축하고 테스트하는 데 많은 도움이 됩니다.
(" 아래예다참조제쿼리는시하작로음으다▁with▁▁querythe▁see()▁below"로 시작하는 쿼리)WITH).
찬성:
- 큰 뷰에서 시작하여 실제로 필요한 항목을 선택하고 필터링하여 쉽게 구축할 수 있습니다.
- 테스트하기 쉽습니다.
단점:
- 일부 사람들은 CDE를 싫어합니다. CDE 쿼리는 길고 이해하기 어려운 것 같습니다.
뷰 작성
위와 유사하지만 임시 테이블 대신 뷰를 만듭니다(같은 쿼리를 자주 사용하고 인덱싱된 뷰를 지원하는 MS SQL 버전이 있는 경우).
관심 있는 데이터의 하위 집합에 뷰 또는 인덱싱된 뷰를 작성하고 뷰에서 쿼리를 실행할 수 있습니다. 쿼리에는 전체 테이블보다 훨씬 작은 데이터의 하위 집합만 포함되어야 합니다.
찬성:
- 하기 쉽습니다.
- 소스 데이터로 최신 상태입니다.
단점:
- 정의된 데이터 부분 집합에 대해서만 가능합니다.
- 업데이트 비율이 높은 대형 테이블의 경우 비효율적일 수 있습니다.
- 관리하기가 쉽지 않습니다.
- 전체 시스템 성능에 영향을 미칠 수 있습니다.
- 모든 MS SQL 버전에서 인덱싱된 뷰를 사용할 수 있는지 확신할 수 없습니다.
모든 열 선택
실행 중인 별 쿼리(SELECT * FROM큰 테이블 위에 있는 것은 좋은 것이 아닙니다...
열이 큰 경우(긴 문자열과 같은) 디스크에서 열을 읽고 네트워크를 통과하는 데 많은 시간이 걸립니다.
제가 대신할 수 있도록 노력하겠습니다.*꼭 필요한 열 이름을 입력합니다.
또는 모든 열이 필요한 경우 다음과 같은 형식으로 쿼리를 다시 작성합니다(공통 데이터 식 사용).
;WITH recs AS (
SELECT TOP 100
id as rec_id -- select primary key only
FROM
er101_acct_order_dtl
ORDER BY
er101_upd_date_iso DESC
)
SELECT
er101_acct_order_dtl.*
FROM
recs
JOIN
er101_acct_order_dtl
ON
er101_acct_order_dtl.id = recs.rec_id
ORDER BY
er101_upd_date_iso DESC
더티 읽기
애드혹 쿼리를 가속화할 수 있는 마지막 방법은 테이블 힌트가 있는 더티 읽기를 허용하는 것입니다.
힌트 대신 트랜잭션 분리 수준을 커밋되지 않은 읽기로 설정할 수 있습니다.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
또는 적절한 SQL Management Studio 설정을 설정합니다.
애드혹 쿼리의 경우 더티 읽기로 충분하다고 생각합니다.
테이블 스캔을 받는 중입니다. 즉, 다음에 정의된 인덱스가 없습니다.er101_upd_date_iso또는 해당 열이 기존 인덱스의 일부인 경우 기본 인덱서 열이 아닌 경우 인덱스를 사용할 수 없습니다.
누락된 인덱스를 추가하면 성능이 무한대로 향상됩니다.
가장 일반적으로 쿼리되는 열에 이미 인덱스가 있습니다.
그렇다고 해서 이 쿼리에 사용되는 것은 아니며, 그렇지 않을 수도 있습니다.
Gail Shaw의 SQL Server 성능 저하의 원인 찾기, 파트 1 및 파트 2를 읽는 것을 제안합니다.
이 질문은 특히 임시 쿼리에 대해 성능을 개선해야 하며 인덱스를 추가할 수 없음을 나타냅니다.이를 현실적으로 고려하면 어떤 테이블의 성능을 향상시키기 위해 무엇을 할 수 있을까요?
임시 쿼리를 고려하고 있으므로 WHERE 절과 ORDER BY 절은 열의 임의 조합을 포함할 수 있습니다.즉, 테이블에 배치된 인덱스와 거의 상관없이 테이블 검색이 필요한 쿼리가 있습니다. 위의 쿼리 계획에서 잘 수행되지 않는 쿼리에서 볼 수 있습니다.
이를 고려하여 기본 키의 클러스터된 인덱스를 제외하고 테이블에 인덱스가 전혀 없다고 가정합니다.이제 성능을 극대화하기 위해 어떤 옵션이 있는지 살펴보겠습니다.
테이블 조각 모음
클러스터된 인덱스가 있는 한 DBCC INDEX DEFRAG(사용되지 않음) 또는 ALTER INDEX를 사용하여 테이블을 조각 모음할 수 있습니다.이렇게 하면 테이블을 검색하는 데 필요한 디스크 읽기 수가 최소화되고 속도가 향상됩니다.
가능한 가장 빠른 디스크를 사용합니다.어떤 디스크를 사용하고 있는지는 말하지 않지만 SSD를 사용할 수 있는지는 알 수 없습니다.
tempdb를 최적화합니다.tempdb를 SSD와 같은 가장 빠른 디스크에 저장합니다. 이 SO 기사와 이 RedGate 기사를 참조하십시오.
다른 답변에서 언급한 바와 같이, 더 선택적인 쿼리를 사용하면 더 적은 데이터를 반환하므로 더 빨라야 합니다.
이제 인덱스를 추가할 수 있는 경우 수행할 수 있는 작업에 대해 살펴보겠습니다.
애드혹 쿼리에 대해 언급하지 않았다면 테이블에 대해 실행 중인 제한된 쿼리 집합에 대해 인덱스를 추가했을 것입니다.임시 쿼리에 대해 논의하고 있기 때문에 대부분의 경우 속도를 향상시키기 위해 무엇을 할 수 있습니까?
- 각 열에 단일 열 인덱스를 추가합니다.이를 통해 SQL Server는 적어도 대부분의 쿼리에 대해 속도를 향상시킬 수 있는 기능을 제공해야 하지만 최적의 상태는 아닙니다.
- 가장 일반적인 쿼리에 대한 특정 인덱스를 추가하여 쿼리가 최적화되도록 합니다.
- 제대로 수행되지 않는 쿼리를 모니터링하여 필요에 따라 특정 인덱스를 추가합니다.
편집
저는 2천 2백만 줄의 '큰' 테이블에서 몇 가지 테스트를 했습니다.내 테이블에는 6개의 열만 있지만 4GB의 데이터가 들어 있습니다.제 컴퓨터는 8Gb RAM과 쿼드 코어 CPU를 갖춘 훌륭한 데스크톱이며 단일 Agility 3 SSD를 갖추고 있습니다.
ID 열에 있는 기본 키를 제외한 모든 인덱스를 제거했습니다.
SQL 서버를 먼저 재시작한 경우 질문에 제시된 문제와 유사한 쿼리가 5초, 그 후 3초가 소요됩니다.데이터베이스 조정 어드바이저는 이 쿼리를 개선하기 위해 인덱스를 추가하여 99% 이상 개선할 것을 권장합니다.인덱스를 추가하면 쿼리 시간이 사실상 0이 됩니다.
또한 흥미로운 점은 제 쿼리 계획이 (클러스터된 인덱스 검색을 통해) 귀하의 쿼리 계획과 동일하지만 인덱스 검색이 쿼리 비용의 9%, 정렬이 나머지 91%를 차지한다는 것입니다.테이블에 엄청난 양의 데이터가 포함되어 있거나 디스크가 매우 느리거나 매우 느린 네트워크 연결을 통해 위치한다고 가정할 수 있습니다.
일부 쿼리에 사용되는 일부 열에 인덱스가 있더라도 '임의' 쿼리로 인해 테이블 검색이 발생한다는 사실은 인덱스가 부족하여 이 쿼리를 효율적으로 완료할 수 없음을 나타냅니다.
특히 날짜 범위의 경우 좋은 인덱스를 추가하기가 어렵습니다.
쿼리만 봐도 DB는 선택한 열별로 모든 레코드를 정렬해야 처음 n개의 레코드를 반환할 수 있습니다.
db도 order by clause 없이 전체 테이블 스캔을 하나요?테이블에 기본 키가 있습니까? PK가 없으면 db가 정렬을 수행하기 위해 더 많은 작업을 수행해야 합니까?
이것이 어떻게 가능한 걸까요?er101_upd_date_iso 열에 인덱스가 없으면 클러스터된 인덱스를 어떻게 사용할 수 있습니까?
인덱스는 각 리프 노드가 '행의 묶음'(SQL 내부 용어로 '페이지'라고 함)을 가리키는 B-트리입니다. 즉, 인덱스가 비클러스터형 인덱스인 경우입니다.
클러스터된 인덱스는 리프 노드가 행을 가리키는 대신 '행의 묶음'을 갖는 특별한 경우입니다.그래서...
테이블에는 클러스터된 인덱스가 하나만 있을 수 있습니다.
또한 전체 테이블이 클러스터된 인덱스로 저장되므로 테이블 검색이 아닌 인덱스 검색이 표시되기 시작했습니다.
클러스터된 인덱스를 사용하는 작업은 일반적으로 비클러스터된 인덱스보다 빠릅니다.
http://msdn.microsoft.com/en-us/library/ms177443.aspx 에서 더 읽어보기
새 인덱스(또는 기존 인덱스에 열)를 추가하면 INSERT/UPDATE 비용이 증가한다고 했으므로 이 열을 인덱스에 추가하는 것을 고려해야 합니다.그러나 사용률이 낮은 일부 인덱스(또는 기존 인덱스의 열)를 제거하여 'er101_upd_date_iso'로 대체할 수도 있습니다.
인덱스 변경이 불가능한 경우 열에 통계를 추가하는 것이 좋습니다. 열이 인덱스된 열과 어느 정도 상관 관계가 있을 때 해당 열을 고정할 수 있습니다.
http://msdn.microsoft.com/en-us/library/ms188038.aspx
참고로, ER101_ACCT_ORDER_DTL의 테이블 스키마와 기존 인덱스를 게시할 수 있다면 훨씬 더 많은 도움을 받을 수 있습니다. 아마도 쿼리를 다시 작성하여 일부를 사용할 수 있을 것입니다.
1M 테스트가 더 빨리 실행되는 이유 중 하나는 임시 테이블이 완전히 메모리에 있고 서버에서 메모리 압력이 발생하는 경우에만 디스크로 이동하기 때문일 수 있습니다.쿼리를 다시 작성하여 주문을 제거하거나, 앞서 언급한 것처럼 양호한 클러스터형 인덱스 및 피복 인덱스를 추가하거나, DMV를 쿼리하여 IO 압력을 확인하여 하드웨어와 관련이 있는지 확인할 수 있습니다.
-- From Glen Barry
-- Clear Wait Stats (consider clearing and running wait stats query again after a few minutes)
-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
-- Check Task Counts to get an initial idea what the problem might be
-- Avg Current Tasks Count, Avg Runnable Tasks Count, Avg Pending Disk IO Count across all schedulers
-- Run several times in quick succession
SELECT AVG(current_tasks_count) AS [Avg Task Count],
AVG(runnable_tasks_count) AS [Avg Runnable Task Count],
AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count]
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255 OPTION (RECOMPILE);
-- Sustained values above 10 suggest further investigation in that area
-- High current_tasks_count is often an indication of locking/blocking problems
-- High runnable_tasks_count is a good indication of CPU pressure
-- High pending_disk_io_count is an indication of I/O pressure
인덱스를 추가하는 것은 선택사항이 아니지만 테이블 스캔을 제거할 수 있는 유일한 옵션이라고 말씀하신 것으로 알고 있습니다.검색을 수행할 때 SQL Server는 테이블의 2백만 행을 모두 읽어 쿼리를 수행합니다.
이 문서에서는 더 많은 정보를 제공하지만 다음 사항을 기억하십시오.검색 = 양호, 검색 = 불량.
둘째, 선택 *를 없애고 필요한 열만 선택할 수는 없습니까?셋째, "어디" 조항이 없습니까?인덱스가 있더라도 모든 것을 읽고 있기 때문에 가장 잘 얻을 수 있는 것은 인덱스 스캔입니다(테이블 스캔보다 낫지만 탐색이 아니라 목표로 해야 하는 것).
처음부터 꽤 오랜 시간이 흘렀다는 것을 압니다...이 모든 대답에는 많은 지혜가 있습니다.쿼리를 개선하려면 색인을 잘 작성하는 것이 가장 중요합니다.음, 거의 첫번째.가장 먼저(즉) 코드를 효율적으로 변경하는 것입니다.따라서 WHERE가 없는 쿼리가 있거나 WHERE 조건이 충분히 선택적이지 않은 경우 데이터를 가져오는 방법은 TABLE SCAN(INDEX SCAN)뿐입니다.테이블의 모든 열이 필요한 경우 TABLE SCAN이 사용됩니다. 이에 대한 질문은 없습니다.데이터 조직 유형에 따라 힙 검색 또는 클러스터된 인덱스 검색일 수 있습니다.(가능한 경우) 작업 속도를 높이는 마지막 방법은 가능한 한 많은 코어가 검색에 사용되도록 하는 것입니다. OPTION(MAXDOP 0).물론 스토리지의 주제를 무시하고 있지만 RAM이 무제한인지 확인해야 합니다. 말할 필요도 없습니다. :)
언급URL : https://stackoverflow.com/questions/10025569/improve-sql-server-query-performance-on-large-tables
'source' 카테고리의 다른 글
| Python 스크립트 내에서 UAC 권한 상승을 요청하시겠습니까? (0) | 2023.04.27 |
|---|---|
| 엔티티 프레임워크의 모든 엔티티 삭제 (0) | 2023.04.27 |
| Eclipse용 플러그인을 작성하는 방법은 무엇입니까? (0) | 2023.04.27 |
| Ruby on Rails vs.의 경우 NET MVC 3.NET 가이? (0) | 2023.04.27 |
| WPF Textblock, Text Atribut의 줄 바꿈 (0) | 2023.04.22 |