Joining Views & Query Performance

Over the years I've read and experienced where joining more then 5 tables can lead to performance problems. This number can vary based upon the amount of data in each table, if and how indexes are used and the complexity of the query, but 5 has always been a good rule of thumb. Unfortunately I do not know what rule to apply in regards to joing views.

A developer has experienced timeout problems periodically when opening a view in EM or when running the code which makes-up the view. I decided to look at the view and noticed it references tables and views, which reference more views, which in turn reference other views. In all the initial view references 5 tables and 8 views directly and indirectly, with some of the views containing function calls. What are your thoughts on how many views and tables are too many when it comes to joins and query performance.

Thanks, Davequerying a query that queries a query that queries yet another query. yeah that might make query processor a little pissy. Views use indexes and statistics. Have you looked at the query execution plan? Could just be a couple of bad scans.

I discourage nested views among my developers. They say "code reuse". I call them lazy.|||The execution plan looks like a map of Illinois listing the location of every McDonald's. In other words it's full of icons. As for the use of indexes, I don't believe these views meet the criteria, especially the one indicating "The view must not reference any other views, only base tables."

I suggested they rewrite the code to not use so many views.|||yeah the views may be doing a lot of extra junk you just do not need.|||...As for the use of indexes, I don't believe these views meet the criteria,...I don't think Thrasy was referring to "indexed views", but to the fact the views can make use of indexes on their source tables.|||Ok. Either way the execution plan was so large it wasn't worth the effort of debugging. Do you know of any documentation indicating to avoid using nested views? I would like to pass it along to our developers, assuming I can find anything.

Dave|||Hmmm...pity there isn't some sort of public bulletin board, or forum, monitored by experienced and knowledgeable experts to which you could direct them. :(

JOIN Query Problem

Hi, I'm not that experienced with this so I need some help :)
I've got 2 tables a Header and a Line table
Now I want to select all headers but I want to filter out those that
have lines with the Status Finished
So I came up with this query:
SELECT *
FROM Header
LEFT JOIN Line ON (Header.ID=Line.HeaderID AND
Line.Status<>'FINISHED')
WHERE Header.ID<>'';
The problem here is that I get all headers even the ones with the have
lines with the status Finished. Only those lines don't show, but the
orders do.
Can't I do this in 1 single query?
Thanks in advanceHi
SELECT *
FROM Header
JOIN Line ON (Header.ID=Line.HeaderID ) WHERE Line.Status<>'FINISHED' AND
Header.ID<>''
<kenny.vaes@.gmail.com> wrote in message
news:1133263151.681455.181630@.z14g2000cwz.googlegroups.com...
> Hi, I'm not that experienced with this so I need some help :)
> I've got 2 tables a Header and a Line table
> Now I want to select all headers but I want to filter out those that
> have lines with the Status Finished
> So I came up with this query:
> SELECT *
> FROM Header
> LEFT JOIN Line ON (Header.ID=Line.HeaderID AND
> Line.Status<>'FINISHED')
> WHERE Header.ID<>'';
> The problem here is that I get all headers even the ones with the have
> lines with the status Finished. Only those lines don't show, but the
> orders do.
> Can't I do this in 1 single query?
> Thanks in advance
>|||And this here don=B4t work for you ?
SELECT *
FROM Header
LEFT JOIN Line ON (Header.ID=3DLine.HeaderID
WHERE Header.ID<>'' AND Line.Status<>'FINISHED'=20
HTH, jens Suessmeyer.|||Without that parentethes which priduces an error:
SELECT *
FROM Header
LEFT JOIN Line ON Header.ID=Line.HeaderID
WHERE Header.ID<>'' AND Line.Status<>'FINISHED'|||Jens
Yep, that's exactly what I've posted , in that case he does not need to
have LEFT JOIN . It produces the same result
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1133264002.077565.231590@.z14g2000cwz.googlegroups.com...
> Without that parentethes which priduces an error:
> SELECT *
> FROM Header
> LEFT JOIN Line ON Header.ID=Line.HeaderID
> WHERE Header.ID<>'' AND Line.Status<>'FINISHED'
>|||When I use this syntax I do not see the Headers that do not have a
corresponding line.
When i do not use the filter AND Line.Status<>'FINISHED' I see all
Headers, even the ones that have no lines. But when I now filter on the
status. All the ones With status = finished dissappear but also the
empty ones.
And i really need to see the empty ones also :(|||Or is there a way to select only the headers that have no Lines? That
way I can put all the ID's in a collection and do a select at the end
of al those ID's.
So it would be a select of all the ones that have not been finished and
afterwards one woth all the empty ones.|||kenny.vaes@.gmail.com wrote on 29 Nov 2005 04:59:49 -0800:

> When I use this syntax I do not see the Headers that do not have a
> corresponding line.
> When i do not use the filter AND Line.Status<>'FINISHED' I see all
> Headers, even the ones that have no lines. But when I now filter on the
> status. All the ones With status = finished dissappear but also the
> empty ones.
> And i really need to see the empty ones also :(
SELECT *
FROM Header
LEFT JOIN Line ON Header.ID=Line.HeaderID
WHERE Header.ID<>'' AND (Line.Status<>'FINISHED' or Line.Status IS NULL)
Dan