CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
Dennis
Dennis

Reputation: 728

Slow Query Due to Sub Select

I have several SQL Server 2014 queries that pull back a data set where we need to get a count on related, but different criteria along with that data. We do this with a sub query, but that is slowing it down immensely. It was fine until now where we are getting more data in our database to count on. Here is the query:

SELECT 
    T.*, 
    ISNULL((SELECT COUNT(1)
            FROM EventRegTix ERT, EventReg ER
            WHERE ER.EventRegID = ERT.EventRegID 
              AND ERT.TicketID = T.TicketID
              AND ER.OrderCompleteFlag = 1), 0) AS NumTicketsSold
FROM 
    Tickets T 
WHERE 
    T.EventID = 12345
    AND T.DeleteFlag = 0 
    AND T.ActiveFlag = 1
ORDER BY 
    T.OrderNumber ASC

I am pretty sure its mostly due to the relation back outside of the sub query to the Tickets table. If I change the T.TicketID to an actual ticket # (999 for example), the query is MUCH faster.

I have attempted to join together these queries into one, but since there are other fields in the sub query, I just cannot get it to work properly. I was playing around with 

COUNT(1) OVER (PARTITION BY T.TicketID) AS NumTicketsSold

but could not figure that out either.

Any help would be much appreciated!

Upvotes: 0

Views: 72

Answers (3)

Dennis
Dennis

Reputation: 728

Fixed this - query went from 5+ seconds to 1/2 second or less. Issues were:

1) No indexes. Did not know all FK fields needed indexes as well. I indexed all the fields that we joined or were in WHERE clause.

2) Used SQL Execution Plan to see the place where the bottle neck was. Told me no index, hence 1) above! :)

Thanks for all your help guys, hopefully this post helps someone else.

Dennis

PS: Changed the syntax too!

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

I would try with OUTER APPLY :

SELECT T.*, T1.*
FROM Tickets T OUTER APPLY
     (SELECT COUNT(1) AS NumTicketsSold
      FROM EventRegTix ERT JOIN
           EventReg ER
           ON ER.EventRegID = ERT.EventRegID 
        WHERE ERT.TicketID = T.TicketID AND ER.OrderCompleteFlag = 1
      ) T1
WHERE T.EventID = 12345 AND
      T.DeleteFlag = 0 AND
      T.ActiveFlag = 1
ORDER BY T.OrderNumber ASC;

And, obvious you need indexes Tickets(EventID, DeleteFlag, ActiveFlag, OrderNumber), EventRegTix(TicketID, EventRegID), and EventReg(EventRegID, OrderCompleteFlag) to gain the performance.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I would write this as:

SELECT T.*, 
       (SELECT COUNT(1)
        FROM EventRegTix ERT JOIN
             EventReg ER
             ON ER.EventRegID = ERT.EventRegID 
        WHERE ERT.TicketID = T.TicketID AND ER.OrderCompleteFlag = 1
      ) AS NumTicketsSold
FROM Tickets T 
WHERE T.EventID = 12345 AND
      T.DeleteFlag = 0 AND
      T.ActiveFlag = 1
ORDER BY T.OrderNumber ASC;

Proper, explicit, standard JOIN syntax does not improve performance; it is just the correct syntax. COUNT(*) cannot return NULL values, so COALESCE() or a similar function is unnecessary.

You need indexes. The obvious ones are on Tickets(EventID, DeleteFlag, ActiveFlag, OrderNumber), EventRegTix(TicketID, EventRegID), and EventReg(EventRegID, OrderCompleteFlag).

Upvotes: 5

Related Questions