Views

#41

On Wed, 17 Dec 2008 16:11:00 -0800, Dave wrote:

Quote:

It's true that both queries will return the same results. But, unfortunatly,
SQL Server sometimes decides to materialize (not the right word?) the view
into memory before applying the where clause. So, all 10,000 rows are put
into play before applying the where clause. I'm trying to find a way to
prevent that.

Hi Dave,

You mean that you have found a situation where SQL Server reads all
rows, discarding all but the one you need, when accessing the view, but
goes directly to the correct row if you access the base table?

I've never seen this happpen. There are sometimes situations, with very
complicated queries, where suboptimal plans are used. Maybe you are
running into such a situation. If so, then please post the layout of all
tables involved (as CREATE TABLE statements, including constraints,
indexes, and properties), the view definitions (as CREATE VIEW
statements) and the query (or queries) where you observed this
behaviour, so that we can reproduce this and try to find a way to work
around the problem. There is no generic "one cure fix all" solution, so
we really need the specifics.

And if you reallly see this happen with views and tables as simple as
those you posted here, then you appear to have run into a bug. Try to
create a repro so that I (or others here) can verify if it is indeed or
bug, or an oversight on your part. And then, if it is indeed a bug, open
a case with Microsoft Support, or post a bug report on Connect.

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

#42

Yes, Hugo, your description is perfect (and much better than mine).

I'll produce a repo and post all of the details. Unfortunatly, this problem
occurs with even simple queries.

What is the best way to provide the test data?

It might take me a week-or-two because I'm out of the office. Thanks for
all your help!

"Hugo Kornelis" wrote:

Quote:

On Wed, 17 Dec 2008 16:11:00 -0800, Dave wrote:

It's true that both queries will return the same results. But, unfortunatly,
SQL Server sometimes decides to materialize (not the right word?) the view
into memory before applying the where clause. So, all 10,000 rows are put
into play before applying the where clause. I'm trying to find a way to
prevent that.

Hi Dave,

You mean that you have found a situation where SQL Server reads all
rows, discarding all but the one you need, when accessing the view, but
goes directly to the correct row if you access the base table?

I've never seen this happpen. There are sometimes situations, with very
complicated queries, where suboptimal plans are used. Maybe you are
running into such a situation. If so, then please post the layout of all
tables involved (as CREATE TABLE statements, including constraints,
indexes, and properties), the view definitions (as CREATE VIEW
statements) and the query (or queries) where you observed this
behaviour, so that we can reproduce this and try to find a way to work
around the problem. There is no generic "one cure fix all" solution, so
we really need the specifics.

And if you reallly see this happen with views and tables as simple as
those you posted here, then you appear to have run into a bug. Try to
create a repro so that I (or others here) can verify if it is indeed or
bug, or an oversight on your part. And then, if it is indeed a bug, open
a case with Microsoft Support, or post a bug report on Connect.

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

#43

Yes, Hugo, your description is perfect (and much better than mine).

I'll produce a repo and post all of the details. Unfortunatly, this problem
occurs with even simple queries.

What is the best way to provide the test data?

It might take me a week-or-two because I'm out of the office. Thanks for
all your help!

"Hugo Kornelis" wrote:

Quote:

On Wed, 17 Dec 2008 16:11:00 -0800, Dave wrote:

It's true that both queries will return the same results. But, unfortunatly,
SQL Server sometimes decides to materialize (not the right word?) the view
into memory before applying the where clause. So, all 10,000 rows are put
into play before applying the where clause. I'm trying to find a way to
prevent that.

Hi Dave,

You mean that you have found a situation where SQL Server reads all
rows, discarding all but the one you need, when accessing the view, but
goes directly to the correct row if you access the base table?

I've never seen this happpen. There are sometimes situations, with very
complicated queries, where suboptimal plans are used. Maybe you are
running into such a situation. If so, then please post the layout of all
tables involved (as CREATE TABLE statements, including constraints,
indexes, and properties), the view definitions (as CREATE VIEW
statements) and the query (or queries) where you observed this
behaviour, so that we can reproduce this and try to find a way to work
around the problem. There is no generic "one cure fix all" solution, so
we really need the specifics.

And if you reallly see this happen with views and tables as simple as
those you posted here, then you appear to have run into a bug. Try to
create a repro so that I (or others here) can verify if it is indeed or
bug, or an oversight on your part. And then, if it is indeed a bug, open
a case with Microsoft Support, or post a bug report on Connect.

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

#44

Yes, Hugo, your description is perfect (and much better than mine).

I'll produce a repo and post all of the details. Unfortunatly, this problem
occurs with even simple queries.

What is the best way to provide the test data?

It might take me a week-or-two because I'm out of the office. Thanks for
all your help!

"Hugo Kornelis" wrote:

Quote:

On Wed, 17 Dec 2008 16:11:00 -0800, Dave wrote:

It's true that both queries will return the same results. But, unfortunatly,
SQL Server sometimes decides to materialize (not the right word?) the view
into memory before applying the where clause. So, all 10,000 rows are put
into play before applying the where clause. I'm trying to find a way to
prevent that.

Hi Dave,

You mean that you have found a situation where SQL Server reads all
rows, discarding all but the one you need, when accessing the view, but
goes directly to the correct row if you access the base table?

I've never seen this happpen. There are sometimes situations, with very
complicated queries, where suboptimal plans are used. Maybe you are
running into such a situation. If so, then please post the layout of all
tables involved (as CREATE TABLE statements, including constraints,
indexes, and properties), the view definitions (as CREATE VIEW
statements) and the query (or queries) where you observed this
behaviour, so that we can reproduce this and try to find a way to work
around the problem. There is no generic "one cure fix all" solution, so
we really need the specifics.

And if you reallly see this happen with views and tables as simple as
those you posted here, then you appear to have run into a bug. Try to
create a repro so that I (or others here) can verify if it is indeed or
bug, or an oversight on your part. And then, if it is indeed a bug, open
a case with Microsoft Support, or post a bug report on Connect.

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

#45

Yes, Hugo, your description is perfect (and much better than mine).

I'll produce a repo and post all of the details. Unfortunatly, this problem
occurs with even simple queries.

What is the best way to provide the test data?

It might take me a week-or-two because I'm out of the office. Thanks for
all your help!

"Hugo Kornelis" wrote:

Quote:

On Wed, 17 Dec 2008 16:11:00 -0800, Dave wrote:

It's true that both queries will return the same results. But, unfortunatly,
SQL Server sometimes decides to materialize (not the right word?) the view
into memory before applying the where clause. So, all 10,000 rows are put
into play before applying the where clause. I'm trying to find a way to
prevent that.

Hi Dave,

You mean that you have found a situation where SQL Server reads all
rows, discarding all but the one you need, when accessing the view, but
goes directly to the correct row if you access the base table?

I've never seen this happpen. There are sometimes situations, with very
complicated queries, where suboptimal plans are used. Maybe you are
running into such a situation. If so, then please post the layout of all
tables involved (as CREATE TABLE statements, including constraints,
indexes, and properties), the view definitions (as CREATE VIEW
statements) and the query (or queries) where you observed this
behaviour, so that we can reproduce this and try to find a way to work
around the problem. There is no generic "one cure fix all" solution, so
we really need the specifics.

And if you reallly see this happen with views and tables as simple as
those you posted here, then you appear to have run into a bug. Try to
create a repro so that I (or others here) can verify if it is indeed or
bug, or an oversight on your part. And then, if it is indeed a bug, open
a case with Microsoft Support, or post a bug report on Connect.

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

#46

Yes, Hugo, your description is perfect (and much better than mine).

I'll produce a repo and post all of the details. Unfortunatly, this problem
occurs with even simple queries.

What is the best way to provide the test data?

It might take me a week-or-two because I'm out of the office. Thanks for
all your help!

"Hugo Kornelis" wrote:

Quote:

On Wed, 17 Dec 2008 16:11:00 -0800, Dave wrote:

It's true that both queries will return the same results. But, unfortunatly,
SQL Server sometimes decides to materialize (not the right word?) the view
into memory before applying the where clause. So, all 10,000 rows are put
into play before applying the where clause. I'm trying to find a way to
prevent that.

Hi Dave,

You mean that you have found a situation where SQL Server reads all
rows, discarding all but the one you need, when accessing the view, but
goes directly to the correct row if you access the base table?

I've never seen this happpen. There are sometimes situations, with very
complicated queries, where suboptimal plans are used. Maybe you are
running into such a situation. If so, then please post the layout of all
tables involved (as CREATE TABLE statements, including constraints,
indexes, and properties), the view definitions (as CREATE VIEW
statements) and the query (or queries) where you observed this
behaviour, so that we can reproduce this and try to find a way to work
around the problem. There is no generic "one cure fix all" solution, so
we really need the specifics.

And if you reallly see this happen with views and tables as simple as
those you posted here, then you appear to have run into a bug. Try to
create a repro so that I (or others here) can verify if it is indeed or
bug, or an oversight on your part. And then, if it is indeed a bug, open
a case with Microsoft Support, or post a bug report on Connect.

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

#47

Yes, Hugo, your description is perfect (and much better than mine).

I'll produce a repo and post all of the details. Unfortunatly, this problem
occurs with even simple queries.

What is the best way to provide the test data?

It might take me a week-or-two because I'm out of the office. Thanks for
all your help!

"Hugo Kornelis" wrote:

Quote:

On Wed, 17 Dec 2008 16:11:00 -0800, Dave wrote:

It's true that both queries will return the same results. But, unfortunatly,
SQL Server sometimes decides to materialize (not the right word?) the view
into memory before applying the where clause. So, all 10,000 rows are put
into play before applying the where clause. I'm trying to find a way to
prevent that.

Hi Dave,

You mean that you have found a situation where SQL Server reads all
rows, discarding all but the one you need, when accessing the view, but
goes directly to the correct row if you access the base table?

I've never seen this happpen. There are sometimes situations, with very
complicated queries, where suboptimal plans are used. Maybe you are
running into such a situation. If so, then please post the layout of all
tables involved (as CREATE TABLE statements, including constraints,
indexes, and properties), the view definitions (as CREATE VIEW
statements) and the query (or queries) where you observed this
behaviour, so that we can reproduce this and try to find a way to work
around the problem. There is no generic "one cure fix all" solution, so
we really need the specifics.

And if you reallly see this happen with views and tables as simple as
those you posted here, then you appear to have run into a bug. Try to
create a repro so that I (or others here) can verify if it is indeed or
bug, or an oversight on your part. And then, if it is indeed a bug, open
a case with Microsoft Support, or post a bug report on Connect.

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

#48

Yes, Hugo, your description is perfect (and much better than mine).

I'll produce a repo and post all of the details. Unfortunatly, this problem
occurs with even simple queries.

What is the best way to provide the test data?

It might take me a week-or-two because I'm out of the office. Thanks for
all your help!

"Hugo Kornelis" wrote:

Quote:

On Wed, 17 Dec 2008 16:11:00 -0800, Dave wrote:

It's true that both queries will return the same results. But, unfortunatly,
SQL Server sometimes decides to materialize (not the right word?) the view
into memory before applying the where clause. So, all 10,000 rows are put
into play before applying the where clause. I'm trying to find a way to
prevent that.

Hi Dave,

You mean that you have found a situation where SQL Server reads all
rows, discarding all but the one you need, when accessing the view, but
goes directly to the correct row if you access the base table?

I've never seen this happpen. There are sometimes situations, with very
complicated queries, where suboptimal plans are used. Maybe you are
running into such a situation. If so, then please post the layout of all
tables involved (as CREATE TABLE statements, including constraints,
indexes, and properties), the view definitions (as CREATE VIEW
statements) and the query (or queries) where you observed this
behaviour, so that we can reproduce this and try to find a way to work
around the problem. There is no generic "one cure fix all" solution, so
we really need the specifics.

And if you reallly see this happen with views and tables as simple as
those you posted here, then you appear to have run into a bug. Try to
create a repro so that I (or others here) can verify if it is indeed or
bug, or an oversight on your part. And then, if it is indeed a bug, open
a case with Microsoft Support, or post a bug report on Connect.

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

#49

Yes, Hugo, your description is perfect (and much better than mine).

I'll produce a repo and post all of the details. Unfortunatly, this problem
occurs with even simple queries.

What is the best way to provide the test data?

It might take me a week-or-two because I'm out of the office. Thanks for
all your help!

"Hugo Kornelis" wrote:

Quote:

On Wed, 17 Dec 2008 16:11:00 -0800, Dave wrote:

It's true that both queries will return the same results. But, unfortunatly,
SQL Server sometimes decides to materialize (not the right word?) the view
into memory before applying the where clause. So, all 10,000 rows are put
into play before applying the where clause. I'm trying to find a way to
prevent that.

Hi Dave,

You mean that you have found a situation where SQL Server reads all
rows, discarding all but the one you need, when accessing the view, but
goes directly to the correct row if you access the base table?

I've never seen this happpen. There are sometimes situations, with very
complicated queries, where suboptimal plans are used. Maybe you are
running into such a situation. If so, then please post the layout of all
tables involved (as CREATE TABLE statements, including constraints,
indexes, and properties), the view definitions (as CREATE VIEW
statements) and the query (or queries) where you observed this
behaviour, so that we can reproduce this and try to find a way to work
around the problem. There is no generic "one cure fix all" solution, so
we really need the specifics.

And if you reallly see this happen with views and tables as simple as
those you posted here, then you appear to have run into a bug. Try to
create a repro so that I (or others here) can verify if it is indeed or
bug, or an oversight on your part. And then, if it is indeed a bug, open
a case with Microsoft Support, or post a bug report on Connect.

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

#50

Yes, Hugo, your description is perfect (and much better than mine).

I'll produce a repo and post all of the details. Unfortunatly, this problem
occurs with even simple queries.

What is the best way to provide the test data?

It might take me a week-or-two because I'm out of the office. Thanks for
all your help!

"Hugo Kornelis" wrote:

Quote:

On Wed, 17 Dec 2008 16:11:00 -0800, Dave wrote:

It's true that both queries will return the same results. But, unfortunatly,
SQL Server sometimes decides to materialize (not the right word?) the view
into memory before applying the where clause. So, all 10,000 rows are put
into play before applying the where clause. I'm trying to find a way to
prevent that.

Hi Dave,

You mean that you have found a situation where SQL Server reads all
rows, discarding all but the one you need, when accessing the view, but
goes directly to the correct row if you access the base table?

I've never seen this happpen. There are sometimes situations, with very
complicated queries, where suboptimal plans are used. Maybe you are
running into such a situation. If so, then please post the layout of all
tables involved (as CREATE TABLE statements, including constraints,
indexes, and properties), the view definitions (as CREATE VIEW
statements) and the query (or queries) where you observed this
behaviour, so that we can reproduce this and try to find a way to work
around the problem. There is no generic "one cure fix all" solution, so
we really need the specifics.

And if you reallly see this happen with views and tables as simple as
those you posted here, then you appear to have run into a bug. Try to
create a repro so that I (or others here) can verify if it is indeed or
bug, or an oversight on your part. And then, if it is indeed a bug, open
a case with Microsoft Support, or post a bug report on Connect.

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