Hello all,

Please bear with the long explanation of my scenario.

As I'm relatively new to the query world, I like to write my queries
using the visual toos such as the "View" option in SQL Server or in MS
Access. If I have a complicated query with sub-queries, I create a
query (view1) as (for example):

select ID,count(ID) as NumberOfUsers from tblContact
where Type > 3
GROUP BY ID

then create another query (view2) which uses the first view in another
select statement:

select count(NumberOfUsers) from view1 where NumberOfUsers > 1

What I've noted above is a very simple example but, you get the idea (I
hope).

My question, however, is regarding a very complex query with
cascading/nested views.

Scenario:

View1 is joining 5 tables

View2 is using a join between View1 and 3 more tables

View3 is using a join between View2 and 5 more tables

View4 is using a join between view3 and 2 more tables

When I run VIEW3, it executes within 10 seconds. When I run VIEW4, it
takes 4 hours! What I did to get around this problem was this:

1. I renamed View3 to something else - like View3_Test
2. I then exported the new View3_Test into a table called View3

After this modification, when I run View4, it executes in 15 seconds!

It seems to me that SQL Server is bundling the joins used in
View4...all as 1 view.

Is there any way, I can make the execution plan in such a way that it
executes (the original) view3 FIRST and then proceeds to execute the
rest of the joins in View4?

This would be extremely helpful for me because in the interim (sp?), I
will have to schedule a DTS package to export the View3_Test to the
table called View3 first...upon completion, run the rest of the report.
That's just cheating. I'd like to be able to provide a better
solution to my employer.

Any help will be much appreciated.

Thank you in advance for your expert advice,

-Umar Farooq.

You can't really control how MSSQL processes the views, because the
query optimizer never 'sees' them - when you query a view, its name is
replaced by its definition, and the optimizer then finds a plan for the
expanded query. Unfortunately, it seems that in your case it isn't
finding a very good plan.

You can use Query Analyzer to check the query plan, and see which joins
or other operations are the 'heavy' ones. That may give you a clue as
to how to improve your queries.

Alternatively, if your views are used for reporting, and if the data in
the tables doesn't change very much, you could consider using indexed
views. These are more like tables, and they do store the data in the
view, so the optimizer doesn't need to go to the base tables. But this
will make changes to data much slower, so it's probably most useful in
a reporting scenario.

Simon

Hello Simon,

Thanks for your reply.

I guess my next question would be "How do I set up an indexed view?" I
look in the Enterprise manager and when I right-click on the view,
under "All Tasks" it gives me "Manage Triggers" and "Permissions" but
"Manage Indexes" is grayed out.

I downloaded the Production.BAK file to the QA and restored it and ran
these views on it. Another point to note is that on the QA server, the
QA implementation of the same database, this query runs just
fine...max, 35 seconds. But on the Production database, it chokes.

I tried doing a DBCC "soft reindexing" on all the tables and then took
a SQL trace and ran the Query Optimizer on the Production database on
the QA server. Still no luck.

I'd like to learn more about the "Indexed Views" as that sounds like a
viable solution. You said the data will be stored in the view? I'm
not sure I understand how that works. It's a bit of an "overload" for
my Microsoft brain.

While on that subject, would anyone happen to know if I can set up
defaults in views? I know how to set it up on the table but I have a
scenario where I'd like for different people to see a table through a
view and on their "UserID" column, for example, I'd like to default
their UserID value on the view.

Thank you again for your help,

-Umar Farooq.

"Umar Farooq" <UmarAlFarooq (AT) gmail (DOT) com> wrote

I don't use EM much myself, so I don't know if it's possible to create an
indexed view from there. However, not all views are indexable, and you have
to make sure all the conditions are met, so it may simply be that your view
is not indexable without some changes - see "Creating an Indexed View" in
Books Online for a list of requirements.

I'm not really sure I follow this - you mean you copied your DB from
Production to QA, and the same query runs in 35 seconds in QA but 4 hours in
Production? If so, there should be something fairly obviously different in
the query plans to suggest what's going on.

See "Designing an Indexed View" in Books Online. Basically, you first create
a clustered index on the view (you can create additional nonclustered
indexes after that); since a clustered index always contains data in its
leaf level, the new index now has the view data in it. You can check Books
Online for more details.

An important point to remember with indexed views is that you can create
them in any edition of MSSQL, but only Enterprise Edition will automatically
use them - other editions will continue to expand the view and ignore the
indexes. To force them to use the indexes, you need to use the WITH
(NOEXPAND) hint in your queries - see the sections on hints under "FROM" in
BOL.

I don't know what you mean by a default, but if you want to show different
users different data based on their login, then something like this may
work:

create view dbo.MyCustomers
as
select
CustID,
CustName,
...
from
dbo.Customers
where
CustomerAccountRep = system_user -- see BOL

You're welcome.

Simon

/*


I'm not really sure I follow this - you mean you copied your DB from
Production to QA, and the same query runs in 35 seconds in QA but 4
hours in
Production? If so, there should be something fairly obviously different
in
the query plans to suggest what's going on.
*/

I mean that I copied the DB from Production to QA and ran the query and
it took just as long as it did on production. However, the same tables
and structures and most of the data is in the QA DB on the QA server.
On that DB, the query runs super fast. Since the QA DB and the
Production DB are identical in structure, I thought re-indexing the
Production DB will make it run faster. No such luck. Not only that,
once I have the query running in 20 seconds on the QA DB, even if I
make a slight modification to the query (like adding some additional
fields, etc.) the query starts to bog down again.

I wish there was a way by which I could tell the query to treat the
steps I want as a self-contained query and not to combine the execution
plan with the other queries in the view.

By default, I mean for inserts. On a table, if I wanted to enter a
default date, for example, I'd just do a "getdate()" function on the
default property of the date field and it will insert a date in the
field by default. I'd like a view to to insert a default value in a
column just like the table default. For example:

tblSuggestion
ID, SuggestorID, Category, Suggestion, SuggestionDetails,
DateSubmitted, DateModified

Let's say there are 3 Suggestors in tblSuggestor: James, John and Mike

I want to create 3 views:

View1
ID, 1, Category, Suggestion, SuggestionDetails, DateSubmitted,
DateModified

View2
ID, 2, Category, Suggestion, SuggestionDetails, DateSubmitted,
DateModified

View3
ID, 3, Category, Suggestion, SuggestionDetails, DateSubmitted,
DateModified

I give James View1 which will automatically enter a 1 in the
SuggestorID field so he doesn't have to do it every time. All he has
to enter will be Category, Suggestion and SuggestionDetails.
DateSubmitted is a table default of "getdate()" and any changes to the
record will fire up a trigger to update DateModified with "getdate()"
value.

The problem is that the scenario I've listed above is only for Select
views and not for update views. I know I can accomplish this task with
a stored procedure which will accept values and hard-code the
information into the table but I wanted to do that via a view if
possible.

Thanks,

-Ashwin Sharma.

Although it is generally not recommended, you could try to add the query
hint "OPTION (FORCE ORDER)", and see how that works for you.

HTH,
Gert-Jan


Umar Farooq wrote:

<snip>

Gert-Jan's FORCE ORDER suggestion is probably the closest thing there is
without using indexed views - generally you don't want to impose a certain
plan on the optimizer, but sometimes it just gets it wrong. You might also
want to make sure you have the latest servicepack installed, as they may
include changes to the optimizer.

<snip>

A stored procedure is usually a better way to modify data than a view, since
it gives you more flexibility in terms of validating input, performing other
actions, cascading changes to other tables, applying procedural logic and so
on.

In your example, I don't really see why you need a view - you could just
SELECT/INSERT on the table directly. Unless tblSuggestion is in fact a view
and not a table? One view per user is not a very scalable solution anyway,
and maintenance would be an issue.

If you can replace SuggestorID with a user's login name, then you can simply
make SYSTEM_USER the default for that column, as you've already done with
GETDATE() elsewhere. If that isn't possible, then I would use a stored
procedure; if you absolutely have to use a view for some reason, then you
could create an INSTEAD OF trigger on the view. That assumes you have
SQL2000 - I don't think you've mentioned which version you have.

If this isn't helpful, I suggest you post CREATE TABLE and INSERT statements
to show exactly what you're trying to achieve.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon