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

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...... Expand / Collapse
Author
Message
Posted Sunday, February 3, 2013 3:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 2, 2014 2:11 PM
Points: 34, Visits: 100
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
Posted Sunday, February 3, 2013 4:21 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 20,801, Visits: 32,730
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.



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
Posted Sunday, February 3, 2013 4:24 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 20,801, Visits: 32,730
Also, have you tried using the NOEXPAND query hint when querying your indexed view?



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 #1415069
Posted Sunday, February 3, 2013 4:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 2, 2014 2:11 PM
Points: 34, Visits: 100
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
Posted Sunday, February 3, 2013 4:54 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 20,801, Visits: 32,730
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?????



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 #1415072
Posted Sunday, February 3, 2013 4:55 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 20,801, Visits: 32,730
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.



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 #1415073
Posted Sunday, February 3, 2013 4:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 2, 2014 2:11 PM
Points: 34, Visits: 100
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 »

Add to briefcase

Permissions Expand / Collapse