InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 14:43:54 GMT20http://www.sqlservercentral.com/Forums/Topic1415065-391-1.aspxAllright thanks for ur concerns!!got it resolved now, used statistics PFB, the query------------------------------------------------------CREATE STATISTICS [_dta_stat_1233491523_19_15] ON [dbo].[AR_BHVR_DIM]([PD_KEY], [EMPE_KEY])---------------CREATE STATISTICS [_dta_stat_1233491523_1_19_15] ON [dbo].[AR_BHVR_DIM]([AR_BHVR_KEY], [PD_KEY], [EMPE_KEY])------------CREATE STATISTICS [_dta_stat_1998630163_1_3] ON [dbo].[CSTMR_DIM]([CSTMR_KEY], [CSTMR_NM])------------CREATE STATISTICS [_dta_stat_838294046_23_8_6] ON [dbo].[CSTMR_DUE_BLNC_FCT]([AR_BHVR_KEY], [CNTRCT_DD], [CSTMR_KEY])--------------CREATE STATISTICS [_dta_stat_838294046_23_14] ON [dbo].[CSTMR_DUE_BLNC_FCT]([AR_BHVR_KEY], [FCT_DT_KEY])---------------CREATE STATISTICS [_dta_stat_838294046_14_6_23_8] ON [dbo].[CSTMR_DUE_BLNC_FCT]([FCT_DT_KEY], [CSTMR_KEY], [AR_BHVR_KEY], [CNTRCT_DD])-------------CREATE STATISTICS [_dta_stat_578101100_20_21_14_3_31] ON [dbo].[DT_DIM]([MO_OF_YR], [MO_OF_YR_NM], [MIC_WK_OF_MO_NM], [DT], [YR])-------------CREATE STATISTICS [_dta_stat_578101100_1_21_20_14_3_31] ON [dbo].[DT_DIM]([DT_KEY], [MO_OF_YR_NM], [MO_OF_YR], [MIC_WK_OF_MO_NM], [DT], [YR])-------------CREATE STATISTICS [_dta_stat_1582680736_1_5] ON [dbo].[EMPE_DIM]([EMPE_KEY], [EMPE_NM])-------------CREATE VIEW IV_TEST_DFRO WITH SCHEMABINDING AS SELECT [dbo].[PD_DIM].[BS_LN_NM] as _col_1, [dbo].[EMPE_DIM].[EMPE_NM] as _col_2, [dbo].[AR_BHVR_DIM].[AR_BHVR_KEY] as _col_3 FROM [dbo].[AR_BHVR_DIM], [dbo].[EMPE_DIM], [dbo].[PD_DIM] WHERE [dbo].[AR_BHVR_DIM].[PD_KEY] = [dbo].[PD_DIM].[PD_KEY] AND [dbo].[AR_BHVR_DIM].[EMPE_KEY] = [dbo].[EMPE_DIM].[EMPE_KEY] --------------------------------SET ARITHABORT ONSET CONCAT_NULL_YIELDS_NULL ONSET QUOTED_IDENTIFIER ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONSET NUMERIC_ROUNDABORT OFFCREATE UNIQUE CLUSTERED INDEX INDEX_TEST_DFRO ON IV_TEST_DFRO ( [_col_3] ASC)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [DWEDWFGUAT]----------------------------------------------------------------------------------------Now its working fineThanks All....Cheers Sun, 03 Feb 2013 16:58:39 GMTsaxena200http://www.sqlservercentral.com/Forums/Topic1415065-391-1.aspxAlso, still don't see DDL for the tables, indexes, or views, nor have I seen an actual execution plan. How many shots in the dark do you want before posting the information we need to really help. We can't see what you see.Sun, 03 Feb 2013 16:55:59 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1415065-391-1.aspx[quote][b]saxena200 (2/3/2013)[/b][hr]i have allready removed all table hints like nolock and distinct count before creating the indexed view, tried noexpand also, still cant force optimizer to pick from IV[/quote]Once more with feeling: Have you tried using the NOEXPAND query hint when querying the indexed view?????Sun, 03 Feb 2013 16:54:13 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1415065-391-1.aspxi have allready removed all table hints like nolock and distinct count before creating the indexed view, tried noexpand also, still cant force optimizer to pick from IVSun, 03 Feb 2013 16:43:37 GMTsaxena200http://www.sqlservercentral.com/Forums/Topic1415065-391-1.aspxAlso, have you tried using the NOEXPAND query hint when querying your indexed view?Sun, 03 Feb 2013 16:24:58 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1415065-391-1.aspx[strike]First, the query posted isn't an indexed view (has parameters in the query).[/strike][b]Sorry, just caught that those were commented out. The rest of this still stands.[/b]Second, without table and index definitions, the actual execution plan of the query, all we can do is guess at what may be wrong.Please read the second article I link to below in my signature block regarding performance problems. Gail Shaw wrote a very informative article about what you need to post and how to post it to get the best answers possible to solve performance problems.Also, you really should look at dropping all those NOLOCK hints in your queries unless you like the possibility of getting erroneous answers.Sun, 03 Feb 2013 16:21:47 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1415065-391-1.aspxSELECT DT.MO_OF_YR_NM [Month], DT.MO_OF_YR [month_No], DT.YR [Year], DT.MIC_WK_OF_MO_NM [Week], DT.DT [Date], CD.CSTMR_NM [Organisation], PD.BS_LN_NM [Business Line], ED.EMPE_NM [Sales Executive], COUNT( CDBF.CSTMR_KEY) [Total no of Lines], (CASE WHEN SUM(isnull(CDBF.BLNC,0))>0 then COUNT(DISTINCT CDBF.CSTMR_KEY) else 0 END) [Total No Of Unpaid Lines], SUM(isnull(CDBF.BLNC,0)) [Total Amount Due] FROM DT_DIM DT with (NOLOCK)INNER JOIN CSTMR_DUE_BLNC_FCT CDBF with (NOLOCK) ON DT.DT_KEY=CDBF.FCT_DT_KEY Inner Join CSTMR_DIM CD with (NOLOCK) ON CD.CSTMR_KEY=CDBF.CSTMR_KEYInner join AR_BHVR_DIM ABD with (NOLOCK) on ABD.AR_BHVR_KEY = CDBF.AR_BHVR_KEYInner join PD_DIM PD with (NOLOCK) on PD.PD_KEY = ABD.PD_KEYInner Join EMPE_DIM ED with (NOLOCK) ON ED.EMPE_KEY=ABD.EMPE_KEY --WHERE DT.DT>=@FromDate AND DT.DT<=@ToDate--AND CD.CSTMR_NM IN (@Organisation)--AND PD.BS_LN_NM IN (@BusinessLine)--AND ED.EMPE_NM IN (@SalesExecutive)GROUP BY DT.MO_OF_YR_NM, DT.MO_OF_YR, DT.MIC_WK_OF_MO_NM, DT.DT, DT.YR, CD.CSTMR_NM, PD.BS_LN_NM, ED.EMPE_NM, --CDBF.FCT_DT_KEY, CDBF.AR_BHVR_KEY, CDBF.CNTRCT_DDORDER BY DT.MO_OF_YRSun, 03 Feb 2013 15:58:21 GMTsaxena200