Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Clustered Index Seek Causing Poor Performance - Suggestions For Indexes Expand / Collapse
Author
Message
Posted Tuesday, December 8, 2009 8:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:17 PM
Points: 16, Visits: 276
I have an query, that is being sent via an application that is resulting in poor performance. For approximatly 2.1 million records to return, it is taking about 3 minutes.

I have tried using the Database Tuning Wizard to try to get some suggestions for this query, but it is not returning any suggestions for indexes.

As you will see below, the query is selecting a large number of columns and joins about 4 tables.

I did a display estimated execution plan, and the clustered index seek it is using, has a job operator cost of 131%; with an estimate I/O cost of 129.

The query is as follows:
SELECT
LG.ACCNT_CODE,LG.PERIOD,CONVERT (CHAR(8),LG.TRANS_DATETIME,112), LG.JRNAL_NO,LG.JRNAL_LINE,LG.AMOUNT,LG.D_C,LG.ALLOCATION,
LG.JRNAL_TYPE,LG.JRNAL_SRCE,LG.TREFERENCE,LG.DESCRIPTN,CASE WHEN LG.ENTRY_DATETIME IS NULL THEN '00000000' ELSE CONVERT (CHAR(8),
LG.ENTRY_DATETIME,112) END, LG.ENTRY_PRD,CASE WHEN LG.DUE_DATETIME IS NULL THEN '00000000' ELSE CONVERT (CHAR(8),LG.DUE_DATETIME,112) END,
LG.ALLOC_REF,CASE WHEN LG.ALLOC_DATETIME IS NULL THEN '00000000' ELSE CONVERT (CHAR(8),LG.ALLOC_DATETIME,112) END, LG.ALLOC_PERIOD,
LG.ASSET_IND,ASSET_CODE,ASSET_SUB,CONV_CODE,CONV_RATE,OTHER_AMT,OTHER_DP,CLEARDOWN,REVERSAL,LOSS_GAIN,ROUGH_FLAG,IN_USE_FLAG,ANAL_T0,
ANAL_T1,ANAL_T2,ANAL_T3,ANAL_T4,ANAL_T5,ANAL_T6,ANAL_T7,ANAL_T8,ANAL_T9,CASE WHEN POSTING_DATETIME IS NULL THEN '00000000'
ELSE CONVERT (CHAR(8),POSTING_DATETIME,112) END, ALLOC_IN_PROGRESS,HOLD_REF, HOLD_OP_ID, BASE_RATE, BASE_OPERATOR, CONV_OPERATOR,
REPORT_RATE,REPORT_OPERATOR,REPORT_AMT, MEMO_AMT,EXCLUDE_BAL,LE_DETAILS_IND, CONSUMED_BDGT_ID, CV4_CONV_CODE, CV4_AMT, CV4_CONV_RATE,
CV4_OPERATOR, CV4_DP, CV5_CONV_CODE, CV5_AMT, CV5_CONV_RATE, CV5_OPERATOR, CV5_DP, RTRIM(LINK_REF_1), RTRIM(LINK_REF_2), RTRIM(LINK_REF_3),
ALLOCN_CODE, ALLOCN_STMNTS, OPR_CODE, SPLIT_ORIG_LINE, CASE WHEN VAL_DATETIME IS NULL THEN '00000000' ELSE CONVERT (CHAR(8),
VAL_DATETIME,112) END, RTRIM(SIGNING_DETAILS), CASE WHEN INSTLMT_DATETIME IS NULL THEN '00000000' ELSE CONVERT (CHAR(8),
INSTLMT_DATETIME,112) END, BINDER_STATUS, AGREED_STATUS, RTRIM(SPLIT_LINK_REF), RTRIM(PSTG_REF), TRUE_RATED, CASE WHEN
HOLD_DATETIME IS NULL THEN '00000000' ELSE CONVERT (CHAR(8),HOLD_DATETIME,112) END, RTRIM(HOLD_TEXT), INSTLMT_NUM, SUPPLMNTRY_EXTSN,
APRVLS_EXTSN, REVAL_LINK_REF, SAVED_SET_NUM, AUTHORISTN_SET_REF, PYMT_AUTHORISTN_SET_REF, MAN_PAY_OVER, PYMT_STAMP, AUTHORISTN_IN_PROGRESS,
SPLIT_IN_PROGRESS, VCHR_NUM, JNL_CLASS_CODE, ORIGINATOR_ID, CASE WHEN ORIGINATED_DATETIME IS NULL THEN '00000000'
ELSE CONVERT (CHAR(8),ORIGINATED_DATETIME,112) END, LG.LAST_CHANGE_USER_ID, CASE WHEN LG.LAST_CHANGE_DATETIME IS NULL THEN '00000000'
ELSE CONVERT (CHAR(8),LG.LAST_CHANGE_DATETIME,112) END, AFTER_PSTG_ID, CASE WHEN AFTER_PSTG_DATETIME IS NULL THEN '00000000'
ELSE CONVERT (CHAR(8),AFTER_PSTG_DATETIME,112) END, POSTER_ID, ALLOC_ID, JNL_REVERSAL_TYPE, CASE
WHEN DOC_1_DATETIME IS NULL THEN '00000000' ELSE CONVERT (CHAR(8),DOC_1_DATETIME,112) END, CASE WHEN DOC_2_DATETIME IS NULL
THEN '00000000' ELSE CONVERT (CHAR(8),DOC_2_DATETIME,112) END, CASE WHEN DOC_3_DATETIME IS NULL THEN '00000000'
ELSE CONVERT (CHAR(8),DOC_3_DATETIME,112) END, CASE WHEN DOC_4_DATETIME IS NULL THEN '00000000' ELSE CONVERT (CHAR(8),DOC_4_DATETIME,112)
END, DOC_NUM_PREF_1, DOC_NUMBER_1, DOC_NUM_PREF_2, DOC_NUMBER_2, DOC_NUM_PREF_3, DOC_NUMBER_3, DOC_NUM_PREF_4, DOC_NUMBER_4,
CASE WHEN DISC_1_DATETIME IS NULL THEN '00000000' ELSE CONVERT (CHAR(8),DISC_1_DATETIME,112) END, DISC_PERCENT_1, CASE
WHEN DISC_2_DATETIME IS NULL THEN '00000000' ELSE CONVERT (CHAR(8),DISC_2_DATETIME,112) END, DISC_PERCENT_2, CASE
WHEN INTEREST_DATETIME IS NULL THEN '00000000' ELSE CONVERT (CHAR(8),INTEREST_DATETIME,112) END, INTEREST_PERCENT,
CASE WHEN LATE_PAY_DATETIME IS NULL THEN '00000000' ELSE CONVERT (CHAR(8),LATE_PAY_DATETIME,112) END, LATE_PAY_PERCENT,PAYMENT_REF,
BANK_CODE,SOURCE_REF,MODULE_CODE,PAYMENT_TERMS_CODE , GNRL_DESCR_01, GNRL_DESCR_02, GNRL_DESCR_03, GNRL_DESCR_04, GNRL_DESCR_05,
GNRL_DESCR_06, GNRL_DESCR_07, GNRL_DESCR_08, GNRL_DESCR_09, GNRL_DESCR_10, GNRL_DESCR_11, GNRL_DESCR_12, GNRL_DESCR_13, GNRL_DESCR_14,
GNRL_DESCR_15, GNRL_DESCR_16, GNRL_DESCR_17, GNRL_DESCR_18, GNRL_DESCR_19, GNRL_DESCR_20, GNRL_DESCR_21, GNRL_DESCR_22, GNRL_DESCR_23,
GNRL_DESCR_24, GNRL_DESCR_25, CASE WHEN GNRL_1_DATETIME IS NULL THEN '00000000' ELSE CONVERT (CHAR(8),GNRL_1_DATETIME,112) END,
CASE WHEN GNRL_2_DATETIME IS NULL THEN '00000000' ELSE CONVERT (CHAR(8),GNRL_2_DATETIME,112) END,
CASE WHEN GNRL_3_DATETIME IS NULL THEN '00000000' ELSE CONVERT (CHAR(8),GNRL_3_DATETIME,112) END,
CASE WHEN GNRL_4_DATETIME IS NULL THEN '00000000' ELSE CONVERT (CHAR(8),GNRL_4_DATETIME,112) END,
CASE WHEN GNRL_5_DATETIME IS NULL THEN '00000000' ELSE CONVERT (CHAR(8),GNRL_5_DATETIME,112) END,
USER_NAME , STD_TEXT_CLASS_CODE,STD_TEXT_CODE

FROM BD1_A_SALFLDG LG LEFT OUTER JOIN BD1_A_SALFLEX LX ON
(LG.ACCNT_CODE = LX.ACCNT_CODE AND LG.PERIOD = LX.PERIOD AND LG.TRANS_DATETIME = LX.TRANS_DATETIME AND LG.JRNAL_NO = LX.JRNAL_NO AND
LG.JRNAL_LINE = LX.JRNAL_LINE )LEFT OUTER JOIN BD1_A_SALFLDG_LAD LD ON (LG.ACCNT_CODE = LD.ACCNT_CODE AND LG.PERIOD = LD.PERIOD AND
LG.TRANS_DATETIME = LD.TRANS_DATETIME AND LG.JRNAL_NO = LD.JRNAL_NO AND LG.JRNAL_LINE = LD.JRNAL_LINE )
LEFT OUTER JOIN BD1_STD_TEXT_LDG ST ON (LG.ACCNT_CODE = ST.ACCNT_CODE AND LG.PERIOD = ST.PERIOD AND
LG.TRANS_DATETIME = ST.TRANS_DATETIME AND LG.JRNAL_NO = ST.JRNAL_NO AND LG.JRNAL_LINE = ST.JRNAL_LINE )
WHERE LG.ACCNT_CODE = '15010' AND LG.PERIOD <= 000000000002009009 AND CONV_CODE > ''
AND AUTHORISTN_IN_PROGRESS = 0 ORDER BY LG.ACCNT_CODE, LG.PERIOD, LG.TRANS_DATETIME, LG.JRNAL_NO, LG.JRNAL_LINE

Clustered indexes for each table in the join:
BD1_A_SALFLDG:
PRIMARY KEY CLUSTERED
(
[ACCNT_CODE] ASC,
[PERIOD] ASC,
[TRANS_DATETIME] ASC,
[JRNAL_NO] ASC,
[JRNAL_LINE] ASC


BD1_A_SALFLEX:
PRIMARY KEY CLUSTERED
(
[ACCNT_CODE] ASC,
[PERIOD] ASC,
[TRANS_DATETIME] ASC,
[JRNAL_NO] ASC,
[JRNAL_LINE] ASC

BD1_A_SALFLDG_LAD
PRIMARY KEY CLUSTERED
(
[ACCNT_CODE] ASC,
[PERIOD] ASC,
[TRANS_DATETIME] ASC,
[JRNAL_NO] ASC,
[JRNAL_LINE] ASC

BD1_STD_TEXT_LDG:
PRIMARY KEY CLUSTERED
[ACCNT_CODE] ASC,
[PERIOD] ASC,
[TRANS_DATETIME] ASC,
[JRNAL_NO] ASC,
[JRNAL_LINE] ASC,
[STD_TEXT_CLASS_CODE] ASC,
[STD_TEXT_CODE] ASC

To test I just selected 2 columns in the select statment, using the same from and where portion, and the result is about 9 seconds.

Is there anything else I need to provide to help me in figuring out what new indexes I might need to create to get this query to perform better?

Any suggestions or hints would be greatly appreciated.

Thanks!

Post #830749
Posted Tuesday, December 8, 2009 9:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
can you post your actual execution plan?

also do you have any non-clustered indexes or only clustered?
Post #830779
Posted Tuesday, December 8, 2009 9:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:29 AM
Points: 11,141, Visits: 12,882
Please share the full table definitions (including any NC indexes) and the execution plans. It would also be helpful if you prefixed each column with the table alias as you may know what table each column belongs to, but we have no way of knowing.

Without the information requested we can't be much help. For example if either CONV_CODE or AUTHORISTN_IN_PROGESS is a column in any table other than BD1_A_SALFLDG then you are essentially creating an INNER JOIN and if either is in that table, they are not in the clustered index so it would have to do a SCAN since those columns are not indexed.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #830809
Posted Tuesday, December 8, 2009 11:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:17 PM
Points: 16, Visits: 276
FULL TABLE DEFINITIONS INCLUDE:

TABLE:
CREATE TABLE [dbo].[BD1_STD_TEXT_LDG](
[ACCNT_CODE] [dbo].[REF_MAINT_CODE] NOT NULL,
[PERIOD] [dbo].[FIN_PERD_INT] NOT NULL,
[TRANS_DATETIME] [dbo].[FIN_DATETIME] NOT NULL,
[JRNAL_NO] [dbo].[FIN_JNL_NUM] NOT NULL,
[JRNAL_LINE] [dbo].[FIN_JNL_LINE] NOT NULL,
[STD_TEXT_CLASS_CODE] [dbo].[REF_MAINT_CODE] NOT NULL,
[STD_TEXT_CODE] [dbo].[REF_MAINT_CODE] NOT NULL,
[UPDATE_COUNT] [dbo].[UPDATE_CNT] NOT NULL,
[LAST_CHANGE_USER_ID] [dbo].[LAST_CHANGE_USER_ID] NOT NULL,
[LAST_CHANGE_DATETIME] [dbo].[LAST_CHANGE_DATETIME] NOT NULL,
PRIMARY KEY NONCLUSTERED
(
[ACCNT_CODE] ASC,
[PERIOD] ASC,
[TRANS_DATETIME] ASC,
[JRNAL_NO] ASC,
[JRNAL_LINE] ASC,
[STD_TEXT_CLASS_CODE] ASC,
[STD_TEXT_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[BD1_STD_TEXT_LDG] WITH NOCHECK ADD CONSTRAINT [FK_BD1_0414_01_0901] FOREIGN KEY([ACCNT_CODE], [PERIOD], [TRANS_DATETIME], [JRNAL_NO], [JRNAL_LINE])
REFERENCES [dbo].[BD1_A_SALFLDG] ([ACCNT_CODE], [PERIOD], [TRANS_DATETIME], [JRNAL_NO], [JRNAL_LINE])
GO
ALTER TABLE [dbo].[BD1_STD_TEXT_LDG] CHECK CONSTRAINT [FK_BD1_0414_01_0901]
GO
ALTER TABLE [dbo].[BD1_STD_TEXT_LDG] WITH NOCHECK ADD CONSTRAINT [FK_BD1_0414_02_0136] FOREIGN KEY([STD_TEXT_CLASS_CODE], [STD_TEXT_CODE])
REFERENCES [dbo].[STD_TEXT] ([STD_TEXT_CLASS_CODE], [STD_TEXT_CODE])
GO
ALTER TABLE [dbo].[BD1_STD_TEXT_LDG] CHECK CONSTRAINT [FK_BD1_0414_02_0136]

TABLE
CREATE TABLE [dbo].[BD1_A_SALFLEX](
[ACCNT_CODE] [dbo].[REF_MAINT_CODE] NOT NULL,
[PERIOD] [dbo].[FIN_PERD_INT] NOT NULL,
[TRANS_DATETIME] [dbo].[FIN_DATETIME] NOT NULL,
[JRNAL_NO] [dbo].[FIN_JNL_NUM] NOT NULL,
[JRNAL_LINE] [dbo].[FIN_JNL_LINE] NOT NULL,
[DOC_1_DATETIME] [dbo].[FIN_DATETIME] NULL,
[DOC_2_DATETIME] [dbo].[FIN_DATETIME] NULL,
[DOC_3_DATETIME] [dbo].[FIN_DATETIME] NULL,
[DOC_4_DATETIME] [dbo].[FIN_DATETIME] NULL,
[DOC_NUM_PREF_1] [dbo].[FIN_DOC_NUM_PREF_1] NULL,
[DOC_NUMBER_1] [dbo].[FIN_DOC_NUM] NOT NULL,
[DOC_NUM_PREF_2] [dbo].[FIN_DOC_NUM_PREF_1] NULL,
[DOC_NUMBER_2] [dbo].[FIN_DOC_NUM] NOT NULL,
[DOC_NUM_PREF_3] [dbo].[FIN_DOC_NUM_PREF_1] NULL,
[DOC_NUMBER_3] [dbo].[FIN_DOC_NUM] NOT NULL,
[DOC_NUM_PREF_4] [dbo].[FIN_DOC_NUM_PREF_1] NULL,
[DOC_NUMBER_4] [dbo].[FIN_DOC_NUM] NOT NULL,
[DISC_1_DATETIME] [dbo].[FIN_DATETIME] NULL,
[DISC_PERCENT_1] [dbo].[FIN_PERCENTAGE] NOT NULL,
[DISC_2_DATETIME] [dbo].[FIN_DATETIME] NULL,
[DISC_PERCENT_2] [dbo].[FIN_PERCENTAGE] NOT NULL,
[INTEREST_DATETIME] [dbo].[FIN_DATETIME] NULL,
[INTEREST_PERCENT] [dbo].[FIN_PERCENTAGE] NOT NULL,
[LATE_PAY_DATETIME] [dbo].[FIN_DATETIME] NULL,
[LATE_PAY_PERCENT] [dbo].[FIN_PERCENTAGE] NOT NULL,
[PAYMENT_REF] [dbo].[FIN_PYMT_REF] NOT NULL,
[BANK_CODE] [dbo].[FIN_BANK_CODE] NOT NULL,
[SOURCE_REF] [dbo].[FIN_SRCE_REF] NOT NULL,
[MODULE_CODE] [dbo].[FIN_MODULE_CODE] NOT NULL,
[PAYMENT_TERMS_CODE] [dbo].[FIN_PYMT_TERMS_CODE] NOT NULL,
[PYMT_SPLITS_TERMS_CODE] [dbo].[REF_MAINT_CODE] NULL,
PRIMARY KEY CLUSTERED
(
[ACCNT_CODE] ASC,
[PERIOD] ASC,
[TRANS_DATETIME] ASC,
[JRNAL_NO] ASC,
[JRNAL_LINE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[BD1_A_SALFLEX] WITH NOCHECK ADD CONSTRAINT [FK_BD1_0900_01_0901] FOREIGN KEY([ACCNT_CODE], [PERIOD], [TRANS_DATETIME], [JRNAL_NO], [JRNAL_LINE])
REFERENCES [dbo].[BD1_A_SALFLDG] ([ACCNT_CODE], [PERIOD], [TRANS_DATETIME], [JRNAL_NO], [JRNAL_LINE])
GO
ALTER TABLE [dbo].[BD1_A_SALFLEX] CHECK CONSTRAINT [FK_BD1_0900_01_0901]



TABLE
CREATE TABLE [dbo].[BD1_A_SALFLDG](
[ACCNT_CODE] [dbo].[REF_MAINT_CODE] NOT NULL,
[PERIOD] [dbo].[FIN_PERD_INT] NOT NULL,
[TRANS_DATETIME] [dbo].[FIN_DATETIME] NOT NULL,
[JRNAL_NO] [dbo].[FIN_JNL_NUM] NOT NULL,
[JRNAL_LINE] [dbo].[FIN_JNL_LINE] NOT NULL,
[AMOUNT] [dbo].[FIN_AMOUNT_BASE] NOT NULL,
[D_C] [dbo].[FIN_FLAG_D_C] NOT NULL,
[ALLOCATION] [dbo].[FIN_FLAG_ALLOCN_BR] NOT NULL,
[JRNAL_TYPE] [dbo].[FIN_JNL_TYPE] NULL,
[JRNAL_SRCE] [dbo].[FIN_JNL_SRCE] NOT NULL,
[TREFERENCE] [dbo].[TRANSACTION_REF_MIXED] NULL,
[DESCRIPTN] [dbo].[FIN_DESCR_50] NULL,
[ENTRY_DATETIME] [dbo].[FIN_DATETIME] NULL,
[ENTRY_PRD] [dbo].[FIN_PERD_INT] NOT NULL,
[DUE_DATETIME] [dbo].[FIN_DATETIME] NULL,
[ALLOC_REF] [dbo].[FIN_ALLOCN_REF] NOT NULL,
[ALLOC_DATETIME] [dbo].[FIN_DATETIME] NULL,
[ALLOC_PERIOD] [dbo].[FIN_PERD_INT] NOT NULL,
[ASSET_IND] [dbo].[FIN_FLAG_ASSET] NOT NULL,
[ASSET_CODE] [dbo].[FIN_ASSET_CODE] NOT NULL,
[ASSET_SUB] [dbo].[FIN_ASSET_SUB_CODE] NOT NULL,
[CONV_CODE] [dbo].[FIN_CONV_CODE] NOT NULL,
[CONV_RATE] [dbo].[FIN_CONV_RATE] NOT NULL,
[OTHER_AMT] [dbo].[FIN_OTHER_AMT_ACC] NOT NULL,
[OTHER_DP] [dbo].[FIN_FLAG_OTHER_DP] NOT NULL,
[CLEARDOWN] [dbo].[FIN_SEQ] NOT NULL,
[REVERSAL] [dbo].[FIN_FLAG_Y_N_NA] NOT NULL,
[LOSS_GAIN] [dbo].[FIN_LOSS_GAIN] NOT NULL,
[ROUGH_FLAG] [dbo].[FIN_FLAG_ROUGH] NOT NULL,
[IN_USE_FLAG] [dbo].[FIN_FLAG_Y_N_NA] NOT NULL,
[ANAL_T0] [dbo].[FIN_ANL_T0] NOT NULL,
[ANAL_T1] [dbo].[FIN_ANL_T1] NOT NULL,
[ANAL_T2] [dbo].[FIN_ANL_T2] NOT NULL,
[ANAL_T3] [dbo].[FIN_ANL_T3] NOT NULL,
[ANAL_T4] [dbo].[FIN_ANL_T4] NOT NULL,
[ANAL_T5] [dbo].[FIN_ANL_T5] NOT NULL,
[ANAL_T6] [dbo].[FIN_ANL_T6] NOT NULL,
[ANAL_T7] [dbo].[FIN_ANL_T7] NOT NULL,
[ANAL_T8] [dbo].[FIN_ANL_T8] NOT NULL,
[ANAL_T9] [dbo].[FIN_ANL_T9] NOT NULL,
[POSTING_DATETIME] [dbo].[FIN_DATETIME] NULL,
[ALLOC_IN_PROGRESS] [dbo].[FIN_ALLOCN_IN_PROG] NOT NULL,
[HOLD_REF] [dbo].[FIN_JNL_REF] NOT NULL,
[HOLD_OP_ID] [dbo].[FIN_OPR_ID] NOT NULL,
[BASE_RATE] [dbo].[FIN_CONV_RATE] NOT NULL,
[BASE_OPERATOR] [dbo].[FIN_FLAG_OPN] NOT NULL,
[CONV_OPERATOR] [dbo].[FIN_FLAG_OPN] NOT NULL,
[REPORT_RATE] [dbo].[FIN_CONV_RATE] NOT NULL,
[REPORT_OPERATOR] [dbo].[FIN_FLAG_OPN] NOT NULL,
[REPORT_AMT] [dbo].[FIN_AMT_RPTG_ACC] NOT NULL,
[MEMO_AMT] [dbo].[FIN_AMT_MEMO_ACC] NOT NULL,
[EXCLUDE_BAL] [dbo].[FIN_FLAG_Y_N_NA] NOT NULL,
[LE_DETAILS_IND] [dbo].[FIN_FLAG_LE_DETAILS] NOT NULL,
[CONSUMED_BDGT_ID] [dbo].[NUM_INT] NOT NULL,
[CV4_CONV_CODE] [dbo].[FIN_CONV_CODE] NOT NULL DEFAULT (' '),
[CV4_AMT] [dbo].[FIN_AMOUNT_BASE] NOT NULL DEFAULT ((0)),
[CV4_CONV_RATE] [dbo].[FIN_CONV_RATE] NOT NULL DEFAULT ((0)),
[CV4_OPERATOR] [dbo].[FIN_FLAG_OPN] NOT NULL DEFAULT ('*'),
[CV4_DP] [dbo].[FIN_FLAG_OTHER_DP] NOT NULL DEFAULT (' '),
[CV5_CONV_CODE] [dbo].[FIN_CONV_CODE] NOT NULL DEFAULT (' '),
[CV5_AMT] [dbo].[FIN_AMOUNT_BASE] NOT NULL DEFAULT ((0)),
[CV5_CONV_RATE] [dbo].[FIN_CONV_RATE] NOT NULL DEFAULT ((0)),
[CV5_OPERATOR] [dbo].[FIN_FLAG_OPN] NOT NULL DEFAULT ('*'),
[CV5_DP] [dbo].[FIN_FLAG_OTHER_DP] NOT NULL DEFAULT (' '),
[LINK_REF_1] [dbo].[CHAR_ALPHA_V15] NULL,
[LINK_REF_2] [dbo].[CHAR_ALPHA_V15] NULL,
[LINK_REF_3] [dbo].[CHAR_ALPHA_V15] NULL,
[ALLOCN_CODE] [dbo].[CHAR_CODE_5] NULL,
[ALLOCN_STMNTS] [dbo].[NUM_SMALLINT] NULL,
[OPR_CODE] [dbo].[OPERATOR_CODE] NULL,
[SPLIT_ORIG_LINE] [dbo].[NUM_INT] NOT NULL DEFAULT ((0)),
[VAL_DATETIME] [dbo].[FIN_DATETIME] NULL,
[SIGNING_DETAILS] [dbo].[STRING_V30] NULL,
[INSTLMT_DATETIME] [dbo].[FIN_DATETIME] NULL,
[PRINCIPAL_REQD] [dbo].[FLAG_Y_N] NOT NULL DEFAULT ((0)),
[BINDER_STATUS] [dbo].[FIN_FLAG_BINDER_STATUS] NOT NULL DEFAULT (' '),
[AGREED_STATUS] [dbo].[FLAG_Y_N] NOT NULL DEFAULT ((1)),
[SPLIT_LINK_REF] [dbo].[CHAR_ALPHA_V15] NULL,
[PSTG_REF] [dbo].[CHAR_ALPHA_V15] NULL,
[TRUE_RATED] [dbo].[FLAG_TRUE_RATED] NOT NULL DEFAULT ((0)),
[HOLD_DATETIME] [dbo].[FIN_DATETIME] NULL,
[HOLD_TEXT] [dbo].[STRING_V30] NULL,
[INSTLMT_NUM] [dbo].[NUM_SMALLINT] NULL,
[SUPPLMNTRY_EXTSN] [dbo].[FLAG_Y_N] NOT NULL DEFAULT ((0)),
[APRVLS_EXTSN] [dbo].[FLAG_Y_N] NOT NULL DEFAULT ((0)),
[REVAL_LINK_REF] [dbo].[NUM_INT] NULL,
[SAVED_SET_NUM] [dbo].[NUM_18_0] NULL,
[AUTHORISTN_SET_REF] [int] NULL,
[PYMT_AUTHORISTN_SET_REF] [int] NULL,
[MAN_PAY_OVER] [dbo].[FLAG_Y_N] NOT NULL DEFAULT ((0)),
[PYMT_STAMP] [dbo].[STRING_V10] NULL,
[AUTHORISTN_IN_PROGRESS] [dbo].[FLAG_Y_N] NOT NULL DEFAULT ((0)),
[SPLIT_IN_PROGRESS] [dbo].[FLAG_Y_N] NOT NULL DEFAULT ((0)),
[VCHR_NUM] [dbo].[CHAR_ALPHA_V30] NULL,
[JNL_CLASS_CODE] [dbo].[REF_MAINT_CODE] NULL,
[ORIGINATOR_ID] [dbo].[OPERATOR_CODE] NULL,
[ORIGINATED_DATETIME] [datetime] NULL,
[LAST_CHANGE_USER_ID] [dbo].[LAST_CHANGE_USER_ID] NULL,
[LAST_CHANGE_DATETIME] [datetime] NULL,
[AFTER_PSTG_ID] [dbo].[OPERATOR_CODE] NULL,
[AFTER_PSTG_DATETIME] [datetime] NULL,
[POSTER_ID] [dbo].[OPERATOR_CODE] NULL,
[ALLOC_ID] [dbo].[OPERATOR_CODE] NULL,
[JNL_REVERSAL_TYPE] [dbo].[FLAG_JNL_REVERSAL_TYPE] NOT NULL DEFAULT ((0)),
PRIMARY KEY CLUSTERED
(
[ACCNT_CODE] ASC,
[PERIOD] ASC,
[TRANS_DATETIME] ASC,
[JRNAL_NO] ASC,
[JRNAL_LINE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CONSTRAINT [FK_BD1_0901_01_0075] FOREIGN KEY([SAVED_SET_NUM])
REFERENCES [dbo].[BD1_SAVED_SET] ([SAVED_SET_NUM])
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] CHECK CONSTRAINT [FK_BD1_0901_01_0075]
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CONSTRAINT [FK_BD1_0901_01_0152] FOREIGN KEY([ACCNT_CODE])
REFERENCES [dbo].[BD1_ACNT] ([ACNT_CODE])
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] CHECK CONSTRAINT [FK_BD1_0901_01_0152]
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CONSTRAINT [FK_BD1_0901_01_0354] FOREIGN KEY([JRNAL_TYPE])
REFERENCES [dbo].[BD1_JNL_DEFN] ([JOURNAL_TYPE])
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] CHECK CONSTRAINT [FK_BD1_0901_01_0354]
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CONSTRAINT [FK_BD1_0901_01_0541] FOREIGN KEY([JNL_CLASS_CODE])
REFERENCES [dbo].[BD1_JNL_CLASS] ([JNL_CLASS_CODE])
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] CHECK CONSTRAINT [FK_BD1_0901_01_0541]
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CONSTRAINT [FK_BD1_0901_01_0923] FOREIGN KEY([AUTHORISTN_SET_REF])
REFERENCES [dbo].[BD1_AUTHORISTN_SET_HDR] ([SET_REF_NUM])
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] CHECK CONSTRAINT [FK_BD1_0901_01_0923]
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CONSTRAINT [FK_BD1_0901_02_0923] FOREIGN KEY([PYMT_AUTHORISTN_SET_REF])
REFERENCES [dbo].[BD1_AUTHORISTN_SET_HDR] ([SET_REF_NUM])
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] CHECK CONSTRAINT [FK_BD1_0901_02_0923]
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([D_C]='T' OR [D_C]='P' OR [D_C]='J' OR [D_C]='D' OR [D_C]='C'))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([AGREED_STATUS]=(1) OR [AGREED_STATUS]=(0)))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([ALLOCATION]='9' OR [ALLOCATION]='8' OR [ALLOCATION]='7' OR [ALLOCATION]='6' OR [ALLOCATION]='5' OR [ALLOCATION]='4' OR [ALLOCATION]='3' OR [ALLOCATION]='2' OR [ALLOCATION]='1' OR [ALLOCATION]='0' OR [ALLOCATION]='T' OR [ALLOCATION]='F' OR [ALLOCATION]='W' OR [ALLOCATION]='B' OR [ALLOCATION]='C' OR [ALLOCATION]='R' OR [ALLOCATION]='P' OR [ALLOCATION]='A' OR [ALLOCATION]=' '))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([APRVLS_EXTSN]=(1) OR [APRVLS_EXTSN]=(0)))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([ASSET_IND]='V' OR [ASSET_IND]='I' OR [ASSET_IND]='D' OR [ASSET_IND]=' '))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([AUTHORISTN_SET_REF]<=(999999999)))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([AUTHORISTN_IN_PROGRESS]=(1) OR [AUTHORISTN_IN_PROGRESS]=(0)))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([BASE_OPERATOR]='T' OR [BASE_OPERATOR]='/' OR [BASE_OPERATOR]='-' OR [BASE_OPERATOR]='+' OR [BASE_OPERATOR]='*' OR [BASE_OPERATOR]='%'))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([BINDER_STATUS]='B' OR [BINDER_STATUS]='A' OR [BINDER_STATUS]=' '))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([CONV_OPERATOR]='T' OR [CONV_OPERATOR]='/' OR [CONV_OPERATOR]='-' OR [CONV_OPERATOR]='+' OR [CONV_OPERATOR]='*' OR [CONV_OPERATOR]='%'))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([CV4_DP]='5' OR [CV4_DP]='4' OR [CV4_DP]='3' OR [CV4_DP]='2' OR [CV4_DP]='1' OR [CV4_DP]='0' OR [CV4_DP]=' '))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([CV4_OPERATOR]='T' OR [CV4_OPERATOR]='/' OR [CV4_OPERATOR]='-' OR [CV4_OPERATOR]='+' OR [CV4_OPERATOR]='*' OR [CV4_OPERATOR]='%'))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([CV5_DP]='5' OR [CV5_DP]='4' OR [CV5_DP]='3' OR [CV5_DP]='2' OR [CV5_DP]='1' OR [CV5_DP]='0' OR [CV5_DP]=' '))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([CV5_OPERATOR]='T' OR [CV5_OPERATOR]='/' OR [CV5_OPERATOR]='-' OR [CV5_OPERATOR]='+' OR [CV5_OPERATOR]='*' OR [CV5_OPERATOR]='%'))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([EXCLUDE_BAL]=' ' OR [EXCLUDE_BAL]='N' OR [EXCLUDE_BAL]='Y'))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([IN_USE_FLAG]=' ' OR [IN_USE_FLAG]='N' OR [IN_USE_FLAG]='Y'))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([JNL_REVERSAL_TYPE]=(3) OR [JNL_REVERSAL_TYPE]=(2) OR [JNL_REVERSAL_TYPE]=(1) OR [JNL_REVERSAL_TYPE]=(0)))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([LE_DETAILS_IND]=' ' OR [LE_DETAILS_IND]='Y'))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([MAN_PAY_OVER]=(1) OR [MAN_PAY_OVER]=(0)))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([OTHER_DP]='5' OR [OTHER_DP]='4' OR [OTHER_DP]='3' OR [OTHER_DP]='2' OR [OTHER_DP]='1' OR [OTHER_DP]='0' OR [OTHER_DP]=' '))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([PRINCIPAL_REQD]=(1) OR [PRINCIPAL_REQD]=(0)))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([PYMT_AUTHORISTN_SET_REF]<=(999999999)))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([REPORT_OPERATOR]='T' OR [REPORT_OPERATOR]='/' OR [REPORT_OPERATOR]='-' OR [REPORT_OPERATOR]='+' OR [REPORT_OPERATOR]='*' OR [REPORT_OPERATOR]='%'))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([REVERSAL]=' ' OR [REVERSAL]='N' OR [REVERSAL]='Y'))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([ROUGH_FLAG]=' ' OR [ROUGH_FLAG]='Y'))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([SAVED_SET_NUM]>=(1) AND [SAVED_SET_NUM]<=(9999999)))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([SPLIT_IN_PROGRESS]=(1) OR [SPLIT_IN_PROGRESS]=(0)))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([SUPPLMNTRY_EXTSN]=(1) OR [SUPPLMNTRY_EXTSN]=(0)))
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG] WITH NOCHECK ADD CHECK (([TRUE_RATED]=(11) OR [TRUE_RATED]=(10) OR [TRUE_RATED]=(2) OR [TRUE_RATED]=(1) OR [TRUE_RATED]=(0)))


TABLE
CREATE TABLE [dbo].[BD1_A_SALFLDG_LAD](
[ACCNT_CODE] [dbo].[REF_MAINT_CODE] NOT NULL,
[PERIOD] [dbo].[FIN_PERD_INT] NOT NULL,
[TRANS_DATETIME] [dbo].[FIN_DATETIME] NOT NULL,
[JRNAL_NO] [dbo].[FIN_JNL_NUM] NOT NULL,
[JRNAL_LINE] [dbo].[FIN_JNL_LINE] NOT NULL,
[UPDATE_COUNT] [dbo].[UPDATE_CNT] NOT NULL,
[LAST_CHANGE_USER_ID] [dbo].[LAST_CHANGE_USER_ID] NOT NULL,
[LAST_CHANGE_DATETIME] [dbo].[LAST_CHANGE_DATETIME] NOT NULL,
[GNRL_DESCR_01] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_02] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_03] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_04] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_05] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_06] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_07] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_08] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_09] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_10] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_11] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_12] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_13] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_14] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_15] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_16] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_17] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_18] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_19] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_20] [dbo].[STRING_V30] NULL,
[GNRL_1_DATETIME] [datetime] NULL,
[GNRL_2_DATETIME] [datetime] NULL,
[GNRL_3_DATETIME] [datetime] NULL,
[GNRL_4_DATETIME] [datetime] NULL,
[GNRL_5_DATETIME] [datetime] NULL,
[GNRL_DESCR_21] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_22] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_23] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_24] [dbo].[STRING_V30] NULL,
[GNRL_DESCR_25] [dbo].[STRING_V30] NULL,
[USER_NAME] [dbo].[STRING_V30] NULL,
PRIMARY KEY CLUSTERED
(
[ACCNT_CODE] ASC,
[PERIOD] ASC,
[TRANS_DATETIME] ASC,
[JRNAL_NO] ASC,
[JRNAL_LINE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG_LAD] WITH NOCHECK ADD CONSTRAINT [FK_BD1_0887_01_0901] FOREIGN KEY([ACCNT_CODE], [PERIOD], [TRANS_DATETIME], [JRNAL_NO], [JRNAL_LINE])
REFERENCES [dbo].[BD1_A_SALFLDG] ([ACCNT_CODE], [PERIOD], [TRANS_DATETIME], [JRNAL_NO], [JRNAL_LINE])
GO
ALTER TABLE [dbo].[BD1_A_SALFLDG_LAD] CHECK CONSTRAINT [FK_BD1_0887_01_0901]


ACTUAL EXECUTION PLAN:
see attachment


  Post Attachments 
actual_execution_plan.JPG (43 views, 93.85 KB)
Post #830857
Posted Tuesday, December 8, 2009 12:36 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 42,422, Visits: 35,483
See this article for how top save and post an execution plan. A picture of it is not that useful.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #830919
Posted Tuesday, December 8, 2009 1:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:17 PM
Points: 16, Visits: 276
Thanks!
Here it is.


  Post Attachments 
sqlplan.sqlplan (52 views, 343.98 KB)
Post #830989
Posted Tuesday, December 8, 2009 9:21 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 21,187, Visits: 14,879
What is the fragmentation level of the indexes involved in this query (for the tables listed)?



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #831149
Posted Wednesday, December 9, 2009 1:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 42,422, Visits: 35,483
Firstly, what's been done with those 2.1 million rows?

Try creating this index
BD1_A_SALFLDG ([ACCNT_CODE], [AUTHORISTN_IN_PROGRESS], [PERIOD],[CONV_CODE])

Post new exec plan if it makes any difference.

The reason those clustered index seeks are taking a long time is the shear amount of data being returned. 1.4 GB of data from one, 0.9 GB of data from the other. Regardless of what type of index, that's not going to be quick



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #831213
Posted Wednesday, December 9, 2009 11:23 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:31 AM
Points: 15,496, Visits: 27,879
I'd say that moving a gb of data in a single query in 3 minutes is decent, if not great, performance. What is the user doing with 2 million rows of data?

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #831667
Posted Wednesday, December 9, 2009 11:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:17 PM
Points: 16, Visits: 276
doesnt appear to really have helped....attached is the new sqlplan.

also my indexes do NOT have much fragmentation..I do a reorg every other day and rebuild once a week.


  Post Attachments 
sqlplan2.sqlplan (19 views, 239.56 KB)
Post #831675
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse