Databases
StackOverflow2013
Postsdbo
POImprove SQL Server query performance on large tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POImprove SQL Server query performance on large tables
    primarykey
    data
    text
    <p>I have a relatively large table (currently 2 million records) and would like to know if it's possible to improve performance for ad-hoc queries. The word <strong>ad-hoc</strong> being key here. Adding indexs is not an option (there are already indexs on the columns which are queried most commonly).</p> <p>Running a simple query to return the 100 most recently updated records:</p> <pre><code>select top 100 * from ER101_ACCT_ORDER_DTL order by er101_upd_date_iso desc </code></pre> <p>Takes several minutes. See execution plan below:</p> <p><img src="https://i.stack.imgur.com/oB1H2.png" alt="enter image description here"></p> <p>Additional detail from the table scan:</p> <p><img src="https://i.stack.imgur.com/SNYjI.png" alt="enter image description here"></p> <pre><code>SQL Server Execution Times: CPU time = 3945 ms, elapsed time = 148524 ms. </code></pre> <p>The server is pretty powerful (from memory 48GB ram, 24 core processor) running sql server 2008 r2 x64.</p> <p><strong>Update</strong></p> <p>I found this code to create a table with 1,000,000 records. I thought i could then run <code>SELECT TOP 100 * FROM testEnvironment ORDER BY mailAddress DESC</code> on a few different servers to find out if my disk access speeds were poor on the server.</p> <pre><code>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; </code></pre> <p>But on the three test servers the query ran almost instantaneously. Can anyone explain this?</p> <p><img src="https://i.stack.imgur.com/fXPTV.png" alt="enter image description here"></p> <p><strong>Update 2</strong></p> <p>Thank you for the comments- please keep them coming... they led me to try changing the primary key index from non-clustered to clustered with rather interesting (and unexpected?) results.</p> <p>Non-clustered:</p> <p><img src="https://i.stack.imgur.com/EOgQv.png" alt="enter image description here"></p> <pre><code>SQL Server Execution Times: CPU time = 3634 ms, elapsed time = 154179 ms. </code></pre> <p>Clustered:</p> <p><img src="https://i.stack.imgur.com/3jM0o.png" alt="enter image description here"></p> <pre><code>SQL Server Execution Times: CPU time = 2650 ms, elapsed time = 52177 ms. </code></pre> <p>How is this possible? Without an index on the er101_upd_date_iso column how can a clustered index scan be used?</p> <p><strong>Update 3</strong></p> <p>As requested- this is the create table script:</p> <pre><code>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] </code></pre> <p>The table is 2.8 GB in size with index size standing at 3.9 GB.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLee Tickett
    1. USLee Tickett
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTDuplicate
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POImprove SQL Server query performance on large tables
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POImprove SQL Server query performance on large tables
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POImprove SQL Server query performance on large tables
      1. USLee Tickett
      1. VTBountyStart
    1. COThere are little hints when you hover mouse cursor over plan item. They show estimated I/O and CPU cost. I would take care about I/O cost at first.
      singulars
      1. POImprove SQL Server query performance on large tables
      1. USGrzegorz Gierlik
    2. CO`Table Scan` indicates a **heap** (no clustered index) - so the first step would be to add a **good, speedy** clustered index to your table. Second step might be to investigate if a nonclustered index on `er101_upd_date_iso` would help (and not cause other performance drawbacks)
      singulars
      1. POImprove SQL Server query performance on large tables
      1. USmarc_s
    3. COthanks @GrzegorzGierlik the hover tip was helpful- i actually re-ran the query (previously something was cached as the results are quite different). now i think it actually looks like it's disk time rather than cpu?
      singulars
      1. POImprove SQL Server query performance on large tables
      1. USLee Tickett
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload