Joins and inices

I can't understand this behavior:
I'm doing a join on a large table, and I created a
clustered index on the column in the join condition.
I would expect that this should enhance performance,
because of the merge join advantage.
It didn't perform well.
I then tried making that index non-clustered, and doing
a clustered index on an identity column (as a primary
key).
It's weird, but the join performs far better this way.
The only way I can explain this is that the non-clustered
index keeps the values of the join column in fewer pages,
and therefore closer together on disk (fewer reads).
Then again, there must still be the additional seek to
get to the rest of the "data" that row contains.
Even stranger, if the clustered index on the identity col-
umn (PK) is not there, the join doesn't perform well also.
If anyone has any experience with this, or suggestions,
I'd be really grateful for some help.
Thanks in advance,
Andrew.It would be easier to offer ideas if you posted DDL and ideally insert
statements to load some sample data...
this doesn't answer all of your questions but as an FYI...
<<
Even stranger, if the clustered index on the identity col-
umn (PK) is not there, the join doesn't perform well also.
the NC index actually keeps the clustering key as part of it's own key
information at the leaf level of the NC index. So... you're actually
changing the contents of the NC index if you get rid of the clustering key.
Also, NC index is stored entirely differently if there is no clustered index
at all...
--
Brian
"Andrew" <a@.b.com> wrote in message
news:01a401c38dc5$d91d84d0$a401280a@.phx.gbl...
> I can't understand this behavior:
> I'm doing a join on a large table, and I created a
> clustered index on the column in the join condition.
> I would expect that this should enhance performance,
> because of the merge join advantage.
> It didn't perform well.
> I then tried making that index non-clustered, and doing
> a clustered index on an identity column (as a primary
> key).
> It's weird, but the join performs far better this way.
> The only way I can explain this is that the non-clustered
> index keeps the values of the join column in fewer pages,
> and therefore closer together on disk (fewer reads).
> Then again, there must still be the additional seek to
> get to the rest of the "data" that row contains.
> Even stranger, if the clustered index on the identity col-
> umn (PK) is not there, the join doesn't perform well also.
> If anyone has any experience with this, or suggestions,
> I'd be really grateful for some help.
> Thanks in advance,
> Andrew.|||Still...
><<
>Even stranger, if the clustered index on the identity
col-
>umn (PK) is not there, the join doesn't perform well
also.
>the NC index actually keeps the clustering key as part
of it's own key
>information at the leaf level of the NC index. So...
you're actually
>changing the contents of the NC index if you get rid of
the clustering key.
>Also, NC index is stored entirely differently if there
is no clustered index
>at all...
>--
I realize the NC index keeps the clustering key as part
of it's own key, but I had understood that SQL server
keeps a RID (row identifier) internally when there is no
clustered index... so rather thank keeping the clustered
index in the leaf, the RID is kept.
Now, if accessing that RID is slow, I can understand, but
I'd imagined it as a kind of internal indexed identity
of its own. I guess this doesn't make sense, though.
As a follow up, though, is there no use for a NC index
without a clustered index when you want to access columns
outside of the NC indexed column?
Also, can a column be both a clustered and a non-clustered
index (would this help on join performance to access those
other columns?).
Thanks again,
Andrew
>Brian
>
>"Andrew" <a@.b.com> wrote in message
>news:01a401c38dc5$d91d84d0$a401280a@.phx.gbl...
>> I can't understand this behavior:
>> I'm doing a join on a large table, and I created a
>> clustered index on the column in the join condition.
>> I would expect that this should enhance performance,
>> because of the merge join advantage.
>> It didn't perform well.
>> I then tried making that index non-clustered, and doing
>> a clustered index on an identity column (as a primary
>> key).
>> It's weird, but the join performs far better this way.
>> The only way I can explain this is that the non-
clustered
>> index keeps the values of the join column in fewer
pages,
>> and therefore closer together on disk (fewer reads).
>> Then again, there must still be the additional seek to
>> get to the rest of the "data" that row contains.
>> Even stranger, if the clustered index on the identity
col-
>> umn (PK) is not there, the join doesn't perform well
also.
>> If anyone has any experience with this, or suggestions,
>> I'd be really grateful for some help.
>> Thanks in advance,
>> Andrew.
>
>.
>

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
>

Joining and Conditional Column Data Help

I have 2 table that I want to join and output a row on a condition that one of the records have a null in the field. Heres what I have.

employee table (empid, name)
tasks table (taskid, empid, taskname, resolution)

If the resolution is null than I want it to be accounted for in each employee record. Heres my query so far that joins the 2 tables and accounts for each employee and counts each task they have. I need another column that counts the tasks.resolution's null values for each employee but cant figure it out. Thanks for any help!

SELECT e.empid,
e.name,
COUNT(t.ID) as 'tcount'
FROM tasks t
RIGHT JOIN employee e ON c.empid = t.empid
GROUP BY e.empid, e.name
order by 'tcount' desc

SELECT

e.empid,

e

.empname,

COUNT

(t.taskid)as'tcount'

FROM

tasks t

LEFT

JOIN employee eON e.empid= t.empid

WHERE

t.resolutionISNULL

GROUP

BY e.empid, e.empname

Order

by tcountDesc|||

limno's query doesn't quite work the way you'd expect. Because it is being filtered by where the resolution is null from within the WHERE clause, it will eliminate employees that have no records where resolution is null from the output. If you want the employees listed even if they have no tasks, then use this instead:

SELECT empid, empname, (SELECTCOUNT(*)FROM tasksWHERE tasks.empid=employee.empidAND resolutionISNULL)as'tcount'FROM employeeOrder by tcountDesc

|||

Thanks. That helped me put together someting else

selecte.empid, e.ename, count(*) as 'tcount', sum(case when t.resolution isnull and t.empid is not null then 1 else 0 end) as 'NULL resolution'
from employee as e
left join tasks as t on t.empid = e.empid
group by e.empid, e.ename
order by 3 desc

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 Small Table to Big Table or Vice Versa, does it matter?

If I join Table1 to Table2 with a WHERE condition, is
it the same if I would join Table2 to Table1 considering
that the size of the tables are different.

Let's assume Table2 is much bigger than Table1.

I've never used MERGE, HASH JOINs etc, do any of
these help in this scenario?

Thank youserge (sergea@.nospam.ehmail.com) writes:
> If I join Table1 to Table2 with a WHERE condition, is
> it the same if I would join Table2 to Table1 considering
> that the size of the tables are different.
> Let's assume Table2 is much bigger than Table1.

For an inner join the order does not matter.

> I've never used MERGE, HASH JOINs etc, do any of
> these help in this scenario?

These are optimizer hints, and you should use them if you can get
good performance in any other way.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> If I join Table1 to Table2 with a WHERE condition, is
> it the same if I would join Table2 to Table1 considering
> that the size of the tables are different.

Yes.

> I've never used MERGE, HASH JOINs etc, do any of
> these help in this scenario?

No.

--
David Portas
SQL Server MVP
--

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:7_%te.82758$Jk6.1151808@.wagner.videotron.net. ..
> If I join Table1 to Table2 with a WHERE condition, is
> it the same if I would join Table2 to Table1 considering
> that the size of the tables are different.
> Let's assume Table2 is much bigger than Table1.
> I've never used MERGE, HASH JOINs etc, do any of
> these help in this scenario?
>
> Thank you|||On Tue, 21 Jun 2005 17:52:02 -0400, serge wrote:

> If I join Table1 to Table2 with a WHERE condition, is
> it the same if I would join Table2 to Table1 considering
> that the size of the tables are different.
> Let's assume Table2 is much bigger than Table1.

It doesn't matter because the query optimizer will go through thousands of
optimization iterations and choose the best plan it found so far in the
time frame it knows it should not spend any further.

> I've never used MERGE, HASH JOINs etc, do any of
> these help in this scenario?

no.. The query optimizer is smart enough to choose the best plan.

Tony
--
http://www.dotnet-hosting.com
Free web hosting with ASP.NET & SQL Server