December 29, 2015 at 9:00 am
I have 3 tables as part of a normalized database, I have a main records table that come from an ETL job Table1, I also have a set of records that need to be referenced but change ever month Table3, and Table3 Records have an effective and end date that is found in Table2
Now I have a query that outer joins Table1 to Table3 on a key looking for a null value, we are looking for records that do not exist but we need to make sure to only look at records that match a date.
I can write this as a sub query and then outer join that to Table1 or I can create a view of table2&table3 and outer join the view, which is a better practice?
December 29, 2015 at 9:11 am
John1.5.nlt (12/29/2015)
I have 3 tables as part of a normalized database, I have a main records table that come from an ETL job Table1, I also have a set of records that need to be referenced but change ever month Table3, and Table3 Records have an effective and end date that is found in Table2Now I have a query that outer joins Table1 to Table3 on a key looking for a null value, we are looking for records that do not exist but we need to make sure to only look at records that match a date.
I can write this as a sub query and then outer join that to Table1 or I can create a view of table2&table3 and outer join the view, which is a better practice?
I don't think either is really a better practice. The optimizer will likely treat both the same way if written the same way. I tend to avoid views because I'd rather have all my code for a specific use in one place and have to fix code in multiple places when there is a structure change, but if you are always (or most often) using table2 and table 3 together the same way, you could use the view so that logic is maintained in one place. The 2 issues with the using a view that I see are:
1. People re-using it without really looking to see how it works and that it really does what they need.
2. People nesting it in another view which can become a performance issue.
I don't think either way is wrong, I'm just not a big fan of code re-use in T-SQL whether it be via a UDF or a view so I tend to not use views.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 30, 2015 at 3:19 am
As Jack says, in the scenario described, the optimizer is likely to arrive at the same plan regardless of the approach you take. However, what you're proposing to do with the view can lead down a very slippery slope. Next you'll look at this query you have and realize that you need to reference it in one or two other places, so you make it a view. Then you start joining one view to another and suddenly you have nested and joined views. This is a classic code smell that leads to performance problems. I would absolutely avoid using a view for the purposes you're describing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply