큰 테이블에서 SQL Server 쿼리 성능 향상
비교적 큰 테이블 (현재 2 백만 개의 레코드)이 있고 임시 쿼리의 성능을 향상시킬 수 있는지 알고 싶습니다. 여기에서 임시 단어 가 핵심입니다. 인덱스 추가는 옵션이 아닙니다 (가장 일반적으로 쿼리되는 열에 이미 인덱스가 있습니다).
간단한 쿼리를 실행하여 가장 최근에 업데이트 된 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)가 너무 빨리 실행 된 경우에도 간단한 쿼리가 왜 그렇게 오래 걸리는지에 대해 좀 더 명확 해졌습니다.
여기서 (그리고 많은 성능이 떨어지는 상황에서) 주된 문제는 잘못된 데이터 모델링입니다. 238 개의 열은 999 개의 인덱스가있는 것처럼 금지되지 않지만 일반적으로 그다지 현명하지 않습니다.
권장 사항 :
- 첫째,이 테이블은 정말 리모델링이 필요합니다. 이것이 데이터웨어 하우스 테이블이라면 아마도, 그렇지 않다면이 필드들은 실제로 모두 동일한 PK를 가질 수있는 여러 테이블로 분할되어야합니다. 마스터 레코드 테이블이 있고 하위 테이블은 일반적으로 연관된 속성을 기반으로하는 종속 정보 일 뿐이며 해당 테이블의 PK는 마스터 테이블의 PK와 동일하므로 마스터 테이블에 대한 FK도 동일합니다. 마스터와 모든 하위 테이블 사이에는 일대일 관계가 있습니다.
ANSI_PADDING OFF시간이 지남에 따라 다양한 컬럼 추가로 인해 테이블 내에서 일관성이없는 것은 말할 것도없고 의 사용 은 방해가됩니다. 지금 수정할 수 있는지 확실하지 않지만 이상적으로는 항상을 사용ANSI_PADDING ON하거나 최소한 모든ALTER TABLE문 에서 동일한 설정을 사용합니다 .- 2 개의 추가 파일 그룹 (테이블 및 인덱스)을 만드는 것을 고려하십시오.
PRIMARYSQL SERVER가 개체에 대한 모든 데이터와 메타 데이터를 저장하는 곳이므로 물건을 넣지 않는 것이 가장 좋습니다 . 테이블 및 클러스터형 인덱스 (테이블의 데이터 인 것처럼)[Tables]및 모든 비 클러스터형 인덱스를[Indexes] - 채우기 비율을 50 %에서 늘립니다. 이 낮은 숫자는 인덱스 공간이 데이터 공간보다 큰 이유 일 수 있습니다. 인덱스 재 빌드를 수행하면 데이터에 최대 4k (총 8k 페이지 크기 중)가 사용 된 데이터 페이지가 다시 생성되므로 테이블이 넓은 영역에 분산됩니다.
- 대부분 또는 모든 쿼리에
WHERE조건 에 "ER101_ORG_CODE"가있는 경우 클러스터형 인덱스의 선행 열로 이동하는 것이 좋습니다. "ER101_ORD_NBR"보다 더 자주 사용된다고 가정합니다. "ER101_ORD_NBR"이 더 자주 사용되면 유지하십시오. 필드 이름이 "OrganizationCode"및 "OrderNumber"를 의미한다고 가정하면 "OrgCode"가 그 안에 여러 "OrderNumbers"가있을 수있는 더 나은 그룹화입니다. - 사소한 점이지만 "ER101_ORG_CODE"가 항상 2 자이면
CHAR(2)대신 사용VARCHAR(2)하여 가변 너비 크기를 추적하고 수백만 행을 더하는 행 헤더에 바이트를 저장합니다. - 다른 사람들이 언급했듯이 사용
SELECT *하면 성능이 저하됩니다. SQL Server가 모든 열을 반환해야하기 때문에 다른 인덱스에 관계없이 클러스터형 인덱스 스캔을 수행 할 가능성이 더 높을뿐만 아니라 SQL Server에서 테이블 정의로 이동*하여 모든 열 이름으로 변환하는 데 시간이 걸립니다. . 목록 에서 238 개의 열 이름을 모두 지정하는 것이 약간 더 빠르지SELECT만 Scan 문제에 도움이되지는 않습니다. 하지만 어쨌든 동시에 238 개의 컬럼이 모두 필요합니까?
행운을 빕니다!
업데이트
"임시 쿼리를 위해 큰 테이블에서 성능을 향상시키는 방법"이라는 질문에 대한 완전성을 위해 누군가 SQL Server 2012 (또는 최신 버전)를 사용하는 경우이 특정 경우에는 도움이되지 않는다는 점에 유의해야합니다. 그 때가되면) 테이블이 업데이트되지 않는 경우 Columnstore Indexes를 사용하는 것이 옵션입니다. 새로운 기능에 대한 자세한 내용은 http://msdn.microsoft.com/en-us/library/gg492088.aspx를 참조하십시오 (SQL Server 2014부터 업데이트 할 수 있도록 만들어 졌다고 생각합니다).
업데이트 2
추가 고려 사항은 다음과 같습니다.
- 클러스터형 인덱스에서 압축을 활성화합니다. 이 옵션은 SQL Server 2008에서 사용할 수 있지만 Enterprise Edition 전용 기능입니다. 그러나 SQL Server 2016 SP1 부터 모든 버전에서 데이터 압축을 사용할 수 있습니다 ! 행 및 페이지 압축에 대한 자세한 내용 은 데이터 압축에 대한 MSDN 페이지를 참조하십시오 .
- 당신은 데이터 압축을 사용할 수없는 경우, 또는 당신이 고정 길이 형식의 열이 경우, 특정 테이블에 대한 많은 혜택을 제공 (하지 않을 경우
INT,BIGINT,TINYINT,SMALLINT,CHAR,NCHAR,BINARY,DATETIME,SMALLDATETIME,MONEY, 등) 훨씬 넘는 50 행의 %가이면 SQL Server 2008에서 사용할NULL수있는SPARSE옵션을 활성화하는 것이 좋습니다. 자세한 내용은 스파 스 열 사용에 대한 MSDN 페이지를 참조 하십시오.
이 쿼리에는 몇 가지 문제가 있습니다 (모든 쿼리에 적용됨).
색인 부족
er101_upd_date_iso열에 대한 인덱스 부족은 Oded 가 이미 언급했듯이 가장 중요한 것 입니다.
일치하는 인덱스가 없으면 (이로 인해 테이블 스캔이 발생할 수 있음) 큰 테이블에서 빠른 쿼리를 실행할 기회가 없습니다.
인덱스를 추가 할 수없는 경우 ( 하나의 임시 쿼리에 대한 인덱스를 생성 할 필요가없는 등 다양한 이유로 ) 몇 가지 해결 방법을 제안합니다 (임시 쿼리에 사용할 수 있음).
1. 임시 테이블 사용
관심있는 데이터의 하위 집합 (행 및 열)에 임시 테이블을 만듭니다. 임시 테이블은 원본 소스 테이블보다 훨씬 작아야하며 필요한 경우 쉽게 인덱싱 할 수 있으며 관심있는 데이터의 하위 집합을 캐시 할 수 있습니다 .
임시 테이블을 만들려면 다음과 같은 코드 (테스트되지 않음)를 사용할 수 있습니다.
-- 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
장점 :
- 모든 데이터 하위 집합에 대해 쉽게 수행 할 수 있습니다.
- 관리하기 쉬움- 일시 적이고 테이블 입니다.
- .NET과 같은 전체 시스템 성능에 영향을주지 않습니다
view. - 임시 테이블을 인덱싱 할 수 있습니다.
- 당신은 그것에 대해 신경 쓸 필요가 없습니다-그것은 일시적입니다 :).
단점 :
- 데이터의 스냅 샷이지만 대부분의 임시 쿼리에 충분할 것입니다.
2. 공통 테이블 표현-CTE
개인적으로 저는 임시 쿼리와 함께 CTE 를 많이 사용 합니다. 쿼리를 하나씩 작성 (및 테스트)하는 데 많은 도움이됩니다.
아래 예를 참조하십시오 (으로 시작하는 쿼리 WITH).
장점 :
- 큰보기 에서 시작한 다음 실제로 필요한 것을 선택하고 필터링 하기 쉽습니다 .
- 테스트하기 쉽습니다.
단점 :
- 어떤 사람들은 CDE를 싫어합니다. CDE 쿼리는 길고 이해하기 어려운 것 같습니다.
3.보기 만들기
위와 비슷하지만 임시 테이블 대신 뷰를 만듭니다 (동일한 쿼리를 자주 사용하고 인덱싱 된 뷰를 지원하는 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
더티 읽기
ad-hoc 쿼리의 속도를 높일 수있는 마지막 방법은 table hint로 더티 읽기 를 허용하는 것 입니다.WITH (NOLOCK)
힌트 대신 커밋되지 않은 상태 로 읽도록 트랜잭션 격리 수준 을 설정할 수 있습니다 .
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
또는 적절한 SQL Management Studio 설정을 지정하십시오.
애드혹 쿼리의 경우 더티 읽기 가 충분 하다고 가정합니다 .
거기에서 테이블 스캔을 받고 있습니다. 즉 ,에 인덱스가 정의되어 있지 않거나er101_upd_date_iso 해당 열이 기존 인덱스의 일부인 경우 인덱스를 사용할 수 없습니다 (기본 인덱서 열이 아닐 수 있음).
누락 된 인덱스를 추가하면 성능에 도움이됩니다.
가장 일반적으로 쿼리되는 열에 이미 인덱스가 있습니다.
그렇다고이 쿼리에서 사용된다는 의미는 아닙니다 (아마도 사용하지 않을 것입니다).
Gail Shaw의 1 부 및 2 부 에서 SQL Server에서 성능 저하의 원인 찾기를 읽는 것이 좋습니다 .
이 질문은 특히 임시 쿼리에 대해 성능을 개선해야하며 인덱스를 추가 할 수 없다고 명시합니다. 그렇다면이를 액면 그대로 받아들이면 테이블의 성능을 향상시키기 위해 무엇을 할 수 있습니까?
임시 쿼리를 고려 중이므로 WHERE 절과 ORDER BY 절은 모든 열 조합을 포함 할 수 있습니다. 즉, 테이블에 배치 된 인덱스에 관계없이 위의 성능이 낮은 쿼리의 쿼리 계획에서 볼 수 있듯이 테이블 스캔이 필요한 쿼리가있을 수 있습니다.
이를 고려하여 기본 키의 클러스터형 인덱스를 제외하고 테이블에 인덱스가 전혀 없다고 가정 해 보겠습니다. 이제 성능을 최대화하기 위해 필요한 옵션을 고려해 보겠습니다.
테이블 조각 모음
클러스터형 인덱스가있는 한 DBCC INDEXDEFRAG (사용되지 않음) 또는 바람직하게는 ALTER INDEX를 사용하여 테이블 조각 모음을 수행 할 수 있습니다 . 이렇게하면 테이블을 스캔하는 데 필요한 디스크 읽기 수가 최소화되고 속도가 향상됩니다.
가능한 가장 빠른 디스크를 사용하십시오. 사용중인 디스크가 아니라 SSD를 사용할 수 있는지 여부는 말하지 않습니다.
tempdb를 최적화합니다. 가능한 가장 빠른 디스크, 다시 SSD에 tempdb를 배치합니다. 이 SO 기사 와이 RedGate 기사를 참조하십시오 .
다른 답변에서 언급했듯이 더 선택적 쿼리를 사용하면 더 적은 데이터가 반환되므로 더 빠릅니다.
이제 인덱스를 추가 할 수있는 경우 무엇을 할 수 있는지 살펴 보겠습니다.
우리가하면 되지 않은 임시 쿼리에 대해 얘기, 우리는 테이블에 대해 실행되는 쿼리의 제한된 집합을 위해 특별히 인덱스를 추가합니다. 임시 쿼리에 대해 논의 하고 있으므로 대부분 의 경우 속도를 향상시키기 위해 무엇을 할 수 있습니까?
- 각 열에 단일 열 인덱스를 추가합니다. 이렇게하면 대부분의 쿼리에 대한 속도를 향상시키기 위해 SQL Server에 최소한 작업 할 수있는 기능이 제공되지만 최적은 아닙니다.
- 최적화되도록 가장 일반적인 쿼리에 대한 특정 인덱스를 추가합니다.
- 성능이 저조한 쿼리를 모니터링하여 필요에 따라 특정 인덱스를 추가합니다.
편집하다
2,200 만 행의 '대형'테이블에서 몇 가지 테스트를 실행했습니다. 내 테이블에는 6 개의 열만 있지만 4GB의 데이터가 포함되어 있습니다. 내 컴퓨터는 8Gb RAM과 쿼드 코어 CPU를 갖춘 존경받는 데스크탑이며 단일 Agility 3 SSD가 있습니다.
Id 열의 기본 키를 제외한 모든 인덱스를 제거했습니다.
질문에 제공된 문제와 유사한 쿼리는 SQL 서버가 먼저 다시 시작되고 3 초 후에 다시 시작되면 5 초가 걸립니다. 데이터베이스 튜닝 어드바이저는 99 % 이상의 예상 개선으로이 쿼리를 개선하기 위해 인덱스를 추가 할 것을 분명히 권장합니다. 인덱스를 추가하면 쿼리 시간이 사실상 0이됩니다.
흥미로운 점은 내 쿼리 계획이 (클러스터형 인덱스 스캔과 함께) 귀하의 것과 동일하지만 인덱스 스캔이 쿼리 비용의 9 %를 차지하고 나머지 91 %를 정렬한다는 것입니다. 테이블에 엄청난 양의 데이터가 포함되어 있거나 디스크가 매우 느리거나 매우 느린 네트워크 연결에 있다고 가정 할 수 있습니다.
일부 쿼리에서 사용되는 일부 열에 인덱스가 있더라도 '임시'쿼리로 인해 테이블 스캔이 발생한다는 사실은이 쿼리를 효율적으로 완료 할 수있는 충분한 인덱스가 없음을 나타냅니다.
특히 날짜 범위의 경우 좋은 색인을 추가하기가 어렵습니다.
쿼리 만 보면 db는 처음 n 개의 레코드를 반환 할 수 있도록 선택한 열별로 모든 레코드를 정렬해야합니다.
db는 order by 절없이 전체 테이블 스캔을 수행합니까? 테이블에 기본 키가 있습니까? PK가 없으면 db가 정렬을 수행하기 위해 더 열심히 일해야합니까?
이것이 어떻게 가능한지? er101_upd_date_iso 열에 인덱스가 없으면 클러스터형 인덱스 스캔을 어떻게 사용할 수 있습니까?
인덱스는 각 리프 노드가 '행 묶음'(SQL 내부 용어로 '페이지'라고 함)을 가리키는 B- 트리입니다. 즉, 인덱스가 클러스터되지 않은 인덱스 인 경우입니다.
클러스터형 인덱스는 리프 노드에 '행 무리'가있는 특수한 경우입니다. 그래서 ...
1) 테이블에는 클러스터형 인덱스가 하나만있을 수 있습니다.
이것은 또한 전체 테이블이 클러스터 된 인덱스로 저장된다는 것을 의미하므로 테이블 스캔이 아닌 인덱스 스캔을보기 시작했습니다.
2) 클러스터형 인덱스를 사용하는 작업은 일반적으로 비 클러스터형 인덱스보다 빠릅니다.
http://msdn.microsoft.com/en-us/library/ms177443.aspx 에서 자세히 알아보세요 .
문제가있는 경우 새 인덱스 (또는 기존 인덱스에 열)를 추가하면 INSERT / UPDATE 비용이 증가하므로이 열을 인덱스에 추가하는 것을 실제로 고려해야합니다. 그러나 'er101_upd_date_iso'로 대체하기 위해 활용도가 낮은 일부 인덱스 (또는 기존 인덱스에서 열)를 제거 할 수 있습니다.
인덱스 변경이 불가능한 경우 컬럼에 통계를 추가하는 것이 좋습니다. 컬럼이 인덱싱 된 컬럼과 상관 관계가있을 때 문제를 해결할 수 있습니다.
http://msdn.microsoft.com/en-us/library/ms188038.aspx
BTW, 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 백만 행을 읽습니다.
이 기사는 더 많은 정보를 제공하지만 기억하라 : Seek = good, Scan = bad.
둘째, select *를 제거하고 필요한 열만 선택할 수 없습니까? 셋째, "where"절이 없습니까? 인덱스가 있어도 모든 것을 읽고 있기 때문에 인덱스 스캔을 얻을 수 있습니다 (테이블 스캔보다 낫지 만 목표로해야하는 탐색이 아닙니다).
I know it's been quite a time since the beginning... There is a lot of wisdom in all these answers. Good indexing is the first thing when trying to improve a query. Well, almost the first. The most-first (so to speak) is making changes to code so that it's efficient. So, after all's been said and done, if one has a query with no WHERE, or when the WHERE-condition is not selective enough, there is only one way to get the data: TABLE SCAN (INDEX SCAN). If one needs all the columns from a table, then TABLE SCAN will be used - no question about it. This might be a heap scan or clustered index scan, depending on the type of data organization. The only last way to speed things up (if at all possible), is to make sure that as many cores are used as possible to do the scan: OPTION (MAXDOP 0). I'm ignoring the subject of storage, of course, but one should make sure that one has unlimited RAM, which goes without saying :)
참고 URL : https://stackoverflow.com/questions/10025569/improve-sql-server-query-performance-on-large-tables
'code' 카테고리의 다른 글
| 관련된 모든 Django 모델 개체 가져 오기 (0) | 2020.09.25 |
|---|---|
| jQuery UI 자동 완성에서 HTML 사용 (0) | 2020.09.25 |
| 추적 된 원격 분기의 변경 사항으로 로컬 분기 업데이트 (0) | 2020.09.25 |
| vue.js 웹팩 프로젝트에서 favicon.ico를 올바르게 설정하는 방법은 무엇입니까? (0) | 2020.09.25 |
| 데이터베이스에서 장고 모델을 생성 할 수 있습니까? (0) | 2020.09.25 |