An SQL query with an 'IN' within the where clause is very slow to run

Question

I have created an SQL query which runs but it takes about 17 seconds to complete. I have narrowed down the problem to the IN clause within the Where section of the query. This section is responsible for also finding all records within the same table where the kitref of one record matches the partofkit field of multiple other records.

Could anyone help with suggestions on how I may be able to make this more efficient?

The full SQL is below:

SELECT
    tblProductions.ProductionName, tblEquipment.KitRef, tblEquipment.PartOfKit, tblEquipment.Description, 
    tblCollection.HireID, tblCollection.CollectedBy, Format(tblCollection.DueBack,'dd/MM/yyyy') AS DueBack, Format(tblCollection.CollectionDate,'dd/MM/yyyy') AS CollectionDate, Format(tblCollection.CollectionTime,'HH:mm') AS CollectionTime, tblCollection.DiscountPC, 
    tblCollectionItemized.HireLine, tblCollectionItemized.Notes, tblCollectionItemized.BookingActive, tblCollectionItemized.DepositReturned, tblTariff.Tariff
FROM tblTariff
INNER JOIN (
    tblProductions INNER JOIN (
        tblCollection INNER JOIN (
            tblEquipment 
            INNER JOIN tblCollectionItemized  ON tblEquipment.KitKey = tblCollectionItemized.KitKey
        ) ON tblCollection.HireID = tblCollectionItemized.HireID) 
    ON tblProductions.ProductionIDKey = tblCollection.ProductionName
) ON tblTariff.TariffKey = tblCollection.Tarriff
WHERE (
    tblCollectionItemized.BookingActive='TRUE'
    AND tblEquipment.PartOfKit IN (
        SELECT tblEquipment.KitRef
        FROM tblEquipment
        INNER JOIN tblCollectionItemized ON tblEquipment.KitKey = tblCollectionItemized.KitKey
        WHERE tblCollectionItemized.ReturnsNumber =43
    )
) 
OR (
    tblCollectionItemized.BookingActive='TRUE'
    AND tblCollectionItemized.ReturnsNumber =43
)

first question with performance is always indexes. Which ones do you have? Also add example data and of how many records we are talking here — juergen d, May 08 '18 at 16:29
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, May 08 '18 at 16:30
`WHERE IN` should really only be used when you have a value set (ie. parameter values passed in) that you want to compare against. Otherwise you should write a `JOIN` or use `WHERE EXISTS`. — Igor, May 08 '18 at 16:34
Try making the inner select after 'IN' into its own temp table before you start the query and then join to the temp table itself in the query — Daniel Marcus, May 08 '18 at 16:55

score 0 · Answer 1 · answered May 08 '18 at 17:02

Not a complete answer here but using some aliases and joins in a logical order make this nightmarish query into something a lot easier to see what is going on.

SELECT
    p.ProductionName
    , e.KitRef
    , e.PartOfKit
    , e.Description
    , c.HireID
    , c.CollectedBy
    , Format(c.DueBack,'dd/MM/yyyy') AS DueBack
    , Format(c.CollectionDate,'dd/MM/yyyy') AS CollectionDate
    , Format(c.CollectionTime,'HH:mm') AS CollectionTime
    , c.DiscountPC
    , ci.HireLine
    , ci.Notes
    , ci.BookingActive
    , ci.DepositReturned
    , t.Tariff

FROM tblTariff t
INNER JOIN tblCollection c ON t.TariffKey = c.Tarriff
INNER JOIN tblProductions p ON p.ProductionIDKey = c.ProductionName
INNER JOIN tblCollectionItemized ci ON c.HireID = ci.HireID
INNER JOIN tblEquipment e ON e.KitKey = ci.KitKey
WHERE ci.BookingActive = 'TRUE'
AND e.PartOfKit IN 
(
    SELECT e2.KitRef
    FROM tblEquipment e2
    INNER JOIN tblCollectionItemized ci2 ON e2.KitKey = ci2.KitKey
    WHERE ci2.ReturnsNumber = 43
) 
OR 
(
    ci.ReturnsNumber = 43
)

score 0 · Answer 2 · answered May 08 '18 at 17:22

you can try EXISTS instead of IN and add (nolock) hint to tables

SELECT
    P.ProductionName, 
    E.KitRef, 
    E.PartOfKit, 
    E.Description, 
    C.HireID, 
    C.CollectedBy, 
    Format(C.DueBack,'dd/MM/yyyy') AS DueBack, 
    Format(C.CollectionDate,'dd/MM/yyyy') AS CollectionDate, 
    Format(C.CollectionTime,'HH:mm') AS CollectionTime, 
    C.DiscountPC, 
    CI.HireLine, 
    CI.Notes, 
    CI.BookingActive, 
    CI.DepositReturned, 
    T.Tariff
FROM tblTariff T
    INNER JOIN tblCollection C (nolock) ON T.TariffKey = C.Tarriff
    INNER JOIN tblProductions P (nolock) ON P.ProductionIDKey = C.ProductionName
    INNER JOIN tblCollectionItemized CI (nolock) ON C.HireID = CI.HireID
    INNER JOIN tblEquipment E (nolock) ON E.KitKey = CI.KitKey     
WHERE (
    tblCollectionItemized.BookingActive='TRUE'
    AND EXISTS (
        SELECT *
        FROM tblEquipment E2 (nolock)
        INNER JOIN tblCollectionItemized CI2 (nolock) ON E2.KitKey = CI2.KitKey
        WHERE CI2.ReturnsNumber =43 AND E.PartOfKit = E2.KitRef )

) 
OR (
    CI.BookingActive='TRUE'
    AND CI.ReturnsNumber =43
)

An SQL query with an 'IN' within the where clause is very slow to run

2 Answers2