May 29, 2006 at 10:43 am
if i have to join 2 tables with alot of columns, will the querry run faster if i
join them like:
select ...
from tableA a inner join (select id,col2,col3 from tableB) as q on q.id = a.id
, i meen just select a couple of columns from the table i need less instead of joining
2 big tables when i only need 5 columns.
May 29, 2006 at 11:02 am
Faster than what?
If you had an index on id, col2, col3 then it would go faster regardless of the # of columns.
May 29, 2006 at 7:47 pm
Is there a specific reason have a select inside your inner join?
Not that it is wrong to do so, but may be confusing to the next person who needs to maintain it.
And I don't think it makes any difference to the overall select statement in terms of speeding it up.
May 29, 2006 at 11:49 pm
thank you for your answer,
i thought that because i select only 3 columns from the second table it will go faster then simply
select
from hugetable inner join bigtable_i_only_need_three_columns on ....
May 30, 2006 at 3:36 am
Hi Tudor,
I agree with the answers of both Steves, but I'll elaborate a little on them:
- it makes no difference if you join the "many column tabel of which you only need 3" complete or using an inline view. The optimizer will "see" that you only select the three columns and will take this into consideration when creating the execution plan for the query. You can actually compare the execution plans for both variants by looking at the estmated and actual execution plans for both queries (if you put both queries in one batch, you will even see their (estimated) cost relatively to each other). This can be done in SQL Query analyzer (SQL Server 200 client tool) as well as in SQL Server Management Studio (SQL Server 2005 client tool) using the appropriate buttons on the toolbar.
- Steve Jones mentions that your query would benefit from an index on the selected columns. That is absolutely correct. For the ins and outs you may want to look further by using the search term "covering index". Make sure to also read about other considerations for creating indexes in the BOL, as each index comes at a price and - for example - the sequence of the columns in de index matters as well.
Cheers,
Henk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply