如何为查询提供更好的性能？

i have been reading some articles about "in statement" for sql. i learned "IN" creates some bad performance. i would like to make better performance above my codes. Can you give me some advises to achieve this, please?

我一直在阅读一些关于sql的“in statement”的文章。我学会了“IN”创造了一些糟糕的表现。我想在我的代码之上做出更好的表现。你能给我一些建议吗?

select 
    v.Id,  
    v.MissingVehicleFlag,  
    v.Make,  
    v.OatsModelId,  
    v.Model,  
    v.PersonalisedName,  
    v.RegistrationPlate, 
    v.ImageUrl,  
    v.ImageCropStartingX,  
    v.ImageCropStartingY, 
    v.LastModified, 
    v.Account_Id, 
    v.Deleted
from RobbinsuatLatest.dbo.Vehicles v  
    where v.Id in(
        select v1.Id
        from RobbinsuatLatest.dbo.Vehicles v1 
        except
        select v2.Id from RobbinsuatLatest.dbo.VehiclesV2 v2
        )

2 个解决方案

#1

Use NOT EXISTS

使用NOT EXISTS

select 
    v.Id,  
    v.MissingVehicleFlag,  
    v.Make,  
    v.OatsModelId,  
    v.Model,  
    v.PersonalisedName,  
    v.RegistrationPlate, 
    v.ImageUrl,  
    v.ImageCropStartingX,  
    v.ImageCropStartingY, 
    v.LastModified, 
    v.Account_Id, 
    v.Deleted
from RobbinsuatLatest.dbo.Vehicles v  
    where NOT EXISTS (
        select v2.Id 
        from RobbinsuatLatest.dbo.VehiclesV2 v2
        WHERE v.Id = v2.Id
        )

And make sure there is an index on Id in both RobbinsuatLatest.dbo.Vehicles and RobbinsuatLatest.dbo.VehiclesV2

并确保在RobbinsuatLatest.dbo.Vehicles和RobbinsuatLatest.dbo.VehiclesV2中都有Id的索引

#2

SELECT 
    v.Id,  
    v.MissingVehicleFlag,  
    v.Make,  
    v.OatsModelId,  
    v.Model,  
    v.PersonalisedName,  
    v.RegistrationPlate, 
    v.ImageUrl,  
    v.ImageCropStartingX,  
    v.ImageCropStartingY, 
    v.LastModified, 
    v.Account_Id, 
    v.Deleted
FROM RobbinsuatLatest.dbo.Vehicles v  
LEFT JOIN RobbinsuatLatest.dbo.VehiclesV2 v2 on v.Id = v2.Id
WHERE v2.Id IS NULL

#1