Views

#1

This question pertains to the Query Plan for views.

Assume that I have a table of 10,000 widgets (widgets_table) with a primary
key on the identity column widget_id. Now, assume I have a view
(widgets_view) defined as:

select *
from widgets_table

The plan for the following query (against the view) puts 10,000 rows into
play:

select *
from widgets_view
where widgets_id = 5000

The plan for the following query (against the table) only puts 1 row into
play:

select *
from widgets_table
where widgets_id = 5000

How can I hit the view but get the efficiencies of a table? Are there
hints, compiler options, configuration options, etc... that can be used to
influence the query plan of the view?

#2

On Wed, 17 Dec 2008 06:37:15 -0800, Dave wrote:

Quote:

This question pertains to the Query Plan for views.

Assume that I have a table of 10,000 widgets (widgets_table) with a primary
key on the identity column widget_id. Now, assume I have a view
(widgets_view) defined as:

select *
from widgets_table

The plan for the following query (against the view) puts 10,000 rows into
play:

select *
from widgets_view
where widgets_id = 5000

The plan for the following query (against the table) only puts 1 row into
play:

select *
from widgets_table
where widgets_id = 5000

How can I hit the view but get the efficiencies of a table? Are there
hints, compiler options, configuration options, etc... that can be used to
influence the query plan of the view?

Hi Dave,

I'm afraid that either I don't understand your question, or you don't
understand how views are processed internally.

In the example you give above, both queries will return only one row,
and they will use the exact same execution plan.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

#3

On Wed, 17 Dec 2008 06:37:15 -0800, Dave wrote:

Quote:

This question pertains to the Query Plan for views.

Assume that I have a table of 10,000 widgets (widgets_table) with a primary
key on the identity column widget_id. Now, assume I have a view
(widgets_view) defined as:

select *
from widgets_table

The plan for the following query (against the view) puts 10,000 rows into
play:

select *
from widgets_view
where widgets_id = 5000

The plan for the following query (against the table) only puts 1 row into
play:

select *
from widgets_table
where widgets_id = 5000

How can I hit the view but get the efficiencies of a table? Are there
hints, compiler options, configuration options, etc... that can be used to
influence the query plan of the view?

Hi Dave,

I'm afraid that either I don't understand your question, or you don't
understand how views are processed internally.

In the example you give above, both queries will return only one row,
and they will use the exact same execution plan.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

#4

On Wed, 17 Dec 2008 06:37:15 -0800, Dave wrote:

Quote:

This question pertains to the Query Plan for views.

Assume that I have a table of 10,000 widgets (widgets_table) with a primary
key on the identity column widget_id. Now, assume I have a view
(widgets_view) defined as:

select *
from widgets_table

The plan for the following query (against the view) puts 10,000 rows into
play:

select *
from widgets_view
where widgets_id = 5000

The plan for the following query (against the table) only puts 1 row into
play:

select *
from widgets_table
where widgets_id = 5000

How can I hit the view but get the efficiencies of a table? Are there
hints, compiler options, configuration options, etc... that can be used to
influence the query plan of the view?

Hi Dave,

I'm afraid that either I don't understand your question, or you don't
understand how views are processed internally.

In the example you give above, both queries will return only one row,
and they will use the exact same execution plan.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

#5

On Wed, 17 Dec 2008 06:37:15 -0800, Dave wrote:

Quote:

This question pertains to the Query Plan for views.

Assume that I have a table of 10,000 widgets (widgets_table) with a primary
key on the identity column widget_id. Now, assume I have a view
(widgets_view) defined as:

select *
from widgets_table

The plan for the following query (against the view) puts 10,000 rows into
play:

select *
from widgets_view
where widgets_id = 5000

The plan for the following query (against the table) only puts 1 row into
play:

select *
from widgets_table
where widgets_id = 5000

How can I hit the view but get the efficiencies of a table? Are there
hints, compiler options, configuration options, etc... that can be used to
influence the query plan of the view?

Hi Dave,

I'm afraid that either I don't understand your question, or you don't
understand how views are processed internally.

In the example you give above, both queries will return only one row,
and they will use the exact same execution plan.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

#6

On Wed, 17 Dec 2008 06:37:15 -0800, Dave wrote:

Quote:

This question pertains to the Query Plan for views.

Assume that I have a table of 10,000 widgets (widgets_table) with a primary
key on the identity column widget_id. Now, assume I have a view
(widgets_view) defined as:

select *
from widgets_table

The plan for the following query (against the view) puts 10,000 rows into
play:

select *
from widgets_view
where widgets_id = 5000

The plan for the following query (against the table) only puts 1 row into
play:

select *
from widgets_table
where widgets_id = 5000

How can I hit the view but get the efficiencies of a table? Are there
hints, compiler options, configuration options, etc... that can be used to
influence the query plan of the view?

Hi Dave,

I'm afraid that either I don't understand your question, or you don't
understand how views are processed internally.

In the example you give above, both queries will return only one row,
and they will use the exact same execution plan.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

#7

On Wed, 17 Dec 2008 06:37:15 -0800, Dave wrote:

Quote:

This question pertains to the Query Plan for views.

Assume that I have a table of 10,000 widgets (widgets_table) with a primary
key on the identity column widget_id. Now, assume I have a view
(widgets_view) defined as:

select *
from widgets_table

The plan for the following query (against the view) puts 10,000 rows into
play:

select *
from widgets_view
where widgets_id = 5000

The plan for the following query (against the table) only puts 1 row into
play:

select *
from widgets_table
where widgets_id = 5000

How can I hit the view but get the efficiencies of a table? Are there
hints, compiler options, configuration options, etc... that can be used to
influence the query plan of the view?

Hi Dave,

I'm afraid that either I don't understand your question, or you don't
understand how views are processed internally.

In the example you give above, both queries will return only one row,
and they will use the exact same execution plan.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

#8

On Wed, 17 Dec 2008 06:37:15 -0800, Dave wrote:

Quote:

This question pertains to the Query Plan for views.

Assume that I have a table of 10,000 widgets (widgets_table) with a primary
key on the identity column widget_id. Now, assume I have a view
(widgets_view) defined as:

select *
from widgets_table

The plan for the following query (against the view) puts 10,000 rows into
play:

select *
from widgets_view
where widgets_id = 5000

The plan for the following query (against the table) only puts 1 row into
play:

select *
from widgets_table
where widgets_id = 5000

How can I hit the view but get the efficiencies of a table? Are there
hints, compiler options, configuration options, etc... that can be used to
influence the query plan of the view?

Hi Dave,

I'm afraid that either I don't understand your question, or you don't
understand how views are processed internally.

In the example you give above, both queries will return only one row,
and they will use the exact same execution plan.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

#9

On Wed, 17 Dec 2008 06:37:15 -0800, Dave wrote:

Quote:

This question pertains to the Query Plan for views.

Assume that I have a table of 10,000 widgets (widgets_table) with a primary
key on the identity column widget_id. Now, assume I have a view
(widgets_view) defined as:

select *
from widgets_table

The plan for the following query (against the view) puts 10,000 rows into
play:

select *
from widgets_view
where widgets_id = 5000

The plan for the following query (against the table) only puts 1 row into
play:

select *
from widgets_table
where widgets_id = 5000

How can I hit the view but get the efficiencies of a table? Are there
hints, compiler options, configuration options, etc... that can be used to
influence the query plan of the view?

Hi Dave,

I'm afraid that either I don't understand your question, or you don't
understand how views are processed internally.

In the example you give above, both queries will return only one row,
and they will use the exact same execution plan.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

#10

On Wed, 17 Dec 2008 06:37:15 -0800, Dave wrote:

Quote:

This question pertains to the Query Plan for views.

Assume that I have a table of 10,000 widgets (widgets_table) with a primary
key on the identity column widget_id. Now, assume I have a view
(widgets_view) defined as:

select *
from widgets_table

The plan for the following query (against the view) puts 10,000 rows into
play:

select *
from widgets_view
where widgets_id = 5000

The plan for the following query (against the table) only puts 1 row into
play:

select *
from widgets_table
where widgets_id = 5000

How can I hit the view but get the efficiencies of a table? Are there
hints, compiler options, configuration options, etc... that can be used to
influence the query plan of the view?

Hi Dave,

I'm afraid that either I don't understand your question, or you don't
understand how views are processed internally.

In the example you give above, both queries will return only one row,
and they will use the exact same execution plan.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis