SQL long execution time

Question

This query works fine there is only one problem and that's his long execution time. Can someone show me some good optimizations and explain them.

  SELECT TOP 5 PC.PersonID, P.FirstName, P.LastName, P.A, COUNT(*) Together
    FROM PersonCheckIn PC

    INNER JOIN Person P ON P.PersonID = PC.PersonID

    WHERE CAST(CheckInDate AS DATE) IN (SELECT CAST(CheckInDate AS DATE)
                        FROM PersonCheckIn C    
                            WHERE C.PersonId = 20)  AND

                        PC.TimeTableID IN (SELECT CIn.TimeTableID
                        FROM PersonCheckIn CIn  
                            WHERE CIn.PersonId = 20)
    AND PC.PersonId <> 20  -- not count same person

    GROUP BY PC.PersonId, P.FirstName, P.LastName, P.A
    ORDER BY Together DESC;

At a very minimum you should [include the actual Execution Plan](https://stackoverflow.com/a/7359705/1260204), you could use [Paste the Plan](https://www.brentozar.com/pastetheplan/) and share the link in your question. Also [try to read it yourself](https://stackoverflow.com/a/759097/1260204), maybe you can figure out the performance issue(s) with your query. Finally include the [schema DDL](https://en.wikipedia.org/wiki/Data_definition_language) along with the query you are executing. — Igor, Sep 28 '17 at 14:40
That order by is going to take a while if there are a lot of records. What happens when you remove the two derived tables? I agree with @Igor — S3S, Sep 28 '17 at 14:55

score 0 · Accepted Answer · answered Sep 28 '17 at 14:55

Avoiding sub-queries and IN() Statement will drasticaly decrease execution time...

Even so CheckInDate is not prefixed with its table alias in your query (And we have to guess witch table is concerned :( ), maybe this will do the job :

SELECT TOP 5 PC.PersonID, P.FirstName, P.LastName, P.A, COUNT(*) Together
    FROM PersonCheckIn PC
    INNER JOIN Person P ON P.PersonID = PC.PersonID
    INNER JOIN PersonCheckIn LU ON (LU.PersonId = 20 AND PC.CheckInDate = LU.CheckInDate AND PC.TimeTableID = LU.TimeTableID)     
    WHERE PC.PersonId <> 20  -- not count same person
    GROUP BY PC.PersonId, P.FirstName, P.LastName, P.A
    ORDER BY Together DESC;

If not, please :
- set the missing alias.
- give use data structure.
- give us data exemple.
- give us expected result exemple.

SQL long execution time

1 Answers1