I've the two following queries:
declare @UserId as int
set @UserId = 1
-- Query #1: Sub-query
SELECT
u.[Id] ,
u.[Name] ,
u.[OrgId] AS Organization,
(SELECT o.[Name] FROM Org o WHERE o.Id = u.OrgId) As OrganizationName,
[UserRoleId] AS UserRole,
[UserCode] AS UserCode,
[EmailAddress] As EmailAddress,
(SELECT SearchExpression FROM SearchCriteria WHERE UserId = @UserId AND IsDefault=1 ) AS SearchCriteria,
(SELECT PageSize FROM UserPreferences WHERE UserId = @UserId) AS UserPreferencePageSize,
(SELECT DrilldownPageSize FROM UserPreferences WHERE UserId = @UserId) AS UserPreferenceDrilldownPageSize
FROM [User] as u
WHERE u.Id = @UserId
-- Query #2: LEFT OUTER JOIN-query
SELECT
u.[Id] ,
u.[Name] ,
u.[OrgId] AS Organization,
(SELECT o.[Name] FROM Org o WHERE o.Id = u.OrgId) As OrganizationName,
[UserRoleId] AS UserRole,
[UserCode] AS UserCode,
[EmailAddress] As EmailAddress,
sc.SearchExpression As SearchExpression,
up.PageSize As PageSize,
up.DrilldownPageSize As DrilldownPageSize
FROM [User] as u
LEFT OUTER JOIN [UserPreferences] as up ON u.id = up.UserId
LEFT OUTER JOIN [SearchCriteria] as sc ON u.id = sc.UserId
WHERE ISNULL(sc.IsDefault,1)=1 AND u.Id = @UserId
Query execution plan statistics: (Query cost relative to batch)
- Query#1 (Sub-Query) : 56%
- Query#2 (JOIN) : 44%
I thot the sub-query would be optimal because the sub-query will be executed after the WHERE filter is applied. The statistics say the Query#2 - JOIN approach is better.
Pls suggest. Also as a moderate SQL-Server user how can I derive which query is better (anything other then execution-plan, if it is more helpful)
Thank you.