|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How does a view workCan anyone explain to me how does a vew work internally?
If a view is a replicate of a table, will it be faster to query from a table then a view? Thanks >> Can anyone explain to me how does a VIEW work internally? << The SQL Standard requires that it act as if it is materailized wheninvoke. The actual SQL products can do whatever they want to get that effect. The three most common approaches are: 1) Copy the SELECT statement body into the invoking query like a derived table. 2) Materialize the SELECT statement body in a working table for that session. 3) Materialize the SELECT statement body in a working table for all sessions using the VIEW and share it. If there is a WITH CHECK OPTION, things change. No, before you ask, I am not going to tell you. You actually will have to make an effort to learn that on your own. A view doesn't normally contain data. It is just a query. However, an
indexed view does contain data and *may* improve your query performance. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Toronto, ON Canada "SQL Ken" <SQL***@gmail.com> wrote in message Can anyone explain to me how does a vew work internally?news:1157592184.497564.25560@b28g2000cwb.googlegroups.com... If a view is a replicate of a table, will it be faster to query from a table then a view? Thanks Tom,
If a view and a table are indexed properly, shouldn't querying from a table be faster because it go directly against the table where the querying from a view require an additional loop? For example query from a table >> SELECT * FROM table1 where as Query from a view would be equivalent to >> SELECT * FROM (SELECT * FROM table1 ) MyView Thanks Ken Tom Moreau wrote: Show quote > A view doesn't normally contain data. It is just a query. However, an > indexed view does contain data and *may* improve your query performance. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Toronto, ON Canada > > "SQL Ken" <SQL***@gmail.com> wrote in message > news:1157592184.497564.25560@b28g2000cwb.googlegroups.com... > Can anyone explain to me how does a vew work internally? > > If a view is a replicate of a table, will it be faster to query from a > table then a view? > > Thanks >> If a view and a table are indexed properly, shouldn't querying from a table be faster because it go directly against the table where the querying from a view require an additional loop? << No, not always. Consider this skeleton:CREATE TABLE Personnel (emp_id, emp_loc, ...); CREATE VIEW NYC_Personnel ( ..) AS SELECT emp_id, ... FROM Personnel WHERE emp_loc = 'New York City'; The query SELECT N.emp_id, F.stuff, .. FROM NYC_Personnel AS N, Foobar AS F WHERE F.emp_id = N.emp_id; Will be converted to this by every optimizer I know. : SELECT N.emp_id, F.stuff, .. FROM Personnel AS N, Foobar AS F WHERE F.emp_id = N.emp_id AND N.emp_loc = 'New York City'; The NYC_Personnel will not be materialized, but you get the same effect. No, there is no additional 'loop' involved. If the view was a simple SELECT,
selecting from the view or the table would be the exact same work. SQL Server would translate the select from the view into a select from the underlying table before execution. So selecting from a view should never be any WORSE performance than doing the direct query against the base table(s). The benefit comes in when the view is based on an aggregation of table data, e.g. select the sum of sales for each of the 50 states in the US). Building an index on the view actually permanently stores the aggregated data. So for the example given, instead of millions of rows for your individual sales, you'll only have 50 rows, one for each state. Without the index on the view, every time you selected the aggregated data, SQL would have to RECOMPUTE the sums for each state. With the indexed view, the sums are physically stored in a mere 50 rows, and it's very fast to get the data you need. -- Show quoteHTH Kalen Delaney, SQL Server MVP "SQL Ken" <SQL***@gmail.com> wrote in message news:1157650871.227855.289080@m79g2000cwm.googlegroups.com... > Tom, > If a view and a table are indexed properly, > shouldn't querying from a table be faster because it go directly > against the table where the querying from a view require an additional > loop? > For example > query from a table >> > > SELECT * > FROM table1 > > where as > Query from a view would be equivalent to >> > > SELECT * > FROM > (SELECT * > FROM table1 > ) MyView > > > Thanks > > Ken > > > Tom Moreau wrote: >> A view doesn't normally contain data. It is just a query. However, an >> indexed view does contain data and *may* improve your query performance. >> >> -- >> Tom >> >> ---------------------------------------------------- >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >> SQL Server MVP >> Toronto, ON Canada >> >> "SQL Ken" <SQL***@gmail.com> wrote in message >> news:1157592184.497564.25560@b28g2000cwb.googlegroups.com... >> Can anyone explain to me how does a vew work internally? >> >> If a view is a replicate of a table, will it be faster to query from a >> table then a view? >> >> Thanks > Kalen,
I would like to comment that in OLTP situation an indexed view like this might introduce a lot of competition on locks and might cause deadlocks. Even though it's only 50 rows, under read committed isolation level your select query might spend quite a time in lock waiting state... Yes of course, there's a lot of things to consider before building indexed
views. Anyone expecting to use them should do careful planning, understand the prerequisites, and be aware of the costs as well as the benefits. -- Show quoteHTH Kalen Delaney, SQL Server MVP "Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message news:1157663477.320776.20800@m79g2000cwm.googlegroups.com... > Kalen, > > I would like to comment that in OLTP situation an indexed view like > this might introduce a lot of competition on locks and might cause > deadlocks. Even though it's only 50 rows, under read committed > isolation level your select query might spend quite a time in lock > waiting state... > SQL Ken wrote:
Show quote > Tom, Ken, compare the execution plan of these two queries, you'll see that > If a view and a table are indexed properly, > shouldn't querying from a table be faster because it go directly > against the table where the querying from a view require an additional > loop? > For example > query from a table >> > > SELECT * > FROM table1 > > where as > Query from a view would be equivalent to >> > > SELECT * > FROM > (SELECT * > FROM table1 > ) MyView > > > Thanks > > Ken > internally they do the exact same thing...  |
|||||||||||||||||||||||
Other interesting topics