Subquery

Question

Post reply

Subquery

Data Rat

SSC Eights!

Points: 943
More actions
August 14, 2019 at 7:40 pm
Go to Answer
#3671425

Hi Friends,
I was wondering if someone could give me some help with reading this I guess you would call subquery. Thanks !!!

Begin
Select COUNT(*) from ouhsc_Common.FileHeader
where AuditUserId in (
SELECT distinct(o.UserId) from ouhsc_Security.AccountUsers o
where o.UnitNumber + o.AccountNumber in
(
SELECT au.UnitNumber + au.AccountNumber
FROM ouhsc_Security.AccountUsers au
LEFT JOIN dbo.aspnet_Users us
ON us.UserId=au.UserId
WHERE us.UserId = @userid
)
and ouhsc_Security.GetRoleIdFromRoleUid(o.RoleId) in (1,6,7,2)
)
and ouhsc_Common.FileHeader.Quarter = @Quarter
and ouhsc_Common.FileHeader.Year = @Year
and ouhsc_Common.FileHeader.FileStatusCode not in (300,600,900,1100,1300,1400)
End

Jonathan AC Roberts

SSCoach

Points: 18363

August 15, 2019 at 2:51 pm

Answer

#3671582

The query smells pretty bad.

It has a LEFT JOIN that is really an INNER JOIN
It is using IN on the concatenation of two columns, this makes that part of the query unsargable.
There is no need to use DISTINCT after IN operator, so it should be removed.

I would rewrite the query to use EXISTS instead of IN, make the LEFT JOIN an INNER JOIN and remove the DISTINCT:

BEGIN
    SELECT COUNT(*) [Count]
      FROM ouhsc_Common.FileHeader
     WHERE EXISTS(SELECT *
                    FROM ouhsc_Security.AccountUsers o
                   WHERE EXISTS(SELECT *
                                  FROM ouhsc_Security.AccountUsers au
                                 INNER JOIN dbo.aspnet_Users us
                                         ON us.UserId = au.UserId
                                 WHERE au.UnitNumber = o.UnitNumber 
                                   AND au.AccountNumber = o.AccountNumber 
                                   AND au.UserId = @userid
                               )
                     AND ouhsc_Security.GetRoleIdFromRoleUid(o.RoleId) IN (1, 6, 7, 2)
                     AND o.UserId = ouhsc_Common.FileHeader.AuditUserId
                 )
       AND ouhsc_Common.FileHeader.Quarter = @Quarter
       AND ouhsc_Common.FileHeader.Year = @Year
       AND ouhsc_Common.FileHeader.FileStatusCode NOT IN (300, 600, 900, 1100, 1300, 1400);
END;

Viewing 8 posts - 1 through 7 (of 7 total)

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

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

It's a subquery within a subquery (a subsubquery??) – it's much easier to read with some formatting applied:

BEGIN
    SELECT COUNT(*)
    FROM ouhsc_Common.FileHeader
    WHERE AuditUserId IN
          (
              SELECT DISTINCT
                     (o.UserId)
              FROM ouhsc_Security.AccountUsers o
              WHERE o.UnitNumber + o.AccountNumber IN
                    (
                        SELECT au.UnitNumber + au.AccountNumber
                        FROM ouhsc_Security.AccountUsers au
                            LEFT JOIN dbo.aspnet_Users us
                                ON us.UserId = au.UserId
                        WHERE us.UserId = @userid
                    )
                    AND ouhsc_Security.GetRoleIdFromRoleUid(o.RoleId) IN (1, 6, 7, 2)
          )
          AND ouhsc_Common.FileHeader.Quarter = @Quarter
          AND ouhsc_Common.FileHeader.Year = @Year
          AND ouhsc_Common.FileHeader.FileStatusCode NOT IN (300, 600, 900, 1100, 1300, 1400);
END;

I suggest you start with the inner subquery and work outwards.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Data Rat SSC Eights! Points: 943 More actions · Answer 2

Thanks for your comment. But why would you use an inner join instead of a left join?

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 3

Data Rat wrote:

Thanks for your comment. But why would you use an inner join instead of a left join?

WHERE us.UserId = @userid

Phil Parkin SSC Guru Points: 246987 More actions · Answer 4

Jonathan AC Roberts wrote:

Data Rat wrote:
Thanks for your comment. But why would you use an inner join instead of a left join?
WHERE us.UserId = @userid

Because you've effectively turned the LEFT JOIN into an INNER JOIN by adding

WHERE us.UserId = @userid

To make the LEFT JOIN meaningful would require something like

WHERE us.UserId = @userid OR us.UserId IS NULL

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Data Rat SSC Eights! Points: 943 More actions · Answer 5

Data Rat

SSC Eights!

Points: 943

August 15, 2019 at 9:08 pm

#3671701

That's Excellent. Thanks !!!!

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 6

Phil Parkin wrote:

Jonathan AC Roberts wrote:
Data Rat wrote:
Thanks for your comment. But why would you use an inner join instead of a left join?
WHERE us.UserId = @userid
Because you've effectively turned the LEFT JOIN into an INNER JOIN by adding
WHERE us.UserId = @userid
To make the LEFT JOIN meaningful would require something like
WHERE us.UserId = @userid OR us.UserId IS NULL

Additional comment - when SQL Server sees the outer join it is going to look at how it builds its execution plan differently. You could end up with a merge join where a hash match would be a better option - but SQL Server cannot use the hash match because it needs to evaluate all rows from the preserved table - join the rows from the outer table - then apply the filter to satisfy the outer join.

Although the where clause 'effectively' converts the join to an inner join - the resulting execution plan could be a lot different depending on indexes and tables involved.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs