楼主:
会不会是你的P_PORDUCT 等报表记录很多(虽然筛选的结果不多)?
试试这样做:
把这段语句……FROM p_Product
left JOIN p_product2type b ON p_Product.productguid = b.productguid……
改成:……FROM p_Product order by productguid) a left join (select FROM P_PORDUCT2TYPE ORDRE BY PRODUCTCODE) B……
select *
from
(SELECT p_Product.ProductGUID, ProductName, ProductCode, ProductSpec, Unit, Price, Remarks
FROM p_Product
left JOIN p_product2type b ON p_Product.productguid = b.productguid
LEFT JOIN vp_product2unit c ON p_Product.productguid =c.productguid
left JOIN mybusinessunit d ON c.bucode = d.hierarchycode
WHERE (1=1) AND ( CharIndex('01.01.01' + '.',b.ProductTypeCode + '.')=1 )
AND d.BUGUID=('11b11db4-e907-4f1f-8835-b9daab6e1f23')
) p_Product
结果集有18条记录。执行时间:76 ms
select *
from
(SELECT p_Product.ProductGUID, ProductName, ProductCode, ProductSpec, Unit, Price, Remarks
FROM p_Product
left JOIN p_product2type b ON p_Product.productguid = b.productguid
LEFT JOIN vp_product2unit c ON p_Product.productguid =c.productguid
left JOIN mybusinessunit d ON c.bucode = d.hierarchycode
WHERE (1=1) AND ( CharIndex('01.01.01' + '.',b.ProductTypeCode + '.')=1 )
AND d.BUGUID=('11b11db4-e907-4f1f-8835-b9daab6e1f23')
) p_Product
ORDER BY p_Product.ProductCode ASC,p_Product.ProductGUID
结果集:还是18条记录。 执行时间:9733 ms
想不通为什么会差别那么大,仅仅排18条记录的序,就要费那么长时间吗?
---补充说明:mybusinessunit 表有24条记录,其他表都是3000条记录。
主要原因还是它把sql语句改了,从查询计划上看到,它把语句改成下面的样式,但是就是下面的语句执行起来也只有5秒左右,也不会那么慢呀。
SELECT p_Product.ProductGUID, ProductName, ProductCode, ProductSpec, Unit, Price, Remarks
from
(select top 100000000 p_Product.ProductGUID, ProductName, ProductCode, ProductSpec, Unit, Price, Remarks,d.bucode
from p_Product
LEFT JOIN vp_product2unit c ON p_Product.productguid =c.productguid
left JOIN mybusinessunit d ON c.bucode = d.hierarchycode
where d.BUGUID=('11b11db4-e907-4f1f-8835-b9daab6e1f23')
order by p_Product.ProductCode ASC,p_Product.ProductGUID
)
p_Product
left JOIN mybusinessunit d ON p_Product.bucode = d.hierarchycode
left JOIN p_product2type b ON p_Product.productguid = b.productguid
where ( CharIndex('01.01.01' + '.',b.ProductTypeCode + '.')=1 )
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
我同事用的企业版sp1,也有同样的问题。