Is this Index supposed to make view faster?

Ok,
When I did this to add the index on the view, the view is no faster at
all...Did I do something wrong? :
USE tsNess
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENTIF
IER,ANSI_NULLS
ON
GO
CREATE VIEW V1
WITH SCHEMABINDING
AS
SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type,
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
FROM dbo.tblTravelDetail t1 INNER JOIN
dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
= t2.TravelDetailId INNER JOIN
dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
= t4.TravelDetailId INNER JOIN
dbo.tblTravelEvent t5 ON t1.TravelEventId =
t5.TravelEventId INNER JOIN
dbo.amount_type t6 ON t2.amountTypeId =
t6.amount_type_id INNER JOIN
dbo.period t8 ON t1.PeriodID = t8.period_id
WHERE (t1.MemberId = '222') AND (t2.amount <> 0)
GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type
Thanks,
TrintHi
select <column lists> from V1 with (noexpand)
See an execution plan for the query
"trint" <trinity.smith@.gmail.com> wrote in message
news:1123505778.551931.242730@.o13g2000cwo.googlegroups.com...
> Ok,
> When I did this to add the index on the view, the view is no faster at
> all...Did I do something wrong? :
> USE tsNess
> GO
> SET NUMERIC_ROUNDABORT OFF
> GO
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENT
IFIER,ANSI_NULLS
> ON
> GO
> CREATE VIEW V1
> WITH SCHEMABINDING
> AS
> SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type,
> SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
> WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
> SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
> WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
> SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
> WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
> SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
> WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
> SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
> WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
> SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
> WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
> SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
> WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
> SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
> WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
> FROM dbo.tblTravelDetail t1 INNER JOIN
> dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
> = t2.TravelDetailId INNER JOIN
> dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
> = t4.TravelDetailId INNER JOIN
> dbo.tblTravelEvent t5 ON t1.TravelEventId =
> t5.TravelEventId INNER JOIN
> dbo.amount_type t6 ON t2.amountTypeId =
> t6.amount_type_id INNER JOIN
> dbo.period t8 ON t1.PeriodID = t8.period_id
> WHERE (t1.MemberId = '222') AND (t2.amount <> 0)
> GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type
> Thanks,
> Trint
>|||Did you create indexes on the view?
Once you create an indexed view, make sure your view does not use the base
table indexes.
The way to do that is use the option (noexpand)
eg:-
Select PeriodID from V1 (noexpand)
where ...
To get the adv of indexed view, you should make sure its using the indexes
on the view rather than the old base table indexes.
The next thing is see the reads/Cpu and Dur with and without the option
noexpand. Moreover dont leave out exe plan.
Before you go for indexed view it wud be good to see how much time it would
take to create an index on a prod server.
The retireval performance should not be an overhead while saving
or in other words see whether the tables used in your indexed view are
updated/inserted frequently. If so I dont think its a good idea to go for
indexed view.
Thanks,
Prad
"trint" <trinity.smith@.gmail.com> wrote in message
news:1123505778.551931.242730@.o13g2000cwo.googlegroups.com...
> Ok,
> When I did this to add the index on the view, the view is no faster at
> all...Did I do something wrong? :
> USE tsNess
> GO
> SET NUMERIC_ROUNDABORT OFF
> GO
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENT
IFIER,ANSI_NULLS
> ON
> GO
> CREATE VIEW V1
> WITH SCHEMABINDING
> AS
> SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type,
> SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
> WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
> SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
> WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
> SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
> WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
> SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
> WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
> SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
> WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
> SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
> WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
> SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
> WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
> SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
> WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
> FROM dbo.tblTravelDetail t1 INNER JOIN
> dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
> = t2.TravelDetailId INNER JOIN
> dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
> = t4.TravelDetailId INNER JOIN
> dbo.tblTravelEvent t5 ON t1.TravelEventId =
> t5.TravelEventId INNER JOIN
> dbo.amount_type t6 ON t2.amountTypeId =
> t6.amount_type_id INNER JOIN
> dbo.period t8 ON t1.PeriodID = t8.period_id
> WHERE (t1.MemberId = '222') AND (t2.amount <> 0)
> GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type
> Thanks,
> Trint
>|||Ok,
Uri and Pradeep, I get this error when trying to create an index on
view one or V1:
An index cannot be created on the view 'V1' because the view definition
includes an unknown value (the sum of a nullable expression).
Thanks,
Trint|||Trint,
Another reason why we dont go for indexed view always...
See BOL you have a lot of requirements to be satisfied in creating one apart
from the time it takes and the ovehead while saving...
One more thing that I have obsereved is the fields on which u intend to
create index should not have duplicates...
You cannot have :
a.. A derived table.
a.. Rowset functions.
a.. UNION operator.
a.. Subqueries.
a.. Outer or self joins.
a.. TOP clause.
a.. ORDER BY clause.
a.. DISTINCT keyword.
a.. COUNT(*) but (COUNT_BIG(*) is allowed.)
a.. A SUM function that references a nullable expression.
a.. The full-text predicates CONTAINS or FREETEXT.
a.. COMPUTE or COMPUTE BY clause.
a.. If GROUP BY is not specified, the view select list cannot contain
aggregate expressions.
a.. If GROUP BY is specified, the view select list must contain a
COUNT_BIG(*) expression, and the view definition cannot specify HAVING,
CUBE, or ROLLUP.
a.. A column resulting from an expression that either evaluates to a float
value or uses float expressions for its evaluation cannot be a key of an
index in an indexed view or a table.
From BOL
Thanks,
Pradeep Kutty
"trint" <trinity.smith@.gmail.com> wrote in message
news:1123514588.376478.113330@.g43g2000cwa.googlegroups.com...
> Ok,
> Uri and Pradeep, I get this error when trying to create an index on
> view one or V1:
> An index cannot be created on the view 'V1' because the view definition
> includes an unknown value (the sum of a nullable expression).
> Thanks,
> Trint
>|||Trint,
To start off, I would like to ask if you have already indexed the base
tables? Indexing a view is not the place to start. Under normal
conditions, view performance is just fine if you properly index the base
tables.
For the rest of the reply, I will assume you have a properly normalized
data model that is properly indexed.
If you must index the view, then make sure sure:
- dbo.tblTravelDetailAmount.amount is defined as NOT NULL;
- the expressions never evaluate to NULL. Currently your CASE
expressions will evaluate to NULL for each amountTypeID that is not
explicitely mentioned in the expression. You could add "ELSE 0" to each
CASE expression to solve that;
- in the case of dbo.tblTravelDetail.MemberId that you match the
literal's data type to the column's. For example, if the MemberId is
defined as int, then change the predicate to WHERE t1.MemberId = 222;
- you add COUNT_BIG(*) to the selection list.
Maybe then you can index the view.
Note that if only few rows in table dbo.tblTravelDetailAmount have
amount=0, then the predicate WHERE t2.amount<>0 may not help performance
(it may even hurt performance) if you select from the base tables.
HTH,
Gert-Jan
trint wrote:
> Ok,
> When I did this to add the index on the view, the view is no faster at
> all...Did I do something wrong? :
> USE tsNess
> GO
> SET NUMERIC_ROUNDABORT OFF
> GO
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENT
IFIER,ANSI_NULLS
> ON
> GO
> CREATE VIEW V1
> WITH SCHEMABINDING
> AS
> SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type,
> SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
> WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
> SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
> WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
> SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
> WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
> SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
> WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
> SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
> WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
> SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
> WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
> SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
> WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
> SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
> WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
> FROM dbo.tblTravelDetail t1 INNER JOIN
> dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
> = t2.TravelDetailId INNER JOIN
> dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
> = t4.TravelDetailId INNER JOIN
> dbo.tblTravelEvent t5 ON t1.TravelEventId =
> t5.TravelEventId INNER JOIN
> dbo.amount_type t6 ON t2.amountTypeId =
> t6.amount_type_id INNER JOIN
> dbo.period t8 ON t1.PeriodID = t8.period_id
> WHERE (t1.MemberId = '222') AND (t2.amount <> 0)
> GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type
> Thanks,
> Trint