好久没在csdn上发帖子了,公司运营一个网上订货的系统,最开始的时候是是有一个库存表,每次业务更新库存,这样的好处是查询实时库存速度很快,但是到盘点的时候总有误差,特别是业务修改涉及到的操作更多,后来改成只保留盘点期初值,然后通过一个业务视图把和库存想关的业务汇总起来,通过盘点期初数和业务视图来进行计算,这样的好处是库存准确,有业务修改,库存也会自动更正过来,因为库存是实时计算出来的,但是这样性能比较差,数据量稍微一大就查询很慢,用的sql2008,大概也就100万数据量,后来分析发现是因为视图不能利用基表的索引造成性能低下,想请教一下有没合理的折中方案。
FROM inventory_m m, inventory_s s
WHERE order_id = order_id_fk AND states_id_fk = '3'
UNION ALL
/*调出*/ SELECT '调库-', head_date, order_id, from_id_fk, pro_no_fk, pro_num_fk, (- 1) * num
FROM storage_m m, storage_s s
WHERE order_id = order_id_fk AND states_id_fk = '21'
UNION ALL
/*调入*/ SELECT '调库+', head_date, order_id, to_id_fk, pro_no_fk, pro_num_fk, num
FROM storage_m m, storage_s s
WHERE order_id = order_id_fk AND states_id_fk = '21'
UNION ALL
/*订单-*/ SELECT '订单-', fill_date, order_id, fill_id_fk, pro_no_fk, pro_num_fk, (- 1) * fill_num
FROM order_m m, order_s s
WHERE order_id = order_id_fk AND states_id_fk = '31'
UNION ALL
/*订单+*/ SELECT '订单+', fill_date, order_id, sell_id_fk, pro_no_fk, pro_num_fk, fill_num
FROM order_m m, order_s s
WHERE order_id = order_id_fk AND states_id_fk = '31'
UNION ALL
/*退货-*/ SELECT '退货-', fill_date, order_id, sell_id_fk, pro_no_fk, pro_num_fk, (- 1) * s.fill_num
FROM return_m m, return_s s
WHERE order_id = order_id_fk AND states_id_fk = '31'
UNION ALL
/*退货+*/ SELECT '退货+', fill_date, order_id, fill_id_fk, pro_no_fk, pro_num_fk, s.fill_num
FROM return_m m, return_s s
WHERE order_id = order_id_fk AND states_id_fk = '31'
UNION ALL
/*销售-*/ SELECT '销售-', order_date, order_id, sell_id_fk, pro_no_fk, pro_num_fk, (- 1) * num
FROM sell_order_m m, sell_order_s s
WHERE order_id = order_id_fk
UNION ALL
/*冻结-*/ SELECT '冻结-', sell_date, order_id, fill_id_fk, pro_no_fk, pro_num_fk, sell_num * (- 1)
FROM order_m m, order_s s
WHERE order_id = order_id_fk AND states_id_fk IN ('0', '11', '21')
UNION ALL
/*虚拟+*/ SELECT '虚拟+', getdate(), '-', sell_id_fk, pro_no_fk, pro_num_fk, num
FROM stock_ex