server view performance worse than not using view

Consider pseudo-DDL created on an "archive" machine, that wants to get data
from the "live" machine.
CREATE VIEW AllTransactions AS
SELECT * FROM Tranasction_Archived
UNION ALL
SELECT * FROM LinkedServerToLive.Northwind.dbo.Transactions
Now ideally, you can query the Transactions view on the archive machine:
SELECT *
FROM AllTransactions t
INNER JOIN Customers c
ON t.cid = c.cid
and get presented a unified result set. But it turns out this performance is
horrible.
But, if you simply change your query to:
SELECT *
FROM Transactions_Archived t
INNER JOIN Customers c
ON t.cid = c.cid
UNION ALL
SELECT *
FROM LinkedServerToLive.Northwind.dbo.Transactions t
INNER JOIN Customers c
ON t.cid = c.cid
You will get a phenominal performance boost. All i did was break out what
the view contained,
and did the UNION ALL at the query level rather than abstract the complexity
into a view.
i am using SQL2000, is this a known optimizer failing that is fixed in
SQL2005?What are the differences in the execution plans? Are indexes being used to
their full advantage on the joins for both queries?
"Ian Boyd" wrote:

> Consider pseudo-DDL created on an "archive" machine, that wants to get dat
a
> from the "live" machine.
> CREATE VIEW AllTransactions AS
> SELECT * FROM Tranasction_Archived
> UNION ALL
> SELECT * FROM LinkedServerToLive.Northwind.dbo.Transactions
> Now ideally, you can query the Transactions view on the archive machine:
> SELECT *
> FROM AllTransactions t
> INNER JOIN Customers c
> ON t.cid = c.cid
> and get presented a unified result set. But it turns out this performance
is
> horrible.
> But, if you simply change your query to:
> SELECT *
> FROM Transactions_Archived t
> INNER JOIN Customers c
> ON t.cid = c.cid
> UNION ALL
> SELECT *
> FROM LinkedServerToLive.Northwind.dbo.Transactions t
> INNER JOIN Customers c
> ON t.cid = c.cid
> You will get a phenominal performance boost. All i did was break out what
> the view contained,
> and did the UNION ALL at the query level rather than abstract the complexi
ty
> into a view.
> i am using SQL2000, is this a known optimizer failing that is fixed in
> SQL2005?
>
>|||My understanding, from a logical perspective, is this (see disclaimer
below)...
Regardless of your DBMS product, if you are using Oracle DB links or Linked
Servers in SQL Server, or some other artificial method to make DatabaseA
access a table in DatabaseB as if the tables were in the same database, then
the standard tuning mechanisms can no longer be used.
Basically, DatabaseA knows nothign about the tables in DatabaseB, and the
best it can do is link one column in the source database to the result set
in the remote database. IF the remote database has a view, the view cannot
be broken down efficiently into its parts, as it would if the databases were
the same. The remote database ends up processing the entire view in order
to return one row (although it may use an index based on criteria passed).
If the view were in the same database, then the tuning engine would
essentially rewrite the SQL and process the individual joins more
efficiently.
Think of it like going to the library and getting 5 books that you want, as
compared to submitting 5 requests for someone else to get the book, all of
which get handled seperately.
As usual, I am speaking regarding my own limited understanding of the SQL
Server engine. If anyone knows differently, or can explain it better,
please do.
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%23wziZJfIGHA.532@.TK2MSFTNGP15.phx.gbl...
> Consider pseudo-DDL created on an "archive" machine, that wants to get
data
> from the "live" machine.
> CREATE VIEW AllTransactions AS
> SELECT * FROM Tranasction_Archived
> UNION ALL
> SELECT * FROM LinkedServerToLive.Northwind.dbo.Transactions
> Now ideally, you can query the Transactions view on the archive machine:
> SELECT *
> FROM AllTransactions t
> INNER JOIN Customers c
> ON t.cid = c.cid
> and get presented a unified result set. But it turns out this performance
is
> horrible.
> But, if you simply change your query to:
> SELECT *
> FROM Transactions_Archived t
> INNER JOIN Customers c
> ON t.cid = c.cid
> UNION ALL
> SELECT *
> FROM LinkedServerToLive.Northwind.dbo.Transactions t
> INNER JOIN Customers c
> ON t.cid = c.cid
> You will get a phenominal performance boost. All i did was break out what
> the view contained,
> and did the UNION ALL at the query level rather than abstract the
complexity
> into a view.
> i am using SQL2000, is this a known optimizer failing that is fixed in
> SQL2005?
>|||"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:B88552FB-15AD-443B-ACC9-8A8269741E50@.microsoft.com...
> What are the differences in the execution plans? Are indexes being used to
> their full advantage on the joins for both queries?
It looks as though in the slow case, SQL Server is performing tens of
thounds of executes on a single-row returning query, verses bringing back
tens of thousands of rows.
i would have posted the showplan_text outputs, but it did not show the
number of executes or rows returned through each step.