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

Performance question

sql functions vs sub selects vs inner joins instead of sub selects. Which
is faster."isicoder" <isicoder@.newsgroups.nospam> wrote in message
news:eRs9$YBLGHA.1132@.TK2MSFTNGP10.phx.gbl...
> sql functions vs sub selects vs inner joins instead of sub selects. Which
> is faster.
>
>
Depends on many things. Indexes, parallelism and so forth.
Start doing some reading at www.sql-server-performance.com
Check the execution plans and so forth.
Rick Sawtell
MCT, MCSD, MCDBA

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.