很奇怪的问题 困扰我很久了
zxkid 2009-03-24 10:09:03 一条SQL语句在SQL Server 2005的Managment Studio中执行很快(0-1秒)的时间,在.net应用程序中却要5-6分钟才出来
返回的结果也就4000条行,SQL Server Profiler跟踪的结果是执行下面的SQL花了很长时间
请各位高手帮我看看,SQL Server的版本2005企业版SP2,硬件是双4核CPU,16G内存
EXEC sp_executesql
N'SELECT i.DataAreaId, i.ItemId, q.ConfigId, c.ItemStatus
, q.InventQty, imi.UnitId, imp.DeliveryTime
, ItemType = e1.ElementNameENG, i.ItemGroupId
, i.ItemCategory, i.ItemName
, ItemBuyer = eb.Name, ItemSouring = es.Name
, i.PrimaryVendorId, VendorName = v.Name
FROM AX4.dbo.InventTable i
INNER JOIN AX4.dbo.DataAreaCompany y
ON y.DataArea = i.DataAreaId
INNER JOIN AX4.dbo.InventTableModule imi
ON imi.ItemId = i.ItemId
AND imi.ModuleType = 0
AND imi.DataAreaId = i.DataAreaId
INNER JOIN AX4.dbo.InventTableModule imp
ON imp.ItemId = i.ItemId
AND imp.ModuleType = 1
AND imp.DataAreaId = i.DataAreaId
INNER JOIN dbo.AxEnum e1
ON e1.EnumName = N''ItemType''
AND e1.ElementValue = i.ItemType
LEFT JOIN
(
SELECT s.DataAreaId, s.ItemId, d.ConfigId
, InventQty = CAST(CAST(SUM(CASE WHEN d.InventLocationId = N''MRB'' THEN 0 ELSE s.PhysicalInvent END) AS NUMERIC(20, 2)) AS FLOAT)
FROM AX4.dbo.InventSum s
INNER JOIN AX4.dbo.InventDim d
ON d.InventDimId = s.InventDimId
AND d.DataAreaId = s.DataAreaId
WHERE s.Closed = 0
GROUP BY s.DataAreaId, s.ItemId, d.ConfigId
HAVING SUM(s.PhysicalInvent) <> 0
) q
ON i.ItemId = q.ItemId
AND i.DataAreaId = q.DataAreaId
LEFT JOIN AX4.dbo.ConfigTable c
ON c.ItemId = q.ItemId
AND c.ConfigId = q.ConfigId
AND c.DataAreaId = q.DataAreaId
LEFT JOIN AX4.dbo.VendTable v
ON v.AccountNum = i.PrimaryVendorId
AND v.DataAreaId = i.DataAreaId
LEFT JOIN AX4.dbo.EmplTable eb
ON eb.EmplId = i.ItemBuyerGroupId
AND eb.DataAreaId = i.DataAreaId
LEFT JOIN AX4.dbo.EmplTable es
ON es.EmplId = i.Souring
AND es.DataAreaId = i.DataAreaId
WHERE i.ItemType <> 3
AND i.DataAreaId = @Company
AND i.ItemId = ISNULL(@ItemId, i.ItemId)
AND CASE WHEN @ItemId IS NULL AND q.InventQty IS NULL THEN 0 ELSE 1 END = 1
ORDER BY i.DataAreaId, i.ItemId, q.ConfigId', N'@Company nvarchar(3), @ItemId nvarchar(60)'
, @Company = N'itc', @ItemId = null