Re: Views
- From: Dave <daveII@xxxxxxxxxxxxx>
- Date: Fri, 19 Dec 2008 05:58:10 -0800
Thanks for the help Mark. A couple of points:
a. I'm not using indexed views. The base tables have the proper indexes.
b. I can tell 10K rows are read and then all but the single row I need are
discarded by examining the query plan. Circumstantial evidence is that the
View takes much longer to run then when hitting the base tables.
c. I found the http://msdn.microsoft.com/en-us/library/ms190237.aspx
article helpful but it does not seem to be entirely accurate in describing my
experience.
I'll produce a repo and post all of the details in the next week-or-two.
P.S. We are running SQL 2005.
"Mark Han[MSFT]" wrote:
Hi DaveII,.
Thank you for using the Microsoft MSDN Managed Newsgroup. My name is Mark
Han. I am delighted to work with you on this issue.
in order to address your concerns, i would like to explain the following.
1The rows of an indexed view are stored in the database in the same format
as a table. If the query optimizer decides to use an indexed view in a
query plan, the indexed view is treated the same way as a base table.
2 Only the definition of a nonindexed view is stored, not the rows of the
view. The query optimizer incorporates the logic from the view definition
into the execution plan it builds for the SQL statement that references the
nonindexed view.
Based on the above the execution plan of the view query should be as same
as the execution plan of the table query.
To better assist you with the issue, please help to confirm the following
1 what you meaning of the following. For example
The plan for the following query (against the view) puts 10,000 rows into
play:
select *
from widgets_view
where widgets_id = 5000
2 what plan the above qurey will puts 10,000 into?
3 How do you find that the above qurey will puts 10,000 into rows into play?
Besides, there are some articles to share with you:
View Resolution:http://msdn.microsoft.com/en-us/library/ms190237.aspx
Execution Plan Caching and
Reuse:http://msdn.microsoft.com/en-us/library/ms181055.aspx
If there are any additional questions or concerns, please let me know.
Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@xxxxxxxxxxxxxx
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
- References:
- Prev by Date: Re: Views
- Next by Date: Re: Views
- Previous by thread: Re: Views
- Next by thread: Re: Views
- Index(es):
Relevant Pages
|
