CODEWITHSUNDEEP
sql-server
Juninho
Juninho

Reputation: 13

SQL slow subquery

I am trying to retrieve data from a very large Audits table (millions of rows). So I need to make the query run as efficiently as possible. First I am playing with a subquery to return the ObjectTypeId and use this to limit the query on the Audit table

This query is taking 4 minutes to run:

select distinct Audits.ObjectTypeID,  COUNT(*) as Count
from Audits as Audits
where Audits.ObjectTypeID = 
(select distinct ObjectType.ObjectTypeID from ObjectType where ObjectName = 'Data')
group by Audits.ObjectTypeID

If I default in the ObjectTypeID the query runs in 42 seconds

select distinct(Audits.ObjectTypeID), COUNT(*) as Count
from Audits 
where Audits.ObjectTypeID = 1
group by Audits.ObjectTypeID

But the subquery when run in isolation only takes only a second to run. So why should the first query take so long?

Upvotes: 1

Views: 119

Answers (3)

ps_prakash02
ps_prakash02

Reputation: 583

Can you try this

SELECT DISTINCT Audits.ObjectTypeID,  COUNT(*) as Count
FROM Audits as Audits
INNER JOIN 
(SELECT DISTINCT ObjectTypeId, ObjectName FROM ObjectType 
 WHERE ObjectName = 'Data') as ObjectType ON Audits.ObjectTypeID = ObjectType.ObjectTypeID
GROUP BY Audits.ObjectTypeID

Upvotes: 0

John Odom
John Odom

Reputation: 1213

The part where you are getting the most performance hit could be this line:

where Audits.ObjectTypeID = 
(select distinct ObjectType.ObjectTypeID from ObjectType where ObjectName = 'Data')

You are actually calling the same query on every row of your table and it will search the ENTIRE ObjectType table and return the ENTIRE result of that subquery. This will be a big performance hit if your ObjectType table is HUGE. You could speed up that section of the query by using EXISTS so that it will return early once a result was found. Here is an example:

SELECT a.ObjectTypeID,  COUNT(*) as Count
FROM Audits a
WHERE EXISTS 
(
    SELECT ot.ObjectTypeID
    FROM ObjectType ot 
    WHERE ot.ObjectName = 'Data' AND ot.ObjectTypeID = a.ObjectTypeID
)
GROUP BY a.ObjectTypeID

Upvotes: 1

D Stanley
D Stanley

Reputation: 152644

I can see three things that might help:

  1. Pull the ObjectTypeID into a variable: since there should be only one value for it
  2. Take out the DISTINCT on both queries since they should be unnecessary (the subquery should only have one value and you are grouping by that value in the outer query
  3. Take out the GROUP BY since you are only querying for one ObjectTypeID

So the final query would be:

DECLARE @ObjectTypeID INT

SELECT @ObjectTypeID = (select ObjectType.ObjectTypeID 
                        from ObjectType 
                        where ObjectName = 'Data')

select Audits.ObjectTypeID,  COUNT(*) as Count
from Audits as Audits
where Audits.ObjectTypeID = @ObjectTypeID

If you are executing this as a single statement and not as a batch or stored procedure (meaning you can't use variables) thne you can keep the subquery:

select Audits.ObjectTypeID,  COUNT(*) as Count
from Audits as Audits
where Audits.ObjectTypeID = 
    (select ObjectType.ObjectTypeID 
     from ObjectType 
     where ObjectName = 'Data')

Upvotes: 1

Related Questions