Hi all, I am new to the forum~
Suppose I have multiple tables, T1, T2, T3. I will use SELECT queries and apply AVG() and STDEV() on each of their columns, and average their results.
I can do this in two ways: one is to apply my SELECT multiple (3) times, then divide it by 3 in this case.
Another way is I create a View that UNIONS all T1 T2 T3, and apply AVG() and STDEV() on each columns.
Which solution is better? I mean, from a performance point of view. This is just a simplified version of my problem, and I would like to know what is the performance of using one (View) over the other (Table)... Does using View instead of table give me any performance overhead? Thanks everyone...They are the same solution. whether you use a select statment or a veiw you will be pulling data off the drives (or cache) to produce the answer. The view has the advantage of being pr-optimized. Views are generally used for this type of thing as it hides all the aggregation.
Don't know if this helped, Books Online has some interesting info look up views-SQL Server, overview and follow the hyper link to Scenarios for Using Views.sql
Showing posts with label view. Show all posts
Showing posts with label view. Show all posts
Monday, March 26, 2012
Performance Question Using Views
When creating views, do you follow the same rule to select as little as
possible? For instance, is it bad practice to create a view that selects
everything from one of my tables and certain columns from another table, and
when executing a stored procedure against the view to limit that to only the
columns I need at the time. So if I had a view that included 20 fields from
one table and 10 fields from another table, is that slower than if I create
a
view that only shows the actual fields I'll need for the particular lookup
I'll be doing? Hope this makes sense."Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Yes it does. And if you don't reference the extra columns of the view,
there is no additional cost. SQL Server will even eliminate joins which
exist in the view if none of the joined columns are referneced and the join
can't add or remove rows from the result.
David|||Mike
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Definitly ,Mike.When you using SELECT * in the view , SQL Server won't be
ably to use indexes in that case I mean COVERING indexs and as result you
will get a bad performance.
Actually , views are good choice for sequrity reasons I mean not letting
users an access to underlying tables directly
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.|||When you say views are good for security, does this apply to users of a web
application. They will not be able to do any adhoc reporting or running any
select statements directly against the database. So I'm wondering if we
should even use views, except for maybe a complex join that we don't want in
a stored procedure.
"Uri Dimant" wrote:
> Mike
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't b
e
> ably to use indexes in that case I mean COVERING indexs and as result you
> will get a bad performance.
> Actually , views are good choice for sequrity reasons I mean not letting
> users an access to underlying tables directly
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
>
>|||That's good to know...As I replied to Uri, I'm still wondering whether to us
e
views since the users will not be directly quering the database...they'll be
only seeing our web pages and interacting with the database that way. Thanks
.
"David Browne" wrote:
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> Yes it does. And if you don't reference the extra columns of the view,
> there is no additional cost. SQL Server will even eliminate joins which
> exist in the view if none of the joined columns are referneced and the joi
n
> can't add or remove rows from the result.
> David
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> Mike
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't
> be ably to use indexes in that case I mean COVERING indexs and as result
> you will get a bad performance.
>
Um, no. SELECT * in a view does not force SQL Server to access all the
columns in the intermediate result. The views definition is incorproated
into the overall query before optimization, and if certian columns or joins
are not needed, they won't be touched.
EG|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23Otlon4%23FHA.208@.tk2msftngp13.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> Um, no. SELECT * in a view does not force SQL Server to access all the
> columns in the intermediate result. The views definition is incorproated
> into the overall query before optimization, and if certian columns or
> joins are not needed, they won't be touched.
> EG
>
[oops]
Here's the example I intended to include. In it a view contains both a join
and a SELECT *, both of which are disregarded when querying against the
view, and and only a small covering index is accessed.
create table B
(
ID int primary key,
Description varchar(50)
)
Create table T
(
ID int primary key,
A varchar(10),
B int not null references B
)
create index IX_T_A on T(A)
go
create view VT
as
select T.*,B.Description BDescription
from T
join B
on T.B = B.ID
go
insert into B(ID, Description) values (1,'One')
insert into T(ID,A,B) values (1,'Hello', 1)
go
set showplan_xml on
go
select A from VT
go
set showplan_xml off
/*
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
Version="1.0" Build="9.00.1399.06">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="select A from VT "
StatementId="1" StatementCompId="1" StatementType="SELECT"
StatementSubTreeCost="0.0032831" StatementEstRows="1"
StatementOptmLevel="TRIVIAL">
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false"
ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="8">
<RelOp NodeId="0" PhysicalOp="Index Scan" LogicalOp="Index Scan"
EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581"
AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" Parallel="0"
EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</OutputList>
<IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</DefinedValue>
</DefinedValues>
<Object Database="[test]" Schema="[dbo]" Table="[T]"
Index="[IX_T_A]" />
</IndexScan>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
David
possible? For instance, is it bad practice to create a view that selects
everything from one of my tables and certain columns from another table, and
when executing a stored procedure against the view to limit that to only the
columns I need at the time. So if I had a view that included 20 fields from
one table and 10 fields from another table, is that slower than if I create
a
view that only shows the actual fields I'll need for the particular lookup
I'll be doing? Hope this makes sense."Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Yes it does. And if you don't reference the extra columns of the view,
there is no additional cost. SQL Server will even eliminate joins which
exist in the view if none of the joined columns are referneced and the join
can't add or remove rows from the result.
David|||Mike
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Definitly ,Mike.When you using SELECT * in the view , SQL Server won't be
ably to use indexes in that case I mean COVERING indexs and as result you
will get a bad performance.
Actually , views are good choice for sequrity reasons I mean not letting
users an access to underlying tables directly
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.|||When you say views are good for security, does this apply to users of a web
application. They will not be able to do any adhoc reporting or running any
select statements directly against the database. So I'm wondering if we
should even use views, except for maybe a complex join that we don't want in
a stored procedure.
"Uri Dimant" wrote:
> Mike
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't b
e
> ably to use indexes in that case I mean COVERING indexs and as result you
> will get a bad performance.
> Actually , views are good choice for sequrity reasons I mean not letting
> users an access to underlying tables directly
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
>
>|||That's good to know...As I replied to Uri, I'm still wondering whether to us
e
views since the users will not be directly quering the database...they'll be
only seeing our web pages and interacting with the database that way. Thanks
.
"David Browne" wrote:
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> Yes it does. And if you don't reference the extra columns of the view,
> there is no additional cost. SQL Server will even eliminate joins which
> exist in the view if none of the joined columns are referneced and the joi
n
> can't add or remove rows from the result.
> David
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> Mike
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't
> be ably to use indexes in that case I mean COVERING indexs and as result
> you will get a bad performance.
>
Um, no. SELECT * in a view does not force SQL Server to access all the
columns in the intermediate result. The views definition is incorproated
into the overall query before optimization, and if certian columns or joins
are not needed, they won't be touched.
EG|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23Otlon4%23FHA.208@.tk2msftngp13.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> Um, no. SELECT * in a view does not force SQL Server to access all the
> columns in the intermediate result. The views definition is incorproated
> into the overall query before optimization, and if certian columns or
> joins are not needed, they won't be touched.
> EG
>
[oops]
Here's the example I intended to include. In it a view contains both a join
and a SELECT *, both of which are disregarded when querying against the
view, and and only a small covering index is accessed.
create table B
(
ID int primary key,
Description varchar(50)
)
Create table T
(
ID int primary key,
A varchar(10),
B int not null references B
)
create index IX_T_A on T(A)
go
create view VT
as
select T.*,B.Description BDescription
from T
join B
on T.B = B.ID
go
insert into B(ID, Description) values (1,'One')
insert into T(ID,A,B) values (1,'Hello', 1)
go
set showplan_xml on
go
select A from VT
go
set showplan_xml off
/*
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
Version="1.0" Build="9.00.1399.06">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="select A from VT "
StatementId="1" StatementCompId="1" StatementType="SELECT"
StatementSubTreeCost="0.0032831" StatementEstRows="1"
StatementOptmLevel="TRIVIAL">
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false"
ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="8">
<RelOp NodeId="0" PhysicalOp="Index Scan" LogicalOp="Index Scan"
EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581"
AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" Parallel="0"
EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</OutputList>
<IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</DefinedValue>
</DefinedValues>
<Object Database="[test]" Schema="[dbo]" Table="[T]"
Index="[IX_T_A]" />
</IndexScan>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
David
Performance Question Using Views
When creating views, do you follow the same rule to select as little as
possible? For instance, is it bad practice to create a view that selects
everything from one of my tables and certain columns from another table, and
when executing a stored procedure against the view to limit that to only the
columns I need at the time. So if I had a view that included 20 fields from
one table and 10 fields from another table, is that slower than if I create a
view that only shows the actual fields I'll need for the particular lookup
I'll be doing? Hope this makes sense.Mike
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Definitly ,Mike.When you using SELECT * in the view , SQL Server won't be
ably to use indexes in that case I mean COVERING indexs and as result you
will get a bad performance.
Actually , views are good choice for sequrity reasons I mean not letting
users an access to underlying tables directly
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.|||"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Yes it does. And if you don't reference the extra columns of the view,
there is no additional cost. SQL Server will even eliminate joins which
exist in the view if none of the joined columns are referneced and the join
can't add or remove rows from the result.
David|||When you say views are good for security, does this apply to users of a web
application. They will not be able to do any adhoc reporting or running any
select statements directly against the database. So I'm wondering if we
should even use views, except for maybe a complex join that we don't want in
a stored procedure.
"Uri Dimant" wrote:
> Mike
> > columns I need at the time. So if I had a view that included 20 fields
> > from
> > one table and 10 fields from another table, is that slower than if I
> > create a
> > view that only shows the actual fields I'll need for the particular lookup
> > I'll be doing? Hope this makes sense.
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't be
> ably to use indexes in that case I mean COVERING indexs and as result you
> will get a bad performance.
> Actually , views are good choice for sequrity reasons I mean not letting
> users an access to underlying tables directly
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> > When creating views, do you follow the same rule to select as little as
> > possible? For instance, is it bad practice to create a view that selects
> > everything from one of my tables and certain columns from another table,
> > and
> > when executing a stored procedure against the view to limit that to only
> > the
> > columns I need at the time. So if I had a view that included 20 fields
> > from
> > one table and 10 fields from another table, is that slower than if I
> > create a
> > view that only shows the actual fields I'll need for the particular lookup
> > I'll be doing? Hope this makes sense.
>
>|||That's good to know...As I replied to Uri, I'm still wondering whether to use
views since the users will not be directly quering the database...they'll be
only seeing our web pages and interacting with the database that way. Thanks.
"David Browne" wrote:
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> > When creating views, do you follow the same rule to select as little as
> > possible? For instance, is it bad practice to create a view that selects
> > everything from one of my tables and certain columns from another table,
> > and
> > when executing a stored procedure against the view to limit that to only
> > the
> > columns I need at the time. So if I had a view that included 20 fields
> > from
> > one table and 10 fields from another table, is that slower than if I
> > create a
> > view that only shows the actual fields I'll need for the particular lookup
> > I'll be doing? Hope this makes sense.
> Yes it does. And if you don't reference the extra columns of the view,
> there is no additional cost. SQL Server will even eliminate joins which
> exist in the view if none of the joined columns are referneced and the join
> can't add or remove rows from the result.
> David
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> Mike
>> columns I need at the time. So if I had a view that included 20 fields
>> from
>> one table and 10 fields from another table, is that slower than if I
>> create a
>> view that only shows the actual fields I'll need for the particular
>> lookup
>> I'll be doing? Hope this makes sense.
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't
> be ably to use indexes in that case I mean COVERING indexs and as result
> you will get a bad performance.
>
Um, no. SELECT * in a view does not force SQL Server to access all the
columns in the intermediate result. The views definition is incorproated
into the overall query before optimization, and if certian columns or joins
are not needed, they won't be touched.
EG|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23Otlon4%23FHA.208@.tk2msftngp13.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
>> Mike
>> columns I need at the time. So if I had a view that included 20 fields
>> from
>> one table and 10 fields from another table, is that slower than if I
>> create a
>> view that only shows the actual fields I'll need for the particular
>> lookup
>> I'll be doing? Hope this makes sense.
>>
>> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't
>> be ably to use indexes in that case I mean COVERING indexs and as result
>> you will get a bad performance.
> Um, no. SELECT * in a view does not force SQL Server to access all the
> columns in the intermediate result. The views definition is incorproated
> into the overall query before optimization, and if certian columns or
> joins are not needed, they won't be touched.
> EG
>
[oops]
Here's the example I intended to include. In it a view contains both a join
and a SELECT *, both of which are disregarded when querying against the
view, and and only a small covering index is accessed.
create table B
(
ID int primary key,
Description varchar(50)
)
Create table T
(
ID int primary key,
A varchar(10),
B int not null references B
)
create index IX_T_A on T(A)
go
create view VT
as
select T.*,B.Description BDescription
from T
join B
on T.B = B.ID
go
insert into B(ID, Description) values (1,'One')
insert into T(ID,A,B) values (1,'Hello', 1)
go
set showplan_xml on
go
select A from VT
go
set showplan_xml off
/*
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
Version="1.0" Build="9.00.1399.06">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="select A from VT "
StatementId="1" StatementCompId="1" StatementType="SELECT"
StatementSubTreeCost="0.0032831" StatementEstRows="1"
StatementOptmLevel="TRIVIAL">
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false"
ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="8">
<RelOp NodeId="0" PhysicalOp="Index Scan" LogicalOp="Index Scan"
EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581"
AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" Parallel="0"
EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</OutputList>
<IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</DefinedValue>
</DefinedValues>
<Object Database="[test]" Schema="[dbo]" Table="[T]"
Index="[IX_T_A]" />
</IndexScan>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Davidsql
possible? For instance, is it bad practice to create a view that selects
everything from one of my tables and certain columns from another table, and
when executing a stored procedure against the view to limit that to only the
columns I need at the time. So if I had a view that included 20 fields from
one table and 10 fields from another table, is that slower than if I create a
view that only shows the actual fields I'll need for the particular lookup
I'll be doing? Hope this makes sense.Mike
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Definitly ,Mike.When you using SELECT * in the view , SQL Server won't be
ably to use indexes in that case I mean COVERING indexs and as result you
will get a bad performance.
Actually , views are good choice for sequrity reasons I mean not letting
users an access to underlying tables directly
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.|||"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Yes it does. And if you don't reference the extra columns of the view,
there is no additional cost. SQL Server will even eliminate joins which
exist in the view if none of the joined columns are referneced and the join
can't add or remove rows from the result.
David|||When you say views are good for security, does this apply to users of a web
application. They will not be able to do any adhoc reporting or running any
select statements directly against the database. So I'm wondering if we
should even use views, except for maybe a complex join that we don't want in
a stored procedure.
"Uri Dimant" wrote:
> Mike
> > columns I need at the time. So if I had a view that included 20 fields
> > from
> > one table and 10 fields from another table, is that slower than if I
> > create a
> > view that only shows the actual fields I'll need for the particular lookup
> > I'll be doing? Hope this makes sense.
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't be
> ably to use indexes in that case I mean COVERING indexs and as result you
> will get a bad performance.
> Actually , views are good choice for sequrity reasons I mean not letting
> users an access to underlying tables directly
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> > When creating views, do you follow the same rule to select as little as
> > possible? For instance, is it bad practice to create a view that selects
> > everything from one of my tables and certain columns from another table,
> > and
> > when executing a stored procedure against the view to limit that to only
> > the
> > columns I need at the time. So if I had a view that included 20 fields
> > from
> > one table and 10 fields from another table, is that slower than if I
> > create a
> > view that only shows the actual fields I'll need for the particular lookup
> > I'll be doing? Hope this makes sense.
>
>|||That's good to know...As I replied to Uri, I'm still wondering whether to use
views since the users will not be directly quering the database...they'll be
only seeing our web pages and interacting with the database that way. Thanks.
"David Browne" wrote:
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> > When creating views, do you follow the same rule to select as little as
> > possible? For instance, is it bad practice to create a view that selects
> > everything from one of my tables and certain columns from another table,
> > and
> > when executing a stored procedure against the view to limit that to only
> > the
> > columns I need at the time. So if I had a view that included 20 fields
> > from
> > one table and 10 fields from another table, is that slower than if I
> > create a
> > view that only shows the actual fields I'll need for the particular lookup
> > I'll be doing? Hope this makes sense.
> Yes it does. And if you don't reference the extra columns of the view,
> there is no additional cost. SQL Server will even eliminate joins which
> exist in the view if none of the joined columns are referneced and the join
> can't add or remove rows from the result.
> David
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> Mike
>> columns I need at the time. So if I had a view that included 20 fields
>> from
>> one table and 10 fields from another table, is that slower than if I
>> create a
>> view that only shows the actual fields I'll need for the particular
>> lookup
>> I'll be doing? Hope this makes sense.
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't
> be ably to use indexes in that case I mean COVERING indexs and as result
> you will get a bad performance.
>
Um, no. SELECT * in a view does not force SQL Server to access all the
columns in the intermediate result. The views definition is incorproated
into the overall query before optimization, and if certian columns or joins
are not needed, they won't be touched.
EG|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23Otlon4%23FHA.208@.tk2msftngp13.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
>> Mike
>> columns I need at the time. So if I had a view that included 20 fields
>> from
>> one table and 10 fields from another table, is that slower than if I
>> create a
>> view that only shows the actual fields I'll need for the particular
>> lookup
>> I'll be doing? Hope this makes sense.
>>
>> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't
>> be ably to use indexes in that case I mean COVERING indexs and as result
>> you will get a bad performance.
> Um, no. SELECT * in a view does not force SQL Server to access all the
> columns in the intermediate result. The views definition is incorproated
> into the overall query before optimization, and if certian columns or
> joins are not needed, they won't be touched.
> EG
>
[oops]
Here's the example I intended to include. In it a view contains both a join
and a SELECT *, both of which are disregarded when querying against the
view, and and only a small covering index is accessed.
create table B
(
ID int primary key,
Description varchar(50)
)
Create table T
(
ID int primary key,
A varchar(10),
B int not null references B
)
create index IX_T_A on T(A)
go
create view VT
as
select T.*,B.Description BDescription
from T
join B
on T.B = B.ID
go
insert into B(ID, Description) values (1,'One')
insert into T(ID,A,B) values (1,'Hello', 1)
go
set showplan_xml on
go
select A from VT
go
set showplan_xml off
/*
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
Version="1.0" Build="9.00.1399.06">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="select A from VT "
StatementId="1" StatementCompId="1" StatementType="SELECT"
StatementSubTreeCost="0.0032831" StatementEstRows="1"
StatementOptmLevel="TRIVIAL">
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false"
ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="8">
<RelOp NodeId="0" PhysicalOp="Index Scan" LogicalOp="Index Scan"
EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581"
AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" Parallel="0"
EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</OutputList>
<IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</DefinedValue>
</DefinedValues>
<Object Database="[test]" Schema="[dbo]" Table="[T]"
Index="[IX_T_A]" />
</IndexScan>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Davidsql
Performance Question Using Views
When creating views, do you follow the same rule to select as little as
possible? For instance, is it bad practice to create a view that selects
everything from one of my tables and certain columns from another table, and
when executing a stored procedure against the view to limit that to only the
columns I need at the time. So if I had a view that included 20 fields from
one table and 10 fields from another table, is that slower than if I create a
view that only shows the actual fields I'll need for the particular lookup
I'll be doing? Hope this makes sense.
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Yes it does. And if you don't reference the extra columns of the view,
there is no additional cost. SQL Server will even eliminate joins which
exist in the view if none of the joined columns are referneced and the join
can't add or remove rows from the result.
David
|||Mike
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Definitly ,Mike.When you using SELECT * in the view , SQL Server won't be
ably to use indexes in that case I mean COVERING indexs and as result you
will get a bad performance.
Actually , views are good choice for sequrity reasons I mean not letting
users an access to underlying tables directly
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
|||When you say views are good for security, does this apply to users of a web
application. They will not be able to do any adhoc reporting or running any
select statements directly against the database. So I'm wondering if we
should even use views, except for maybe a complex join that we don't want in
a stored procedure.
"Uri Dimant" wrote:
> Mike
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't be
> ably to use indexes in that case I mean COVERING indexs and as result you
> will get a bad performance.
> Actually , views are good choice for sequrity reasons I mean not letting
> users an access to underlying tables directly
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
>
>
|||That's good to know...As I replied to Uri, I'm still wondering whether to use
views since the users will not be directly quering the database...they'll be
only seeing our web pages and interacting with the database that way. Thanks.
"David Browne" wrote:
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> Yes it does. And if you don't reference the extra columns of the view,
> there is no additional cost. SQL Server will even eliminate joins which
> exist in the view if none of the joined columns are referneced and the join
> can't add or remove rows from the result.
> David
>
>
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> Mike
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't
> be ably to use indexes in that case I mean COVERING indexs and as result
> you will get a bad performance.
>
Um, no. SELECT * in a view does not force SQL Server to access all the
columns in the intermediate result. The views definition is incorproated
into the overall query before optimization, and if certian columns or joins
are not needed, they won't be touched.
EG
|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23Otlon4%23FHA.208@.tk2msftngp13.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> Um, no. SELECT * in a view does not force SQL Server to access all the
> columns in the intermediate result. The views definition is incorproated
> into the overall query before optimization, and if certian columns or
> joins are not needed, they won't be touched.
> EG
>
[oops]
Here's the example I intended to include. In it a view contains both a join
and a SELECT *, both of which are disregarded when querying against the
view, and and only a small covering index is accessed.
create table B
(
ID int primary key,
Description varchar(50)
)
Create table T
(
ID int primary key,
A varchar(10),
B int not null references B
)
create index IX_T_A on T(A)
go
create view VT
as
select T.*,B.Description BDescription
from T
join B
on T.B = B.ID
go
insert into B(ID, Description) values (1,'One')
insert into T(ID,A,B) values (1,'Hello', 1)
go
set showplan_xml on
go
select A from VT
go
set showplan_xml off
/*
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
Version="1.0" Build="9.00.1399.06">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="select A from VT "
StatementId="1" StatementCompId="1" StatementType="SELECT"
StatementSubTreeCost="0.0032831" StatementEstRows="1"
StatementOptmLevel="TRIVIAL">
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false"
ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="8">
<RelOp NodeId="0" PhysicalOp="Index Scan" LogicalOp="Index Scan"
EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581"
AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" Parallel="0"
EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</OutputList>
<IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</DefinedValue>
</DefinedValues>
<Object Database="[test]" Schema="[dbo]" Table="[T]"
Index="[IX_T_A]" />
</IndexScan>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
David
possible? For instance, is it bad practice to create a view that selects
everything from one of my tables and certain columns from another table, and
when executing a stored procedure against the view to limit that to only the
columns I need at the time. So if I had a view that included 20 fields from
one table and 10 fields from another table, is that slower than if I create a
view that only shows the actual fields I'll need for the particular lookup
I'll be doing? Hope this makes sense.
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Yes it does. And if you don't reference the extra columns of the view,
there is no additional cost. SQL Server will even eliminate joins which
exist in the view if none of the joined columns are referneced and the join
can't add or remove rows from the result.
David
|||Mike
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Definitly ,Mike.When you using SELECT * in the view , SQL Server won't be
ably to use indexes in that case I mean COVERING indexs and as result you
will get a bad performance.
Actually , views are good choice for sequrity reasons I mean not letting
users an access to underlying tables directly
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
|||When you say views are good for security, does this apply to users of a web
application. They will not be able to do any adhoc reporting or running any
select statements directly against the database. So I'm wondering if we
should even use views, except for maybe a complex join that we don't want in
a stored procedure.
"Uri Dimant" wrote:
> Mike
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't be
> ably to use indexes in that case I mean COVERING indexs and as result you
> will get a bad performance.
> Actually , views are good choice for sequrity reasons I mean not letting
> users an access to underlying tables directly
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
>
>
|||That's good to know...As I replied to Uri, I'm still wondering whether to use
views since the users will not be directly quering the database...they'll be
only seeing our web pages and interacting with the database that way. Thanks.
"David Browne" wrote:
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> Yes it does. And if you don't reference the extra columns of the view,
> there is no additional cost. SQL Server will even eliminate joins which
> exist in the view if none of the joined columns are referneced and the join
> can't add or remove rows from the result.
> David
>
>
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> Mike
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't
> be ably to use indexes in that case I mean COVERING indexs and as result
> you will get a bad performance.
>
Um, no. SELECT * in a view does not force SQL Server to access all the
columns in the intermediate result. The views definition is incorproated
into the overall query before optimization, and if certian columns or joins
are not needed, they won't be touched.
EG
|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23Otlon4%23FHA.208@.tk2msftngp13.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> Um, no. SELECT * in a view does not force SQL Server to access all the
> columns in the intermediate result. The views definition is incorproated
> into the overall query before optimization, and if certian columns or
> joins are not needed, they won't be touched.
> EG
>
[oops]
Here's the example I intended to include. In it a view contains both a join
and a SELECT *, both of which are disregarded when querying against the
view, and and only a small covering index is accessed.
create table B
(
ID int primary key,
Description varchar(50)
)
Create table T
(
ID int primary key,
A varchar(10),
B int not null references B
)
create index IX_T_A on T(A)
go
create view VT
as
select T.*,B.Description BDescription
from T
join B
on T.B = B.ID
go
insert into B(ID, Description) values (1,'One')
insert into T(ID,A,B) values (1,'Hello', 1)
go
set showplan_xml on
go
select A from VT
go
set showplan_xml off
/*
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
Version="1.0" Build="9.00.1399.06">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="select A from VT "
StatementId="1" StatementCompId="1" StatementType="SELECT"
StatementSubTreeCost="0.0032831" StatementEstRows="1"
StatementOptmLevel="TRIVIAL">
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false"
ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="8">
<RelOp NodeId="0" PhysicalOp="Index Scan" LogicalOp="Index Scan"
EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581"
AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" Parallel="0"
EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</OutputList>
<IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</DefinedValue>
</DefinedValues>
<Object Database="[test]" Schema="[dbo]" Table="[T]"
Index="[IX_T_A]" />
</IndexScan>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
David
Wednesday, March 21, 2012
Performance Problems - Possible Because of Indexed View
I recently added several indexed views to a high traffic table (high volume of both inserts and selects) because I needed to have some complicated unique constraints involving columns that allow NULL.
While I didn't notice performance problems at first, it appears to be that the CPU on the SQL Server is getting pegged when more than 10 or 15 simultaneous inserts are happening on the table. This is a quad proc 3Ghz Xeon, so the fact the CPU is hitting 90%+ while doing 15 inserts a second doesn't make sense to me.
Very quickly the sproc that is being repeatedly called by some middle tier components is taking 30+ seconds to execute, eventually causing timeouts. This sproc is very simple. It does a few quick select statements (that take well under 100ms), and then does an insert into the table in question. That's it.
The only thing I can think of is that the overhead of 3 separate indexed views on the table, each of which contains a significant subset of the total rows in the table (550,000+ rows), is causing SQL Server to get swamped trying to keep the indexes up to date.
Does this seem like a possibility? I'm planning on temporarily removing those indexed views to see how it performs without them, although this is dangerous because it creates the potential for invalid data.
In case anybody is interested, it wasn't the Indexed Views. While these did account for a sizable portion of the increased CPU load (~5%), it wasn't almost entirely due to a really, really bad query that wasn't hitting the proper indexes.
Essentially, I had a query that was resulting in 3 index scans and a bookmark lookup on a table with about 1 million rows. This was happening on every insert. (Whoops!)
I modified some indexes and now the execution plan does two index seeks. This resulted in individual inserts going from taking about 5 seconds to taking about 100ms. Load that would have taken over an hour to process before now takes about 15 seconds.
Just goes to show... it pays to understand query plans.
Performance Problems - Possible Because of Indexed View
I recently added several indexed views to a high traffic table (high volume of both inserts and selects) because I needed to have some complicated unique constraints involving columns that allow NULL.
While I didn't notice performance problems at first, it appears to be that the CPU on the SQL Server is getting pegged when more than 10 or 15 simultaneous inserts are happening on the table. This is a quad proc 3Ghz Xeon, so the fact the CPU is hitting 90%+ while doing 15 inserts a second doesn't make sense to me.
Very quickly the sproc that is being repeatedly called by some middle tier components is taking 30+ seconds to execute, eventually causing timeouts. This sproc is very simple. It does a few quick select statements (that take well under 100ms), and then does an insert into the table in question. That's it.
The only thing I can think of is that the overhead of 3 separate indexed views on the table, each of which contains a significant subset of the total rows in the table (550,000+ rows), is causing SQL Server to get swamped trying to keep the indexes up to date.
Does this seem like a possibility? I'm planning on temporarily removing those indexed views to see how it performs without them, although this is dangerous because it creates the potential for invalid data.
In case anybody is interested, it wasn't the Indexed Views. While these did account for a sizable portion of the increased CPU load (~5%), it wasn't almost entirely due to a really, really bad query that wasn't hitting the proper indexes.
Essentially, I had a query that was resulting in 3 index scans and a bookmark lookup on a table with about 1 million rows. This was happening on every insert. (Whoops!)
I modified some indexes and now the execution plan does two index seeks. This resulted in individual inserts going from taking about 5 seconds to taking about 100ms. Load that would have taken over an hour to process before now takes about 15 seconds.
Just goes to show... it pays to understand query plans.
Monday, March 12, 2012
Performance problem
Hello there
I have two tables with combination of two fields that aren't unique in both
tables.
To make them unique i must, create view of the last id of the fields.
example SELECT Fld1, Fld2, max(id)
FROM tbl
GROUP BY Fld1, Fld2
This cause serious performace problem.
Is there a way to handle it?1)How much of a performance problem?
2)can you send details of indexes implemented?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:u8JN8LSYGHA.3604@.TK2MSFTNGP02.phx.gbl...
> Hello there
> I have two tables with combination of two fields that aren't unique in
both
> tables.
> To make them unique i must, create view of the last id of the fields.
> example SELECT Fld1, Fld2, max(id)
> FROM tbl
> GROUP BY Fld1, Fld2
> This cause serious performace problem.
> Is there a way to handle it?
>|||Whell Jack:
1. according to execution plan it caust at least 10 subtree cost more. and
on data which is more then 1,000,000 records any query can take more then an
hour.
2. fld1, and fld2 are indexes as non clustered.
What i need to do?
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:Beydna5nIMCvcdzZnZ2dnUVZ8qWdnZ2d@.bt
.com...
> 1)How much of a performance problem?
> 2)can you send details of indexes implemented?
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:u8JN8LSYGHA.3604@.TK2MSFTNGP02.phx.gbl...
> both
>|||Hi Roy
Do you have an index on [ID] column?
I suggest you to create a COVERING index as
CREATE INDEX Covering_idx on TableName(Fld1, Fld2, id) which may very useful
particular along with ORDER BY clause
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ug0QbaSYGHA.3328@.TK2MSFTNGP02.phx.gbl...
> Whell Jack:
> 1. according to execution plan it caust at least 10 subtree cost more. and
> on data which is more then 1,000,000 records any query can take more then
> an hour.
> 2. fld1, and fld2 are indexes as non clustered.
> What i need to do?
> "Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
> news:Beydna5nIMCvcdzZnZ2dnUVZ8qWdnZ2d@.bt
.com...
>|||Roy Goldhammer (roy@.hotmail.com) writes:
> I have two tables with combination of two fields that aren't unique in
> both tables.
> To make them unique i must, create view of the last id of the fields.
> example SELECT Fld1, Fld2, max(id)
> FROM tbl
> GROUP BY Fld1, Fld2
> This cause serious performace problem.
> Is there a way to handle it?
Maybe there is, but with the tiny amount of information you have posted,
it is impossible to give any useful advice.
I suggest that you post:
1) CREATE TABLE statements for you tables.
2) CREATE INDEX statements for the tables.
3) CREATE VIEW statement for your view.
4) A sample query from the view.
5) An indication of the number of rows in the table.
Output from SET STATISTICS PROFILE ON is also good.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That you need to do is redesign your tables to have a primary key. If
you can't alter this schema, then consider implementing a reporting
database that has a correct schema and design a process to move data
from the original source to your reporting warehouse.
Performance may be an issue, but if you're only moving data
periodically, that issue is mitigated. As others have posted, a
clearer definition would be helpful.
HTH,
Stu|||Yes Uri. The ID is the primary key clustered index.
I have also indexes on Fld1 and Fld2
Whay this is not enouth?
"Uri Dimant" <test@.test.com> wrote in message
news:Od9nH6SYGHA.4652@.TK2MSFTNGP04.phx.gbl...
> Hi Roy
> Do you have an index on [ID] column?
> I suggest you to create a COVERING index as
> CREATE INDEX Covering_idx on TableName(Fld1, Fld2, id) which may very
> useful particular along with ORDER BY clause
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:ug0QbaSYGHA.3328@.TK2MSFTNGP02.phx.gbl...
>
I have two tables with combination of two fields that aren't unique in both
tables.
To make them unique i must, create view of the last id of the fields.
example SELECT Fld1, Fld2, max(id)
FROM tbl
GROUP BY Fld1, Fld2
This cause serious performace problem.
Is there a way to handle it?1)How much of a performance problem?
2)can you send details of indexes implemented?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:u8JN8LSYGHA.3604@.TK2MSFTNGP02.phx.gbl...
> Hello there
> I have two tables with combination of two fields that aren't unique in
both
> tables.
> To make them unique i must, create view of the last id of the fields.
> example SELECT Fld1, Fld2, max(id)
> FROM tbl
> GROUP BY Fld1, Fld2
> This cause serious performace problem.
> Is there a way to handle it?
>|||Whell Jack:
1. according to execution plan it caust at least 10 subtree cost more. and
on data which is more then 1,000,000 records any query can take more then an
hour.
2. fld1, and fld2 are indexes as non clustered.
What i need to do?
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:Beydna5nIMCvcdzZnZ2dnUVZ8qWdnZ2d@.bt
.com...
> 1)How much of a performance problem?
> 2)can you send details of indexes implemented?
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:u8JN8LSYGHA.3604@.TK2MSFTNGP02.phx.gbl...
> both
>|||Hi Roy
Do you have an index on [ID] column?
I suggest you to create a COVERING index as
CREATE INDEX Covering_idx on TableName(Fld1, Fld2, id) which may very useful
particular along with ORDER BY clause
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ug0QbaSYGHA.3328@.TK2MSFTNGP02.phx.gbl...
> Whell Jack:
> 1. according to execution plan it caust at least 10 subtree cost more. and
> on data which is more then 1,000,000 records any query can take more then
> an hour.
> 2. fld1, and fld2 are indexes as non clustered.
> What i need to do?
> "Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
> news:Beydna5nIMCvcdzZnZ2dnUVZ8qWdnZ2d@.bt
.com...
>|||Roy Goldhammer (roy@.hotmail.com) writes:
> I have two tables with combination of two fields that aren't unique in
> both tables.
> To make them unique i must, create view of the last id of the fields.
> example SELECT Fld1, Fld2, max(id)
> FROM tbl
> GROUP BY Fld1, Fld2
> This cause serious performace problem.
> Is there a way to handle it?
Maybe there is, but with the tiny amount of information you have posted,
it is impossible to give any useful advice.
I suggest that you post:
1) CREATE TABLE statements for you tables.
2) CREATE INDEX statements for the tables.
3) CREATE VIEW statement for your view.
4) A sample query from the view.
5) An indication of the number of rows in the table.
Output from SET STATISTICS PROFILE ON is also good.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That you need to do is redesign your tables to have a primary key. If
you can't alter this schema, then consider implementing a reporting
database that has a correct schema and design a process to move data
from the original source to your reporting warehouse.
Performance may be an issue, but if you're only moving data
periodically, that issue is mitigated. As others have posted, a
clearer definition would be helpful.
HTH,
Stu|||Yes Uri. The ID is the primary key clustered index.
I have also indexes on Fld1 and Fld2
Whay this is not enouth?
"Uri Dimant" <test@.test.com> wrote in message
news:Od9nH6SYGHA.4652@.TK2MSFTNGP04.phx.gbl...
> Hi Roy
> Do you have an index on [ID] column?
> I suggest you to create a COVERING index as
> CREATE INDEX Covering_idx on TableName(Fld1, Fld2, id) which may very
> useful particular along with ORDER BY clause
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:ug0QbaSYGHA.3328@.TK2MSFTNGP02.phx.gbl...
>
performance penalty using views
Is there much of a performance penalty for using views? When I use the view
it takes 42 seconds to return results but when I run the query inside the
view by itself it returns records in 5 to 7 seconds.
Would a stored procedure be better? Would the stored procedure be better at
keeping the execution plan?
Thanks,
--
Dan D.Views require some extra time to look up info in system tables and although
they should offer the same execution time as the statement itself they may
have a slower execution time as a result. Although this may improve if the
execution plan is cached. A stored procedure would likely offer better
execution time over a view.
"Dan D." wrote:
> Is there much of a performance penalty for using views? When I use the view
> it takes 42 seconds to return results but when I run the query inside the
> view by itself it returns records in 5 to 7 seconds.
> Would a stored procedure be better? Would the stored procedure be better at
> keeping the execution plan?
> Thanks,
> --
> Dan D.|||After some more testing, I've discovered that if I delete the view and then
recreate it, I get the same performance from both the view and the raw query.
I guess something got out of sync somewhere.
Thanks,
--
Dan D.
"Francis" wrote:
> Views require some extra time to look up info in system tables and although
> they should offer the same execution time as the statement itself they may
> have a slower execution time as a result. Although this may improve if the
> execution plan is cached. A stored procedure would likely offer better
> execution time over a view.
>
> "Dan D." wrote:
> > Is there much of a performance penalty for using views? When I use the view
> > it takes 42 seconds to return results but when I run the query inside the
> > view by itself it returns records in 5 to 7 seconds.
> >
> > Would a stored procedure be better? Would the stored procedure be better at
> > keeping the execution plan?
> >
> > Thanks,
> > --
> > Dan D.
it takes 42 seconds to return results but when I run the query inside the
view by itself it returns records in 5 to 7 seconds.
Would a stored procedure be better? Would the stored procedure be better at
keeping the execution plan?
Thanks,
--
Dan D.Views require some extra time to look up info in system tables and although
they should offer the same execution time as the statement itself they may
have a slower execution time as a result. Although this may improve if the
execution plan is cached. A stored procedure would likely offer better
execution time over a view.
"Dan D." wrote:
> Is there much of a performance penalty for using views? When I use the view
> it takes 42 seconds to return results but when I run the query inside the
> view by itself it returns records in 5 to 7 seconds.
> Would a stored procedure be better? Would the stored procedure be better at
> keeping the execution plan?
> Thanks,
> --
> Dan D.|||After some more testing, I've discovered that if I delete the view and then
recreate it, I get the same performance from both the view and the raw query.
I guess something got out of sync somewhere.
Thanks,
--
Dan D.
"Francis" wrote:
> Views require some extra time to look up info in system tables and although
> they should offer the same execution time as the statement itself they may
> have a slower execution time as a result. Although this may improve if the
> execution plan is cached. A stored procedure would likely offer better
> execution time over a view.
>
> "Dan D." wrote:
> > Is there much of a performance penalty for using views? When I use the view
> > it takes 42 seconds to return results but when I run the query inside the
> > view by itself it returns records in 5 to 7 seconds.
> >
> > Would a stored procedure be better? Would the stored procedure be better at
> > keeping the execution plan?
> >
> > Thanks,
> > --
> > Dan D.
Friday, March 9, 2012
Performance on Fact table connected in View
Hi,
I have a Fact table and I'm accessing it through SQL Views where I have "group by". This is the one I feed to the Analysis Services. Would it be faster to access data if I have group by's in Views and feed that to Analysis Services or would it be better to just do the select statement in View and let the Analysis do all the aggregations?
cherriesh
If SQL and SSAS are on the same box then removing the group by should make things go faster as SQL will not need to sort the results and spool them temporarily out to tempdb until all the results have been read and there will be little latency to deal with between the two services.
If they are on separate machines it depends if the amount of time it takes to do the group by is more or less than the time it would take to transmitt the additional data over the network.
Wednesday, March 7, 2012
performance of indexed views
I see their benefit -- trust me. one question I have is that it looks like my indexed view has to get updated each time the underlying base table changes. What happens if I do an insert or bcp into the underlying table -- will me table go offline while this data gets re-aggregated -- is there a way for me to schedule this? I see UDAs as well, and I think they are more flexible, but I'd be concerned that they are getting too far away from the optimizer and how are they refreshed -- recompile of the code?
Help..and I know it's marketing -- but what direction is the best for people that use the product, UDAs or Indexed Views.
Indexed views get updated in-line with table updates, just as if you basically had an additional index on the base table. So, if you perform an insert into the base table, it will not commit/finish until all indexes on the base table AND indexes from materialized views are updated. They basically are very similair in the way that the engine ensures additional indexes on the base table are kept updated. The same applies to how statistics are updated and managed, the same as the corresponding base table. An indexed view has an associated b-tree(s) structure backing it that must be kept up to date just as the base table is in real-time with data modifications.
As for Indexed Views vs. UDA's, they are 2 totally different technologies for different uses...I really don't see how the 2 would even compare. A UDA is calculated from an instruction and dataset you pass it at runtime, with no physical backing at all (unless you create for example an indexed computed column that is based on the UDA, but I won't go there). A UDA is basically the same as using any of the existing built-in aggregate functions in SQL Server (i.e. sum(), count(), min(), max(), etc.).
HTH
|||The other thing to be aware of is that you always have to have a clustered index on an indexed view (this is what defines it) If you therefore have additional indexes on the indexed view, you will have the additional knock on impact when the underlying data is changed.
Indexed views are great in that they provide any easy way of providing the indexed view data quickly to the user with little effort, however you need to appreciate the impact on performance as well as the restrictions on how data is updated (SET statements etc).
|||I don't understand the 2nd part of the response you say they are different technologies, I agree, but they can be used for the same things no? If I wnatd to sum the sales of a bunch of regions and roll them up I could use an Indexed View for that -- aka summary table -- no? That's what I can do with a UDA? Help me here..|||Hi Chad -- I think there's something I just picked up on that I didn't relase -- that the Indexed View is a preset aggregate, while the UDA takes the data set and perform the aggregation dynamically, correct? If so, then is this just not a set of logic to run on the data set? Would you recommend a UDA over calling a SP to do the work? Do you see a lot of people using this, or would suggest it? What does it buy me -- just trying to understand how much CLR objects we want and should use based on the countless resources that say it's good for some stuff and not for others :)|||Yes, that is correct. You could use either to achieve what you are attempting to get, however the biggest difference between the 2 technologies is that an indexed view is materialized on-disk, just like a table, and a UDA performs the aggregation on the data-set at time of request, like a query against a table.
A UDA is just like you mentioned, basically logic that is performed against the data set...the advantage of a UDA in 2005 is that you can create your own aggregates that don't already exist as pre-defined (i.e. sum(), min(), max())...for example, you could create a median() aggregate for example.
As for why you would use a UDA over a stored procedure would come down to a couple of things that would be different for many different scenarios, including performance, encapsulation, type of use, etc. For example, you could use a UDA just like you could use an existing pre-defined aggregate (i.e. within a select statement), like this:
select sum(column), myuda(column) from table
whereas you couldn't do the above with a stored procedure. Also, you may need to perform complex computational logic on the data, which the CLR would be better at. However, if you're just grabbing data, a stored procedure may be better...all would depend on the scenario.
HTH,
Subscribe to:
Posts (Atom)