sql-server - 如果某些参数未传递给过程,则参数化存储过程会绕过左连接
问题描述
我已经向我的存储过程添加了一个左连接,但是即使我没有传递连接所需的参数,我的查询现在也需要 4 到 10 倍的时间才能运行。
这是我的存储过程:
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[OrderLoadAllPaged]
@OrderId int = 0,
@WarehouseId int = 0,
@PaymentMethodSystemName nvarchar(max) = null,
@OrderStatusId int = 0,
@PaymentStatusId int = 0,
@ShippingStatusId int = 0,
@BillingEmail nvarchar(max) = null,
@BillingFirstName nvarchar(max) = null,
@BillingLastName nvarchar(max) = null,
@ShippingMethod nvarchar(max) = null,
@CreatedFromUtc datetime = null,
@CreatedToUtc datetime = null
AS
BEGIN
DECLARE
@sql nvarchar(max)
SET NOCOUNT ON;
SELECT TOP 100 *
FROM [Test].[dbo].[Order] o with (NOLOCK)
LEFT join [Test].[dbo].[Address] a on (a.Id LIKE o.BillingAddressId)
WHERE (@BillingEmail IS null OR a.[Email] = @BillingEmail)
AND (@BillingFirstName IS null OR a.[FirstName] = @BillingFirstName)
AND (@BillingLastName IS null OR a.[LastName] = @BillingLastName)
AND
o.[Deleted] = 0
AND (o.[Id] = @OrderId OR @OrderId = 0)
AND (o.[WarehouseId] = @WarehouseId OR @WarehouseId = 0)
AND (@PaymentMethodSystemName IS null OR o.[PaymentMethodSystemName] = @PaymentMethodSystemName)
AND (o.[OrderStatusId] = @OrderStatusId OR @OrderStatusId = 0)
AND (o.[PaymentStatusId] = @PaymentStatusId OR @PaymentStatusId = 0)
AND (o.[ShippingStatusId] = @ShippingStatusId OR @ShippingStatusId = 0)
AND (@ShippingMethod IS null OR o.[ShippingMethod] = @ShippingMethod)
AND o.[CreatedOnUtc] >= ISNULL(@CreatedFromUtc, '1/1/1900')
AND o.[CreatedOnUtc] < ISNULL(@CreatedToUtc, '1/1/2999')
ORDER BY o.[CreatedOnUtc] DESC
END
因此,如果我不传入@BillingEmail
,@BillingFirstName
或者@BillingLastName
就像仍然考虑左连接一样:
如果我注释掉左连接,我的查询真的很快:
/*LEFT join [Test].[dbo].[Address] a on (a.Id LIKE o.BillingAddressId)
WHERE (@BillingEmail IS null OR a.[Email] = @BillingEmail)
AND (@BillingFirstName IS null OR a.[FirstName] = @BillingFirstName)
AND (@BillingLastName IS null OR a.[LastName] = @BillingLastName)*/
WHERE --need to change
o.[Deleted] = 0
如果或全部为空@BillingEmail
,我如何才能一起绕过该查询。@BillingFirstName
@BillingLastName
我想可能是这样的,但这是不对的:
case
when (ISNULL(@BillingEmail) OR ISNULL(@BillingFirstName) OR ISNULL(@BillingLastName))
then
WHERE
o.[Deleted] = 0
ELSE
LEFT join [Test].[dbo].[Address] a on (a.Id LIKE o.BillingAddressId)
WHERE (@BillingEmail IS null OR a.[Email] = @BillingEmail) --LIKE '%' + @BillingEmail + '%'
AND (@BillingFirstName IS null OR a.[FirstName] = @BillingFirstName)
AND (@BillingLastName IS null OR a.[LastName] = @BillingLastName)
AND
o.[Deleted] = 0
end
我查看了索引,但我认为我不能为这 3 个参数创建索引。有什么想法可以绕过左连接吗?
解决方案
让我们尝试将您的left join
表格移动到经过过滤的subquery
.
left join
(select * from [Test].[dbo].[Address] a
where (@BillingEmail IS null OR a.[Email] = @BillingEmail)
AND (@BillingFirstName IS null OR a.[FirstName] = @BillingFirstName)
AND (@BillingLastName IS null OR a.[LastName] = @BillingLastName)) t1
ON t1.Id = o.BillingAddressId
要根据您的条件绕过左连接,您可以使用以下代码。
if (ISNULL(@BillingEmail, '') = '' and ISNULL(@BillingFirstName, '') = '' and ISNULL(@BillingLastName, '') = '')
begin
SELECT TOP 100 *
FROM [Test].[dbo].[Order] o with (NOLOCK)
WHERE o.[Deleted] = 0
AND (o.[Id] = @OrderId OR @OrderId = 0)
AND (o.[WarehouseId] = @WarehouseId OR @WarehouseId = 0)
AND (@PaymentMethodSystemName IS null OR o.[PaymentMethodSystemName] = @PaymentMethodSystemName)
AND (o.[OrderStatusId] = @OrderStatusId OR @OrderStatusId = 0)
AND (o.[PaymentStatusId] = @PaymentStatusId OR @PaymentStatusId = 0)
AND (o.[ShippingStatusId] = @ShippingStatusId OR @ShippingStatusId = 0)
AND (@ShippingMethod IS null OR o.[ShippingMethod] = @ShippingMethod)
AND o.[CreatedOnUtc] >= ISNULL(@CreatedFromUtc, '1/1/1900')
AND o.[CreatedOnUtc] < ISNULL(@CreatedToUtc, '1/1/2999')
ORDER BY o.[CreatedOnUtc] DESC
end
else
begin
SELECT TOP 100 *
FROM [Test].[dbo].[Order] o with (NOLOCK)
left join
(select * from [Test].[dbo].[Address] a
where (@BillingEmail IS null OR a.[Email] = @BillingEmail)
AND (@BillingFirstName IS null OR a.[FirstName] = @BillingFirstName)
AND (@BillingLastName IS null OR a.[LastName] = @BillingLastName)) t1
ON t1.Id = o.BillingAddressId
WHERE o.[Deleted] = 0
AND (o.[Id] = @OrderId OR @OrderId = 0)
AND (o.[WarehouseId] = @WarehouseId OR @WarehouseId = 0)
AND (@PaymentMethodSystemName IS null OR o.[PaymentMethodSystemName] = @PaymentMethodSystemName)
AND (o.[OrderStatusId] = @OrderStatusId OR @OrderStatusId = 0)
AND (o.[PaymentStatusId] = @PaymentStatusId OR @PaymentStatusId = 0)
AND (o.[ShippingStatusId] = @ShippingStatusId OR @ShippingStatusId = 0)
AND (@ShippingMethod IS null OR o.[ShippingMethod] = @ShippingMethod)
AND o.[CreatedOnUtc] >= ISNULL(@CreatedFromUtc, '1/1/1900')
AND o.[CreatedOnUtc] < ISNULL(@CreatedToUtc, '1/1/2999')
ORDER BY o.[CreatedOnUtc] DESC
end
推荐阅读
- c# - 使用数组调用验证方法,并带有明确的失败消息
- spring - NullPointerException 单元测试控制器弹簧
- javascript - React:在不同组件中提取相同逻辑的好模式是什么?
- python - 静态文件图像未找到 Django
- sql-server - EXECUTE 之后的 SQL 事务计数指示 BEGIN 和 COMMIT 语句的数量不匹配
- c# - WPF - 窗口数据上下文中的命令和用户控件中列表视图中的命令参数
- opengl - glDrawArrays - 获取 GL_INVALID_OPERATION 和 GL_INVALID_VALUE
- reactjs - Mac M1 的电子问题
- javascript - 匹配数组值并创建最终数组 - Javascript
- c++ - 使用 C++ 中的 BOOST 库解析嵌套 XML 文件的属性