数据窗口的条件查询比SQL查询分析器慢

[rain8843]

SQL 查询一张视图
select *
from V_Inventory_search_Test
where posting_date = '2010-4-6'
--------------------------------------需要6秒
DW 事务日期= '2010-4-6' ----需要10秒，这个是数据量比较小的情况

1.数据窗口的条件查询比SQL查询分析器慢，尤其数据量大的时候
2.数据窗口有时查询返回结果是0，要二次查询才能得到结果，再抽的时候数据还不完整

请教怎么优化？

[永生天地]

只能看视图了，想办法优化视图

[dawugui]

对posting_date 建立索引.

[rain8843]

汗，这个视图已经是以前的优化版了，把数据分成2半了，2月份之前的的数据都汇总放在一张表里了。

那查询结果有时是0是怎么回事？ 我看SQL也没有超时的限制啊

[zhgwbzhd]

不太明白的你数据量较小的含义。

数据量是多少条啊。

有人把100W条记录叫做小。

有人把10W条记录叫做多啊。

[rain8843]

select count(*)
from V_Inventory_search_Test

共14808条
用了43秒

PS:我那数据量小是相对的说，返回结果不多

[zhgwbzhd]

1W多条的记录应该是比较少的。

不应该用这么长的时间。

[rain8843]

不对，这1W是合计出来的结果 这是一张库存表 它是计算出了有1W多笔库存 所以慢的吧

[zhgwbzhd]

不管怎样。
1W条记录从数据库内读出来，用几十秒，有点慢啊。

你这个是局域网啊。

如果从广域网上，就令说了。

计算的功能不要留给数据库啊。

最好是本地计算。

[jimwoo]

贴上视图看看

[doodle]

只有优化视图了

[rain8843]

SELECT EE.Item_no, I.Description, EE.Variant_code, EE.Lot_No, EE.Location_code, SUM(EE.qty) AS qty, EE.Unit,
I.[Item Category Code] AS Item_Category_Code, I.[Product Group Code] AS Product_Group_code, L.Message1 AS Quality, L.Message2 AS Mrb,
L.Message3 AS Message, L.[Date Code] AS Date_code, L.[Shipping Control] AS Shipping_Control, L.Remarks, T.Document_No, T.Result,
T.Item_Control, MIN(EE.Posting_Date) AS posting_date, EE.Sub_Inventory
FROM (SELECT Item_no, Variant_code, Lot_No, Location_code, qty, Unit, Posting_Date, Sub_Inventory
FROM (SELECT E.[Item No_] AS Item_no, E.[Variant Code] AS Variant_code, E.[Lot No_] AS Lot_No, E.[Location Code] AS Location_code,
SUM(E.Quantity) AS qty, I.[Base Unit of Measure] AS Unit, MIN(E.[Posting Date]) AS Posting_Date,
E.[Subcontracting Inventory] AS Sub_Inventory
FROM dbo.[Gulf Semi(V080601)$Item Ledger Entry] AS E LEFT OUTER JOIN
dbo.[Gulf Semi(V080601)$item] AS I ON E.[Item No_] = I.No_
WHERE (E.[Location Code] NOT IN
(SELECT Code
FROM dbo.[Gulf Semi(V080601)$Location]
WHERE ([Directed Put-away and Pick] = 1))) AND (E.[Posting Date] >= '2010-02-01')
GROUP BY E.[Item No_], E.[Variant Code], E.[Lot No_], E.[Location Code], I.[Base Unit of Measure], E.[Subcontracting Inventory]
UNION
SELECT [Item No_] AS Item_no, [Variant Code] AS Variant_code, [Lot No_] AS Lot_No, [Bin Code] AS Location_code, SUM(Quantity) AS qty,
[Unit of Measure Code] AS Unit, MIN([Registering Date]) AS Posting_Date, Subcontracting AS Sub_Inventory
FROM dbo.[Gulf Semi(V080601)$Warehouse Entry] AS E
WHERE ([Bin Code] <> '999') AND ([Registering Date] >= '2010-02-01')
GROUP BY [Item No_], [Variant Code], [Lot No_], [Bin Code], [Unit of Measure Code], Subcontracting) AS kucun
UNION ALL
SELECT Item_no, Variant_code, Lot_No, Location_code, qty, Unit, Posting_Date, Sub_Inventory
FROM dbo.Summary100201) AS EE LEFT OUTER JOIN
dbo.[Gulf Semi(V080601)$Lot No_ Information] AS L LEFT OUTER JOIN
dbo.V_Lot_information_Entry AS T ON L.[Lot No_] = T.Lot_no ON L.[Item No_] = EE.Item_no AND L.[Variant Code] = EE.Variant_code AND
L.[Lot No_] = EE.Lot_No INNER JOIN
dbo.[Gulf Semi(V080601)$item] AS I WITH (nolock) ON EE.Item_no = I.No_
GROUP BY EE.Item_no, I.Description, EE.Location_code, EE.Variant_code, EE.Unit, I.[Item Category Code], I.[Product Group Code], EE.Lot_No, L.Message1,
L.Message2, L.Message3, L.[Date Code], L.[Shipping Control], T.Document_No, T.Result, T.Item_Control, EE.Sub_Inventory, L.Remarks
HAVING (SUM(EE.qty) > 0)



这里面一部分是来自select count(*)
from [Gulf Semi(V080601)$Warehouse Entry]
where [Registering Date] >= '2010-02-01'----------308201条


select count(*)
from [Gulf Semi(V080601)$Item Ledger Entry]
where [posting Date] >= '2010-02-01'--------------236846条

2010-02-01之前的已经sum出放在另一张Summary100201的表中，
其他的表都是带出的一些基本信息。