Performance Tuning Of SQL using ISNULL in JOINS

Question

Performance Tuning Of SQL using ISNULL in JOINS

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76989 More actions · Answer 1

Eric M Russell (7/21/2011)

SQL_By_Chance (7/21/2011)
Assuming that changing the data is not an viable option, then I suggest you try modifying the SQL like below, doing a left outer join(s), and then put the ISNULL transform around the column(s) in the SELECT clause.

Making that change will completely change the results of the query. The original query will match either NULL or 1 on the left side with 1 on the right side. Your change will cause only 1 on the left side to match 1 on the right side. The NULL will not match anything on the right side (with the default value for ANSI NULLS).

Also, if it were me, I'd supply better names for the table aliases than what the original SQL coder used. Those sequential numbers are not helpful when trying to read this.

I completely agree on this point.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Eric M Russell SSC Guru Points: 125561 More actions · Answer 2

drew.allen (7/22/2011)

Eric M Russell (7/21/2011)
SQL_By_Chance (7/21/2011)
Assuming that changing the data is not an viable option, then I suggest you try modifying the SQL like below, doing a left outer join(s), and then put the ISNULL transform around the column(s) in the SELECT clause.
Making that change will completely change the results of the query. The original query will match either NULL or 1 on the left side with 1 on the right side. Your change will cause only 1 on the left side to match 1 on the right side. The NULL will not match anything on the right side (with the default value for ANSI NULLS).
Also, if it were me, I'd supply better names for the table aliases than what the original SQL coder used. Those sequential numbers are not helpful when trying to read this.
I completely agree on this point.
Drew

The SQL modification I suggested was based on the assumption that in the case of a NULL foreign key, a join to the dimension table would not be needed, so I replaced the orignal inner join with an outer join to insure that the fact row would always be returned, and a single default code or description was substituted in the select clause. However, looking at the original SQL again, I now see that multiple columns are being returned from some of these dimension tables, so it seems what I suggested wouldn't be practical. Of couse updating the foreign key columns with default values and removing the isnull() function from the joins would be the best solution.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho