sql-server - sql中的动态查询性能比较

Question

我通过以下方式创建了一个 Sql 动态查询(我使用的是存储过程)，但我想知道，与我们以字符串形式执行的动态查询相比，它是否有任何性能下降 sp_Executesql sp .

SELECT   *
FROM CityToCityTransferPricing as ctp
INNER JOIN VehicleDynamicPackage as vdp on ctp.vehicleId=vdp.vehicleId
INNER JOIN VehicleType as vt on vdp.vehicleTypeId=vt.vehicleTypeId
WHERE cityToCityTransferID=@cityToCityTransferID 
    AND (
        @vehicleID is null 
        OR ctp.vehicleId=@vehicleID
    )       
    AND (
        @status is null 
        OR ctp.status=@status
    )

Answer 1

在一般情况下，使用动态查询时，您不会看到显着的性能下降。

也看到这个 -

SELECT * 
FROM dbo.CityToCityTransferPricing ctp 
JOIN dbo.VehicleDynamicPackage vdp ON ctp.vehicleId = vdp.vehicleId 
JOIN dbo.VehicleType vt ON vdp.vehicleTypeId = vt.vehicleTypeId
WHERE cityToCityTransferID = @cityToCityTransferID
    AND ISNULL(@vehicleID, ctp.vehicleId) = ctp.vehicleId
    AND ISNULL(@status, ctp.[status]) = ctp.[status]