facing a diffrent problem alltogether - working on indexed views and facing a tough time getting the query optimizer to pick the resultset from Indexed view... :)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » facing a diffrent problem alltogether -...

facing a diffrent problem alltogether - working on indexed views and facing a tough time getting the query optimizer to pick the resultset from Indexed view......

Rate Topic

Display Mode

Topic Options

Author

Message

saxena200

Posted Sunday, February 03, 2013 3:58 PM

Grasshopper

Group: General Forum Members
Last Login: Saturday, May 11, 2013 3:36 AM
Points: 24, Visits: 81

SELECT 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_KEY
Inner join AR_BHVR_DIM ABD with (NOLOCK) on ABD.AR_BHVR_KEY = CDBF.AR_BHVR_KEY
Inner join PD_DIM PD with (NOLOCK) on PD.PD_KEY = ABD.PD_KEY
Inner 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_DD
ORDER BY DT.MO_OF_YR

Post #1415065

Lynn Pettis

Posted Sunday, February 03, 2013 4:21 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588, Visits: 27,375

~~First, the query posted isn't an indexed view (has parameters in the query).~~
Sorry, just caught that those were commented out. The rest of this still stands.

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.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1415068

Lynn Pettis

Posted Sunday, February 03, 2013 4:24 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588, Visits: 27,375

Also, have you tried using the NOEXPAND query hint when querying your indexed view?

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1415069

saxena200

Posted Sunday, February 03, 2013 4:43 PM

Grasshopper

Group: General Forum Members
Last Login: Saturday, May 11, 2013 3:36 AM
Points: 24, Visits: 81

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

Post #1415070

Lynn Pettis

Posted Sunday, February 03, 2013 4:54 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588, Visits: 27,375

saxena200 (2/3/2013)

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

Once more with feeling: Have you tried using the NOEXPAND query hint when querying the indexed view?????

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1415072

Lynn Pettis

Posted Sunday, February 03, 2013 4:55 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588, Visits: 27,375

Also, 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.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1415073

saxena200

Posted Sunday, February 03, 2013 4:58 PM

Grasshopper

Group: General Forum Members
Last Login: Saturday, May 11, 2013 3:36 AM
Points: 24, Visits: 81

Allright 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 ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
CREATE 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 fine

Thanks All....
Cheers

Post #1415074

« Prev Topic | Next Topic »

Permissions