January 31, 2013 at 11:23 am
Select 'Customer' AS 'USERTYPE', DT1.DT, DT1.DT_KEY,
SUM(A1.BLNC_AMNT) ,G1.GOGRPHY_KEY , ''
FROM dbo.AR_BLNC_TRCKNG_FCT AS A1 WITH (NOLOCK) INNER JOIN
dbo.AR_MFS_CL_FCT AS AMC1 WITH (NOLOCK) ON AMC1.AR_BHVR_KEY = A1.AR_BHVR_KEY AND AMC1.FCT_DT_KEY = A1.FCT_DT_KEY INNER JOIN
dbo.DT_DIM AS DT1 WITH (NOLOCK)ON DT1.DT_KEY = A1.FCT_DT_KEY INNER JOIN
dbo.BTS_DIM AS BT1 WITH (NOLOCK) ON AMC1.MFS_MOST_USD_BTS_KEY = BT1.BTS_KEY INNER JOIN
dbo.GOGRPHY_DIM AS G1 WITH (NOLOCK) ON G1.GOGRPHY_KEY = BT1.GOGRPHY_KEY INNER JOIN
dbo.WLLT_DIM AS W WITH (NOLOCK) ON W.WLLT_KEY = A1.WLLT_KEY
WHERE(DT1.DT >= @MLLCMBI_GH_FRM_DT) AND
(DT1.DT<= @MLLCMBI_GH_TO_DT) AND
(W.WLLT_NM = 'e-money Wallet for TigoCash')
GROUP BY DT1.DT, DT1.DT_KEY,G1.GOGRPHY_KEY
January 31, 2013 at 12:03 pm
Your query has parameters. You can't pass parameters to a view. You could build your query without the parameters and then use them in a where clause when you query the view.
And you need to do some reading on NOLOCK. It is very dangerous, especially for what appears to be a financial application. That hint can return rows more than once, or miss rows. It produces incredibly difficult, if not impossible to recreate bugs.
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2013 at 12:17 pm
while creating IV i have allready removed params and NOLOCK, but still my base query is not reading from IV
January 31, 2013 at 12:28 pm
saxena200 (1/31/2013)
while creating IV i have allready removed params and NOLOCK, but still my base query is not reading from IV
What do you mean your base query? I thought what you posted was the query you wanted to change into a view? If your new view is called MyView change your original to "select * from MyView".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2013 at 2:53 pm
---------------------------------------------------
--The underlying IV Not working, kindly help
---------------------------------------------------
CREATE VIEW IV_MMB_REPORT_2 WITH SCHEMABINDING AS
SELECT DT1.DT, DT1.DT_KEY,
SUM(isnull(Ag.BLNC_AMNT,0))[BLNC_AMNT] , G2.GOGRPHY_KEY ,COUNT_BIG(*)[COUNT]
FROMdbo.AGNT_BLNC_TRCKNG_FCT AS Ag INNER JOIN
dbo.EMPE_DIM AS E1 ON E1.EMPE_KEY = Ag.EMPE_KEY INNER JOIN
dbo.BOC_SLLR_DIM AS BO1 ON BO1.BOC_SLLR_KEY = E1.BOC_SLLR_KEY INNER JOIN
dbo.ROLE_DIM AS RD ON BO1.ROLE_KEY = RD.ROLE_KEY INNER JOIN
dbo.GOGRPHY_DIM AS G2 ON G2.GOGRPHY_KEY = BO1.GOGRPHY_KEY INNER JOIN
dbo.DT_DIM AS DT1 ON DT1.DT_KEY = Ag.FCT_DT_KEY INNER JOIN
dbo.WLLT_DIM AS W ON W.WLLT_KEY = Ag.WLLT_KEY AND Ag.WLLT_KEY = W.WLLT_KEY
WHERE --(DT1.DT >= @MLLCMBI_GH_FRM_DT) AND
--(DT1.DT<= @MLLCMBI_GH_TO_DT) AND
(RD.ROLE_NM = 'FREELANCER_GROUP') AND
(ROLE_GRP_CD='MFS')AND
(W.WLLT_NM = 'e-money Wallet for TigoCash') AND
(E1.CHNL_NM = 'direct')
GROUP BY DT1.DT,DT1.DT_KEY,G2.GOGRPHY_KEY
GO
CREATE UNIQUE CLUSTERED INDEX INDEX_MMB2 on IV_MMB_REPORT_2(DT,DT_KEY,GOGRPHY_KEY)
January 31, 2013 at 3:13 pm
saxena200 (1/31/2013)
--The underlying IV Not working, kindly help
---------------------------------------------------
[/quote
"Not working" is not very descriptive. I am guessing you are getting "Must declare the scalar variable.." message???
I reformatted your code so it is more legible.
CREATE VIEW IV_MMB_REPORT_2
WITH SCHEMABINDING
AS
SELECT DT1.DT, DT1.DT_KEY, SUM(isnull(Ag.BLNC_AMNT, 0)) [BLNC_AMNT], G2.GOGRPHY_KEY, COUNT_BIG(*) [COUNT]
FROM dbo.AGNT_BLNC_TRCKNG_FCT AS Ag
INNER JOIN dbo.EMPE_DIM AS E1 ON E1.EMPE_KEY = Ag.EMPE_KEY
INNER JOIN dbo.BOC_SLLR_DIM AS BO1 ON BO1.BOC_SLLR_KEY = E1.BOC_SLLR_KEY
INNER JOIN dbo.ROLE_DIM AS RD ON BO1.ROLE_KEY = RD.ROLE_KEY
INNER JOIN dbo.GOGRPHY_DIM AS G2 ON G2.GOGRPHY_KEY = BO1.GOGRPHY_KEY
INNER JOIN dbo.DT_DIM AS DT1 ON DT1.DT_KEY = Ag.FCT_DT_KEY
INNER JOIN dbo.WLLT_DIM AS W ON W.WLLT_KEY = Ag.WLLT_KEY
AND Ag.WLLT_KEY = W.WLLT_KEY
WHERE RD.ROLE_NM = 'FREELANCER_GROUP'
AND ROLE_GRP_CD = 'MFS'
AND W.WLLT_NM = 'e-money Wallet for TigoCash'
AND E1.CHNL_NM = 'direct'
GROUP BY DT1.DT, DT1.DT_KEY, G2.GOGRPHY_KEY
GO
CREATE UNIQUE CLUSTERED INDEX INDEX_MMB2 on IV_MMB_REPORT_2(DT,DT_KEY,GOGRPHY_KEY)
I don't see anything wrong in here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2013 at 6:08 pm
Thanks a lot for your reply
View is executing fine, but when i see the execution plan i dont see my select query reffring to this Indexed view, it is always hitting the base tables, which is costly
January 31, 2013 at 7:02 pm
What edition of SQL Server are you using.
SQL Server 2008 standard edition does not automatically use indexed views. You need to use the hint "NOEXPAND" if you are using standard edition
January 31, 2013 at 10:33 pm
Use the NOEXPAND hint when you query the indexed view, see if that helps.
February 1, 2013 at 5:40 am
Currently i am using SQL SERVER EE R2 2008
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply