Which is better for performance, selecting all the columns or select only the required columns while performng join?

Question

I am been asked to do performance tuning of a SQL Server query which has so many joins in it.

For example

LEFT JOIN 
    vw_BILLABLE_CENSUS_R CEN ON DE.Client = CEN.Client
                             AND CAL.REPORTING_MONTH = CEN.REPORTING_MONTH

There are almost 25 columns present in vw_Billing_Cenus_R but we wanted to use only 3 of them. So I wanted to know instead of selecting all the columns from the view or table, if I only select those columns which are required and then perform join like this

LEFT JOIN (SELECT [Column_1], [Column_2], [Column_3] 
           FROM vw_BILLABLE_CENSUS_R) CEN ON DE.Client = CEN.Client
                                          AND CAL.REPORTING_MONTH = CEN.REPORTING_MONTH

So Will this improve the performance or not?

Answer 1

The important part is the columns you are actually using on the outmost SELECT, not the ones to are selecting to join. The SQL Server engine is smart enough to realize that he does not need to retrieve all columns from the referenced table (or view) if he doesn't need them.

So the following 2 queries should yield the exact same query execution plan:

SELECT
    A.SomeColumn
FROM
    MyTable AS A
    LEFT JOIN (
        SELECT
            *
        FROM
            OtherTable AS B) AS X ON A.SomeColumn = X.SomeColumn

SELECT
    A.SomeColumn
FROM
    MyTable AS A
    LEFT JOIN (
        SELECT
            B.SomeColumn
        FROM
            OtherTable AS B) AS X ON A.SomeColumn = X.SomeColumn

The difference would be if you actually use the selected column (in a conditional where or actually retrieving the value), as in here:

SELECT
    A.SomeColumn,
    X.* -- * has all X columns
FROM
    MyTable AS A
    LEFT JOIN (
        SELECT
            B.*
        FROM
            OtherTable AS B) AS X ON A.SomeColumn = X.SomeColumn


SELECT
    A.SomeColumn,
    X.* -- * has only X's SomeColumn
FROM
    MyTable AS A
    LEFT JOIN (
        SELECT
            B.SomeColumn
        FROM
            OtherTable AS B) AS X ON A.SomeColumn = X.SomeColumn

Answer 2

I would rather use this approach:

LEFT JOIN 
    vw_BILLABLE_CENSUS_R CEN ON DE.Client = CEN.Client
                             AND CAL.REPORTING_MONTH = CEN.REPORTING_MONTH

than this

LEFT JOIN (SELECT [Column_1], [Column_2], [Column_3] 
           FROM vw_BILLABLE_CENSUS_R) CEN ON DE.Client = CEN.Client
                                          AND CAL.REPORTING_MONTH = CEN.REPORTING_MONTH

Since in this case:

1. you make your query simpler,
2. you does not have to rely on query optimizer smartness and expect that it will eliminate unnecessary columns and rows
3. finally, you can select as many columns in the outer SELECT as necessary without using derived tables techniques.

In some cases, derived tables are welcome, when you want to eliminate duplicates in a table you want to join on a fly, but, imho, not in your case.