query on derived table

Hi,
If I join a table with a derived table, will the index be used? Thanks> If I join a table with a derived table, will the index be used?
Which index?
use northwind
go
set showplan_text on
go
select
c.customerid, c.companyname, a.orderid, a.orderdate
from
customers as c
inner join
(
select customerid, orderid, orderdate
from orders
where shipvia = 3
) as a
on c.customerid = a.customerid
go
set showplan_text off
go
AMB
"Jen" wrote:

> Hi,
> If I join a table with a derived table, will the index be used? Thanks|||You have not provied enough information to answer that question... At least
not without presenting a course in Index Tuning and optimization... Generall
y
whether or not an Index will be used for any specific query is determined by
a whole host of things
1) Whether any columns used as Predicates, Join conditions, or Order By
items, are in the index.
2) Whether the optimizer "Guesstimates" that using the index will result in
fewer IOs than doing a complete Table Scan. And this estimate is influenced
by factors such as:
The percentage of records (from the table(s)) that the query will result
in. (The higher the percentage, the more likely it is to ignore the index.)
wheether the index is clustered, Whether any predicates produce a "Range" o
f
results (like betweene two dates) which is based on two boundary values whic
h
are the columns in a clustered index. etc..
30
"Jen" wrote:

> Hi,
> If I join a table with a derived table, will the index be used? Thanks|||I have clusterd index for table1's primary key accountId, and table2 has
accountId foreign key reference that column and has index for it too. I used
to join these 2 tables on accountId and I know both tables will use index fo
r
accountId.
Now for table2, I only need subset data meaning less columns and by using
group to reduce rows. So I made it a derived table:
select table1.accountId, table1.userName, table1.moreUserInfo,
t2.accountType, t2.amount
from table1 inner join
(select accountId, accountType, min(amount) as amount from table2 where
accountType<>'C' group by accountId, accountType) t2
on table1.accountId = t2.accountId
In this case when the join happens the accountId in the t2 is still indexed?
My concern is that the derived table will affect performance or not?Thanks
"CBretana" wrote:
> You have not provied enough information to answer that question... At leas
t
> not without presenting a course in Index Tuning and optimization... Genera
lly
> whether or not an Index will be used for any specific query is determined
by
> a whole host of things
> 1) Whether any columns used as Predicates, Join conditions, or Order By
> items, are in the index.
> 2) Whether the optimizer "Guesstimates" that using the index will result i
n
> fewer IOs than doing a complete Table Scan. And this estimate is influenc
ed
> by factors such as:
> The percentage of records (from the table(s)) that the query will resu
lt
> in. (The higher the percentage, the more likely it is to ignore the index.
)
> wheether the index is clustered, Whether any predicates produce a "Range"
of
> results (like betweene two dates) which is based on two boundary values wh
ich
> are the columns in a clustered index. etc..
> 30
> "Jen" wrote:
>|||Jen, he answer is Yes, (I am pretty sure!) your query CAN use the index on
AccountID in table2. Whether it actually will or not depends on, again,
whether the query optimizer "decides" that using the index will result in
fewer page reads (IOs, than reading the table directly. Reading your query,
the only predivate is on accountType, so the answer to this question is
somewhat determined by "How many (percentage) of the records in Table2 have
accountType <> 'C' ' If the answer to this is more than 15- 20% (This
threshold is different in every case, but it is surprisingly lower than you
would think. ) than the query will not use the index.
By the way, (I think - try it out) that your query...
Select T1.accountId, T1.userName,
T1.moreUserInfo,
T2.accountType, T2.amount
From table1 T1
Join (Select accountId, accountType,
min(amount) amount
From Table2
Where accountType <> 'C'
Group by accountId, accountType) T2
On T1.AccountId = T2.AccountId
is equivilent to this one, (which does NOT use a derived table)
Select T1.accountId, T1.userName,
T1.moreUserInfo,
T2.accountType, T2.amount
From table1 T1 Join Table2 T2
On T2.AccountId = T1.AccountId
Group By T1.accountId, T1.userName,
T1.moreUserInfo, T2.accountType
"Jen" wrote:
> I have clusterd index for table1's primary key accountId, and table2 has
> accountId foreign key reference that column and has index for it too. I us
ed
> to join these 2 tables on accountId and I know both tables will use index
for
> accountId.
> Now for table2, I only need subset data meaning less columns and by using
> group to reduce rows. So I made it a derived table:
> select table1.accountId, table1.userName, table1.moreUserInfo,
> t2.accountType, t2.amount
> from table1 inner join
> (select accountId, accountType, min(amount) as amount from table2 where
> accountType<>'C' group by accountId, accountType) t2
> on table1.accountId = t2.accountId
> In this case when the join happens the accountId in the t2 is still indexe
d?
> My concern is that the derived table will affect performance or not?Thanks
> "CBretana" wrote:
>|||Is YES for first or second question?
I need as many as 18 columns from table1, so it will be a long group by
clause, do you think this is better than derived table?
Thanks
"CBretana" wrote:
> Jen, he answer is Yes, (I am pretty sure!) your query CAN use the index on
> AccountID in table2. Whether it actually will or not depends on, again,
> whether the query optimizer "decides" that using the index will result in
> fewer page reads (IOs, than reading the table directly. Reading your quer
y,
> the only predivate is on accountType, so the answer to this question is
> somewhat determined by "How many (percentage) of the records in Table2 hav
e
> accountType <> 'C' ' If the answer to this is more than 15- 20% (This
> threshold is different in every case, but it is surprisingly lower than y
ou
> would think. ) than the query will not use the index.
> By the way, (I think - try it out) that your query...
> Select T1.accountId, T1.userName,
> T1.moreUserInfo,
> T2.accountType, T2.amount
> From table1 T1
> Join (Select accountId, accountType,
> min(amount) amount
> From Table2
> Where accountType <> 'C'
> Group by accountId, accountType) T2
> On T1.AccountId = T2.AccountId
> is equivilent to this one, (which does NOT use a derived table)
> Select T1.accountId, T1.userName,
> T1.moreUserInfo,
> T2.accountType, T2.amount
> From table1 T1 Join Table2 T2
> On T2.AccountId = T1.AccountId
> Group By T1.accountId, T1.userName,
> T1.moreUserInfo, T2.accountType
>
> "Jen" wrote:
>