sql查询优化问题 去掉了一个嵌套in查询居然更慢了，为什吗？

[liuhong_0325]

sql查询优化问题 望大侠们帮忙解答下~

SELECT * FROM(
            SELECT ROW_NUMBER() OVER(ORDER BY ProductCode) RN,[dbo].get_DeptCodes('A',Product.productcode) as id,[dbo].get_DeptNames('A',Product.productcode) as deptName,
            [dbo].get_DeptNames('B',Product.productcode) as deptNameB, ProductCode,ProductName,Packing,Manufacturer,OurPrice,PurchasePrice
            ,MerchantManageCode,FinancialType FROM
			 Product(NOLOCK)  WHERE ProductCode IN(
            SELECT distinct ProductCode FROM OrderProducts(NOLOCK) WHERE OrdersCode IN(
            SELECT distinct OrdersCode FROM Orders(NOLOCK) WHERE OrderTime BETWEEN CONVERT(DATETIME, CONVERT(varchar(7), dateadd(mm,-1,getdate()) , 120) + '-1') AND GETDATE()
            )) 
            AND 1=1
            ) TB WHERE TB.RN BETWEEN 1 AND 20

这一段在库中执行时间一般是2秒，我去掉一个in的查询后居然会要16秒这么久。这个是为什么？？

SELECT * FROM(
            SELECT ROW_NUMBER() OVER(ORDER BY ProductCode) RN,[dbo].get_DeptCodes('A',Product.productcode) as id,[dbo].get_DeptNames('A',Product.productcode) as deptName,
            [dbo].get_DeptNames('B',Product.productcode) as deptNameB, ProductCode,ProductName,Packing,Manufacturer,OurPrice,PurchasePrice
            ,MerchantManageCode,FinancialType FROM
			 Product(NOLOCK)  WHERE ProductCode IN(
            SELECT distinct ProductCode FROM OrderProducts(NOLOCK) WHERE CreationDate BETWEEN CONVERT(DATETIME, CONVERT(varchar(7), dateadd(mm,-1,getdate()) , 120) + '-1') AND GETDATE()
            ) 
            AND 1=1
            ) TB WHERE TB.RN BETWEEN 1 AND 20

orders表是没有索引的。 OrderProducts和Product表都有索引。去掉了没有索引的表的in居然会更慢。。实在是搞不懂为什吗！

[Tiger_Zhao]

看起来 OrderProducts 是 Orders 的明细，OrderProducts.OrdersCode 应该有索引。
所以 OrdersCode IN 可以走索引；而 CreationDate BETWEEN 要全文检索了，速度当前不行。
第二种写法需要在 OrderProducts(CreationDate) 上建索引。

[Tiger_Zhao]

更正：速度当然不行。

[liuhong_0325]

引用 2 楼 lovelj2012 的回复:

SELECT * FROM(
            SELECT ROW_NUMBER() OVER(ORDER BY ProductCode) RN,[dbo].get_DeptCodes('A',Product.productcode) as id,[dbo].get_DeptNames('A',Product.productcode) as deptName,
            [dbo].get_DeptNames('B',Product.productcode) as deptNameB, ProductCode,ProductName,Packing,Manufacturer,OurPrice,PurchasePrice
            ,MerchantManageCode,FinancialType 
FROM Product(NOLOCK) x,(SELECT distinct ProductCode FROM OrderProducts(NOLOCK) 
WHERE CreationDate BETWEEN CONVERT(DATETIME, CONVERT(varchar(7), dateadd(mm,-1,getdate()) , 120) + '-1') AND GETDATE()) y 
WHERE  x.ProductCode = y.ProductCode
            ) TB WHERE TB.RN BETWEEN 1 AND 20

试试这个，看效率怎么样↑

这个不对，Product和OrderProducts是一对多的关系这样查慢不说，数据不对!

[liuhong_0325]

引用 3 楼 Tiger_Zhao 的回复:

看起来 OrderProducts 是 Orders 的明细，OrderProducts.OrdersCode 应该有索引。 所以 OrdersCode IN 可以走索引；而 CreationDate BETWEEN 要全文检索了，速度当前不行。 第二种写法需要在 OrderProducts(CreationDate) 上建索引。

可是我的CreationDate 也是有索引的啊

[江南小鱼]

引用 5 楼 liuhong_0325 的回复:

[quote=引用 2 楼 lovelj2012 的回复:]

SELECT * FROM(
            SELECT ROW_NUMBER() OVER(ORDER BY ProductCode) RN,[dbo].get_DeptCodes('A',Product.productcode) as id,[dbo].get_DeptNames('A',Product.productcode) as deptName,
            [dbo].get_DeptNames('B',Product.productcode) as deptNameB, ProductCode,ProductName,Packing,Manufacturer,OurPrice,PurchasePrice
            ,MerchantManageCode,FinancialType 
FROM Product(NOLOCK) x,(SELECT distinct ProductCode FROM OrderProducts(NOLOCK) 
WHERE CreationDate BETWEEN CONVERT(DATETIME, CONVERT(varchar(7), dateadd(mm,-1,getdate()) , 120) + '-1') AND GETDATE()) y 
WHERE  x.ProductCode = y.ProductCode
            ) TB WHERE TB.RN BETWEEN 1 AND 20

试试这个，看效率怎么样↑

这个不对，Product和OrderProducts是一对多的关系这样查慢不说，数据不对![/quote] 内连接和where里面in条件，是等效的。

[liuhong_0325]

引用 2 楼 lovelj2012 的回复:

SELECT * FROM(
            SELECT ROW_NUMBER() OVER(ORDER BY ProductCode) RN,[dbo].get_DeptCodes('A',Product.productcode) as id,[dbo].get_DeptNames('A',Product.productcode) as deptName,
            [dbo].get_DeptNames('B',Product.productcode) as deptNameB, ProductCode,ProductName,Packing,Manufacturer,OurPrice,PurchasePrice
            ,MerchantManageCode,FinancialType 
FROM Product(NOLOCK) x,(SELECT distinct ProductCode FROM OrderProducts(NOLOCK) 
WHERE CreationDate BETWEEN CONVERT(DATETIME, CONVERT(varchar(7), dateadd(mm,-1,getdate()) , 120) + '-1') AND GETDATE()) y 
WHERE  x.ProductCode = y.ProductCode
            ) TB WHERE TB.RN BETWEEN 1 AND 20

试试这个，看效率怎么样↑

我刚才用这个查特别慢。。。

[Tiger_Zhao]

你不贴执行计划只能靠猜啊。下面两个语句什么结果?

SELECT COUNT(*) FROM OrderProducts(NOLOCK) WHERE OrdersCode IN(
            SELECT distinct OrdersCode FROM Orders(NOLOCK) WHERE OrderTime BETWEEN CONVERT(DATETIME, CONVERT(varchar(7), dateadd(mm,-1,getdate()) , 120) + '-1') AND GETDATE()
            )

SELECT COUNT(*) FROM OrderProducts(NOLOCK) WHERE CreationDate BETWEEN CONVERT(DATETIME, CONVERT(varchar(7), dateadd(mm,-1,getdate()) , 120) + '-1') AND GETDATE()

[liuhong_0325]

引用 9 楼 Tiger_Zhao 的回复:

你不贴执行计划只能靠猜啊。下面两个语句什么结果?

SELECT COUNT(*) FROM OrderProducts(NOLOCK) WHERE OrdersCode IN(
            SELECT distinct OrdersCode FROM Orders(NOLOCK) WHERE OrderTime BETWEEN CONVERT(DATETIME, CONVERT(varchar(7), dateadd(mm,-1,getdate()) , 120) + '-1') AND GETDATE()
            )

SELECT COUNT(*) FROM OrderProducts(NOLOCK) WHERE CreationDate BETWEEN CONVERT(DATETIME, CONVERT(varchar(7), dateadd(mm,-1,getdate()) , 120) + '-1') AND GETDATE()

第一句 798023 2秒， 第二句798479 0秒

[Tiger_Zhao]

记录数差不多。
那么你的 OrderProducts 是不是有 (OrderCode,ProductCode) 索引？
如果是：
你原始的第一句虽然通过 Orders 过滤多花了一两秒，但是直接可以从索引用 OrderCode 取到 ProductCode。
原始的第二句虽然可以直接用 CreationDate 索引进行过滤，但是取 ProductCode 必须读取这80万条数据不能利用索引了。

[liuhong_0325]

引用 11 楼 Tiger_Zhao 的回复:

记录数差不多。 那么你的 OrderProducts 是不是有 (OrderCode,ProductCode) 索引？ 如果是： 你原始的第一句虽然通过 Orders 过滤多花了一两秒，但是直接可以从索引用 OrderCode 取到 ProductCode。 原始的第二句虽然可以直接用 CreationDate 索引进行过滤，但是取 ProductCode 必须读取这80万条数据不能利用索引了。

(OrderCode,ProductCode)都是有索引的。 好像懂了，不明白CreationDate 明明有索引 用它筛选数据就会慢那么多。

[Tiger_Zhao]

你一次只能利用一个索引。
在同一个索引中，查找 OrderCode 返回 ProductCode 是纯内存操作。
CreationDate 过滤是很快的，但是要向上返回 ProductCode 就只能读记录了。你可以考虑在 CreationDate 索引中包含 ProductCode。

[liuhong_0325]

引用 13 楼 Tiger_Zhao 的回复:

你一次只能利用一个索引。 在同一个索引中，查找 OrderCode 返回 ProductCode 是纯内存操作。 CreationDate 过滤是很快的，但是要向上返回 ProductCode 就只能读记录了。你可以考虑在 CreationDate 索引中包含 ProductCode。

谢谢，谢谢。根据您的指点总算解决了。我创建了一个复合索引解决了。

[liuhong_0325]

哪位大哥大姐帮帮忙啊~救命啊~

[江南小鱼]

SELECT * FROM(
            SELECT ROW_NUMBER() OVER(ORDER BY ProductCode) RN,[dbo].get_DeptCodes('A',Product.productcode) as id,[dbo].get_DeptNames('A',Product.productcode) as deptName,
            [dbo].get_DeptNames('B',Product.productcode) as deptNameB, ProductCode,ProductName,Packing,Manufacturer,OurPrice,PurchasePrice
            ,MerchantManageCode,FinancialType 
FROM Product(NOLOCK) x,(SELECT distinct ProductCode FROM OrderProducts(NOLOCK) 
WHERE CreationDate BETWEEN CONVERT(DATETIME, CONVERT(varchar(7), dateadd(mm,-1,getdate()) , 120) + '-1') AND GETDATE()) y 
WHERE  x.ProductCode = y.ProductCode
            ) TB WHERE TB.RN BETWEEN 1 AND 20

试试这个，看效率怎么样↑