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. :(

Joining more than one table

Hai,
I need the query for joining more than one table in views without
using where condition in sqlserver.Can anyone help me?
Looking frward for ur reply...
Thanx in advancePrince
SELECT <column lists> FROM Table1 JOIN Table2
ON Table1.pk=Table2.pk JOIN Table3 ON
Table1.pk=Table3.pk
But ,why don't you need a WHERE condition?
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1123762724.429184.8090@.g49g2000cwa.googlegroups.com...
> Hai,
> I need the query for joining more than one table in views without
> using where condition in sqlserver.Can anyone help me?
> Looking frward for ur reply...
> Thanx in advance
>|||Well, without more specific information,
SELECT t1.*, t2.*, t3.*
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.key = t2.key
INNER JOIN Table3 t3
ON t2.key = t3.key
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1123762724.429184.8090@.g49g2000cwa.googlegroups.com...
> Hai,
> I need the query for joining more than one table in views without
> using where condition in sqlserver.Can anyone help me?
> Looking frward for ur reply...
> Thanx in advance
>|||hai Uri Dimant,
i dont much about sqlserver...
i need a query without that ON condition bcoz my cloumns of first
table is not present in columns of second table and vice versa... can i
use union if yes please tell me how?
Thanx..|||hai Aaron Bertrand,
i dont much about sqlserver...
i need a query without that ON condition bcoz my cloumns of first
table is not present in columns of second table and vice versa... can i
use union if yes please tell me how?
Thanx..|||Prince
I'm not sure what you are doing ,so try this
SELECT col1,col2 FROM Table1
UNION ALL
SELECT col3,col4 FROM Table2
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1123764221.132592.18440@.g43g2000cwa.googlegroups.com...
> hai Uri Dimant,
> i dont much about sqlserver...
> i need a query without that ON condition bcoz my cloumns of first
> table is not present in columns of second table and vice versa... can i
> use union if yes please tell me how?
> Thanx..
>|||hai Uri Dimant ,
what u gave works fine...
but in this case it is showing error
SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2
Thanx|||> i need a query without that ON condition bcoz my cloumns of first
> table is not present in columns of second table and vice versa...
Then how do you expect to join them? Maybe you could start by showing us
your table structure, some sample data, and desired results. Please check
out http://www.aspfaq.com/5006 for some help on the most effective way to do
this...|||> but in this case it is showing error
> SELECT * FROM Table1
> UNION ALL
> SELECT * FROM Table2
What is the error? What does Table1 look like? What does Table2 look like?
If these tables are unrelated and have different column structures, why on
earth do you need to union them? Can't your application deal with more than
one resultset?|||Prince
Perhapa a number of columns in Table1 is not the same as in Table2. It is
always a good practice not to use SELECT * statement ,particular in
production.
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1123765429.665606.217850@.g47g2000cwa.googlegroups.com...
> hai Uri Dimant ,
> what u gave works fine...
> but in this case it is showing error
> SELECT * FROM Table1
> UNION ALL
> SELECT * FROM Table2
> Thanx
>

Join type based on condition?

Given two views, I need to join them based on the number of rows
returned as follows:
ViewA 1+ rows, ViewB 0 rows: ViewA LEFT JOIN ViewB
ViewA 0 rows, ViewB 1+ rows: ViewA RIGHT JOIN ViewB
ViewA 1+ rows, ViewB 1+ rows: ViewA INNER JOIN ViewB
Easy enough to implement in a stored proc with a conditional statement,
but I am looking for syntax that will produce the joins in a view (for
reporting processes that do not support SP execution).Can you give some DDL, sample data, and desired results?
Sounds like a FULL OUTER JOIN to me, we just have to understand what data
you want presented in each condition.
See http://www.aspfaq.com/5006 for help on asking more effective questions.
"Matt" <bsg075@.gmail.com> wrote in message
news:1123614023.861327.180310@.g14g2000cwa.googlegroups.com...
> Given two views, I need to join them based on the number of rows
> returned as follows:
> ViewA 1+ rows, ViewB 0 rows: ViewA LEFT JOIN ViewB
> ViewA 0 rows, ViewB 1+ rows: ViewA RIGHT JOIN ViewB
> ViewA 1+ rows, ViewB 1+ rows: ViewA INNER JOIN ViewB
> Easy enough to implement in a stored proc with a conditional statement,
> but I am looking for syntax that will produce the joins in a view (for
> reporting processes that do not support SP execution).
>|||A Full Outer Join would work when one of the two views returns no
records, but an Inner Join is needed when they both return rows.
Using tables in place of views for an example:
CREATE TABLE tblA (uid INT, orgid INT)
CREATE TABLE tblB (uid INT, orgid INT)
I need to implement the following in a view:
IF SELECT(COUNT(*) FROM tblA)=0 OR SELECT(COUNT(*) FROM tblB)=0
SELECT orgid FROM tblA FULL JOIN tblB on tblA.uid = tblB.uid
ELSE
SELECT orgid FROM tblA INNER JOIN tblB on tblA.uid = tblB.uid|||just a stab in the dark:
--ViewA 1+ rows, ViewB 0 rows: ViewA LEFT JOIN ViewB
select * from <ViewA LEFT JOIN ViewB >
where
--ViewA 1+ rows
exists(select 1 from viewA)
-- ViewB 0 rows:
and not exists(select 1 from viewB)
union all
--ViewA 0 rows, ViewB 1+ rows: ViewA RIGHT JOIN ViewB
select * from <ViewA RIGHT JOIN ViewB >
where
not exists(select 1 from viewA)
and exists(select 1 from viewB)
union all
--ViewA 1+ rows, ViewB 1+ rows: ViewA INNER JOIN ViewB
select * from <ViewA INNER JOIN ViewB >
where
exists(select 1 from viewA)
and exists(select 1 from viewB)|||Try this one:
SELECT tblA.uid, tblA.orgid, tblB.uid, tblB.orgid
FROM tblA
JOIN tblB
ON tblA.uid = tblB.uid
UNION ALL
SELECT tblA.uid, tblA.orgid, tblB.uid, tblB.orgid
FROM tblA
FULL JOIN tblB
ON 1=1
WHERE NOT EXISTS
(SELECT *
FROM tblA)
OR NOT EXISTS
(SELECT *
FROM tblB) ;
David Portas
SQL Server MVP
--|||That will work. Thanks!|||Here's a variation on David's solution that avoids UNION. I
don't know that it will run as fast, though:
select *
from T
full outer join U
on 1=1
where (T.i = U.i)
or not exists (
select * from T
) or not exists (
select * from U
)
Steve Kass
Drew University
"Matt" <bsg075@.gmail.com> wrote in message
news:1123617379.421948.237990@.g47g2000cwa.googlegroups.com...
> That will work. Thanks!
>

Join two views using server aliases

Hello everybody,
I'm working for a hospital and for this job, I created two views using the
server aliases (linked servers).
Both these views are working correctly.
The design of the first view is:
SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
SPECIALTY_DESCRIPTION AS NAME
FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
ORDER BY SPECIALTY_DESCRIPTION
And for the second view is:
SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE AS
SPECIALTY_CODE
FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
ORDER BY NAME
The problem appaers when I try to join both these views with, for example,
this query:
select c.ID
from VW_CONSULTANTS c
left outer join VW_SPECIALTIES s
on c.SPECIALTY_CODE=s.CODE
I have these error messages:
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ORDER'.
Server: Msg 1033, Level 15, State 1, Line 1
The ORDER BY clause is invalid in views, inline functions, derived tables,
and subqueries, unless TOP is also specified.
For me, the problem is because we try to join two views using server aliases
and the sql server is not successfull in creating the temporary table to put
the results of my select...
But I don't know what I have to do to fix the problem.
Can you help me please?
StF"StF" <StF@.discussions.microsoft.com> wrote in message
news:760671EB-8EC8-4464-8972-D570E0FB2F86@.microsoft.com...
> Hello everybody,
> I'm working for a hospital and for this job, I created two views using the
> server aliases (linked servers).
> Both these views are working correctly.
> The design of the first view is:
> SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
> SPECIALTY_DESCRIPTION AS NAME
> FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
> ORDER BY SPECIALTY_DESCRIPTION
> And for the second view is:
> SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE
> AS
> SPECIALTY_CODE
> FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
> WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
> ORDER BY NAME
> The problem appaers when I try to join both these views with, for example,
> this query:
> select c.ID
> from VW_CONSULTANTS c
> left outer join VW_SPECIALTIES s
> on c.SPECIALTY_CODE=s.CODE
>
I don't know exactly what's happening, but I know how to fix it.
Don't use TOP 100 PERCENT in a view. It serves absolutely no purpose.
In SQL 2000 this would cause queries against the view to be sorted. But
this behavior was never guaranteed, and it doesn't always happen in 2005.
David|||Thank you very much.
In fact, we use SQL Server 2000 and that's why I used 'TOP 100 PERCENT'.
So, I removed this thing and the 'ORDER BY' and now my select is working.
Thank you again.
StF
"David Browne" wrote:

> "StF" <StF@.discussions.microsoft.com> wrote in message
> news:760671EB-8EC8-4464-8972-D570E0FB2F86@.microsoft.com...
> I don't know exactly what's happening, but I know how to fix it.
> Don't use TOP 100 PERCENT in a view. It serves absolutely no purpose.
> In SQL 2000 this would cause queries against the view to be sorted. But
> this behavior was never guaranteed, and it doesn't always happen in 2005.
> David
>
>

Join two views using server aliases

Hello everybody,
I'm working for a hospital and for this job, I created two views using the
server aliases (linked servers).
Both these views are working correctly.
The design of the first view is:
SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
SPECIALTY_DESCRIPTION AS NAME
FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
ORDER BY SPECIALTY_DESCRIPTION
And for the second view is:
SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE AS
SPECIALTY_CODE
FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
ORDER BY NAME
The problem appaers when I try to join both these views with, for example,
this query:
select c.ID
from VW_CONSULTANTS c
left outer join VW_SPECIALTIES s
on c.SPECIALTY_CODE=s.CODE
I have these error messages:
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ORDER'.
Server: Msg 1033, Level 15, State 1, Line 1
The ORDER BY clause is invalid in views, inline functions, derived tables,
and subqueries, unless TOP is also specified.
For me, the problem is because we try to join two views using server aliases
and the sql server is not successfull in creating the temporary table to put
the results of my select...
But I don't know what I have to do to fix the problem.
Can you help me please?
StF"StF" <StF@.discussions.microsoft.com> wrote in message
news:760671EB-8EC8-4464-8972-D570E0FB2F86@.microsoft.com...
> Hello everybody,
> I'm working for a hospital and for this job, I created two views using the
> server aliases (linked servers).
> Both these views are working correctly.
> The design of the first view is:
> SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
> SPECIALTY_DESCRIPTION AS NAME
> FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
> ORDER BY SPECIALTY_DESCRIPTION
> And for the second view is:
> SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE
> AS
> SPECIALTY_CODE
> FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
> WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
> ORDER BY NAME
> The problem appaers when I try to join both these views with, for example,
> this query:
> select c.ID
> from VW_CONSULTANTS c
> left outer join VW_SPECIALTIES s
> on c.SPECIALTY_CODE=s.CODE
>
I don't know exactly what's happening, but I know how to fix it.
Don't use TOP 100 PERCENT in a view. It serves absolutely no purpose.
In SQL 2000 this would cause queries against the view to be sorted. But
this behavior was never guaranteed, and it doesn't always happen in 2005.
David|||Thank you very much.
In fact, we use SQL Server 2000 and that's why I used 'TOP 100 PERCENT'.
So, I removed this thing and the 'ORDER BY' and now my select is working.
Thank you again.
StF
"David Browne" wrote:
> "StF" <StF@.discussions.microsoft.com> wrote in message
> news:760671EB-8EC8-4464-8972-D570E0FB2F86@.microsoft.com...
> > Hello everybody,
> >
> > I'm working for a hospital and for this job, I created two views using the
> > server aliases (linked servers).
> > Both these views are working correctly.
> >
> > The design of the first view is:
> > SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
> > SPECIALTY_DESCRIPTION AS NAME
> > FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
> > ORDER BY SPECIALTY_DESCRIPTION
> >
> > And for the second view is:
> > SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE
> > AS
> > SPECIALTY_CODE
> > FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
> > WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
> > ORDER BY NAME
> >
> > The problem appaers when I try to join both these views with, for example,
> > this query:
> > select c.ID
> > from VW_CONSULTANTS c
> > left outer join VW_SPECIALTIES s
> > on c.SPECIALTY_CODE=s.CODE
> >
> I don't know exactly what's happening, but I know how to fix it.
> Don't use TOP 100 PERCENT in a view. It serves absolutely no purpose.
> In SQL 2000 this would cause queries against the view to be sorted. But
> this behavior was never guaranteed, and it doesn't always happen in 2005.
> David
>
>