SQL Server: Design: Embedded Select statement or INNER JOIN ?

Question

I've the following table structure -

Site: Master tablefor site

Org: Master table for Org

User: Master table for User (each user links to a unique Org via User.OrgId)

OrgSite: Store some 'Org specific' Site details (OrgId, SiteId, SiteName, SiteCode). Not ALL sites but only those which are accessible to Org.

UserSite: Link User to his accessible Site(s) (UserId, SiteId). As a user is linked to an Org UserSite will be a subset of the OrgSite table.

ItemSite: Table which stores some Item & Site specific details (ItemID, SiteId, OrgId, ...)

Now, I've to filter\display records from the 'ItemSite' and in that I also need to display the Sitecode. So, I see the following two options -

1. Create a VIEW: vw_ItemSite_UserSite_OrgSite (INNER JOIN all the tables on SiteId) - this will give me access to ALL the Org specific details available in the 'OrgSite' table (i.e. SiteCode, etc..)

If you can notice I've to include the 'OrgSite' in the view only because I want Org specific SiteCode & SiteName. Because the UserSite is already filtering the Sites - so I can 'exclude' the OrgSite table and eliminate an unnecessary INNER JOIN.

2. Based on the above note - the second option is to create a VIEW: vw_ItemSite_UserSite and in the 'SELECT' statement of the VIEW I can embed the following SELECT like -

CREATE VIEW vw_ItemSite_UserSite AS
SELECT ItemSite.SiteID,
(SELECT TOP 1 [SiteCode] FROM OrgSite WHERE OrgId = ItemSite.OrgId) AS SiteCode,
...
FROM ItemSite INNER JOIN UserSite ON ItemSite.SiteId = UserSite.SiteId

---

My only intention is that - I believe the INNER JOIN and WHERE will be evaluted before the evalution of the embedded select statement. So, does this save me some performance? Or is the idea of having the vw_ItemSite_UserSite_OrgSite is better.

Option#1 or option#2?

Thank you.

Answer 1

Beware of Premature optimization. If both queries return the same result, use the one that is easier for you to understand and maintain. It's SQL Server's task to make sure that the query operations (join, select, ...) are performed in the order which optimizes performance. And, usually, SQL Server does quite a good job on that.

That said, there are some occasions where the SQL Server query analyzer does not find the optimal query plan and you need to fine-tune yourself. However, these are rare cases. Unless you already have performance problems with your query (and they cannot be fixed by introducing missing indexes), this is something you should not worry about right now.

Answer 2

I'll take the easy answer approach.Create some tests and check them for performance and see which one really performs best for your given environment.

Answer 3

Option 1 will almost certainly be faster, the embedded SELECT is usually a bad idea for performance.

BUT - don't take our word for it. Code up both and try them, checking the query plans. It's probably premature optimisation in this case, but it's also a good simple test case on which to learn so you know properly how to do it and what the implications are for when you have a problem that really needs the right way to do it. There are sometimes huge performance differences between different ways of writing the same query that the optimiser can do nothing about so learn the general rules up front and your life will be happier.