December 8, 2009 at 8:51 am
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!
December 8, 2009 at 9:13 am
can you post your actual execution plan?
also do you have any non-clustered indexes or only clustered?
December 8, 2009 at 9:59 am
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
Consultant - Straight Path Solutions
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
December 8, 2009 at 11:22 am
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
December 8, 2009 at 12:36 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2009 at 1:29 pm
Thanks!
Here it is.
December 8, 2009 at 9:21 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 9, 2009 at 1:50 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2009 at 11:23 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 9, 2009 at 11:34 am
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.
December 9, 2009 at 11:49 am
Grant Fritchey (12/9/2009)
I'd say that moving a gb of data in a single query in 3 minutes is decent, if not great, performance.
I'm in agreement here. That's a lot of data that you're pulling out. I don't really think that we're going to get much better. I know it takes more than 3 minutes to copy 2 GB files around.
Where are those 2.1 million rows going to?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2009 at 11:51 am
This particular select statement is being generated by the SUN app and its done when they run a ledger update.
December 9, 2009 at 12:21 pm
Custom-built app or something that has been bought from elsewhere?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2009 at 12:30 pm
ITs an off the shelf product.
December 10, 2009 at 8:11 am
I bet the CPU usage could be significant here too with all the manipulations done in the select. I would do a waitstats and IO stall analysis to see if you might not simply need more hardware. Also, if these kajillion rows are being sent to a remote client check network utilization/delay as well as the situation on the calling client machine.
Like someone else mentioned though, sending that much data out in 3mins seems pretty decent to me...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply