CODEWITHSUNDEEP
sqlsql-server
TaeV
TaeV

Reputation: 738

How to improve this left join query

I would like to improve this query.

With INNER JOIN it doesn't take time (less than 1 second).

But with LEFT JOIN it take time nearly 1 min.

The result is about 17500 records.

I don't understand why, and i want to improve it.

SELECT  TOP (100) PERCENT iti.Id
        , iti.TransferDate
        , iti.FromSLoc AS FromSLocId
        , slf.Name AS FromSLoc
        , ct.Id AS CrateTypeId
        , ct.Type AS CrateType
        , cs.Id AS CrateSizeId
        , cs.Size AS CrateSize
        , itd.Amount
        , iti.SenderRemark
        , iti.ToSLoc AS ToSLocId
        , slt.Name AS ToSLoc
        , iti.StatusId, ts.Name AS Status
        , iti.CreatedBy
FROM    dbo.tbIntTransferInfo AS iti 
INNER JOIN dbo.tbmStorageLocation AS slf 
        ON slf.Id = iti.FromSLoc 
INNER JOIN dbo.tbmStorageLocation AS slt 
        ON slt.Id = iti.ToSLoc 
INNER JOIN dbo.tbmTransferStatus AS ts 
        ON ts.Id = iti.StatusId 
CROSS JOIN dbo.tbmCrateSize AS cs 
INNER JOIN dbo.tbmCrateType AS ct 
        ON ct.Id = cs.CrateTypeId 
       AND cs.Cancelled = 0 
LEFT JOIN dbo.tbIntTransferDetail AS itd 
        ON iti.Id = itd.IntTransferId 
       AND itd.CrateSizeId = cs.Id
ORDER BY iti.Id, CrateTypeId, CrateSizeId

In my system i have 6 sizes of crate. And one transaction may transfer up to 6 crate sizes. What i want is records that show transaction with 6 crate sizes. If the transaction didn't transfer some crate size, let it NULL.

The result that i want look like this:

Id, ... , CrateType, CrateSize, Amount
1    ...  X          Big        100
1    ...  X          Small      50
1    ...  Y          Big        NULL
1    ...  Y          Small      NULL
1    ...  Z          Big        10
1    ...  Z          Small      20
2    ...  X          Big        30
2    ...  X          Small      40
2    ...  Y          Big        NULL
2    ...  Y          Small      NULL
2    ...  Z          Big        NULL
2    ...  Z          Small      NULL

Transaction 1 --> Transfer crate type 'X' and 'Z' with 'Big' and 'Small' size, didn't transfer crate type 'Y'.

Transaction 2 --> Transfer crate type 'X' with 'Big' and 'Small' size, didn't transfer crate type 'Y' and 'Z'.

Help me to improve please.

Upvotes: 0

Views: 154

Answers (1)

jerry
jerry

Reputation: 1857

you need an index on dbo.tbIntTransferInfo on FromSLoc, StatusId, and Id

you need an index on dbo.tbmStorageLocation on Id

you need an index on dbo.tbmTransferStatus on Id

you need an index on dbo.tbmCrateSize on CrateTypeId, Cancelled, and Id

you need an index on dbo.tbIntTransferDetail on IntTransferId, CrateSizeId

If any of those indexes can be 'unique', it would be better.

I doubt 'TOP (100) Percent' is helping this query, I'd have to see the plan with and without to know.

Upvotes: 1

Related Questions